{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Twitter data cleaning and reshaping\n",
"\n",
"Demo for EMSE 6992, \"Machine Learning for Analytics\" on 1/30/19. \n",
"\n",
"This notebook shows examples of using command-line tools for working with Twitter data. \n",
"\n",
"For this exercise, we're using a dataset with 2,000 tweets collected 01/01/2019 from the Twitter sample stream API. Filename: sample.json"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"How many tweets are in this file? Look at the first tweet."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" 2000 sample.json\r\n"
]
}
],
"source": [
"!wc -l sample.json"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"`head` with a number flag (e.g. -5) determines the number of lines to show. Here's the first line, which is also the first line in line-oriented JSON. "
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"{\"retweeted\": false, \"is_quote_status\": false, \"retweet_count\": 0, \"text\": \"Happy birthday to a real one\\ud83d\\udda4 https://t.co/1o4FxnHgXg\", \"source\": \"Twitter for iPhone\", \"favorited\": false, \"extended_entities\": {\"media\": [{\"id\": 1080174377138823175, \"sizes\": {\"thumb\": {\"h\": 150, \"resize\": \"crop\", \"w\": 150}, \"large\": {\"h\": 2047, \"resize\": \"fit\", \"w\": 1845}, \"small\": {\"h\": 680, \"resize\": \"fit\", \"w\": 613}, \"medium\": {\"h\": 1200, \"resize\": \"fit\", \"w\": 1082}}, \"display_url\": \"pic.twitter.com/1o4FxnHgXg\", \"indices\": [30, 53], \"media_url_https\": \"https://pbs.twimg.com/media/Dv2M33zWoAc83BZ.jpg\", \"id_str\": \"1080174377138823175\", \"media_url\": \"http://pbs.twimg.com/media/Dv2M33zWoAc83BZ.jpg\", \"type\": \"photo\", \"url\": \"https://t.co/1o4FxnHgXg\", \"expanded_url\": \"https://twitter.com/emxdube/status/1080174384776732673/photo/1\"}]}, \"reply_count\": 0, \"in_reply_to_status_id_str\": null, \"in_reply_to_screen_name\": null, \"id_str\": \"1080174384776732673\", \"display_text_range\": [0, 29], \"in_reply_to_user_id_str\": null, \"in_reply_to_user_id\": null, \"favorite_count\": 0, \"contributors\": null, \"filter_level\": \"low\", \"id\": 1080174384776732673, \"truncated\": false, \"created_at\": \"Tue Jan 01 18:50:16 +0000 2019\", \"possibly_sensitive\": false, \"geo\": null, \"quote_count\": 0, \"in_reply_to_status_id\": null, \"place\": null, \"entities\": {\"media\": [{\"id\": 1080174377138823175, \"sizes\": {\"thumb\": {\"h\": 150, \"resize\": \"crop\", \"w\": 150}, \"large\": {\"h\": 2047, \"resize\": \"fit\", \"w\": 1845}, \"small\": {\"h\": 680, \"resize\": \"fit\", \"w\": 613}, \"medium\": {\"h\": 1200, \"resize\": \"fit\", \"w\": 1082}}, \"display_url\": \"pic.twitter.com/1o4FxnHgXg\", \"indices\": [30, 53], \"media_url_https\": \"https://pbs.twimg.com/media/Dv2M33zWoAc83BZ.jpg\", \"id_str\": \"1080174377138823175\", \"media_url\": \"http://pbs.twimg.com/media/Dv2M33zWoAc83BZ.jpg\", \"type\": \"photo\", \"url\": \"https://t.co/1o4FxnHgXg\", \"expanded_url\": \"https://twitter.com/emxdube/status/1080174384776732673/photo/1\"}], \"hashtags\": [], \"urls\": [], \"symbols\": [], \"user_mentions\": []}, \"lang\": \"en\", \"timestamp_ms\": \"1546368616657\", \"coordinates\": null, \"user\": {\"follow_request_sent\": null, \"description\": \"it\\u2019s not that deep\", \"following\": null, \"location\": \"southampton/reading\", \"profile_background_tile\": false, \"default_profile_image\": false, \"url\": \"http://instagram.com/emdube\", \"profile_use_background_image\": false, \"geo_enabled\": false, \"profile_background_image_url\": \"http://abs.twimg.com/images/themes/theme10/bg.gif\", \"profile_link_color\": \"F58EA8\", \"statuses_count\": 8748, \"profile_sidebar_border_color\": \"FFFFFF\", \"contributors_enabled\": false, \"profile_background_color\": \"BAB3BA\", \"id_str\": \"637192198\", \"utc_offset\": null, \"followers_count\": 1591, \"listed_count\": 3, \"notifications\": null, \"translator_type\": \"none\", \"screen_name\": \"emxdube\", \"id\": 637192198, \"profile_text_color\": \"399399\", \"created_at\": \"Mon Jul 16 18:49:43 +0000 2012\", \"profile_background_image_url_https\": \"https://abs.twimg.com/images/themes/theme10/bg.gif\", \"lang\": \"en\", \"profile_banner_url\": \"https://pbs.twimg.com/profile_banners/637192198/1545690059\", \"name\": \"emily\", \"profile_image_url_https\": \"https://pbs.twimg.com/profile_images/1078833355720921089/WaD35kFh_normal.jpg\", \"time_zone\": null, \"profile_image_url\": \"http://pbs.twimg.com/profile_images/1078833355720921089/WaD35kFh_normal.jpg\", \"favourites_count\": 17589, \"protected\": false, \"default_profile\": false, \"friends_count\": 399, \"verified\": false, \"profile_sidebar_fill_color\": \"855682\", \"is_translator\": false}}\r\n"
]
}
],
"source": [
"!head -1 sample.json"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"View the first tweet in the file using jq."
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\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\"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\"text\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Happy birthday to a real one🖤 https://t.co/1o4FxnHgXg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"source\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Twitter for iPhone\"\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\"extended_entities\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"media\"\u001b[0m\u001b[1;39m: \u001b[0m\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;39m1080174377138823200\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"sizes\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"thumb\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"h\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m150\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"resize\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"crop\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"w\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m150\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m}\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"large\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"h\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m2047\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"resize\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"fit\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"w\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m1845\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m}\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"small\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"h\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m680\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"resize\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"fit\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"w\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m613\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m}\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"medium\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"h\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m1200\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"resize\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"fit\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"w\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m1082\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\"display_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"pic.twitter.com/1o4FxnHgXg\"\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;39m30\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;39m53\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m]\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"media_url_https\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://pbs.twimg.com/media/Dv2M33zWoAc83BZ.jpg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"id_str\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"1080174377138823175\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"media_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"http://pbs.twimg.com/media/Dv2M33zWoAc83BZ.jpg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"type\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"photo\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://t.co/1o4FxnHgXg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"expanded_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://twitter.com/emxdube/status/1080174384776732673/photo/1\"\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\"reply_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m0\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[1;30mnull\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[1;30mnull\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"id_str\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"1080174384776732673\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"display_text_range\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m[\r\n",
" \u001b[0;39m0\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;39m29\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m]\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[1;30mnull\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[1;30mnull\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\"contributors\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\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\"id\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m1080174384776732700\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\"created_at\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Tue Jan 01 18:50:16 +0000 2019\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"possibly_sensitive\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\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\"quote_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m0\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[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\"entities\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"media\"\u001b[0m\u001b[1;39m: \u001b[0m\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;39m1080174377138823200\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"sizes\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"thumb\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"h\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m150\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"resize\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"crop\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"w\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m150\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m}\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"large\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"h\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m2047\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"resize\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"fit\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"w\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m1845\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m}\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"small\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"h\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m680\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"resize\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"fit\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"w\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m613\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m}\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"medium\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;39m{\r\n",
" \u001b[0m\u001b[34;1m\"h\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m1200\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"resize\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"fit\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"w\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m1082\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\"display_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"pic.twitter.com/1o4FxnHgXg\"\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;39m30\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0;39m53\u001b[0m\u001b[1;39m\r\n",
" \u001b[1;39m]\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"media_url_https\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://pbs.twimg.com/media/Dv2M33zWoAc83BZ.jpg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"id_str\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"1080174377138823175\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"media_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"http://pbs.twimg.com/media/Dv2M33zWoAc83BZ.jpg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"type\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"photo\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://t.co/1o4FxnHgXg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"expanded_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://twitter.com/emxdube/status/1080174384776732673/photo/1\"\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\"hashtags\"\u001b[0m\u001b[1;39m: \u001b[0m\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\"symbols\"\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[1;39m}\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\"1546368616657\"\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\"user\"\u001b[0m\u001b[1;39m: \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\"description\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"it’s not that deep\"\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\"location\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"southampton/reading\"\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\"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\"url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"http://instagram.com/emdube\"\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;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"geo_enabled\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\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/theme10/bg.gif\"\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\"F58EA8\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"statuses_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m8748\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\"FFFFFF\"\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\"profile_background_color\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"BAB3BA\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"id_str\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"637192198\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"utc_offset\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"followers_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m1591\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"listed_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m3\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[0m\u001b[34;1m\"translator_type\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"none\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"screen_name\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"emxdube\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"id\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m637192198\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\"399399\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"created_at\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"Mon Jul 16 18:49:43 +0000 2012\"\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/theme10/bg.gif\"\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\"profile_banner_url\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"https://pbs.twimg.com/profile_banners/637192198/1545690059\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"name\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;32m\"emily\"\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/1078833355720921089/WaD35kFh_normal.jpg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"time_zone\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[1;30mnull\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/1078833355720921089/WaD35kFh_normal.jpg\"\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"favourites_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m17589\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\"default_profile\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"friends_count\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39m399\u001b[0m\u001b[1;39m,\r\n",
" \u001b[0m\u001b[34;1m\"verified\"\u001b[0m\u001b[1;39m: \u001b[0m\u001b[0;39mfalse\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\"855682\"\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[1;39m}\u001b[0m\u001b[1;39m\r\n",
"\u001b[1;39m}\u001b[0m\r\n"
]
}
],
"source": [
"!head -1 sample.json | jq '.'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Choose multiple fields to extract from all tweets, using jq and the -c flag for compact output. \n",
"\n",
"The following examples uses `head` to only show a few lines and truncate the output here, but you can also use `cat` to process the whole file. "
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\u001b[1;39m[\u001b[0;32m\"1080174384776732673\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"Happy birthday to a real one🖤 https://t.co/1o4FxnHgXg\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"emxdube\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"Tue Jan 01 18:50:16 +0000 2019\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"1080174388975226881\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"RT @aixagroetzner: Todos queremos que nos quieran en voz alta\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"areanax\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"1080174388975157250\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"RT @PCaterianoB: Cateriano afirma que el fujiaprismo se enfoca en “salvar el pellejo de ‘AG’ y ‘señora K’” | Canal N https://t.co/wEL1dpvsy1\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"fokito\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"1080174388987809792\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"RT @QueridoJeito: “Ou você arregaça as mangas e luta pelo que tem, ou você decide que está cansado e você desiste.” \\n— This Is Us\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"ig_sg15\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"1080174388975153152\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"RT @kofi: maybe my wife will reveal herself to me this year. maybe i’ll become the man i’ve always wanted to be.\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"floridbeautyy\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"1080174388987809793\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"@SaaD_M_H7 ماعندي جائزه للي يبتسم عادي تقدر تعدي المقطع\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"AlF__Y\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"1080174388971036672\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"Here is what we came up with after 240 days of work - looking for suggestions/opinions. #indiegames https://t.co/MAwP5vO8MV\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"SpiderMMO\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"1080174388987731968\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"RT @Sofihassan8: بڑا شور سنتے تھے پہلو میں دل کا جو چیرا تو اک قطرہ خوں نہ نکلا…\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"LibertyToExpres\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"1080174388979343360\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"RT @anythingELF: ประกาศตามหาเด็กค่ะ น้องหายออกจากบ้านตั้งแต่เมื่อวานตอนเที่ยงคืน\\nชื่อ คิมแจฮวาน แต่น้องจะเรียกตัวเองว่า แจนี่\\nอายุ 3 ขวบ (ใ…\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"oooootitleottsc\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"1080174389000232960\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"RT @PopularPups: Short leg problems 😂 https://t.co/OddDt1EWvd\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"ubetchiwa\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n"
]
}
],
"source": [
"!head sample.json | jq -c '[.id_str, .text, .user.screen_name, .created_at]'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"For filtering and output, it is helpful to transform the date into a more uesful format. Let's look at the original created_at date and transform it into an ISO 8601 date. "
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\u001b[1;39m[\u001b[0;32m\"Tue Jan 01 18:50:16 +0000 2019\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"2019-01-01T13:50:16Z\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"2019-01-01T13:50:17Z\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"2019-01-01T13:50:17Z\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"2019-01-01T13:50:17Z\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"2019-01-01T13:50:17Z\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"2019-01-01T13:50:17Z\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"2019-01-01T13:50:17Z\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"2019-01-01T13:50:17Z\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"2019-01-01T13:50:17Z\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"Tue Jan 01 18:50:17 +0000 2019\"\u001b[0m\u001b[1;39m,\u001b[0;32m\"2019-01-01T13:50:17Z\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n"
]
}
],
"source": [
"!head sample.json | jq -c '[.created_at, (.created_at | strptime(\"%A %B %d %T %z %Y\") | todate)]'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Look at all of the tweets in particular language, in this case Spanish (\"es\"). "
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\u001b[1;39m[\u001b[0;32m\"RT @aixagroetzner: Todos queremos que nos quieran en voz alta\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n",
"\u001b[1;39m[\u001b[0;32m\"RT @PCaterianoB: Cateriano afirma que el fujiaprismo se enfoca en “salvar el pellejo de ‘AG’ y ‘señora K’” | Canal N https://t.co/wEL1dpvsy1\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\r\n"
]
}
],
"source": [
"!head sample.json | jq -c 'select(.lang | contains(\"es\")) | [.text]'"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Create a CSV with a subset of the fields."
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [],
"source": [
"!cat sample.json | jq -r '[.id_str, .created_at, .text] | @csv' > tweets.csv"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\"1080174384776732673\",\"Tue Jan 01 18:50:16 +0000 2019\",\"Happy birthday to a real one🖤 https://t.co/1o4FxnHgXg\"\r\n",
"\"1080174388975226881\",\"Tue Jan 01 18:50:17 +0000 2019\",\"RT @aixagroetzner: Todos queremos que nos quieran en voz alta\"\r\n",
"\"1080174388975157250\",\"Tue Jan 01 18:50:17 +0000 2019\",\"RT @PCaterianoB: Cateriano afirma que el fujiaprismo se enfoca en “salvar el pellejo de ‘AG’ y ‘señora K’” | Canal N https://t.co/wEL1dpvsy1\"\r\n",
"\"1080174388987809792\",\"Tue Jan 01 18:50:17 +0000 2019\",\"RT @QueridoJeito: “Ou você arregaça as mangas e luta pelo que tem, ou você decide que está cansado e você desiste.” \r\n",
"— This Is Us\"\r\n",
"\"1080174388975153152\",\"Tue Jan 01 18:50:17 +0000 2019\",\"RT @kofi: maybe my wife will reveal herself to me this year. maybe i’ll become the man i’ve always wanted to be.\"\r\n",
"\"1080174388987809793\",\"Tue Jan 01 18:50:17 +0000 2019\",\"@SaaD_M_H7 ماعندي جائزه للي يبتسم عادي تقدر تعدي المقطع\"\r\n",
"\"1080174388971036672\",\"Tue Jan 01 18:50:17 +0000 2019\",\"Here is what we came up with after 240 days of work - looking for suggestions/opinions. #indiegames https://t.co/MAwP5vO8MV\"\r\n",
"\"1080174388987731968\",\"Tue Jan 01 18:50:17 +0000 2019\",\"RT @Sofihassan8: بڑا شور سنتے تھے پہلو میں دل کا جو چیرا تو اک قطرہ خوں نہ نکلا…\"\r\n",
"\"1080174388979343360\",\"Tue Jan 01 18:50:17 +0000 2019\",\"RT @anythingELF: ประกาศตามหาเด็กค่ะ น้องหายออกจากบ้านตั้งแต่เมื่อวานตอนเที่ยงคืน\r\n",
"ชื่อ คิมแจฮวาน แต่น้องจะเรียกตัวเองว่า แจนี่\r\n",
"อายุ 3 ขวบ (ใ…\"\r\n",
"\"1080174389000232960\",\"Tue Jan 01 18:50:17 +0000 2019\",\"RT @PopularPups: Short leg problems 😂 https://t.co/OddDt1EWvd\"\r\n",
"\"1080174388975190016\",\"Tue Jan 01 18:50:17 +0000 2019\",\"RT @BoxOffice: SECOND ACT\r\n",
"$7.36M Weekend (New Est.)\r\n",
"2,607 Screens / $2,823 Avg.\r\n",
"Weekend 2 / +14% Change\r\n",
"$21.91M Total (North America)\r\n",
"#Seco…\"\r\n",
"\"1080174388979294208\",\"Tue Jan 01 18:50:17 +0000 2019\",\"RT @eryndil_staff: しまったぁぁぁ！！！ツイートこけてたぁぁぁ😂😂😂うっそやろぉぉぉ😭\r\n"
]
}
],
"source": [
"!head -20 tweets.csv"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Note that that newlines in the tweet text break the CSV format, which should only have line breaks at the end of the row. Let's fix problems with the text field in the tweets by replacing the \\n newline character with a space. "
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"!cat sample.json | jq -r '[.id_str, (.text | gsub(\"\\n\";\" \"))] | @csv' > tweets.csv"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\"1080174384776732673\",\"Happy birthday to a real one🖤 https://t.co/1o4FxnHgXg\"\r\n",
"\"1080174388975226881\",\"RT @aixagroetzner: Todos queremos que nos quieran en voz alta\"\r\n",
"\"1080174388975157250\",\"RT @PCaterianoB: Cateriano afirma que el fujiaprismo se enfoca en “salvar el pellejo de ‘AG’ y ‘señora K’” | Canal N https://t.co/wEL1dpvsy1\"\r\n",
"\"1080174388987809792\",\"RT @QueridoJeito: “Ou você arregaça as mangas e luta pelo que tem, ou você decide que está cansado e você desiste.” — This Is Us\"\r\n",
"\"1080174388975153152\",\"RT @kofi: maybe my wife will reveal herself to me this year. maybe i’ll become the man i’ve always wanted to be.\"\r\n",
"\"1080174388987809793\",\"@SaaD_M_H7 ماعندي جائزه للي يبتسم عادي تقدر تعدي المقطع\"\r\n",
"\"1080174388971036672\",\"Here is what we came up with after 240 days of work - looking for suggestions/opinions. #indiegames https://t.co/MAwP5vO8MV\"\r\n",
"\"1080174388987731968\",\"RT @Sofihassan8: بڑا شور سنتے تھے پہلو میں دل کا جو چیرا تو اک قطرہ خوں نہ نکلا…\"\r\n",
"\"1080174388979343360\",\"RT @anythingELF: ประกาศตามหาเด็กค่ะ น้องหายออกจากบ้านตั้งแต่เมื่อวานตอนเที่ยงคืน ชื่อ คิมแจฮวาน แต่น้องจะเรียกตัวเองว่า แจนี่ อายุ 3 ขวบ (ใ…\"\r\n",
"\"1080174389000232960\",\"RT @PopularPups: Short leg problems 😂 https://t.co/OddDt1EWvd\"\r\n",
"\"1080174388975190016\",\"RT @BoxOffice: SECOND ACT $7.36M Weekend (New Est.) 2,607 Screens / $2,823 Avg. Weekend 2 / +14% Change $21.91M Total (North America) #Seco…\"\r\n",
"\"1080174388979294208\",\"RT @eryndil_staff: しまったぁぁぁ！！！ツイートこけてたぁぁぁ😂😂😂うっそやろぉぉぉ😭 元旦からこんな調子ですが、本年もよろしくお願いします🙇🙏 https://t.co/AOESjNrXo8\"\r\n",
"\"1080174389000327168\",\"RT @abuaardvark: Inside the stricken court of crown prince Mohammed Bin Salman - profile by Richard Spencer https://t.co/xx39yGb6X8\"\r\n",
"\"1080174388983595011\",\"@MichalKubal @jakubhamleti Michale, když tě tu vidím... prosím tě, ten rozhovor s AB už byl? Bude? Sorry, jsem zmatený\"\r\n",
"\"1080174389008781312\",\"RT @Peppermint_kis: Follow everyone who replies to this 💋💋💋💋💋💋Peppermint Gains 💋💋💋...\"\r\n",
"\"1080174389000265728\",\"🍻🍻\"\r\n",
"\"1080174388983427072\",\"Last puyat na to HAHAHA pero pag may nandemonyo sa monday na ko pasok\"\r\n",
"\"1080174388991864832\",\"RT @6D4GyW2BncWrf2c: ေက်ာင္းသူ😃 https://t.co/GOqc5OtfFG\"\r\n",
"\"1080174388991922177\",\"RT @Kintasz: O melhor bolo do mundo é bolo de bolacha, quem disser o contrário leva porrada\"\r\n",
"\"1080174388987785216\",\"@___Andrzej____ @pisorgpl 👍👍👍👍👍👍👏👏👏👏👏👏👏\"\r\n"
]
}
],
"source": [
"!head -20 tweets.csv"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Hashtags are in nested JSON, so let's flattening the hashtags into a semi-colon delimited list."
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\u001b[1;39m[\u001b[0;32m\"indiegames\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"potus;TDS;haters\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"PossePresidencial\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"trust;trusttheprocess;trustissues;life;livelifetofullest;motivating;motivationalquotes;motivationalquote\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"HappNewYear2019\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"GetUp\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"MerryChristmas\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"ARCplay\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"NH106;BackToTheFuture\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"IfChristmasWereAPerson\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"izmireſcort;bucaeſcort\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"انتصارات_السعوديه_العظمي_2018\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"BamFlinstone;OnTheBeat\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"UniqueNewYearCelebrations\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"TwitterTuesday;Lima;Peru\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"AudiogameJam3;charity;gamejam\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"FiestaBowl\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"RunBTSReturns\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n",
"\u001b[1;39m[\u001b[0;32m\"UniqueNewYearCelebrations\"\u001b[0m\u001b[1;39m\u001b[1;39m]\u001b[0m\n"
]
}
],
"source": [
"!head -200 sample.json | jq -c 'select(.entities.hashtags | length >= 1) | [([.entities.hashtags[].text] | join(\";\"))]'"
]
}
],
"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.6.6"
}
},
"nbformat": 4,
"nbformat_minor": 2
}