<jupyter_start><jupyter_text>Insights on Twitter Tweets from WeRateDogs Table of ContentsIntroductionData Wrangling    Gather three datasets    Start to assess and clean    Store clean datasetExploratory Data AnalysisConclusions  IntroductionWeRateDogs is a Twitter account that rates people's dogs with a humorous comment about the dog.Our focus in this project is producing a high-quality dataset, i.e. wrangling the data to enable further analysis. We collect the tweets and metadata using the Twitter API which is called _Tweepy_. We combine the tweets with a classification of a dogs breed. The classification is based on a neural network and is downloaded at code runtime. Additional information about the tweets is obtained from a file downloaded to the file system.Please refer to `wrangle_report` for details.  Data Wrangling Gather Data<jupyter_code># import libraries
import pandas as pd
import requests, datetime, os, time, tweepy, json<jupyter_output><empty_output><jupyter_text>1: Load WeRateDogs Twitter archive<jupyter_code># note warning in class concerning file 'twitter-archive-enhanced.csv':
# 'The ratings probably aren't all correct. Same goes for the dog names and probably dog stages (see below for more information on these) too. You'll need to assess and clean these columns if you want to use them for analysis and visualization.'

# read CSV to DataFrame
archive = pd.read_csv('twitter-archive-enhanced.csv')
# get an idea that it was successful
# as in "Project Motivation" we have 2356 entries
# only tweets with ratings
archive.shape<jupyter_output><empty_output><jupyter_text>2: Tweet image predictions<jupyter_code># URL to image_predictions.tsv
url = 'https://d17h27t6h515a5.cloudfront.net/topher/2017/August/599fd2ad_image-predictions/image-predictions.tsv'
response = requests.get(url)
# we use byte format to write the data to disk (existing file is replaced):
with open('image_predictions.tsv', mode = 'wb') as file:
    file.write(response.content)
# check that file is created
os.listdir()
image_pred = pd.read_csv('image_predictions.tsv', '\t')<jupyter_output><empty_output><jupyter_text>3. Likes from Twitter API<jupyter_code># no NaN in tweet_id
archive.tweet_id.isna().sum()
# no duplicated IDs in Archive
(archive.tweet_id.duplicated()).sum()<jupyter_output><empty_output><jupyter_text>Reading Tweets using Tweepy<jupyter_code>import tweepy

consumer_key = 'removed private information'
consumer_secret = 'removed private information'
access_token = 'removed private information'
access_secret = 'removed private information'

auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_secret)

api = tweepy.API(auth, wait_on_rate_limit = True, 
                 wait_on_rate_limit_notify = True)<jupyter_output><empty_output><jupyter_text>Learn: read 1 Twitter post, write it to tweet_json.txt<jupyter_code># make sure we have nothing in 'tweet_json.txt' 
with open('tweet_json.txt', 'w', encoding = 'utf-8') as myfile:
    myfile.write('')
# reference:
# https://twitter.com/dog_rates
# https://twitter.com/dog_rates/status/892420643555336193

# read info for 1 tweet
# inspired by https://knowledge.udacity.com/questions/39727
tweet = api.get_status(archive.tweet_id[0], tweet_mode = 'extended')
archive.tweet_id[0], tweet.favorite_count
# write info for 1 post
with open('tweet_json.txt', 'w', encoding = 'utf-8') as file:
    json.dump(tweet._json, file)<jupyter_output><empty_output><jupyter_text>Learn: read 2 Twitter posts, write it to tweet_json.txt<jupyter_code># clean 'tweet_json.txt'
with open('tweet_json.txt', 'w', encoding = 'utf-8') as myfile:
    myfile.write('')
