<jupyter_start><jupyter_text>WeRateDogs Twitter Data Analysis  Table of ContentsIntroductionGather DataAssess DataClean DataAnalyze DataConclusions  IntroductionThe dataset that I will be wrangling (and analyzing and visualizing) is the tweet archive of Twitter user @dog_rates, also known as WeRateDogs. WeRateDogs is a Twitter account that rates people's dogs with a humorous comment about the dog.1. Gather data from 3 difference sources.	- Twitter data in cvs file provided by Udacity	- Image prediction data from Udacity servers	- Additional twitter data via http request’s get method2. Assess visually and programmatically for data quality and tidiness issues.3. Clean and fix data.4. Analyze data.    - How good the image prediction algorithm was to predict dog images?    - Are top breeds of dogs associated with rating?    - Which variables are associated with retweets and favorite counts?<jupyter_code>import numpy as np
import pandas as pd
import requests
import os
import tweepy
import json
import matplotlib.pyplot as plt
import seaborn as sns
import statsmodels.api as sms;
%matplotlib inline<jupyter_output>C:\Users\ayumi\Anaconda3\lib\site-packages\statsmodels\compat\pandas.py:56: FutureWarning: The pandas.core.datetools module is deprecated and will be removed in a future version. Please use the pandas.tseries module instead.
  from pandas.core import datetools<jupyter_text>Gather Data Load The WeRateDogs Twitter archive<jupyter_code># Load The WeRateDogs Twitter archive
twitter_archive = pd.read_csv('data/twitter-archive-enhanced.csv')
twitter_archive.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2356 entries, 0 to 2355
Data columns (total 17 columns):
tweet_id                      2356 non-null int64
in_reply_to_status_id         78 non-null float64
in_reply_to_user_id           78 non-null float64
timestamp                     2356 non-null object
source                        2356 non-null object
text                          2356 non-null object
retweeted_status_id           181 non-null float64
retweeted_status_user_id      181 non-null float64
retweeted_status_timestamp    181 non-null object
expanded_urls                 2297 non-null object
rating_numerator              2356 non-null int64
rating_denominator            2356 non-null int64
name                          2356 non-null object
doggo                         2356 non-null object
floofer                       2356 non-null object
pupper                        2356 non-null object
puppo                         2356 non-null object
dtypes: float64(4), int64(3), ob[...]<jupyter_text>Get tweet image predictions from Udacity Servers and save to 'image_predictions.tsv' file<jupyter_code># Get tweet image predictions from Udacity Servers and save to 'image_predictions.tsv' file
image_predictions_url = 'https://d17h27t6h515a5.cloudfront.net/topher/2017/August/599fd2ad_image-predictions/image-predictions.tsv'
response = requests.get(image_predictions_url)
with open('data/image_predictions.tsv', mode='wb') as file:
    file.write(response.content)<jupyter_output><empty_output><jupyter_text>Load tweet image predictions<jupyter_code># Load tweet image predictions
image_predictions = pd.read_csv('data/image_predictions.tsv', sep = '\t')
image_predictions.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2075 entries, 0 to 2074
Data columns (total 12 columns):
tweet_id    2075 non-null int64
jpg_url     2075 non-null object
img_num     2075 non-null int64
p1          2075 non-null object
p1_conf     2075 non-null float64
p1_dog      2075 non-null bool
p2          2075 non-null object
p2_conf     2075 non-null float64
p2_dog      2075 non-null bool
p3          2075 non-null object
p3_conf     2075 non-null float64
p3_dog      2075 non-null bool
dtypes: bool(3), float64(3), int64(2), object(4)
memory usage: 152.1+ KB<jupyter_text>Get additional data using Tweepy API and save to 'tweet_json.txt' file<jupyter_code># Tweepy OAuthentication 

# Setting up consumer/access token and secret
consumer_key = "<consumer key>"
consumer_secret = "<consumer secret>"
access_token = "<application key>"
access_secret = "<application secret>"

# Create an OAuthHandler instance
auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
# Set access token
auth.set_access_token(access_token, access_secret)

# Create an API instance
api = tweepy.API(auth)
# Get additional data using Twitter API
tweet_ids = twitter_archive.tweet_id
batch_size = 100
count = 0

f = open('data/tweet_json.txt','w')

while batch_size*count < len(tweet_ids):
    
    # Calculate start and end index since we can only batch request 100 twitter ids at a time
    idx_start = batch_size*count
    idx_end = batch_size*(count+1) if batch_size*(count+1) < len(tweet_ids) else len(tweet_ids)
    
    # Get 100 tweet ids
    tweet_ids_request = tweet_ids[idx_start:idx_end]  
    
    # Batch requesting addtional information for 100 tweet ids
    # print('processing tweets %s - %s' % (idx_start+1, idx_end))
    tweets = api.statuses_lookup(id_=tweet_ids_request.tolist(), include_entities = False)

    # Save each tweet's additional data as json format in file 'tweet_json.txt'
    for tweet in tweets:
        json.dump(tweet._json, f)
        f.write("\n")
        
    count += 1

f.close()<jupyter_output><empty_output><jupyter_text>Load additional twitter data, retreat_count and favorite_count.<jupyter_code># Load additional twitter data, retreat_count and favorite_count. 
cols = ['tweet_id', 'retweet_count', 'favorite_count']
tweets_list = []

with open('data/tweet_json.txt') as f:
    for line in f:
        # load each line of data as json format
        tweet = json.loads(line) 
        tweets_list.append([tweet['id'], tweet['retweet_count'], tweet['favorite_count']])
        
