nerd_create_csv_files_from_twitter_json.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1>Simplify the Dataset By Converting JSON to CSV and Extracting Subset of Parameters</h1>\n",
    "<p>In this example, we reduce the dataset from ~70 Mb down to ~5 Mb, and we will save two files, one containing basic tweet parameters and the other containing tweet text. This is probably not necessary for such a small dataset but it becomes useful when working with much larger datasets consisting of millions of tweets.</p>\n",
    "<p>The parameters we choose to retain in the CSV include the following, which is by no means comprehensive:\n",
    "<ul>\n",
    "<li>tweet_id</li>\n",
    "<li>tweet_created_at</li>\n",
    "<li>language</li>\n",
    "<li>user_screen_name</li>\n",
    "<li>user_created_at</li>\n",
    "<li>user_id</li>\n",
    "<li>followers_count</li>\n",
    "<li>friends_count</li>\n",
    "<li>time_zone</li>\n",
    "<li>utc_offset</li>\n",
    "<li>retweeted_status</li>\n",
    "<li>retweet_id</li>\n",
    "<li>retweet_user_screen_name</li>\n",
    "<li>retweet_user_id</li>\n",
    "</ul>\n",
    "<p>The dataset we will be working with was created using the Twitter Search API and searching on the hashtag 'nerd'. Tweets were collected every 15 minutes and saved to a file. After two weeks the files were processed to remove duplicate tweets and combined into a single file. Duplicate tweets are an artifact of requesting the maximum number of tweets for each 15 minute epoch. Twitter limits the Search API to 100 tweets per 15 minute epoch. They post 150 in their documentation but we have observed it to be 100. </p> \n",
    "<h2>Import Packages</h2>\n",
    "<p>As always, first we import the required Python packages.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load packages\n",
    "import os\n",
    "import csv\n",
    "import sys\n",
    "import json\n",
    "import datetime\n",
    "from pprint import pprint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2>Open JSON, Parse Data, Save as CSV</h2>\n",
    "<p>The comments in the code below describe the purpose of each section of code.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Start:2018-12-08 06:56:35.276824\n",
      "Working on file:nerd.json\n",
      "10000\n",
      "End:2018-12-08 06:56:37.554748\n"
     ]
    }
   ],
   "source": [
    "# Print start time at start and end time at end\n",
    "print(\"Start:\" + str(datetime.datetime.now()))\n",
    "\n",
    "# Open CSV output files for writing\n",
    "output_dir = \"csv/\"\n",
    "hashtag = \"nerd\"\n",
    "\n",
    "# Open main twitter data CSV file and write header row\n",
    "main_output_file = output_dir + hashtag + \"_main.csv\"\n",
    "f_main = open(main_output_file, 'w', newline='')\n",
    "mainrowwriter = csv.writer(f_main, delimiter=',')\n",
    "main_outputstring = ['tweet_id','tweet_created_at','language','user_screen_name','user_created_at','user_id','followers_count','friends_count','time_zone','utc_offset','retweeted_status','retweet_id','retweet_user_screen_name','retweet_user_id']\n",
    "mainrowwriter.writerow(main_outputstring)\n",
    "\n",
    "# Open twitter text data CSV file and write header row\n",
    "text_output_file = output_dir + hashtag + \"_text.csv\"\n",
    "f_text = open(text_output_file, 'w', errors='ignore', newline='')\n",
    "textrowwriter = csv.writer(f_text, delimiter=',')\n",
    "text_outputstring = ['tweet_id','text']\n",
    "textrowwriter.writerow(text_outputstring)\n",
    "\n",
    "# Define variables\n",
    "inc = 0\n",
    "val = 0\n",
    "val_inc = 0\n",
    "dir = 'tweet_data/'\n",
    "filename = 'nerd.json'\n",
    "\n",
    "with open(dir + filename, 'r') as f:\n",
    "    print(\"Working on file:\" + filename)\n",
    "    data = json.load(f)\n",
    "    for tweet in data:\n",
    "        if 'user' in tweet:\n",
    "            \n",
    "            # Set standard variables equal to tweet data\n",
    "            tweet_id = tweet['id']\n",
    "            tweet_created_at = tweet['created_at']\n",
    "            text = tweet['text']\n",
    "            language = tweet['lang']\n",
    "            user_screen_name = tweet['user']['screen_name']\n",
    "            user_created_at = tweet['user']['created_at']\n",
    "            user_id = tweet['user']['id']\n",
    "            followers_count = tweet['user']['followers_count']\n",
    "            friends_count = tweet['user']['friends_count']\n",
    "            utc_offset = tweet['user']['utc_offset']\n",
    "            time_zone = tweet['user']['time_zone']\n",
    "            \n",
    "            # Check if a retweet else original tweet\n",
    "            if 'retweeted_status' in tweet:\n",
    "                retweeted_status = 1\n",
    "                retweet_id = tweet['retweeted_status']['id']\n",
    "                retweet_user_screen_name = tweet['retweeted_status']['user']['screen_name']\n",
    "                retweet_user_id = tweet['retweeted_status']['user']['id']\n",
    "            else:\n",
    "                retweeted_status = 0\n",
    "                retweet_id = \"None\"\n",
    "                retweet_user_screen_name = \"None\"\n",
    "                retweet_user_id = \"None\"\n",
    "            \n",
    "            # Write to main output file\n",
    "            main_outputstring = [str(tweet_id), tweet_created_at, language, user_screen_name, user_created_at, str(user_id), str(followers_count), str(friends_count), time_zone, utc_offset, str(retweeted_status), str(retweet_id), retweet_user_screen_name, str(retweet_user_id)] \n",
    "            mainrowwriter.writerow(main_outputstring)\n",
    "            \n",
    "            # Write to text output file\n",
    "            text_outputstring = [str(tweet_id), text]\n",
    "            textrowwriter.writerow(text_outputstring)\n",
    "            \n",
    "            # Increment variables to track progress, mostly for very large files\n",
    "            inc += 1\n",
    "            val_inc += 1\n",
    "            if val_inc > 10000:\n",
    "                val = val + 10000\n",
    "                print(str(val))\n",
    "                val_inc = 0\n",
    "\n",
    "# Close all files\n",
    "f.close()\n",
    "f_main.close()\n",
    "f_text.close()\n",
    "print(\"End:\" + str(datetime.datetime.now()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of tweets:15368\n"
     ]
    }
   ],
   "source": [
    "print(\"Total number of tweets:\" + str(inc-1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}