# practice with two Tweet IDs
tweet_ids = archive.tweet_id[0:2]
tweet_ids
# ID1: https://twitter.com/dog_rates/status/892420643555336193
# ID2: https://twitter.com/dog_rates/status/892177421306343426
# read info for 2 tweets
# write content to file directly after receiving the information, i.e. before next loop run 
for tweet_id in tweet_ids:
    tweet = api.get_status(tweet_id, tweet_mode = 'extended')

    # https://www.programiz.com/python-programming/methods/built-in/open
    # mode 'a' -> "Open for appending at the end of the file"
    with open('tweet_json.txt', mode = 'a', encoding = 'utf-8') as file:
        json.dump(tweet._json, file)
        file.write('\n') # new line for next entry<jupyter_output><empty_output><jupyter_text>In the Archive Tweet_IDs data we have entries until August 2017Possibilites:1. filter Tweet_IDs in 'twitter-archive-enhanced.csv' before obtaining information using Tweepy2. do not filter Tweet_IDs but filter on the Tweepy results$\rightarrow$ Use second possibility for data quality reasons: to apply the Tweet ID filter on the original data (directly querying Twitter) reduces possible errors Learn: filter by date<jupyter_code># filter tweets beyond August 1st, 2017
# use datetime 
# inspired by https://stackoverflow.com/questions/7239315/cant-compare-datetime-datetime-to-datetime-date

# comparison on Tweepy output works as expected:
print(datetime.datetime(2017, 1, 1) < datetime.datetime(2017, 1, 2))
print(datetime.datetime(2017, 1, 2) < datetime.datetime(2017, 1, 2))
print(tweet.created_at)
print('is in considered time period?: ', 
      tweet.created_at < datetime.datetime(2017, 1, 2))<jupyter_output>True
False
2017-08-01 00:17:27
is in considered time period?:  False<jupyter_text>Learn: exclude retweets<jupyter_code># look for retweeted post: 1121911827275718656
tweet = api.get_status('1121911827275718656')
# scroll through JSON output which is formatted more readable 
# using https://jsonformatter.curiousconcept.com
tweet.is_quote_status
# look for a not retweeted post: 892420643555336193
tweet = api.get_status('892420643555336193')
# scroll through JSON output which is formatted more readable 
# using https://jsonformatter.curiousconcept.com
tweet.is_quote_status<jupyter_output><empty_output><jupyter_text>For all Tweets: programmatically with a loop<jupyter_code># clean 'tweet_json.txt' before running loop
with open('tweet_json.txt', 'w', encoding = 'utf-8') as myfile:
    myfile.write('')
# full loop running 32 min
# checks 2356 Tweet IDs
# 2325 lines written to 'tweet_json.txt'
# 19 times "No status found with that ID."

# output examples:
# deleted ID:
# -  "ID# 156 after 28 s: get info for Tweet_ID 861769973181624320 ...
#    [{'code': 144, 'message': 'No status found with that ID.'}]"
# last Tweet ID:
#    ID# 2356 after 1910 s: get info for Tweet_ID 666020888022790149 ...
#    older than 02.08.2017 & not retweed -> write line  2325

start = time.time()
count_requests = 0
count_written_lines = 0
count_errors = 0

for tweet_id in archive.tweet_id:
    count_requests += 1
    print('ID#', count_requests, 'after', round(time.time() - start), 's: get info for Tweet_ID', tweet_id, '...')
    
    # exception handling inspired by https://wiki.python.org/moin/HandlingExceptions
    try:
        tweet = api.get_status(tweet_id, tweet_mode = 'extended')
        
    # inspired for how to catch the exception by https://stackoverflow.com/questions/17157753/get-the-error-code-from-tweepy-exception-instance:
    except tweepy.TweepError as e:
        count_errors += 1
        print(e)

    if tweet.created_at < datetime.datetime(2017, 8, 2):
        if tweet.is_quote_status == False:
            count_written_lines += 1
            print('older than 02.08.2017 & no retweet -> write line ', count_written_lines)
            with open('tweet_json.txt', 'a', encoding = 'utf-8') as file:
                json.dump(tweet._json, file)
                file.write('\n')
   # if count_requests == 5:
   #     break
