!wc -l sample.json

"""
Explanation: Twitter data cleaning and reshaping
Demo for EMSE 6992, "Machine Learning for Analytics" on 1/30/19. 
This notebook shows examples of using command-line tools for working with Twitter data. 
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
How many tweets are in this file? Look at the first tweet.
End of explanation
"""


!head -1 sample.json

"""
Explanation: 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.
End of explanation
"""


!head -1 sample.json | jq '.'

"""
Explanation: View the first tweet in the file using jq.
End of explanation
"""


!head sample.json | jq -c '[.id_str, .text, .user.screen_name, .created_at]'

"""
Explanation: Choose multiple fields to extract from all tweets, using jq and the -c flag for compact output. 
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.
End of explanation
"""


!head sample.json | jq -c '[.created_at, (.created_at | strptime("%A %B %d %T %z %Y") | todate)]'

"""
Explanation: 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.
End of explanation
"""


!head sample.json | jq -c 'select(.lang | contains("es")) | [.text]'

"""
Explanation: Look at all of the tweets in particular language, in this case Spanish ("es").
End of explanation
"""


!cat sample.json | jq -r '[.id_str, .created_at, .text] | @csv' > tweets.csv

!head -20 tweets.csv

"""
Explanation: Create a CSV with a subset of the fields.
End of explanation
"""


!cat sample.json | jq -r '[.id_str, (.text | gsub("\n";" "))] | @csv' > tweets.csv

!head -20 tweets.csv

"""
Explanation: 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.
End of explanation
"""


!head -200 sample.json | jq -c 'select(.entities.hashtags | length >= 1) | [([.entities.hashtags[].text] | join(";"))]'

"""
Explanation: Hashtags are in nested JSON, so let's flattening the hashtags into a semi-colon delimited list.
End of explanation
"""