additional_data = pd.DataFrame(tweets_list, columns=cols)
additional_data.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2345 entries, 0 to 2344
Data columns (total 3 columns):
tweet_id          2345 non-null int64
retweet_count     2345 non-null int64
favorite_count    2345 non-null int64
dtypes: int64(3)
memory usage: 55.0 KB<jupyter_text>Assess Data  `twitter_archive` table<jupyter_code>twitter_archive.head()<jupyter_output><empty_output><jupyter_text>I've used Microsoft Excel to assess twitter_archive data visually.Here are my assessments:- Name column contains words like 'just', 'the', 'a'.- Some tweets have more than 1 dog life stages.- twitter id 786709082849828864 has wrong numerator and denominator. They should be 9.75/10 instead of 75/10.- Extract numerator and denomintor from text columns and replace 'rating_numerator' and 'rating_denominator' with correct ones.- Dog life stage variable is in 4 columns. ('doggo', 'floofer', 'pupper', and 'puppo')Now I will assess twitter_archive data programmatically.<jupyter_code>twitter_archive.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2356 entries, 0 to 2355
Data columns (total 17 columns):
tweet_id                      2356 non-null int64
in_reply_to_status_id         78 non-null float64
in_reply_to_user_id           78 non-null float64
timestamp                     2356 non-null object
source                        2356 non-null object
text                          2356 non-null object
retweeted_status_id           181 non-null float64
retweeted_status_user_id      181 non-null float64
retweeted_status_timestamp    181 non-null object
expanded_urls                 2297 non-null object
rating_numerator              2356 non-null int64
rating_denominator            2356 non-null int64
name                          2356 non-null object
doggo                         2356 non-null object
floofer                       2356 non-null object
pupper                        2356 non-null object
puppo                         2356 non-null object
dtypes: float64(4), int64(3), ob[...]<jupyter_text>There are 2356 entries and 17 columns in twitter_archive.These following columns have missing values:  'in_reply_to_status_id', 'in_reply_to_user_id', 'retweeted_status_id', 'retweeted_status_user_id', 'retweeted_status_timestamp', 'expanded_urls' The data type of 'in_reply_to_status_id', 'in_reply_to_user_id', 'retweeted_status_id', and 'retweeted_status_user_id' columns are float.The data type of the column 'timestamp' and 'retweeted_status_timestamp' are string(object).Since we only want to use original tweets, I will remove 'retweeted_status_id', 'retweeted_status_user_id', and 'retweeted_status_timestamp' columns later.<jupyter_code>twitter_archive['text'][0]<jupyter_output><empty_output><jupyter_text>Text column contains text, numerator, denominator, and short-url.<jupyter_code>twitter_archive['source'][0]<jupyter_output><empty_output><jupyter_text>Source column contains unnecessary and extra information.<jupyter_code>sum(twitter_archive['tweet_id'].duplicated())<jupyter_output><empty_output><jupyter_text>There is no duplicated tweet_id.<jupyter_code>twitter_archive['doggo'].value_counts()
twitter_archive['floofer'].value_counts()
twitter_archive['pupper'].value_counts()
twitter_archive['puppo'].value_counts()<jupyter_output><empty_output><jupyter_text>The columns 'doggo', 'floofer', 'pupper', and 'puppo' only containts its own value and 'None'.<jupyter_code>twitter_archive['rating_numerator'].value_counts()<jupyter_output><empty_output><jupyter_text>Checking tweets with numerator == 0.<jupyter_code># Set max_colwidth so that the context of 'text' column won't be truncated.
pd.set_option('max_colwidth',150)
twitter_archive[twitter_archive['rating_numerator'] == 0]['text']<jupyter_output><empty_output><jupyter_text>0 seem to be a valid numerator. Checking tweets with numerator which are greater than 100.<jupyter_code>twitter_archive[twitter_archive['rating_numerator'] > 100]['text']<jupyter_output><empty_output><jupyter_text>These large numerators seem to be valid also.<jupyter_code>twitter_archive['rating_denominator'].value_counts()<jupyter_output><empty_output><jupyter_text>There is 1 record contains denominator 0.<jupyter_code>twitter_archive[twitter_archive['rating_denominator'] == 0]['text']<jupyter_output><empty_output><jupyter_text>twitter id 835246439529840640 has wrong numerator and denominator. They should be 13/10 instead of 960/0.  `image_predictions` table<jupyter_code>image_predictions.head()<jupyter_output><empty_output><jupyter_text>The format of predicted dog breeds in column p1, p2, and p3 are not consistent. Some are all lower cases, some use dash instead of underscore, etc.'pg_url', 'img_num', 'p1', 'p1_conf', 'p1_dog', 'p2', 'p2_conf', 'p2_dog', 'p3', 'p3_conf', and 'p3_dog' should be part of the twitter_archive table.<jupyter_code>image_predictions.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2075 entries, 0 to 2074
Data columns (total 12 columns):
tweet_id    2075 non-null int64
jpg_url     2075 non-null object
img_num     2075 non-null int64
p1          2075 non-null object
p1_conf     2075 non-null float64
p1_dog      2075 non-null bool
p2          2075 non-null object
p2_conf     2075 non-null float64
p2_dog      2075 non-null bool
p3          2075 non-null object
p3_conf     2075 non-null float64
p3_dog      2075 non-null bool
dtypes: bool(3), float64(3), int64(2), object(4)
memory usage: 152.1+ KB<jupyter_text>There are 2075 entries and 12 columns in image_prediction table. No data is missing.<jupyter_code>sum(image_predictions['tweet_id'].duplicated())<jupyter_output><empty_output><jupyter_text>There is no duplicated tweet_id.<jupyter_code>sum(image_predictions['p1_conf'] > 1) + sum(image_predictions['p2_conf'] > 1) + sum(image_predictions['p3_conf'] > 1)<jupyter_output><empty_output><jupyter_text>Confidence values do not exceed more than 1 for p1, p2, and p3.  `additional_data` table<jupyter_code>additional_data.head()<jupyter_output><empty_output><jupyter_text>'retweet_count' and 'favorite_count' should be part of the twitter_archive table<jupyter_code>additional_data.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2345 entries, 0 to 2344
Data columns (total 3 columns):
tweet_id          2345 non-null int64
retweet_count     2345 non-null int64
favorite_count    2345 non-null int64
dtypes: int64(3)
memory usage: 55.0 KB<jupyter_text>There are 2175 entries and 3 columns in additional_data table.  No is data missing.<jupyter_code>sum(additional_data['tweet_id'].duplicated())<jupyter_output><empty_output><jupyter_text>There is no duplicated tweet_id.<jupyter_code>sum(additional_data['retweet_count'] == 0)
sum(additional_data['favorite_count'] == 0)<jupyter_output><empty_output><jupyter_text>There is 1 tweet which has retweet_count 0 and 170 tweets which have favorite_count 0.They could be valid data as unpopular tweet could have 0 retweet and favorite vote, but further investigation is needed.  Quality  `twitter_archive` table- Dataset contains retweets in addition to original tweets.- 'retweeted_status_id', 'retweeted_status_user_id', and 'retweeted_status_timestamp' columns are not needed.- Missing data : 'in_reply_to_status_id', 'in_reply_to_user_id', and 'expanded_urls'- Source column contains unnecessary and extra information- Name column contains words like 'just', 'the', 'a'.- Some tweets have more than 1 dog life stages.- Erroneous datatypes : 'in_reply_to_status_id' and 'in_reply_to_user_id' are float.- Erroneous datatypes : 'timestamp' is string(object).- Erroneous datatypes : 'doggo', 'floofer', 'pupper', and 'puppo' only containts its own value and 'None'.- Extract numerator and denomintor from text columns and replace 'rating_numerator' and 'rating_denominator' with correct ones.- Text column contains text, numerator, denominator, and short-url.- twitter id 835246439529840640 has wrong numerator and denominator. They should be 13/10 instead of 960/0.- twitter id 786709082849828864 has wrong numerator and denominator. They should be 10/10 instead of 75/10.  `image_predictions` table- The format of predicted dog breeds in column p1, p2, and p3 are not consistent.  Some are all lower cases, some use dash instead of underscore, etc.   `additional_data` table - There is 1 tweet which has retweet_count 0 and 170 tweets which have favorite_count 0. They could be valid data.  Tidiness  `twitter_archive` table- Dog life stage variable is in 4 columns.  `image_predictions` table- 'pg_url', 'img_num', 'p1', 'p1_conf', 'p1_dog', 'p2', 'p2_conf', 'p2_dog', 'p3', 'p3_conf', and 'p3_dog' should be part of the twitter_archive table.  `additional_data` table- 'retweet_count' and 'favorite_count' should be part of the twitter_archive table  Clean Data Prior to cleaning, copies of the original data are made.<jupyter_code># Copy original dataframes to clean versions
twitter_archive_clean = twitter_archive.copy()
image_predictions_clean = image_predictions.copy()
additional_data_clean = additional_data.copy()<jupyter_output><empty_output><jupyter_text>Quality  `twitter_archive` : Dataset contains retweets in addition to original tweets.  DefineRemove retweets since we only want original tweets.   Code<jupyter_code>sum(twitter_archive_clean['retweeted_status_id'].notnull())<jupyter_output><empty_output><jupyter_text>There are 181 retweet entries.<jupyter_code># Excluding retweets since we only want original tweets. 
twitter_archive_clean = twitter_archive_clean[twitter_archive_clean['retweeted_status_id'].isnull()]
# Reset index
twitter_archive_clean = twitter_archive_clean.reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2175 entries, 0 to 2174
Data columns (total 17 columns):
tweet_id                      2175 non-null int64
in_reply_to_status_id         78 non-null float64
in_reply_to_user_id           78 non-null float64
timestamp                     2175 non-null object
source                        2175 non-null object
text                          2175 non-null object
retweeted_status_id           0 non-null float64
retweeted_status_user_id      0 non-null float64
retweeted_status_timestamp    0 non-null object
expanded_urls                 2117 non-null object
rating_numerator              2175 non-null int64
rating_denominator            2175 non-null int64
name                          2175 non-null object
doggo                         2175 non-null object
floofer                       2175 non-null object
pupper                        2175 non-null object
puppo                         2175 non-null object
dtypes: float64(4), int64(3), object(1[...]<jupyter_text>Retweets are dropped. (2356 - 181 = 2175)  Quality  `twitter_archive` : 'retweeted_status_id', 'retweeted_status_user_id', and 'retweeted_status_timestamp' columns are not needed.  DefineDelete 'retweeted_status_id', 'retweeted_status_user_id', and 'retweeted_status_timestamp' columns  Code<jupyter_code>twitter_archive_clean.drop(['retweeted_status_id', 'retweeted_status_user_id', 'retweeted_status_timestamp'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2175 entries, 0 to 2174
Data columns (total 14 columns):
tweet_id                 2175 non-null int64
in_reply_to_status_id    78 non-null float64
in_reply_to_user_id      78 non-null float64
timestamp                2175 non-null object
source                   2175 non-null object
text                     2175 non-null object
expanded_urls            2117 non-null object
rating_numerator         2175 non-null int64
rating_denominator       2175 non-null int64
name                     2175 non-null object
doggo                    2175 non-null object
floofer                  2175 non-null object
pupper                   2175 non-null object
puppo                    2175 non-null object
dtypes: float64(2), int64(3), object(9)
memory usage: 238.0+ KB<jupyter_text>'retweeted_status_id', 'retweeted_status_user_id', and 'retweeted_status_timestamp' are dropped.  Missing Data  `twitter_archive` : Missing data in columns 'in_reply_to_status_id', 'in_reply_to_user_id', and 'expanded_urls'   DefineUnless a tweet has reply, values for 'in_reply_to_status_id' and 'in_reply_to_user_id' columns will not be present. This issue will not be fixed.'extended_url' is missing because it's missing a short-url in text column. There must be a way to get an extended_url from twetter_id.  However, I will not be using 'extended_url' for my analysis nor visualization, so I am going to delete it.  Code<jupyter_code>twitter_archive_clean = twitter_archive_clean.drop('expanded_urls', axis=1)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2175 entries, 0 to 2174
Data columns (total 13 columns):
tweet_id                 2175 non-null int64
in_reply_to_status_id    78 non-null float64
in_reply_to_user_id      78 non-null float64
timestamp                2175 non-null object
source                   2175 non-null object
text                     2175 non-null object
rating_numerator         2175 non-null int64
rating_denominator       2175 non-null int64
name                     2175 non-null object
doggo                    2175 non-null object
floofer                  2175 non-null object
pupper                   2175 non-null object
puppo                    2175 non-null object
dtypes: float64(2), int64(3), object(8)
memory usage: 221.0+ KB<jupyter_text>'extended_url' column is dropped.  Tidiness  `twitter_archive` : Dog life stage variable is in 4 columns.  DefineMelt 'doggo', 'floofer', 'pupper', and 'puppo' columns to a 'life_stage' column. Drop the immediate 'life_stage_category' column.  Code<jupyter_code>twitter_archive_clean = pd.melt(twitter_archive_clean, id_vars=[
    'tweet_id',
    'in_reply_to_status_id',
    'in_reply_to_user_id',
    'timestamp',
    'source',
    'text',
    'rating_numerator',
    'rating_denominator',
    'name'
    ],
    value_vars=['doggo', 'floofer', 'pupper', 'puppo'], var_name='life_stage_category', value_name='life_stage')
twitter_archive_clean.head()
# Drop 'life_stage_category column
twitter_archive_clean = twitter_archive_clean.drop('life_stage_category', axis=1)
# Drop duplicated rows
twitter_archive_clean = twitter_archive_clean.drop_duplicates()
# Subset all duplicated tweet_ids 
duplicated_tweets = twitter_archive_clean[twitter_archive_clean['tweet_id'].duplicated(keep=False)]
# Drop all rows which have 'None' as life_stage from duplicated_tweets
duplicated_tweets = duplicated_tweets[duplicated_tweets['life_stage'] != 'None']<jupyter_output><empty_output><jupyter_text>duplicated_tweets contains tweets with at least 1 life_stages<jupyter_code># Remove all duplicated tweets
twitter_archive_clean = twitter_archive_clean[~twitter_archive_clean['tweet_id'].duplicated(keep=False)]<jupyter_output><empty_output><jupyter_text>twitter_archive_clean now contains tweets without any life_stages.<jupyter_code># Exclude all duplicated tweets from duplicated_tweets as some tweets have more than 1 life_stage
one_life_stage_tweets = duplicated_tweets[~duplicated_tweets['tweet_id'].duplicated(keep=False)]<jupyter_output><empty_output><jupyter_text>one_life_stage_tweets contains tweets with only 1 life_stage.<jupyter_code># Keep all duplicated tweets from duplicated_tweets
multiple_life_stages_tweets = duplicated_tweets[duplicated_tweets['tweet_id'].duplicated(keep=False)]<jupyter_output><empty_output><jupyter_text>multiple_life_stages_tweets contains tweets with more than 1 life stages.<jupyter_code>multiple_life_stages_tweets[multiple_life_stages_tweets['tweet_id'].duplicated()][['tweet_id', 'text']]<jupyter_output><empty_output><jupyter_text>Looking at these 12 tweets, there are multiple tweets talks about 2 dogs. This is why these tweets have more then 1 life stages.  I will assign 'multi' for these tweets.  Others, I will hand pick correct one by investigating text column.<jupyter_code>multiple_life_stages_tweets.loc[multiple_life_stages_tweets['tweet_id'] == 854010172552949760, 'life_stage'] = 'floofer'
multiple_life_stages_tweets.loc[multiple_life_stages_tweets['tweet_id'] == 817777686764523521, 'life_stage'] = 'pupper'
multiple_life_stages_tweets.loc[multiple_life_stages_tweets['tweet_id'] == 808106460588765185, 'life_stage'] = 'multi'
multiple_life_stages_tweets.loc[multiple_life_stages_tweets['tweet_id'] == 802265048156610565, 'life_stage'] = 'multi'
multiple_life_stages_tweets.loc[multiple_life_stages_tweets['tweet_id'] == 801115127852503040, 'life_stage'] = 'pupper'
multiple_life_stages_tweets.loc[multiple_life_stages_tweets['tweet_id'] == 785639753186217984, 'life_stage'] = 'doggo'
multiple_life_stages_tweets.loc[multiple_life_stages_tweets['tweet_id'] == 781308096455073793, 'life_stage'] = 'multi'
multiple_life_stages_tweets.loc[multiple_life_stages_tweets['tweet_id'] == 759793422261743616, 'life_stage'] = 'multi'
multiple_life_stages_tweets.loc[multiple_life_stages_tweets['tweet_id'] == 751583847268179968, 'life_stage'] = 'None'
multiple_life_stages_tweets.loc[multiple_life_stages_tweets['tweet_id'] == 741067306818797568, 'life_stage'] = 'multi'
multiple_life_stages_tweets.loc[multiple_life_stages_tweets['tweet_id'] == 733109485275860992, 'life_stage'] = 'multi'
multiple_life_stages_tweets.loc[multiple_life_stages_tweets['tweet_id'] == 855851453814013952, 'life_stage'] = 'multi'
# Remove duplidates
multiple_life_stages_tweets = multiple_life_stages_tweets[multiple_life_stages_tweets.duplicated()]
multiple_life_stages_tweets<jupyter_output><empty_output><jupyter_text>Correct life stage is assigned for all rows in multiple_life_stages_tweets.Now put everything back together.twitter_archive_clean - tweets without any life stagesone_life_stage_tweets - tweets with 1 life stagemultiple_life_stages_tweets - tweets with more than 1 life stages<jupyter_code># Append all the data back to twitter_archive_clean
twitter_archive_clean = twitter_archive_clean.append(one_life_stage_tweets)
twitter_archive_clean = twitter_archive_clean.append(multiple_life_stages_tweets)

# Reset index
twitter_archive_clean = twitter_archive_clean.reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2175 entries, 0 to 2174
Data columns (total 10 columns):
tweet_id                 2175 non-null int64
in_reply_to_status_id    78 non-null float64
in_reply_to_user_id      78 non-null float64
timestamp                2175 non-null object
source                   2175 non-null object
text                     2175 non-null object
rating_numerator         2175 non-null int64
rating_denominator       2175 non-null int64
name                     2175 non-null object
life_stage               2175 non-null object
dtypes: float64(2), int64(3), object(5)
memory usage: 170.0+ KB<jupyter_text>There are 2175 entries, and no data is missing from life_stage column.  Quality  `twitter_archive` : Source column contains unnecessary and extra information.  DefineIt needs to be cleaned up so that it only contains 'source' informaiton.  Code<jupyter_code>twitter_archive_clean['source'].value_counts()
# Extract hyperlink text
twitter_archive_clean['source'] = twitter_archive_clean['source'].str.extract('>(.*)<\/a>', expand=True)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_clean['source'].value_counts()<jupyter_output><empty_output><jupyter_text>Source column now contains only 'source' information.  Quality  `twitter_archive` : Name column contains words like 'just', 'the', 'a'.  DefineExtract name from text column.  If extracted word doesn't seem like a name, then they need to be changed to 'None'.Here are patterns to find dog name in text column.1. This is Zoey.2. Meet Jax.3. Here is George.4. Say hello to Oakley.  Code Extract name using 4 patterns identified above.<jupyter_code># Extract Name - This is Zoey pattern
extracted_this_is = twitter_archive_clean['text'].str.extract('This is (\w+)', expand=False)
# Extract Name - Meet Jax pattern
extracted_meet = twitter_archive_clean['text'].str.extract('Meet (\w+)', expand=False)
# Extract Name - Here is George pattern
extracted_here = twitter_archive_clean['text'].str.extract('Here is (\w+)', expand=False)
# Extract Name - Say hello to Oakley pattern
extracted_say = twitter_archive_clean['text'].str.extract('Say hello to (\w+)', expand=False)
# Create new dataframe
extracted_word = pd.DataFrame({"this_is": extracted_this_is, "meet": extracted_meet, "here": extracted_here, 'say': extracted_say})
extracted_word.head()
# Replace Nan with None since name column is not numbers
extracted_word = extracted_word.where(extracted_word.notnull(), None)<jupyter_output><empty_output><jupyter_text>Add count to each matched pattern and calculate how many names are found for each tweet.<jupyter_code># ########################################################################
# I was trying to use .apply() to get sum of count,
# but it throws an error, and I couldn't figure out how to fix it.
#
# I'm gonna do it very ugly way.
# Create count for each pattern, using 1 or 1 and just do simple addition
# to create name count,
# -------------------------------------------------------------------------
#def count_name(x):
#    return \
#        sum(x['this_is'].notnull()) + \
#        sum(x['meet'].notnull()) + \
#        sum(x['here'].notnull()) + \
#        sum(x['say'].notnull())
#        
#extracted_word['name_count'] = extracted_word.apply(count_name, axis=1)
#---------------------------------------------------------------------------
# AttributeError: ("'str' object has no attribute 'notnull'", 'occurred at index 0')
# ########################################################################

# Create count for each patter using 0 and 1
extracted_word['this_is_count'] = np.where(extracted_word['this_is'].notnull(), 1, 0)
extracted_word['meet_count'] = np.where(extracted_word['meet'].notnull(), 1, 0)
extracted_word['here_count'] = np.where(extracted_word['here'].notnull(), 1, 0)
extracted_word['say_count'] = np.where(extracted_word['say'].notnull(), 1, 0)

# Add them all to craete name_count
extracted_word['name_count'] = extracted_word['this_is_count'] + \
extracted_word['meet_count'] + extracted_word['here_count'] + extracted_word['say_count']<jupyter_output><empty_output><jupyter_text>Find tweets which had more than 1 name found.<jupyter_code># Find tweets which had more than 1 name found.
extracted_word[extracted_word['name_count'] > 1]<jupyter_output><empty_output><jupyter_text>Manually clean these 2 tweets. Since names found by 'Say hello to ...' pattern is actual name, they should be used as name for these 2 tweets.Replacing name found by 'This is ...' pattern to 'None'.<jupyter_code># Replace 'his' and 'how' in 'this_is' column with None
extracted_word['this_is'] = np.where(extracted_word['this_is'] == 'his', None, extracted_word['this_is'])
extracted_word['this_is'] = np.where(extracted_word['this_is'] == 'how', None, extracted_word['this_is'])
extracted_word[extracted_word['name_count'] > 1]
# drop count columns
extracted_word = extracted_word.drop(['this_is_count', 'meet_count', 'here_count', 'say_count', 'name_count'], axis=1)
extracted_word.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2175 entries, 0 to 2174
Data columns (total 4 columns):
here       13 non-null object
meet       216 non-null object
say        95 non-null object
this_is    1170 non-null object
dtypes: object(4)
memory usage: 68.0+ KB<jupyter_text>count columns are dropped.Create 'name' column from name found by 4 patterns.<jupyter_code># Find name either name extracted from 
# 'This is ..' pattern, 'Meet ..' pattern, 
# 'Here is ..' pattern, or 'Say hello to ..' pattern.
extracted_word['name'] = np.where(extracted_word['this_is'].notnull(), extracted_word['this_is'], extracted_word['meet'])
extracted_word['name'] = np.where(extracted_word['name'].notnull(), extracted_word['name'], extracted_word['here'])
extracted_word['name'] = np.where(extracted_word['name'].notnull(), extracted_word['name'], extracted_word['say'])
extracted_word.sample(10)<jupyter_output><empty_output><jupyter_text>Find name actually not a name (like 'just', 'the', 'a', etc)<jupyter_code># Find name like 'just', 'the', 'a', etc
extracted_word['cap_test'] = extracted_word['name'].str.match('^[A-Z]')
extracted_word[extracted_word['cap_test'] == False].sample(5)<jupyter_output><empty_output><jupyter_text>Replace words (like 'just', 'the', 'a', etc) with 'None'<jupyter_code># Replace words not names with None
extracted_word['name'] = np.where(extracted_word['cap_test'] == False, None, extracted_word['name'])
extracted_word[extracted_word['cap_test'] == False].sample(5)
# None doesn't get written to csv file. Manually assining word 'None'
extracted_word['name'] = np.where(extracted_word['name'].isnull(), 'None', extracted_word['name'])<jupyter_output><empty_output><jupyter_text>Replace name column in twitter_archive_clean with extracted names.<jupyter_code># Replace name column in twitter_archive_clean with 'names in extracted_word
twitter_archive_clean['name'] = extracted_word['name']<jupyter_output><empty_output><jupyter_text>Test Make sure names like 'just' and 'the' are gone.<jupyter_code>twitter_archive_clean[twitter_archive_clean[twitter_archive_clean.notnull()]['text'].str.match('This is just')].name
twitter_archive_clean[twitter_archive_clean[twitter_archive_clean.notnull()]['text'].str.match('This is the')].name<jupyter_output><empty_output><jupyter_text>Quality  `twitter_archive` : Some tweets have more than 1 dog life stages.  DefineThis quality issue was addressed in tidiness issue "Dog life stage variable is in 4 columns" above. No further work needed.  CodeN/A  TestN/A  Quality  `twitter_archive` : Erroneous datatypes : 'in_reply_to_status_id' and 'in_reply_to_user_id' are float.  DefineConvert 'in_reply_to_status_id' and 'in_reply_to_user_id' to integer data type.  Code<jupyter_code>twitter_archive_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2175 entries, 0 to 2174
Data columns (total 10 columns):
tweet_id                 2175 non-null int64
in_reply_to_status_id    78 non-null float64
in_reply_to_user_id      78 non-null float64
timestamp                2175 non-null object
source                   2175 non-null object
text                     2175 non-null object
rating_numerator         2175 non-null int64
rating_denominator       2175 non-null int64
name                     2175 non-null object
life_stage               2175 non-null object
dtypes: float64(2), int64(3), object(5)
memory usage: 170.0+ KB<jupyter_text>Before I can convert data type to int, NaN needs to be replaced with 0 for these columns.<jupyter_code># Replace NaN with 0
twitter_archive_clean['in_reply_to_status_id'].fillna(value=0, inplace=True)
twitter_archive_clean['in_reply_to_user_id'].fillna(value=0, inplace=True)
# Check there is no NaN
sum(twitter_archive_clean['in_reply_to_status_id'].isnull()) + sum(twitter_archive_clean['in_reply_to_user_id'].isnull())<jupyter_output><empty_output><jupyter_text>Convert 'in_reply_to_status_id' and 'in_reply_to_user_id' to integer data type.<jupyter_code># Convert to int data type
twitter_archive_clean['in_reply_to_status_id'] = twitter_archive_clean['in_reply_to_status_id'].astype(int)
twitter_archive_clean['in_reply_to_user_id'] = twitter_archive_clean['in_reply_to_user_id'].astype(int)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2175 entries, 0 to 2174
Data columns (total 10 columns):
tweet_id                 2175 non-null int64
in_reply_to_status_id    2175 non-null int32
in_reply_to_user_id      2175 non-null int32
timestamp                2175 non-null object
source                   2175 non-null object
text                     2175 non-null object
rating_numerator         2175 non-null int64
rating_denominator       2175 non-null int64
name                     2175 non-null object
life_stage               2175 non-null object
dtypes: int32(2), int64(3), object(5)
memory usage: 153.0+ KB<jupyter_text>Data type of 'in_reply_to_status_id' and 'in_reply_to_user_id' is changed to integer.  `twitter_archive` : Erroneous datatypes : 'timestamp' is string(object).  DefineConvert 'timestamp' to datetime data type.   Code<jupyter_code>twitter_archive_clean['timestamp'] = pd.to_datetime(twitter_archive_clean['timestamp'])<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 2175 entries, 0 to 2174
Data columns (total 10 columns):
tweet_id                 2175 non-null int64
in_reply_to_status_id    2175 non-null int32
in_reply_to_user_id      2175 non-null int32
timestamp                2175 non-null datetime64[ns]
source                   2175 non-null object
text                     2175 non-null object
rating_numerator         2175 non-null int64
rating_denominator       2175 non-null int64
name                     2175 non-null object
life_stage               2175 non-null object
dtypes: datetime64[ns](1), int32(2), int64(3), object(4)
memory usage: 153.0+ KB<jupyter_text>Data type of 'timestamp' is changed to datetime.  Quality  `twitter_archive` : Erroneous datatypes : 'doggo', 'floofer', 'pupper', and 'puppo' only containts its own value and 'None'.  DefineThey could have been converted to boolean data types, but this quality issue was addressed in tidiness issue "Dog life stage variable is in 4 columns" above.  Instead of converting these 4 columns to boolean data type, new variable life_style was created. No further cleaning needed.  CodeN/A  TestN/A  Quality  `twitter_archive` : Extract numerator and denomintor from text columns and replace 'rating_numerator' and 'rating_denominator' with correct ones.  DefineAccording to Project Details, 'rating_numerator' and 'rating_denominator' columns might not contain correct values. I'm going to newly extract numerator and denominator from text columns and replace them in 'rating_numerator' and 'rating_denominator' columns.  Code Extract numbers before '/' and after from text column. Some tweets contain more than 1 ratings.<jupyter_code># Extract numbers before '/' and after (\d+)\/(\d+) from text column
extracted = twitter_archive_clean['text'].str.extractall('(\d+)\/(\d+)').unstack()
extracted.head()
# Replace 'None' with NaN since these are numbers
extracted.fillna(value=np.nan, inplace=True)
extracted.head()<jupyter_output><empty_output><jupyter_text>First, find any text which had 3 matched ratings.<jupyter_code>extracted[extracted[0][2].notnull()].count()<jupyter_output><empty_output><jupyter_text>It seems like there is only 1 tweet having 3 matched ratings.<jupyter_code>twitter_archive_clean.iloc[extracted[extracted[0][2].notnull()].index[0]]['tweet_id'], \
twitter_archive_clean.iloc[extracted[extracted[0][2].notnull()].index[0]]['text']<jupyter_output><empty_output><jupyter_text>By investigating the text column, the correct numerator and denominator for tweet_id 672248013293752320 is 10/10.<jupyter_code>twitter_archive_clean.iloc[extracted[extracted[0][2].notnull()].index[0]]['rating_numerator'], \
twitter_archive_clean.iloc[extracted[extracted[0][2].notnull()].index[0]]['rating_denominator']<jupyter_output><empty_output><jupyter_text>Comfirmed that tweet_id 672248013293752320 has the correct numerator and denominator.Now investigating any tweets which had 2 matched ratings.<jupyter_code>extracted[extracted[0][1].notnull()].count()<jupyter_output><empty_output><jupyter_text>There are 30 tweets with 2 matched ratings.<jupyter_code>extracted.head()<jupyter_output><empty_output><jupyter_text>Create new dataframe with current numerator and denominator & 2 matched numerators and denominators for easy comparisons and evlauation.<jupyter_code># Subset tweet_id, text, numerator and denominator from twitter_archive_clean
# for all tweets which had 2 matched pattern.
two_pattern = pd.DataFrame(data = { \
    'tweet_id' : twitter_archive_clean[extracted[0][1].notnull()]['tweet_id'], \
    'text' : twitter_archive_clean[extracted[0][1].notnull()]['text'], \
    'tac_nume' : twitter_archive_clean[extracted[0][1].notnull()]['rating_numerator'], \
    'tac_deno' : twitter_archive_clean[extracted[0][1].notnull()]['rating_denominator']     
})
# Subset 1st and 2nd mattched numerator and denominator.
extracted_two_pattern = extracted[extracted[0][1].notnull()]

extracted_two_pattern_rating = pd.DataFrame(data = { \
    'nume1' : extracted_two_pattern[0][0], \
    'deno1' : extracted_two_pattern[1][0], \
    'nume2' : extracted_two_pattern[0][1], \
    'deno2' : extracted_two_pattern[1][1] \
}) 
# Join them together for new table
two_pattern = two_pattern.join(extracted_two_pattern_rating)
two_pattern.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 30 entries, 45 to 2126
Data columns (total 8 columns):
tac_deno    30 non-null int64
tac_nume    30 non-null int64
text        30 non-null object
tweet_id    30 non-null int64
deno1       30 non-null object
deno2       30 non-null object
nume1       30 non-null object
nume2       30 non-null object
dtypes: int64(3), object(5)
memory usage: 3.4+ KB<jupyter_text>Change data type of deno1, deno2, nume1, nume2 to int<jupyter_code># Change data type to int
two_pattern['deno1'] = two_pattern['deno1'].astype(int)
two_pattern['deno2'] = two_pattern['deno2'].astype(int)
two_pattern['nume1'] = two_pattern['nume1'].astype(int)
two_pattern['nume2'] = two_pattern['nume2'].astype(int)<jupyter_output><empty_output><jupyter_text>Look at new dataframe created.<jupyter_code>two_pattern.head(20)<jupyter_output><empty_output><jupyter_text>By looking at the dataframe above, it seems like there are cases for 2 matched pattern are:- 2 rating for 2 dogs, will keep rating with higher numerator- 1 for dog rating and 1 for date, i.e. 9/11- 1 for dog rating and something else, i.e. 3 1/2 legsIt looks like if I take the rating for higher numerator out of 2, it would work for all cases.Before I do that, I need to drop tweet_id 672248013293752320 (3 matched pattern tweet) from the dataframe as I already determined it has the correct numerator and denominator.<jupyter_code># Removing tweets 672248013293752320
two_pattern = two_pattern[(two_pattern['tweet_id'] != 672248013293752320)]<jupyter_output><empty_output><jupyter_text>Determine which of 2 ratings have higher numerator and replace that with current numerator and denominator.<jupyter_code># Function to pick rating with higher numerator and assign numerator and denominator.
def assign_correct_rating(x):
    if x['nume1'] > x['nume2']:
        nume = x['nume1']
        deno = x['deno1']
    else:
        nume = x['nume2']
        deno = x['deno2']
 
    twitter_archive_clean.loc[twitter_archive_clean['tweet_id'] == x['tweet_id'], 'rating_numerator'] = nume
    twitter_archive_clean.loc[twitter_archive_clean['tweet_id'] == x['tweet_id'], 'rating_denominator'] = deno  

two_pattern.apply(assign_correct_rating, axis=1)<jupyter_output><empty_output><jupyter_text>Test to see if correct rating has been assigned.<jupyter_code># Testing - check if value is changed from 5/10 to 10/10 for tweet_id 691483041324204033
twitter_archive_clean[twitter_archive_clean['tweet_id'] == 691483041324204033]<jupyter_output><empty_output><jupyter_text>Now, I am going to replace numerator and denominator for tweet matching only 1 rating.<jupyter_code>one_pattern = extracted[extracted[0][1].isnull()]
one_pattern.head()<jupyter_output><empty_output><jupyter_text>Replace extracted numerator and denominator with current numerator and denominator.<jupyter_code>twitter_archive_clean.loc[extracted[0][1].isnull(), 'rating_numerator'] = one_pattern[0][0].astype(int)
twitter_archive_clean.loc[extracted[0][1].isnull(), 'rating_denominator'] = one_pattern[1][0].astype(int)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code># Make sure rating in text column matches numerator and denominator columns.
twitter_archive_clean.sample(10)<jupyter_output><empty_output><jupyter_text>Rating in text column matches numerator and denominator columns.  Quality  `twitter_archive` :  Text column contains text, numerator, denominator, and short-url.  DefineName, numerator, and denominator are extracted in previous sections. I will not be using other variables (like short-url) in text column for analysis and visualzation. No further work needed.   CodeN/A  TestN/A  Quality  `twitter_archive` : twitter id 835246439529840640 has wrong numerator and denominator. They should be 13/10 instead of 960/0.  DefineReplace numerator and denominator for row in the twitter_archive table that have numerator 960 and denominator 0 (there is only one) with 13 and 10, respectively.  Code<jupyter_code>twitter_archive_clean[twitter_archive_clean['rating_numerator'] == 960]<jupyter_output><empty_output><jupyter_text>Replace numerator 960 and denominator 0 (there is only one) with 13 and 10.<jupyter_code># Replace numerator with 13
twitter_archive_clean['rating_numerator'] = twitter_archive_clean['rating_numerator'].replace(960, 13)
# Replace denominator with 10
twitter_archive_clean['rating_denominator'] = twitter_archive_clean['rating_denominator'].replace(0, 10)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_clean[twitter_archive_clean['tweet_id'] == 835246439529840640]<jupyter_output><empty_output><jupyter_text>Correct numerator and denominator assigned.  Quality  `twitter_archive` : twitter id 786709082849828864 has wrong numerator and denominator. They should be 10/10 instead of 75/10.  DefineReplace numerator and denominator for row in the twitter_archive table that have numerator 75 with 10.The actual value for numerator is 9.75, but I am rounding it up to 10.  Code<jupyter_code>twitter_archive_clean[twitter_archive_clean['tweet_id'] == 786709082849828864]<jupyter_output><empty_output><jupyter_text>Replace numerator with 10<jupyter_code># Replace numerator with 10
twitter_archive_clean.loc[twitter_archive_clean['tweet_id'] == 786709082849828864, 'rating_numerator'] = 10<jupyter_output><empty_output><jupyter_text>test<jupyter_code>twitter_archive_clean[twitter_archive_clean['tweet_id'] == 786709082849828864]<jupyter_output><empty_output><jupyter_text>Correct numerator and denominator assigned.  Quality  `image_predictions` : The format of predicted dog breeds in column p1, p2, and p3 are not consistent. Some are camelcases, some are lowercases, some use dash instead of underscore, etc.  DefineChange predicted image (p1, p2, p3) to all lower cases and change underscore to dash.  Code<jupyter_code>image_predictions_clean.head()<jupyter_output><empty_output><jupyter_text>Change predicted image (p1, p2, p3) to all lower cases and change underscore to dash.<jupyter_code># Convert to lower cases
image_predictions_clean['p1'] = image_predictions_clean['p1'].str.lower()
image_predictions_clean['p2'] = image_predictions_clean['p2'].str.lower()
image_predictions_clean['p3'] = image_predictions_clean['p3'].str.lower()
# Replace underscore with dash
image_predictions_clean['p1'] = image_predictions_clean['p1'].str.replace('-', '_')
image_predictions_clean['p2'] = image_predictions_clean['p2'].str.replace('-', '_')
image_predictions_clean['p3'] = image_predictions_clean['p3'].str.replace('-', '_')<jupyter_output><empty_output><jupyter_text>Test<jupyter_code># make sure there are no more capital letters and dashes
image_predictions_clean[image_predictions_clean['tweet_id'] == 668567822092664832]
# make sure there are no more capital letters and dashes
image_predictions_clean[image_predictions_clean['tweet_id'] == 667801013445750784]<jupyter_output><empty_output><jupyter_text>There are no more capital letters and dashes.  Tidiness  `image_predictions` : 'pg_url', 'img_num', 'p1', 'p1_conf', 'p1_dog', 'p2', 'p2_conf', 'p2_dog', 'p3', 'p3_conf', and 'p3_dog' should be part of the twitter_archive table.  DefineMerge 'pg_url', 'img_num', 'p1', 'p1_conf', 'p1_dog', 'p2', 'p2_conf', 'p2_dog', 'p3', 'p3_conf', and 'p3_dog'  to the twitter_archive table, joining on tweet_id.  Code Merge twitter_archive_clean and image_prediction_clean tables.<jupyter_code># Merge twitter_archive_clean and image_prediction_clean.
# Using inner join to filter out tweets which do not have image predictions.
twitter_archive_clean = pd.merge(twitter_archive_clean, image_predictions_clean,
                            on = 'tweet_id', how = 'inner')<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 1994 entries, 0 to 1993
Data columns (total 21 columns):
tweet_id                 1994 non-null int64
in_reply_to_status_id    1994 non-null int32
in_reply_to_user_id      1994 non-null int32
timestamp                1994 non-null datetime64[ns]
source                   1994 non-null object
text                     1994 non-null object
rating_numerator         1994 non-null int64
rating_denominator       1994 non-null int64
name                     1994 non-null object
life_stage               1994 non-null object
jpg_url                  1994 non-null object
img_num                  1994 non-null int64
p1                       1994 non-null object
p1_conf                  1994 non-null float64
p1_dog                   1994 non-null bool
p2                       1994 non-null object
p2_conf                  1994 non-null float64
p2_dog                   1994 non-null bool
p3                       1994 non-null object
p3_conf            [...]<jupyter_text>twitter_archive_clean and image_prediction_clean tables are successfully merged.  Tidiness  `additional_data` :  'retweet_count' and 'favorite_count' should be part of the twitter_archive table  DefineMerge  'retweet_count' and 'favorite_count' columns to the twitter_archive table, joining on tweet_id.  Code Merge twitter_archive_clean and additional_data_clean tables.<jupyter_code># Merge twitter_archive_clean and additional_data_clean.
twitter_archive_clean = pd.merge(twitter_archive_clean, additional_data_clean,
                            on = 'tweet_id', how = 'inner')<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>twitter_archive_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 1994 entries, 0 to 1993
Data columns (total 23 columns):
tweet_id                 1994 non-null int64
in_reply_to_status_id    1994 non-null int32
in_reply_to_user_id      1994 non-null int32
timestamp                1994 non-null datetime64[ns]
source                   1994 non-null object
text                     1994 non-null object
rating_numerator         1994 non-null int64
rating_denominator       1994 non-null int64
name                     1994 non-null object
life_stage               1994 non-null object
jpg_url                  1994 non-null object
img_num                  1994 non-null int64
p1                       1994 non-null object
p1_conf                  1994 non-null float64
p1_dog                   1994 non-null bool
p2                       1994 non-null object
p2_conf                  1994 non-null float64
p2_dog                   1994 non-null bool
p3                       1994 non-null object
p3_conf            [...]<jupyter_text>twitter_archive_clean and additional_data_clean tables are sucessfully merged.  Quality  `additional-data` : There is 1 tweet which has retweet_count 0 and 170 tweets which have favorite_count 0.  DefineSince unpopular tweet could have 0 retweet and favorite vote, this could just be valid data. But I will look at them and make sure there are not issues.  Code<jupyter_code>twitter_archive_clean[twitter_archive_clean['retweet_count'] == 0]
twitter_archive_clean[twitter_archive_clean['favorite_count'] == 0]<jupyter_output><empty_output><jupyter_text>As it turns out tweets with retweet_count == 0 and favorite_count == 0 were removed as retweet data was deleted.  No further work needed.  TestN/A  Save Cleaned DataSave cleaned data to 'twitter_archive_master.csv' for analysis and visualization.<jupyter_code>twitter_archive_clean.to_csv('data/twitter_archive_master.csv', index=False)<jupyter_output><empty_output><jupyter_text>Analyze DataLoad data from twitter_archive_master.csv<jupyter_code>twitter_archive_master = pd.read_csv('data/twitter_archive_master.csv')<jupyter_output><empty_output><jupyter_text>After I looked at twitter_archive_master in Microsoft excel file, I came up with 3 questions I would like to explore.1. How good the image prediction algorithm was to predict dog images?2. Are top breeds of dogs associated with rating?3. Which variables are associated with retweets and favorite counts?  How good the image prediction algorithm was to predict dog images?<jupyter_code>twitter_archive_master.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 1994 entries, 0 to 1993
Data columns (total 23 columns):
tweet_id                 1994 non-null int64
in_reply_to_status_id    1994 non-null int64
in_reply_to_user_id      1994 non-null int64
timestamp                1994 non-null object
source                   1994 non-null object
text                     1994 non-null object
rating_numerator         1994 non-null int64
rating_denominator       1994 non-null int64
name                     1994 non-null object
life_stage               1994 non-null object
jpg_url                  1994 non-null object
img_num                  1994 non-null int64
p1                       1994 non-null object
p1_conf                  1994 non-null float64
p1_dog                   1994 non-null bool
p2                       1994 non-null object
p2_conf                  1994 non-null float64
p2_dog                   1994 non-null bool
p3                       1994 non-null object
p3_conf                  19[...]<jupyter_text>First I want to look how many or what percentage of images were correctly predicted with 1st prediction.<jupyter_code>p1_mask = twitter_archive_master['p1_dog'] == True
p1_predicted = twitter_archive_master[p1_mask]
len(p1_predicted)
len(p1_predicted) / len(twitter_archive_master)<jupyter_output><empty_output><jupyter_text>The image prediction algorithm correctly got 74.07% of tweet images (1447 out of 1994) with 1st prediction.<jupyter_code>p1_predicted['p1_conf'].describe()<jupyter_output><empty_output><jupyter_text>Mean p1 confidence is 61.44%, 25th percentile is 39.21%, and 75th percentile 85.32%. Min is 4.43%, and max is 100%.<jupyter_code># Storing prediction number and dog breed
twitter_archive_master['predicted'] = np.where(p1_mask, 'p1', 'no')
twitter_archive_master['predicted_dog'] = np.where(p1_mask, twitter_archive_master['p1'], 'None')<jupyter_output><empty_output><jupyter_text>Now looking at how many or what percentage of images were correctly predicted with 2nd prediction.<jupyter_code>p2_mask = (twitter_archive_master['p1_dog'] == False) & (twitter_archive_master['p2_dog'] == True)
p2_predicted = twitter_archive_master[p2_mask]
len(p2_predicted)
len(p2_predicted) / len(twitter_archive_master)<jupyter_output><empty_output><jupyter_text>The image prediction algorithm correctly got 7.37% of tweet images (147 out of 1994) with 2nd prediction.<jupyter_code>p2_predicted['p2_conf'].describe()<jupyter_output><empty_output><jupyter_text>Mean p2 confidence is 11.31%, 25th percentile is 49.72%, and 75th percentile 16.02%. Min is 0%, and max is 43.06%.<jupyter_code># Storing prediction number and dog breed
twitter_archive_master['predicted'] = np.where(p2_mask, 'p2', twitter_archive_master['predicted'])
twitter_archive_master['predicted_dog'] = np.where(p2_mask, twitter_archive_master['p2'], twitter_archive_master['predicted_dog'])<jupyter_output><empty_output><jupyter_text>Next, I want to look how many or what percentage of images were correctly predicted with 3rd prediction.<jupyter_code>p3_mask = (twitter_archive_master['p1_dog'] == False) & (twitter_archive_master['p2_dog'] == False) & (twitter_archive_master['p3_dog'] == True)

p3_predicted = twitter_archive_master[p3_mask] 
len(p3_predicted)
len(p3_predicted) / len(twitter_archive_master)<jupyter_output><empty_output><jupyter_text>The image prediction algorithm correctly got 3.11% of tweet images (62 out of 1994) with 3rd prediction.<jupyter_code>p3_predicted['p3_conf'].describe()<jupyter_output><empty_output><jupyter_text>Mean p3 confidence is 5.1%, 25th percentile is 1.91%, and 75th percentile 8.21%. Min is 0%, and max is 20.32%.<jupyter_code># Storing prediction number and dog breed
twitter_archive_master['predicted'] = np.where(p3_mask, 'p3', twitter_archive_master['predicted'])
twitter_archive_master['predicted_dog'] = np.where(p3_mask, twitter_archive_master['p3'], twitter_archive_master['predicted_dog'])<jupyter_output><empty_output><jupyter_text>Lastly, I want to look how many or what percentage of images were not predicted.<jupyter_code>no_prediction = \
twitter_archive_master[(twitter_archive_master['p1_dog'] == False) & \
                       (twitter_archive_master['p2_dog'] == False) & \
                       (twitter_archive_master['p3_dog'] == False)] 
len(no_prediction)
len(no_prediction) / len(twitter_archive_master)<jupyter_output><empty_output><jupyter_text>The image prediction algorithm wasn't able to predict 15.45% of tweet images (308 out of 1994).  FindingsThe image prediction algorithm was able to predict 84.55% of dog images.Breakdowns:1st prediction: The prediction algorithm got 74.07% of tweet images (1447 out of 1994) with 61.44% of mean confidence.2st prediction: The prediction algorithm got 7.37% of tweet images (147 out of 1994) with 11.31% of mean confidence.3st prediction: The prediction algorithm got 3.11% of tweet images (62 out of 1994) with 5.1% of mean confidence.no  prediction: The prediction algorithm wasn't able to predict 15.45% of tweet images (308 out of 1994).  Are top breeds of dogs associated with rating? Calculate rating from numerator and demominator and explore the ratings.<jupyter_code># Calculate and create 'rating' from 'rating_numerator' and 'rating _denominator'
twitter_archive_master['rating'] = twitter_archive_master['rating_numerator']/twitter_archive_master['rating_denominator']
twitter_archive_master['rating'].describe()
twitter_archive_master['rating'].value_counts()
twitter_archive_master[twitter_archive_master['rating'] > 1.4]
len(twitter_archive_master[twitter_archive_master['rating'] < 1.0]) / len(twitter_archive_master['rating'])<jupyter_output><empty_output><jupyter_text>Mean rating is 1.17, 25th percentile is 1.00, and 75th percentile 1.20.  Min is 0, and max is 177.60. Majority of ratings are under 1.40, but there are some extreme ratings (like 177.60 and 42.00).Only 5 tweets have rating more han 1.40.21% of tweets have rating under 1.00.<jupyter_code>len(twitter_archive_master['predicted_dog'].value_counts())<jupyter_output><empty_output><jupyter_text>There are 113 dog breeds. ('None' as one of values in 'predicted_dogs')I will pick top 10 dogs to do the analysis.<jupyter_code>twitter_archive_master['predicted_dog'].value_counts()[1:11]
# Get 10 top dog breed
# 'None' has most number of entries, so I'm excluding the first value.
top_breeds = twitter_archive_master['predicted_dog'].value_counts()[1:11].index

# Filtering out tweets which do not match with breeds in top_breed list
twitter_archive_master_top_breeds = \
    twitter_archive_master[twitter_archive_master['predicted_dog'].isin(top_breeds.tolist())]
twitter_archive_master_top_breeds['rating'].describe()<jupyter_output><empty_output><jupyter_text>Removing max rating value as it is an outlier.<jupyter_code>twitter_archive_master_top_breeds.drop(twitter_archive_master_top_breeds[twitter_archive_master_top_breeds['rating'] > 3.0].index, inplace=True)
fig = plt.figure(figsize=(15,8))

ax = sns.boxplot(data=twitter_archive_master_top_breeds, x='predicted_dog', y='rating', palette="Spectral")
ax.set_title('Top 10 Dog Breeds vs Ratings', fontsize=18)
ax.set_xlabel('Dog Breeds', fontsize=14)
ax.set_ylabel('Ratings', fontsize=14)<jupyter_output><empty_output><jupyter_text>FindingsMajority of ratings are under 1.40, but there are some extreme ratings (like 177.60 and 42.00).Only 5 tweets have rating more than 1.40.  21% of tweets have rating under 1.00.There are 113 dog breeds in the data set. I've used 10 top dog breeds to do the analysis.Samoyed has higher median and higher interquartile range and pug has lower median and lower interquartile range than the rest of top 10 breed dogs.Median rating of all top 10 dog breeds are between 1.0 and 1.2.It doesn't seem like dog breeds have any correlation with rating.  Which variables are associated with retweets and favorite counts?<jupyter_code>twitter_archive_master['retweet_count'].describe()<jupyter_output><empty_output><jupyter_text>Mean retweet_count is 2714.04, 25th percentile is 607.00, and 75th percentile 3119.50. Min is 1994.00, and max is 77281.00.<jupyter_code>twitter_archive_master['favorite_count'].describe()<jupyter_output><empty_output><jupyter_text>Mean retweet_count is 8842.77, 25th percentile is 1924.25, and 75th percentile 11137.25. Min is 80.00, and max is 143268.00.First I'd like to see if retweet counts and favorite counts are correlaed with each other. Are retweet counts go up as favorite counts go up, vice versa?<jupyter_code># Scatter plot for retweet counts vs favorite counts with trendline
fig = plt.figure(figsize=(8,5))

ax = sns.regplot(
    x='retweet_count',
    y='favorite_count',
    data=twitter_archive_master
)
ax.set_title('Retweet Counts vs Favorite Counts', fontsize=18)
ax.set_xlabel('Retweet Counts', fontsize=14)
ax.set_ylabel('Favorite Counts', fontsize=14)
# Linear regression model for retweet_count and favorite_count
twitter_archive_master['intercept'] = 1

lm = sms.OLS(twitter_archive_master['retweet_count'], twitter_archive_master[['intercept', 'favorite_count']])
results = lm.fit()
results.summary()<jupyter_output><empty_output><jupyter_text>As I suspected, favorite_count goes up when retweet_count goes up, vise versa. The plot shows there is strong increaseing trendline also.Next, I want to took at rating vs retweet counts and favorites counts.  Is higher rating tweets get more retweet_count and favorite counts?Before I do that, I want to remove couple of outliners from rating.<jupyter_code># Remove outliers from rating.
twitter_archive_master_no_outliers = twitter_archive_master[twitter_archive_master['rating'] < 40]
# Scatter plot for rating vs retweet counts/favorite counts with trendline
fig, (ax1, ax2) = plt.subplots(1,2,figsize=(16,5))

sns.regplot(
    x='retweet_count',
    y='rating',
    data=twitter_archive_master_no_outliers,
    ax=ax1
)
ax1.set_title('Ratings vs Retweet Counts', fontsize=18)
ax1.set_xlabel('Retweet Counts', fontsize=14)
ax1.set_ylabel('Ratings', fontsize=14)

sns.regplot(
    x='favorite_count',
    y='rating',
    data=twitter_archive_master_no_outliers,
    ax=ax2
)
ax2.set_title('Ratings vs Favorite Counts', fontsize=18)
ax2.set_xlabel('Favorite Counts', fontsize=14)
ax2.set_ylabel('Ratings', fontsize=14)<jupyter_output><empty_output><jupyter_text>In the plots above, both retweet and favorite counts have increasing trendline indicating there is positive correlation to ratings.Next, I will explore dog breeds.  Do certain dog breeds get more retweet and favorite counts?I will use top 10 dog breeds I determined previously to do this analysis.Removing some outliers from retweet counts.<jupyter_code># Remove outliers from retweet_count
twitter_archive_master_tb_rc = twitter_archive_master_top_breeds[twitter_archive_master_top_breeds['retweet_count'] <= 10000]
# Boxplot for top 10 dog breeds vs retweet counts
fig = plt.figure(figsize=(15,8))

ax1 = sns.boxplot(data=twitter_archive_master_tb_rc, x='predicted_dog', y='retweet_count', palette="Spectral")
ax1.set_title('Top 10 Dog Breeds vs Retweet Counts', fontsize=18)
ax1.set_xlabel('Dog Breeds', fontsize=14)
ax1.set_ylabel('Retweet Counts', fontsize=14)<jupyter_output><empty_output><jupyter_text>The difference is not significant, but samoyed seems to get most retweet counts, and pug and pomeranian tend to get least retweet counts out of all top 10 dog breeds.Removing some outliers from favorite counts.<jupyter_code># Remove outliers from favorite_counts
twitter_archive_master_tb_fc = twitter_archive_master_top_breeds[twitter_archive_master_top_breeds['favorite_count'] <= 20000]
# Boxplot for top 10 dog breeds vs favorite counts
fig = plt.figure(figsize=(15,8))

ax2 = sns.boxplot(data=twitter_archive_master_tb_fc, x='predicted_dog', y='favorite_count', palette="Spectral")
ax2.set_title('Top 10 Dog Breeds vs Favorite Counts', fontsize=18)
ax2.set_xlabel('Dog Breeds', fontsize=14)
ax2.set_ylabel('Favorite Counts', fontsize=14)<jupyter_output><empty_output><jupyter_text>Golden retriever and labrador retriever seem to get most favorite counts, and pug and chihuahua tend to get least retweet counts out of all top 10 dog breeds.For dog breeds, I didn't find any siginificat relationship between dog breeds and retweet and favorite counts.Lastly, I want to look if life stage of dogs has some assiciation to retweet and favorite counts.I will filter out tweets which do not have dog life stages.<jupyter_code># Remove tweets which do not have life_stage
twitter_archive_master_life_stages = twitter_archive_master[twitter_archive_master['life_stage'] != 'None']<jupyter_output><empty_output><jupyter_text>Removing outliers from retweet counts.<jupyter_code># Remove outliers from retweet_counts
twitter_archive_master_life_stages_rc = twitter_archive_master_life_stages[twitter_archive_master_life_stages['retweet_count'] <= 20000]
# Boxplot for life stages vs retweet counts
fig = plt.figure(figsize=(15,8))

ax1 = sns.boxplot(data=twitter_archive_master_life_stages_rc, x='life_stage', y='retweet_count', palette="Spectral")
ax1.set_title('Life Stages vs Retweet Counts', fontsize=18)
ax1.set_xlabel('Life Stages', fontsize=14)
ax1.set_ylabel('Retweet Counts', fontsize=14)<jupyter_output><empty_output><jupyter_text>Except pupper, median of all other life stages have about the same median. It seems like pupper gets lower retweet counts compare to other life stages.The multi life stages have large interquatile range.  There very little number of tweets which has life stage 'multi', 6 to be exact. So I would guess the range of these values are wide.Removing outliners from favorite counts.<jupyter_code># Remove outliers from favorite_count
twitter_archive_master_life_stages_fc = twitter_archive_master_life_stages[twitter_archive_master_life_stages['favorite_count'] <= 20000]
# Boxplot for life stages vs favorite counts
fig = plt.figure(figsize=(15,8))

ax2 = sns.boxplot(data=twitter_archive_master_life_stages_fc, x='life_stage', y='favorite_count', palette="Spectral")
ax2.set_title('Life Stages vs Favorite Counts', fontsize=18)
ax2.set_xlabel('Life Stages', fontsize=14)
ax2.set_ylabel('Favorite Counts', fontsize=14)<jupyter_output><empty_output><jupyter_text>There are not significat difference in median of life stages of dogs except pupper. The pupper tends to get lower favorite counts.Now, putting everything together to fit multiple linear regression model and see if I can find out which data are associated to retweet counts and favorite counts.<jupyter_code># Get dummies for dog breeds and life stages
# Using twitter_archive_master_top_breeds since it only contains tweets with top 10 dog breeds
dummy_df = pd.get_dummies(twitter_archive_master_top_breeds[['predicted_dog', 'life_stage']], drop_first = True)   

# Join dummies to twitter_master_archive
twitter_archive_master_OLS = twitter_archive_master_top_breeds.join(dummy_df)
# Multiple linear regression for retweet counts
twitter_archive_master_OLS['intercept'] = 1

lm = sms.OLS(twitter_archive_master_OLS['retweet_count'], \
             twitter_archive_master_OLS[['intercept', 'rating', 'predicted_dog_chow', 'predicted_dog_golden_retriever', \
                                         'predicted_dog_labrador_retriever', 'predicted_dog_malamute', \
                                         'predicted_dog_pembroke', 'predicted_dog_pomeranian', 'predicted_dog_pug', \
                                         'predicted_dog_samoyed', 'predicted_dog_toy_poodle', 'life_stage_doggo', \
                                         'life_stage_floofer', 'life_stage_multi', 'life_stage_pupper', 'life_stage_puppo']]) 
results = lm.fit()
results.summary()
# Multiple linear regression for favorite counts
twitter_archive_master_OLS['intercept'] = 1

lm = sms.OLS(twitter_archive_master_OLS['favorite_count'], \
             twitter_archive_master_OLS[['intercept', 'rating', 'predicted_dog_chow', 'predicted_dog_golden_retriever', \
                                         'predicted_dog_labrador_retriever', 'predicted_dog_malamute', \
                                         'predicted_dog_pembroke', 'predicted_dog_pomeranian', 'predicted_dog_pug', \
                                         'predicted_dog_samoyed', 'predicted_dog_toy_poodle', 'life_stage_doggo', \
                                         'life_stage_floofer', 'life_stage_multi', 'life_stage_pupper', 'life_stage_puppo']]) 
results = lm.fit()
results.summary()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09424616337181019, 'fraction_numerical': 0.04289947370748751, 'mean_word_length': 5.028617134731603, 'pattern_counts': {'":': 3, '<': 469, '<?xml version=': 0, '>': 471, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 16, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '114', 'dir': 'jupyter-structured-clean-dedup', 'id': '441340', 'license': 'MIT', 'n_tokens_mistral': 23372, 'n_tokens_neox': 21050, 'n_words': 5722, 'path': '06-twitter-data-analysis/twitter-data-analysis.ipynb', 'repo_name': 'ayumiohashi/udacity-data-analyst', 'size': '67833'}