count_requests, count_written_lines, count_errors<jupyter_output><empty_output><jupyter_text>Read the Data received by Tweepy to DataFrame<jupyter_code># https://stackoverflow.com/questions/12451431/\
# loading-and-parsing-a-json-file-with-multiple-json-objects-in-pythonn
data = []
with open('tweet_json.txt') as tweet_json:
    for line in tweet_json:
        data.append(json.loads(line))
tweet_json = pd.DataFrame(data)<jupyter_output><empty_output><jupyter_text>Start to assess and clean Assess Data: Missing data/completeness & tidiness:<jupyter_code>archive.shape
image_pred.shape
tweet_json.shape
archive.head(1)
image_pred.head(1)
tweet_json.head(1)<jupyter_output><empty_output><jupyter_text>got Tweets in the archive up to August 2017 / all 2,356 entries are from the relevant time period<jupyter_code>archive.timestamp.min(), archive.timestamp.max()<jupyter_output><empty_output><jupyter_text>2.075 lines in image predictions are all in `twitter-archive-enhanced.csv`<jupyter_code>pd.merge(left = archive, right = image_pred, on = 'tweet_id', how = 'inner').shape
# double-check for an ID:
image_pred.tweet_id.head(1)
archive[archive.tweet_id == 666020888022790149]<jupyter_output><empty_output><jupyter_text>There are duplicated results in the Twitter outputWe checked the tweet_ids used to request data using Tweepy to be unique. Even so, the Tweepy output contains duplicated values.<jupyter_code>tweet_json.shape
tweet_json[tweet_json.id.duplicated()].id.count()
# see examples
tweet_json[tweet_json.id.duplicated(keep=False)].id.head()
# identical lines
tweet_json[tweet_json.id == 888554962724278272]<jupyter_output><empty_output><jupyter_text>Cleaning 1: Missing data/completeness: duplicated Tweepy output  Define To remove duplicated values first reduce the data collected from Twitter to the columns of interest. Otherwise the dictionary structure within some fields -- e.g. tweet_json.entities -- does not allow to execute .drop_duplicates ("TypeError: unhashable type: 'list'").Then remove duplicated values from `tweet_json`.  Code<jupyter_code># cleaning a copy of the dataset
tweet_json_clean = tweet_json.copy()
# collect columns of interest
tweet_json_clean = tweet_json_clean[['id', 'favorite_count', 'retweet_count']]
tweet_json_clean.head(1)
tweet_json_clean.drop_duplicates(keep = 'first', inplace = True)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code># 19 rows removed as expected
tweet_json.shape[0] - tweet_json_clean.shape[0]
# no longer duplicates
tweet_json_clean[tweet_json_clean.id.duplicated()].id.count()<jupyter_output><empty_output><jupyter_text>Missing data/completeness: Assess Data (continued) image predictions vs. `tweet_json`  From 2.075 lines in image predictions only 2.065 are in `tweet_json`<jupyter_code>image_pred.shape, tweet_json_clean.shape
pd.merge(left = tweet_json_clean, right = image_pred, left_on = 'id', right_on = 'tweet_id', how = 'inner').shape
# collect Tweets in both datasets
in_pred_and_tweet_json = pd.merge(left = tweet_json_clean, right = image_pred, left_on = 'id', \
                                  right_on = 'tweet_id', how = 'inner').id<jupyter_output><empty_output><jupyter_text>The difference of 10 Tweets: they are not in the data collected using Tweepy because they do not exist any more<jupyter_code># inspired by https://stackoverflow.com/questions/3462143/get-difference-between-two-lists
