{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Getting Started Working with Twitter Data Using jq"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"[jq](https://stedolan.github.io/jq/) is a command line JSON processor that's helpful for working with JSON data from Twitter. You'll want to [download and install jq](https://stedolan.github.io/jq/download/) on your system to use this notebook with your data. You could also use [jqplay](https://jqplay.org/) to try out these jq statements. \n",
"\n",
"This notebook works with tweets collected from the Twitter filterstream API using an earlier version of [Social Feed Manager](http://go.gwu.edu/sfm), but there are lots of tools to get data from the Twitter APIs. To use this notebook with your own data, set the path to your data file as DATA.\n",
"\n",
"As background, Twitter streaming API data is line-oriented JSON, meaning one tweet in JSON format per line. Output from tools such as [twarc](https://github.com/edsu/twarc) is also often line-oriented JSON. \n",
"\n",
"This notebook is intended to help people getting started with working with Twitter data using jq. There are many additional software libraries available to do further analysis, including within a notebook. As an example, see [Cody Buntain's notebook](http://nbviewer.jupyter.org/github/cbuntain/TwitterFergusonTeachIn/blob/master/session_05.ipynb) analyzing #Ferguson tweets as part of the Researching Ferguson Teach-In at MITH in 2015.\n",
"\n",
"We use jq a lot in working with students and faculty at [GW Libraries](https://library.gwu.edu). Do you have useful jq statements we could share here? We welcome suggestions and improvements to this notebook, via [Github](https://github.com/gwu-libraries/notebooks/tree/master/20160407-twitter-analysis-with-jq), Twitter ([@liblaura](https://twitter.com/liblaura), [@dankerchner](https://twitter.com/dankerchner), [@justin_littman](https://twitter.com/justin_littman) or email (lwrubel at gwu dot edu)."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"DATA=\"data/tweets\""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Basic filtering"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"View the JSON data, both keys and values, in a prettified format. I'm using the `head` command to just show the first tweet in the file. Alternatively, you can use `cat` to look at the whole file. "
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"created_at\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Wed Apr 06 22:32:54 +0000 2016\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"id\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m717842544655208400\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"id_str\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"717842544655208449\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"text\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"#WMATA GM says he has lots of partners, critics. \\\"I've got lots of critics and that's good. We should have critics.\\\" Opens up to questions\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"source\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Twitter Web Client\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"truncated\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"in_reply_to_status_id\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m717842320452948000\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"in_reply_to_status_id_str\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"717842320452947968\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"in_reply_to_user_id\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m62842049\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"in_reply_to_user_id_str\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"62842049\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"in_reply_to_screen_name\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"faizsays\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"user\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"id\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m62842049\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"id_str\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"62842049\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"name\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Faiz Siddiqui\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"screen_name\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"faizsays\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"location\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Washington, DC\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://www.washingtonpost.com/people/faiz-siddiqui\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"description\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Transportation reporter @washingtonpost. Formerly @BostonGlobe. Books, biryani, good raps. faiz.siddiqui@washpost.com.\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"protected\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"verified\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mtrue\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"followers_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m1188\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"friends_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m897\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"listed_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m51\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"favourites_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m797\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"statuses_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m6132\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"created_at\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Tue Aug 04 15:13:48 +0000 2009\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"utc_offset\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m-14400\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"time_zone\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Eastern Time (US & Canada)\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"geo_enabled\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mtrue\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"lang\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"en\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"contributors_enabled\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"is_translator\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_background_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"C0DEED\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_background_image_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"http://abs.twimg.com/images/themes/theme1/bg.png\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_background_image_url_https\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://abs.twimg.com/images/themes/theme1/bg.png\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_background_tile\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_link_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"0084B4\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_sidebar_border_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"C0DEED\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_sidebar_fill_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"DDEEF6\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_text_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"333333\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_use_background_image\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mtrue\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_image_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"http://pbs.twimg.com/profile_images/706225181254139909/dXqML--m_normal.jpg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_image_url_https\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://pbs.twimg.com/profile_images/706225181254139909/dXqML--m_normal.jpg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_banner_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://pbs.twimg.com/profile_banners/62842049/1454078321\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"default_profile\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mtrue\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"default_profile_image\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"following\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"follow_request_sent\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"notifications\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m}\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"geo\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"coordinates\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"place\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"contributors\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"is_quote_status\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"retweet_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m0\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"favorite_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m0\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"entities\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"hashtags\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m[\r\n",
" \u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"text\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"WMATA\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"indices\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m[\r\n",
" \u001b[0;39m0\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;39m6\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m]\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m}\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m]\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"urls\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m[]\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"user_mentions\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m[]\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"symbols\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m[]\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m}\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"favorited\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"retweeted\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"filter_level\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"low\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"lang\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"en\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"timestamp_ms\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"1459981974172\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m}\u001b[0m\r\n"
]
}
],
"source": [
"!head -1 $DATA | jq '.'\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"View just the __values__ of each field, without the labels:"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": false,
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\u001b[0;32m\"Wed Apr 06 22:32:54 +0000 2016\"\u001b[0m\r\n",
"\u001b[0;39m717842544655208400\u001b[0m\r\n",
"\u001b[0;32m\"717842544655208449\"\u001b[0m\r\n",
"\u001b[0;32m\"#WMATA GM says he has lots of partners, critics. \\\"I've got lots of critics and that's good. We should have critics.\\\" Opens up to questions\"\u001b[0m\r\n",
"\u001b[0;32m\"Twitter Web Client\"\u001b[0m\r\n",
"\u001b[0;39mfalse\u001b[0m\r\n",
"\u001b[0;39m717842320452948000\u001b[0m\r\n",
"\u001b[0;32m\"717842320452947968\"\u001b[0m\r\n",
"\u001b[0;39m62842049\u001b[0m\r\n",
"\u001b[0;32m\"62842049\"\u001b[0m\r\n",
"\u001b[0;32m\"faizsays\"\u001b[0m\r\n",
"\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"id\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m62842049\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"id_str\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"62842049\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"name\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Faiz Siddiqui\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"screen_name\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"faizsays\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"location\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Washington, DC\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://www.washingtonpost.com/people/faiz-siddiqui\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"description\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Transportation reporter @washingtonpost. Formerly @BostonGlobe. Books, biryani, good raps. faiz.siddiqui@washpost.com.\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"protected\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"verified\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mtrue\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"followers_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m1188\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"friends_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m897\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"listed_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m51\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"favourites_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m797\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"statuses_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m6132\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"created_at\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Tue Aug 04 15:13:48 +0000 2009\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"utc_offset\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m-14400\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"time_zone\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Eastern Time (US & Canada)\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"geo_enabled\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mtrue\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"lang\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"en\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"contributors_enabled\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"is_translator\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_background_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"C0DEED\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_background_image_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"http://abs.twimg.com/images/themes/theme1/bg.png\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_background_image_url_https\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://abs.twimg.com/images/themes/theme1/bg.png\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_background_tile\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_link_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"0084B4\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_sidebar_border_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"C0DEED\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_sidebar_fill_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"DDEEF6\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_text_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"333333\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_use_background_image\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mtrue\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_image_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"http://pbs.twimg.com/profile_images/706225181254139909/dXqML--m_normal.jpg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_image_url_https\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://pbs.twimg.com/profile_images/706225181254139909/dXqML--m_normal.jpg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_banner_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://pbs.twimg.com/profile_banners/62842049/1454078321\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"default_profile\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mtrue\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"default_profile_image\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"following\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"follow_request_sent\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"notifications\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m}\u001b[0m\r\n",
"\u001b[1;30mnull\u001b[0m\r\n",
"\u001b[1;30mnull\u001b[0m\r\n",
"\u001b[1;30mnull\u001b[0m\r\n",
"\u001b[1;30mnull\u001b[0m\r\n",
"\u001b[0;39mfalse\u001b[0m\r\n",
"\u001b[0;39m0\u001b[0m\r\n",
"\u001b[0;39m0\u001b[0m\r\n",
"\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"hashtags\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m[\r\n",
" \u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"text\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"WMATA\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"indices\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m[\r\n",
" \u001b[0;39m0\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;39m6\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m]\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m}\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m]\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"urls\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m[]\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"user_mentions\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m[]\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"symbols\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m[]\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m}\u001b[0m\r\n",
"\u001b[0;39mfalse\u001b[0m\r\n",
"\u001b[0;39mfalse\u001b[0m\r\n",
"\u001b[0;32m\"low\"\u001b[0m\r\n",
"\u001b[0;32m\"en\"\u001b[0m\r\n",
"\u001b[0;32m\"1459981974172\"\u001b[0m\r\n"
]
}
],
"source": [
"!head -1 $DATA| jq '.[]'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Filter your data down to __specific fields__:"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"Wed Apr 06 22:32:54 +0000 2016\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;32m\"#WMATA GM says he has lots of partners, critics. \\\"I've got lots of critics and that's good. We should have critics.\\\" Opens up to questions\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"Wed Apr 06 22:33:10 +0000 2016\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;32m\"Wiedefeld says he has lots of critics, looking for partners. Wants smart people for #wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"Wed Apr 06 22:34:21 +0000 2016\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;32m\"Chairwoman Hermanson is first: Q: With all of the news about long-term plan, are there specific ways that riders can help? #wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n"
]
}
],
"source": [
"!head -3 $DATA | jq '[.created_at, .text]'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The Twitter API documentation describes the responses from the [streaming](https://dev.twitter.com/streaming/overview) (e.g. filter, sample) and [REST](https://dev.twitter.com/rest/public) (user timeline, search) APIs. \n",
"\n",
"JSON is __hierarchical__, and the `created_at` and `text` fields are at the top level of the tweet. Some fields in a tweet have additional fields within them. For example, the `user` field contains fields with information about the user who tweeted, including a count of their followers, location, and a unique id (id_str):"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": false,
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\u001b[1;39m[\r\n",
" \u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"id\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m62842049\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"id_str\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"62842049\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"name\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Faiz Siddiqui\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"screen_name\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"faizsays\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"location\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Washington, DC\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://www.washingtonpost.com/people/faiz-siddiqui\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"description\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Transportation reporter @washingtonpost. Formerly @BostonGlobe. Books, biryani, good raps. faiz.siddiqui@washpost.com.\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"protected\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"verified\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mtrue\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"followers_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m1188\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"friends_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m897\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"listed_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m51\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"favourites_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m797\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"statuses_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m6132\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"created_at\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Tue Aug 04 15:13:48 +0000 2009\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"utc_offset\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m-14400\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"time_zone\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Eastern Time (US & Canada)\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"geo_enabled\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mtrue\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"lang\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"en\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"contributors_enabled\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"is_translator\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_background_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"C0DEED\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_background_image_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"http://abs.twimg.com/images/themes/theme1/bg.png\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_background_image_url_https\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://abs.twimg.com/images/themes/theme1/bg.png\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_background_tile\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_link_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"0084B4\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_sidebar_border_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"C0DEED\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_sidebar_fill_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"DDEEF6\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_text_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"333333\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_use_background_image\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mtrue\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_image_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"http://pbs.twimg.com/profile_images/706225181254139909/dXqML--m_normal.jpg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_image_url_https\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://pbs.twimg.com/profile_images/706225181254139909/dXqML--m_normal.jpg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"profile_banner_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://pbs.twimg.com/profile_banners/62842049/1454078321\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"default_profile\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mtrue\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"default_profile_image\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"following\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"follow_request_sent\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"notifications\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m}\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n"
]
}
],
"source": [
"!head -1 $DATA | jq '[.user]'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To filter for a __subset of the `user` fields__, use dot notation:"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"faizsays\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;32m\"Faiz Siddiqui\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;39m1188\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;32m\"62842049\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"MetroReasons\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;32m\"Metro Reasons\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;39m1492\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;32m\"3378190378\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n"
]
}
],
"source": [
"!head -2 $DATA | jq '[.user.screen_name, .user.name, .user.followers_count, .user.id_str]'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Some fields occur multiple times, such as hashtags and mentions. Pull out the hashtag text fields and put them __together into one field__, separated by commas:"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"WMATA\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"WMATA\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"WMATA,SupportUnions\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"WMATA\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"WMATA\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"WMATA,RAC\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"WMATA\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"WMATA\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"WMATA\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"WMATA\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata,rac\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"WMATA\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata,rac\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\r\n",
" \u001b[0;32m\"wmata\"\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m]\u001b[0m\r\n"
]
}
],
"source": [
"!cat $DATA | jq '[([.entities.hashtags[].text] | join(\",\"))]'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Output to CSV"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"A common use of jq is to turn your __JSON data into a csv file__ to load into other analysis software. The -r option (--raw-output) formats the field as a string suitable for csv, as opposed to a JSON-formatted string with quotes. "
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"collapsed": false,
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\"717842544655208449\",\"Wed Apr 06 22:32:54 +0000 2016\",\"#WMATA GM says he has lots of partners, critics. \"\"I've got lots of critics and that's good. We should have critics.\"\" Opens up to questions\"\r\n",
"\"717842614859513856\",\"Wed Apr 06 22:33:10 +0000 2016\",\"Wiedefeld says he has lots of critics, looking for partners. Wants smart people for #wmata\"\r\n",
"\"717842912202063873\",\"Wed Apr 06 22:34:21 +0000 2016\",\"Chairwoman Hermanson is first: Q: With all of the news about long-term plan, are there specific ways that riders can help? #wmata\"\r\n",
"\"717843310124081153\",\"Wed Apr 06 22:35:56 +0000 2016\",\"RT @FixWMATA: #WMATA RAC meeting is starting - should be live streaming... eventually. https://t.co/iUy3EXkFEJ\"\r\n",
"\"717843816611495936\",\"Wed Apr 06 22:37:57 +0000 2016\",\"@FixWMATA raises potential shutdown conversation to #wmata GM. \"\"I think the ridership shouldn't be blindsided by things like that.\"\"\"\r\n",
"\"717844057104494593\",\"Wed Apr 06 22:38:54 +0000 2016\",\"RT @faizsays: @FixWMATA raises potential shutdown conversation to #wmata GM. \"\"I think the ridership shouldn't be blindsided by things like…\"\r\n",
"\"717844406334853121\",\"Wed Apr 06 22:40:18 +0000 2016\",\"Time to lead people out in handcuffs. @ATULocal689 #wmata https://t.co/ex0nNRzje3\"\r\n",
"\"717844672656326658\",\"Wed Apr 06 22:41:21 +0000 2016\",\".@MetroReasons @ATULocal689: We've swindled riders and taxpayers for decades. That should qualify as being \"\"smart\"\"! #WMATA #SupportUnions\"\r\n"
]
}
],
"source": [
"!head -8 $DATA | jq -r '[.id_str, .created_at, .text] | @csv'"
]
},
{
"cell_type": "markdown",
"metadata": {
"collapsed": true
},
"source": [
"You probably want to write that data to a file, however:"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"!cat $DATA | jq -r '[.id_str, .created_at, .text] | @csv' > tweets.csv"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"collapsed": false,
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\"717842544655208449\",\"Wed Apr 06 22:32:54 +0000 2016\",\"#WMATA GM says he has lots of partners, critics. \"\"I've got lots of critics and that's good. We should have critics.\"\" Opens up to questions\"\r\n",
"\"717842614859513856\",\"Wed Apr 06 22:33:10 +0000 2016\",\"Wiedefeld says he has lots of critics, looking for partners. Wants smart people for #wmata\"\r\n",
"\"717842912202063873\",\"Wed Apr 06 22:34:21 +0000 2016\",\"Chairwoman Hermanson is first: Q: With all of the news about long-term plan, are there specific ways that riders can help? #wmata\"\r\n",
"\"717843310124081153\",\"Wed Apr 06 22:35:56 +0000 2016\",\"RT @FixWMATA: #WMATA RAC meeting is starting - should be live streaming... eventually. https://t.co/iUy3EXkFEJ\"\r\n",
"\"717843816611495936\",\"Wed Apr 06 22:37:57 +0000 2016\",\"@FixWMATA raises potential shutdown conversation to #wmata GM. \"\"I think the ridership shouldn't be blindsided by things like that.\"\"\"\r\n",
"\"717844057104494593\",\"Wed Apr 06 22:38:54 +0000 2016\",\"RT @faizsays: @FixWMATA raises potential shutdown conversation to #wmata GM. \"\"I think the ridership shouldn't be blindsided by things like…\"\r\n",
"\"717844406334853121\",\"Wed Apr 06 22:40:18 +0000 2016\",\"Time to lead people out in handcuffs. @ATULocal689 #wmata https://t.co/ex0nNRzje3\"\r\n",
"\"717844672656326658\",\"Wed Apr 06 22:41:21 +0000 2016\",\".@MetroReasons @ATULocal689: We've swindled riders and taxpayers for decades. That should qualify as being \"\"smart\"\"! #WMATA #SupportUnions\"\r\n",
"\"717844796254117888\",\"Wed Apr 06 22:41:50 +0000 2016\",\"At #WMATA RAC mtg. Glad Wiedefeld showed.\"\r\n",
"\"717844863748816897\",\"Wed Apr 06 22:42:07 +0000 2016\",\"Wiedefeld notes frustration of going from crisis to crisis. Wants to change approach to break that cycle to deal with issues. #wmata\"\r\n"
]
}
],
"source": [
"!head tweets.csv"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Some fields, particularly the text of a tweet, have __newline characters__. This can be a problem with your csv, breaking a tweet across lines. Substitute all occurrences of the newline character (\\n) with a space:"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"!cat $DATA | jq -r '[.id_str, .created_at, (.text | gsub(\"\\n\";\" \"))] | @csv' > tweets-oneline.csv"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\"717842544655208449\",\"Wed Apr 06 22:32:54 +0000 2016\",\"#WMATA GM says he has lots of partners, critics. \"\"I've got lots of critics and that's good. We should have critics.\"\" Opens up to questions\"\r\n",
"\"717842614859513856\",\"Wed Apr 06 22:33:10 +0000 2016\",\"Wiedefeld says he has lots of critics, looking for partners. Wants smart people for #wmata\"\r\n",
"\"717842912202063873\",\"Wed Apr 06 22:34:21 +0000 2016\",\"Chairwoman Hermanson is first: Q: With all of the news about long-term plan, are there specific ways that riders can help? #wmata\"\r\n",
"\"717843310124081153\",\"Wed Apr 06 22:35:56 +0000 2016\",\"RT @FixWMATA: #WMATA RAC meeting is starting - should be live streaming... eventually. https://t.co/iUy3EXkFEJ\"\r\n",
"\"717843816611495936\",\"Wed Apr 06 22:37:57 +0000 2016\",\"@FixWMATA raises potential shutdown conversation to #wmata GM. \"\"I think the ridership shouldn't be blindsided by things like that.\"\"\"\r\n",
"\"717844057104494593\",\"Wed Apr 06 22:38:54 +0000 2016\",\"RT @faizsays: @FixWMATA raises potential shutdown conversation to #wmata GM. \"\"I think the ridership shouldn't be blindsided by things like…\"\r\n",
"\"717844406334853121\",\"Wed Apr 06 22:40:18 +0000 2016\",\"Time to lead people out in handcuffs. @ATULocal689 #wmata https://t.co/ex0nNRzje3\"\r\n",
"\"717844672656326658\",\"Wed Apr 06 22:41:21 +0000 2016\",\".@MetroReasons @ATULocal689: We've swindled riders and taxpayers for decades. That should qualify as being \"\"smart\"\"! #WMATA #SupportUnions\"\r\n",
"\"717844796254117888\",\"Wed Apr 06 22:41:50 +0000 2016\",\"At #WMATA RAC mtg. Glad Wiedefeld showed.\"\r\n",
"\"717844863748816897\",\"Wed Apr 06 22:42:07 +0000 2016\",\"Wiedefeld notes frustration of going from crisis to crisis. Wants to change approach to break that cycle to deal with issues. #wmata\"\r\n"
]
}
],
"source": [
"!head tweets-oneline.csv"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Output to JSON"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"If you'd like JSON format as your output, you can specify the keys in the JSON objects created in the output:"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"cat: data/tweets: No such file or directory\r\n"
]
}
],
"source": [
"!cat $DATA | jq -c '{{id: .id_str, user_id: .user.id_str, screen_name: .user.screen_name, created_at: .created_at, text: .text, user_mentions: [.entities.user_mentions[]?.screen_name], hashtags: [.entities.hashtags[]?.text], urls: [.entities.urls[]?.expanded_url]}}' > newtweets.json"
]
},
{
"cell_type": "code",
"execution_count": 19,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"{\"id\":717842544655208400,\"user_id\":\"62842049\",\"screen_name\":\"faizsays\",\"created_at\":\"Wed Apr 06 22:32:54 +0000 2016\",\"text\":\"#WMATA GM says he has lots of partners, critics. \\\"I've got lots of critics and that's good. We should have critics.\\\" Opens up to questions\",\"user_mentions\":[],\"hashtags\":[\"WMATA\"],\"urls\":[]}\r\n",
"{\"id\":717842614859513900,\"user_id\":\"3378190378\",\"screen_name\":\"MetroReasons\",\"created_at\":\"Wed Apr 06 22:33:10 +0000 2016\",\"text\":\"Wiedefeld says he has lots of critics, looking for partners. Wants smart people for #wmata\",\"user_mentions\":[],\"hashtags\":[\"wmata\"],\"urls\":[]}\r\n",
"{\"id\":717842912202063900,\"user_id\":\"62842049\",\"screen_name\":\"faizsays\",\"created_at\":\"Wed Apr 06 22:34:21 +0000 2016\",\"text\":\"Chairwoman Hermanson is first: Q: With all of the news about long-term plan, are there specific ways that riders can help? #wmata\",\"user_mentions\":[],\"hashtags\":[\"wmata\"],\"urls\":[]}\r\n",
"{\"id\":717843310124081200,\"user_id\":\"105560705\",\"screen_name\":\"waflanagan\",\"created_at\":\"Wed Apr 06 22:35:56 +0000 2016\",\"text\":\"RT @FixWMATA: #WMATA RAC meeting is starting - should be live streaming... eventually. https://t.co/iUy3EXkFEJ\",\"user_mentions\":[\"FixWMATA\"],\"hashtags\":[\"WMATA\"],\"urls\":[\"http://www.wmata.com/about_metro/riders_advisory_council/events_and_docs_by_month.cfm\"]}\r\n",
"{\"id\":717843816611496000,\"user_id\":\"62842049\",\"screen_name\":\"faizsays\",\"created_at\":\"Wed Apr 06 22:37:57 +0000 2016\",\"text\":\"@FixWMATA raises potential shutdown conversation to #wmata GM. \\\"I think the ridership shouldn't be blindsided by things like that.\\\"\",\"user_mentions\":[\"FixWMATA\"],\"hashtags\":[\"wmata\"],\"urls\":[]}\r\n",
"{\"id\":717844057104494600,\"user_id\":\"1587152041\",\"screen_name\":\"FixWMATA\",\"created_at\":\"Wed Apr 06 22:38:54 +0000 2016\",\"text\":\"RT @faizsays: @FixWMATA raises potential shutdown conversation to #wmata GM. \\\"I think the ridership shouldn't be blindsided by things like…\",\"user_mentions\":[\"faizsays\",\"FixWMATA\"],\"hashtags\":[\"wmata\"],\"urls\":[]}\r\n",
"{\"id\":717844406334853100,\"user_id\":\"461101349\",\"screen_name\":\"FireDrGridlock\",\"created_at\":\"Wed Apr 06 22:40:18 +0000 2016\",\"text\":\"Time to lead people out in handcuffs. @ATULocal689 #wmata https://t.co/ex0nNRzje3\",\"user_mentions\":[\"ATULocal689\"],\"hashtags\":[\"wmata\"],\"urls\":[\"https://twitter.com/concernemploye1/status/717008115389095936\"]}\r\n",
"{\"id\":717844672656326700,\"user_id\":\"178065130\",\"screen_name\":\"ConGrpThink\",\"created_at\":\"Wed Apr 06 22:41:21 +0000 2016\",\"text\":\".@MetroReasons @ATULocal689: We've swindled riders and taxpayers for decades. That should qualify as being \\\"smart\\\"! #WMATA #SupportUnions\",\"user_mentions\":[\"MetroReasons\",\"ATULocal689\"],\"hashtags\":[\"WMATA\",\"SupportUnions\"],\"urls\":[]}\r\n",
"{\"id\":717844796254117900,\"user_id\":\"82183980\",\"screen_name\":\"swdcjenn\",\"created_at\":\"Wed Apr 06 22:41:50 +0000 2016\",\"text\":\"At #WMATA RAC mtg. Glad Wiedefeld showed.\",\"user_mentions\":[],\"hashtags\":[\"WMATA\"],\"urls\":[]}\r\n",
"{\"id\":717844863748816900,\"user_id\":\"3378190378\",\"screen_name\":\"MetroReasons\",\"created_at\":\"Wed Apr 06 22:42:07 +0000 2016\",\"text\":\"Wiedefeld notes frustration of going from crisis to crisis. Wants to change approach to break that cycle to deal with issues. #wmata\",\"user_mentions\":[],\"hashtags\":[\"wmata\"],\"urls\":[]}\r\n"
]
}
],
"source": [
"!head newtweets.json"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"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.4.4"
}
},
"nbformat": 4,
"nbformat_minor": 0
}