len(set(image_pred.tweet_id) - set(in_pred_and_tweet_json))
set(image_pred.tweet_id) - set(in_pred_and_tweet_json)
# all 10 Tweets do not exist any longer
# https://twitter.com/dog_rates/status/680055455951884288<jupyter_output><empty_output><jupyter_text>$\rightarrow$ We have image prediction for all Tweepy collected data and we understand the shape of the image prediction dataset$\rightarrow$ Merging `tweet_json_clean` and `image_pred` is possible without relevant data loss.  archive vs. `tweet_json`<jupyter_code>archive.shape, tweet_json_clean.shape
# archive without duplicated Tweet IDs
archive[archive.tweet_id.duplicated()].tweet_id.count()
# all IDs from archive in tweet_json_clean
pd.merge(left = tweet_json_clean, right = archive, left_on = 'id', right_on = 'tweet_id', how = 'inner').shape<jupyter_output><empty_output><jupyter_text>the Tweets in `archive` but not in `tweet_json` are not relevant<jupyter_code># (set(archive.tweet_id) - set(tweet_json_clean.id)) # suppressed due to long output
# 680055455951884288: does not exist any longer
# 706169069255446529, 712309440758808576, ... from first 5 are quotes; 
# not interested in quotes as they do not offer (potrait-like) dog pictures
archive.columns
tweet_json_clean.columns<jupyter_output><empty_output><jupyter_text>Cleaning 2: Tidiness: Merging `tweet_json` and `image_pred` is possible without relevant data loss.  DefineEach observational unit as a table $\rightarrow$ Twitter post as unit and collecting information on the post appears most useful here.Join `tweet_json_clean` and `image_pred` based on the Tweet ID.  Code<jupyter_code>twitter_archive_master = pd.merge(left = tweet_json_clean, right = image_pred, left_on = 'id', \
                                  right_on = 'tweet_id', how = 'inner')<jupyter_output><empty_output><jupyter_text>Test<jupyter_code># The difference of 10 Tweets as expected from analysis above:
# 10 Tweets they are not in the data collected using Tweepy because they do not exist any more
len(set(image_pred.tweet_id) - set(twitter_archive_master.id))
twitter_archive_master.head(1)
twitter_archive_master.shape<jupyter_output><empty_output><jupyter_text>Cleaning 3: Tidiness: Skip irrelevant columns from `archive` and `twitter_archive_master`, merge `twitter_archive_master` and `archive`   DefineKeep `archive` columns 'tweet_id', 'timestamp', 'text', 'rating_numerator', 'rating_denominator', 'name', 'doggo', 'floofer', 'pupper', 'puppo'.Keep `twitter_archive_master` columns 'id', 'favorite_count', 'retweet_count', 'p1', 'p1_conf', 'p1_dog'. I.e. we keep only the most likely prediction.Merge `twitter_archive_master` and `archive_clean` using Tweet_ID. Expect 2,065 rows.Note for predictions:- p1 is the algorithm's 1 prediction for the image in the tweet → golden retriever- p1_conf is how confident the algorithm is in its 1 prediction → 95%- p1_dog is whether or not the 1 prediction is a breed of dog → TRUE  Code<jupyter_code>archive_clean = archive.copy()
archive_clean = archive_clean[['tweet_id', 'timestamp', 'text', 'rating_numerator', 'rating_denominator', \
                               'name', 'doggo', 'floofer', 'pupper', 'puppo']]
twitter_archive_master = twitter_archive_master[['id', 'favorite_count', 'retweet_count', 'p1', 'p1_conf', 'p1_dog']]
twitter_archive_master = pd.merge(left = twitter_archive_master, right = archive_clean, \
                                  left_on = 'id', right_on = 'tweet_id', how = 'inner')
twitter_archive_master.drop(['id'], axis = 1, inplace = True)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>archive_clean.head(1)
# 2065 as expected
twitter_archive_master.shape
twitter_archive_master.head(1)<jupyter_output><empty_output><jupyter_text>Cleaning 4: Tidiness: Tidy dog stages: have the dog stage in a single column.   Assess<jupyter_code>twitter_archive_master.doggo.value_counts()
twitter_archive_master.floofer.value_counts()
twitter_archive_master.pupper.value_counts()
twitter_archive_master.puppo.value_counts()
# 13 Tweets where multiple stages are given
stages = twitter_archive_master.doggo + ',' + twitter_archive_master.floofer + ',' +\
    twitter_archive_master.pupper + ',' + twitter_archive_master.puppo
stages.value_counts()
# doggo,None,pupper,None     11    
# doggo,floofer,None,None    1   
# doggo,None,None,puppo      1<jupyter_output><empty_output><jupyter_text>Define Create a column for stages which contains the single given dog stage and "multiple" alternatively. "multiple" is to be set for 'doggo,None,pupper,None', 'doggo,floofer,None,None', 'doggo,None,None,puppo'.  Code<jupyter_code>stages_multiple = stages.loc[stages == 'doggo,None,pupper,None'].index.\
    append(stages.loc[stages == 'doggo,floofer,None,None'].index).\
    append(stages.loc[stages == 'doggo,None,None,puppo'].index)
stages_multiple
stages_clean = twitter_archive_master.text.str.extract('(doggo|floofer|pupper|puppo)')
stages_clean.loc[stages_multiple] = 'multiple'
twitter_archive_master['stages'] = stages_clean
twitter_archive_master.drop(['doggo', 'floofer', 'pupper', 'puppo'], axis = 1, inplace = True)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_master.columns
twitter_archive_master.stages.value_counts()<jupyter_output><empty_output><jupyter_text>Assess Data: Quality issues remaining<jupyter_code>twitter_archive_master.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 2063 entries, 0 to 2062
Data columns (total 12 columns):
favorite_count        2063 non-null int64
retweet_count         2063 non-null int64
p1                    2063 non-null object
p1_conf               2063 non-null float64
p1_dog                2063 non-null bool
tweet_id              2063 non-null int64
timestamp             2063 non-null object
text                  2063 non-null object
rating_numerator      2063 non-null int64
rating_denominator    2063 non-null int64
name                  2063 non-null object
stages                338 non-null object
dtypes: bool(1), float64(1), int64(5), object(5)
memory usage: 275.4+ KB<jupyter_text>$\rightarrow$ timestamp is not in format timestamp<jupyter_code>twitter_archive_master.describe()<jupyter_output><empty_output><jupyter_text>$\rightarrow$ investigate rating_numerator and rating_denominator values<jupyter_code># example where `rating_numerator` is not correctly extracted:
twitter_archive_master[twitter_archive_master.rating_numerator < 8].head(1)
# check solution with 883482846933004288
# true 0: 835152434251116546
# true 3: 777885040357281792 (a goat)
twitter_archive_master[twitter_archive_master.rating_numerator > 14].head(3)
# rating 84/70 for multiple dogs: 820690176645140481; similar 731156023742988288
# rating >100: 749981277374128128<jupyter_output><empty_output><jupyter_text>$\rightarrow$ the rating_numerator column has rows with inaccurate data extracted from the Tweet text$\rightarrow$ rating_numerator column values >14 contain multiple dogs/special cases<jupyter_code># example where `rating_denominator` is not correctly extracted:
twitter_archive_master[twitter_archive_master.rating_denominator < 7].head(1)
# check solution with 666287406224695296<jupyter_output><empty_output><jupyter_text>$\rightarrow$ the rating_denominator column has rows with inaccurate data extracted from the Tweet text<jupyter_code># rating is not always at the end, or after "."
# however, rating contains a "/" 
pd.set_option('display.max_colwidth', -1)
twitter_archive_master.text
# 2 Tweets where the regex fails due to special cases with 7/11 with meaning plus rating given.
twitter_archive_master[twitter_archive_master.rating_denominator == 11]<jupyter_output><empty_output><jupyter_text>Looking at the dog names<jupyter_code>twitter_archive_master.name.value_counts().head()
pd.set_option('display.max_colwidth', -1)
# Where name is "None" we see that there is actually no name given. I don't see room for improvement here.
twitter_archive_master.loc[twitter_archive_master.name == 'None',['tweet_id', 'name','text']].head(1)<jupyter_output><empty_output><jupyter_text>Dog names 'a' can be improved: For texts beginning with "this is a ..." there is either no name given. Or, we find dog names after "named " and before ".".<jupyter_code>twitter_archive_master.loc[twitter_archive_master.name == 'a', ['tweet_id', 'name','text']].head(1)
# with name 666701168228331520: Johm
# without name 881536004380872706<jupyter_output><empty_output><jupyter_text>correct names 'the' and 'an'<jupyter_code>twitter_archive_master.name.value_counts().head(13)
#  7 Tweets where name is identified as "the". First two are w/o a name given (690360449368465409, 685943807276412928). 
twitter_archive_master[twitter_archive_master.name == 'the'].head(1)
# 6 Tweets where name is identified as "an". First is w/o a name given (703041949650034688). 
twitter_archive_master[twitter_archive_master.name == 'an'].head(1)<jupyter_output><empty_output><jupyter_text>Cleaning 5: timestamp is not in format timestamp   DefineSet column `timestamp` to format timestamp.  Code<jupyter_code>twitter_archive_master.timestamp = twitter_archive_master.timestamp.astype('datetime64')<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_master.timestamp.dtype<jupyter_output><empty_output><jupyter_text>Cleaning 6: rating_numerator and rating_denominator columns with inaccurate data extracted from the Tweet text   DefineExtract the rating from the Tweet text using regular expressions. Use that ratings contain "/". Use that ratings denominator is always 2 digits.  Code<jupyter_code># non-matching group to optionally obtain e.g. "13." from 13.5/10 inspired by 
# https://stackoverflow.com/questions/3512471/what-is-a-non-capturing-group-what-does-do#3513858

pattern_test = pd.concat([twitter_archive_master.tweet_id, twitter_archive_master.text,
           twitter_archive_master.text.str.extract(pat = '((?:\d{1,2}.)?\d{1,2}/\d{2})')], axis = 1)
# rule handles to not wrongly interpret "3 1/2" as a rating:
pattern_test[pattern_test.tweet_id == 666287406224695296]
# also a 13.5/10 is managed:
pattern_test[pattern_test.tweet_id == 883482846933004288]
pattern_test.columns = ['tweet_id', 'text', 'rating']
twitter_archive_master.columns
twitter_archive_master['rating_numerator'], twitter_archive_master['rating_denominator'] = pattern_test.rating.str.split('/').str
twitter_archive_master['rating_numerator'] = twitter_archive_master['rating_numerator'].astype('float')
twitter_archive_master['rating_denominator'] = twitter_archive_master['rating_denominator'].astype('float')<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_master[twitter_archive_master.tweet_id == 666287406224695296]
twitter_archive_master[twitter_archive_master.tweet_id == 883482846933004288]<jupyter_output><empty_output><jupyter_text>Cleaning 7: handle a special rating case  DefineA tweet with a valid rating: the regex fails due to the anormally that we have both "7/11" not standing for a rating plus "10/10" for a rating. The programmatic code for cleaning could be change such that the second occurance of the rating matched by a regex is used. However, as this is a one-off, we manually correct.  Code<jupyter_code>twitter_archive_master.loc[twitter_archive_master.tweet_id == 682962037429899265]
twitter_archive_master.loc[twitter_archive_master.tweet_id == 682962037429899265, 'rating_numerator'] = 10
twitter_archive_master.loc[twitter_archive_master.tweet_id == 682962037429899265, 'rating_denominator'] = 10<jupyter_output><empty_output><jupyter_text>Test<jupyter_code># now it's 10/10 as given in https://twitter.com/dog_rates/status/682962037429899265
twitter_archive_master.loc[twitter_archive_master.tweet_id == 682962037429899265]<jupyter_output><empty_output><jupyter_text>Cleaning 8: rating_numerator and rating_denominator columns with extreme values: rating_numerator column values >14 contain multiple dogs/special cases & denominator > 10 DefineFocus on single dogs. Skip observations with multiple dogs and rare special cases of other ratings with numerator > 14. Skip 2 Tweets where the regex fails due to special cases  Code<jupyter_code>twitter_archive_master.shape
twitter_archive_master = twitter_archive_master[twitter_archive_master.rating_numerator <= 14]
twitter_archive_master = twitter_archive_master[twitter_archive_master.rating_denominator < 11]<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_master.shape
twitter_archive_master.rating_numerator.value_counts()
# fine, normal rating: 778027034220126208
twitter_archive_master[twitter_archive_master.rating_numerator == 11.27]
twitter_archive_master.rating_denominator.value_counts()
pd.set_option('display.max_colwidth', 50)<jupyter_output><empty_output><jupyter_text>Cleaning 9: improve dog names 'a': set names 'a' to none. DefineTexts starting with ""this is a ..." are the names wrongly identified as "a". In most of these Tweets texts no name is given. Next cleaning step is identifying further names given in Tweets. This cleaning step: Set the "a" to "none".  Code<jupyter_code># set all "a" to "none":
twitter_archive_master.loc[twitter_archive_master.name == 'a','name'] = 'None'
##### Test
# no more any names 'a'
twitter_archive_master.name.value_counts().head()
# dog 'Johm' not identified yet; see next cleaning
twitter_archive_master[twitter_archive_master.tweet_id == 666701168228331520]<jupyter_output><empty_output><jupyter_text>Cleaning 10: improve dog names 'a': identify names for Tweets with "this is a ..." DefineFor texts beginning with "this is a ..." we find dog names after "named ". The pattern To be careful we only apply this pattern to rows where no valid name was set before (rows where name "a" was given).  Code<jupyter_code>twitter_archive_master.name.value_counts().head()
twitter_archive_master['name_add'] = twitter_archive_master.text.str.extract(pat = 'named\s(\w+)')
twitter_archive_master.loc[~twitter_archive_master.name_add.isna(), 'name'] = twitter_archive_master.name_add
twitter_archive_master.drop(['name_add'], axis = 1, inplace = True)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code># 23 names identified
twitter_archive_master.name.value_counts().head()
twitter_archive_master.columns
# Johm is now identified: 666701168228331520
twitter_archive_master[twitter_archive_master.tweet_id == 666701168228331520]
# correctly still without name: 881536004380872706
twitter_archive_master[twitter_archive_master.tweet_id == 881536004380872706]<jupyter_output><empty_output><jupyter_text>Cleaning 11: improve dog names 'the' and 'an' Define7 Tweets where name is identified as "the". First two are w/o a name given (690360449368465409, 685943807276412928). Nothing really to gain here $\rightarrow$ set names "the" to "none".   Code<jupyter_code>twitter_archive_master.name.value_counts().head(1)
twitter_archive_master.loc[twitter_archive_master.name == 'the','name'] = 'None'
twitter_archive_master.loc[twitter_archive_master.name == 'an','name'] = 'None'<jupyter_output><empty_output><jupyter_text>Test<jupyter_code># no names with "the" and "an" any longer
# as expected: "None" count increased by 13 = 605 - 592
twitter_archive_master.name.value_counts().head(1)<jupyter_output><empty_output><jupyter_text>Store clean dataset<jupyter_code># file which allows for "," in Tweet text: use pipe as separator
twitter_archive_master.to_csv('twitter_archive_master.csv', sep = ',')
# test read
df = pd.read_csv('twitter_archive_master.csv', sep = ',', index_col = 0)
df.head(1)<jupyter_output><empty_output><jupyter_text>Exploratory Data Analysis<jupyter_code>df = twitter_archive_master<jupyter_output><empty_output><jupyter_text>Question 1: do the ratings change over time?<jupyter_code># know that ratings denominator is always 10
import datetime
import matplotlib.pyplot as plt
val = df.rating_numerator.groupby(df.timestamp.dt.year).mean()
val
fig = plt.figure(figsize = (15, 3))
ax = fig.add_subplot(111)
plt.bar(['2015', '2016', '2017'], list(val.values))
plt.title('Mean WeRateDog rating 2015-2017', fontsize = 20)
ax.set_xlabel('Year', fontsize = 16)
ax.set_ylabel('Avg. Rating', fontsize = 16)
plt.show();<jupyter_output><empty_output><jupyter_text>$\rightarrow$ Clear trend of improving dog ratings from 2015-2017.  Question 2: how are Tweets spread over day of week?<jupyter_code># weekday: https://docs.python.org/2/library/datetime.html
# Monday is 0 and Sunday is 6
from datetime import datetime as dt
tweet_weekday = df.timestamp.apply(dt.weekday).value_counts()
tweet_weekday = tweet_weekday / tweet_weekday.sum()
tweet_weekday
# 18% more likely to have a new Tweet on weekdays
tweet_weekday[0:5].mean() / tweet_weekday[5:7].mean()
# avg. Tweets per day
(df.shape[0]) / (2 * 365)
# Tweets per weekday
(df.shape[0] * tweet_weekday) / ((2 * 365) / 7) # approximately 2 years of posts
df.timestamp.describe()
import seaborn as sb
sb.set_style('darkgrid')
fig = plt.figure(figsize = (15, 3))
ax = fig.add_subplot(111)
plt.bar(tweet_weekday.index, tweet_weekday)
plt.title('Tweets per Day of Week for 11/2015 to 08/2017', fontsize = 20)
ax.set_xlabel('Day (0 = Monday to 6 = Sunday)', fontsize = 16)
ax.set_ylabel('Number of Tweets', fontsize = 16)
plt.show();<jupyter_output><empty_output><jupyter_text>$\rightarrow$ On the weekend there are less tweets. The highest amount of tweets is on Monday.  Question 3: When an algorithm identifies a dog on the picture the dog is visible on the picture in a certain structure. Does that structure correlate with how humans like the picture?Here: Measure by retweet_count how much humans like the dogs.<jupyter_code>df.retweet_count.groupby(df.p1_dog).mean()
# 4% higher retweet count for dogs identified
df.retweet_count[df.p1_dog == True].mean() / df.retweet_count[df.p1_dog == False].mean()<jupyter_output><empty_output><jupyter_text>$\rightarrow$ the average amount of retweets is slightly higher when dogs are identified by the algorithm; more investigation needed to assess the practical relevance/strength of the relationship<jupyter_code>fig = plt.figure(figsize = (15, 3))
ax = fig.add_subplot(111)
(df.retweet_count[df.p1_dog == True]).plot(kind = 'hist', bins = 20, alpha = .4, label = 'Dog identified', xlim = (0,15000))
df.retweet_count[df.p1_dog == False].plot(kind = 'hist', alpha = .4, label = 'Dog not identified')
plt.title('Retweets by Algorithm (ML) Identification', fontsize = 20)
ax.set_xlabel('Retweets', fontsize = 16)
ax.set_ylabel('Number of Tweets', fontsize = 16)
ax.legend();<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10270160882323182, 'fraction_numerical': 0.047117946642382544, 'mean_word_length': 6.017751479289941, 'pattern_counts': {'":': 1, '<': 228, '<?xml version=': 0, '>': 229, 'https://': 18, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '55', 'dir': 'jupyter-structured-clean-dedup', 'id': '127269', 'license': 'MIT', 'n_tokens_mistral': 10630, 'n_tokens_neox': 9538, 'n_words': 2526, 'path': 'notebooks/wrangle_code.ipynb', 'repo_name': 'janhenner/TwitterDataWrangling', 'size': '29649'}