Introduction¶
We wrangle and analyze the WeRateDogs data on Twitter to find interesting insights. We get some of the data from the WeRateDogs Twitter archive and download additional data using the Twitter API based on the tweets that are already available in the archive.
We only work with Tweets that have images attached to it and are not retweets to do the analysis.
Data Wrangling¶
Gather¶
In [1]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import requests
import tweepy
import json
import re
pd.set_option('display.max_colwidth', None)
In [2]:
# WeRateDogs Twitter Archive
# load the WeRateDogs Twitter Archive provided
archive_df = pd.read_csv('twitter-archive-enhanced.csv')
In [3]:
# Predicted Images From Neural Network
# download Image Predictions
images_url = 'https://d17h27t6h515a5.cloudfront.net/topher/2017/August/599fd2ad_image-predictions/image-predictions.tsv'
response = requests.get(images_url)
# # write response to file
with open('image_predictions.tsv', mode='wb') as file:
file.write(response.content)
# Load Predicted images into a dataframe
image_predictions_df = pd.read_csv('image_predictions.tsv', sep='\t')
In [4]:
# # Download additional data from twitter using the Twitter API
# # list of tweet ids
tweet_ids = archive_df['tweet_id'].values
# # initialize twitter api object
consumer_key = 'HIDDEN'
consumer_secret = 'HIDDEN'
access_token = 'HIDDEN'
access_token_secret = 'HIDDEN'
auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)
api = tweepy.API(auth_handler=auth, wait_on_rate_limit=True, wait_on_rate_limit_notify=True)
# # download tweet data and write to file
failed_ids = []
with open('tweet_json.txt', 'w') as file:
for id in tweet_ids:
try:
tweet = api.get_status(id, tweet_mode='extended')
json.dump(tweet._json, file)
file.write('\n')
except tweepy.TweepError as e:
print(e)
failed_ids.append(id)
pass
# read and load downloaded tweet data into a dataframe
tweet_data = []
with open('tweet_json.txt') as file:
for line in file:
tweet_data.append(json.loads(line))
tweet_df = pd.DataFrame(tweet_data)
Assess¶
In [5]:
archive_df.shape
Out[5]:
In [6]:
archive_df.head()
Out[6]:
In [7]:
archive_df.tail()
Out[7]:
In [8]:
archive_df.sample(5)
Out[8]:
In [9]:
# Source containing 2 variables i.e the source_name and the source_link
archive_df.source.head(1)
Out[9]:
In [10]:
# Duplicates in expanded_urls
archive_df['expanded_urls'].sample(15)
Out[10]:
In [11]:
# Tweets with duplicates in expanded_urls
archive_df.loc[1578].expanded_urls
Out[11]:
In [12]:
# Tweets without images. All tweets with an NaN value for expanded_urls were found to be without images.
archive_df[archive_df['expanded_urls'].isnull()]
Out[12]:
In [13]:
# Non-original tweets, Retweets
archive_df[archive_df['text'].str.contains('RT @', flags=re.IGNORECASE, case=False)].head()
Out[13]:
In [14]:
# Non-original tweets, PUPDATE(s)
archive_df[archive_df['text'].str.contains('PUPDATE', flags=re.IGNORECASE, case=False)]
Out[14]:
In [15]:
archive_df.name.value_counts()
Out[15]:
In [16]:
# Dogs whose names were not captured but assigned a value of 'None'
archive_df.query('name == "None"')[['tweet_id','text','name']].sample(15)
Out[16]:
In [17]:
# Dog names that were captured as 'a'
archive_df.query('name == "a" ')[['tweet_id','text','name']]
Out[17]:
In [18]:
archive_df.info()
In [19]:
archive_df.describe()
Out[19]:
In [20]:
# doggo stage
archive_df[archive_df['text'].str.contains('doggo',flags=re.IGNORECASE, case=False)].shape[0]
Out[20]:
In [21]:
# pupper stage
archive_df[archive_df['text'].str.contains('pupper',flags=re.IGNORECASE, case=False)].shape[0]
Out[21]:
In [22]:
# puppo stage
archive_df[archive_df['text'].str.contains('puppo',flags=re.IGNORECASE, case=False)].shape[0]
Out[22]:
In [23]:
# floofer stage
archive_df[archive_df['text'].str.contains('floofer',flags=re.IGNORECASE, case=False)].shape[0]
Out[23]:
In [24]:
# floof stage
archive_df[archive_df['text'].str.contains('floof',flags=re.IGNORECASE, case=False)].shape[0]
Out[24]:
In [25]:
# blep stage
archive_df[archive_df['text'].str.contains('blep',flags=re.IGNORECASE, case=False)].shape[0]
Out[25]:
In [26]:
# snoot stage
archive_df[archive_df['text'].str.contains('snoot',flags=re.IGNORECASE, case=False)].shape[0]
Out[26]:
In [27]:
archive_df.name.value_counts()
Out[27]:
In [28]:
# Tweets with a name of 'a'
a_names = archive_df.query('name == "a"')[['tweet_id','text','name']]
In [29]:
a_names.shape
Out[29]:
In [30]:
# Names that were not captured but begin with named
a_names[a_names['text'].str.contains('named',flags=re.IGNORECASE, case=False)]
Out[30]:
In [31]:
# Names that were not captured but begin with named
a_names[a_names['text'].str.contains('name is', case=False, flags=re.IGNORECASE)]
Out[31]:
In [32]:
# Names that were not captured but begin with 'meet'
a_names[a_names['text'].str.contains('meet', case=False, flags=re.IGNORECASE)]
Out[32]:
In [33]:
# Names that were not captured but begin 'Say hello to'
a_names[a_names['text'].str.contains('hello to', case=False, flags=re.IGNORECASE)]
Out[33]:
In [34]:
# Tweets with a name of 'None'
none_names = archive_df.query('name == "None"')[['tweet_id','text','name']]
In [35]:
none_names.shape
Out[35]:
In [36]:
# Names that have a value of 'None' but were not captured and they begin with 'named'
none_names[none_names['text'].str.contains('named', case=False, flags=re.IGNORECASE)]
Out[36]:
In [37]:
# Names that have a value of 'None' but were not captured and they begin with 'name is'
none_names[none_names['text'].str.contains('name is', case=False, flags=re.IGNORECASE)]
Out[37]:
In [38]:
# Names that have a value of 'None' but were not captured and they begin with 'meet'
none_names[none_names['text'].str.contains('meet', case=False, flags=re.IGNORECASE)]
Out[38]:
In [39]:
# Names that have a value of 'None' but were not captured and they begin with 'meet'
none_names[none_names['text'].str.contains('Say hello to', case=False, flags=re.IGNORECASE)]
Out[39]:
In [40]:
# Names that have a value of 'None' but were not captured and they begin with 'This is'
none_names[none_names['text'].str.contains('This is', case=False, flags=re.IGNORECASE)]
Out[40]:
In [41]:
image_predictions_df.shape
Out[41]:
In [42]:
image_predictions_df.head()
Out[42]:
In [43]:
image_predictions_df.sample(5)
Out[43]:
In [44]:
image_predictions_df.info()
In [45]:
image_predictions_df.describe()
Out[45]:
In [46]:
tweet_df.shape
Out[46]:
In [47]:
tweet_df.head()
Out[47]:
In [48]:
tweet_df.info()
In [49]:
tweet_df.describe()
Out[49]:
In [50]:
all_columns = pd.Series(list(tweet_df) + list(archive_df) + list(image_predictions_df))
all_columns[all_columns.duplicated()]
Out[50]:
Issues¶
Quality¶
twitter archive table
- Retweets
- Tweet Updates
- Duplicates in
expanded_urls - Tweets without images, i.e
expanded_urls == NaN - Full dog rating as one string
- Names with a value of 'a'
- Names with a value of 'None'
- Lowercase and Capitalized names
- Erroneous datatypes
- Lowercase, Capitalized, Uppercase dog_stages.
- Floofer and floof for the same dog stage
image prediction table
- Lowercase and Capitalized algorithm predictions i.e p1, p2, p3
Tidiness¶
twitter archive table
sourcecolumn has full link html tag withsource_nameandsource_link- Separate columns for dog stages
Downloaded tweet data tweet_df table
favorite_count,retweet_countfromtweet_dfto be added toarchive_df
Image predictions dataset
- Separate image prediction tables to be joined to twitter archive
Clean¶
In [51]:
archive_clean_df = archive_df.copy()
images_clean_df = image_predictions_df.copy()
tweets_clean_df = tweet_df.copy()
# Drop unneeded columns
archive_clean_df.drop(columns=['in_reply_to_status_id','in_reply_to_user_id', 'retweeted_status_id',
'retweeted_status_user_id', 'retweeted_status_timestamp'], inplace=True)
Tidiness¶
Code¶
In [52]:
archive_clean_df['source_url'] = archive_clean_df['source'].str.extract(r'href=[\'"]?([^\'" >]+)', expand=True, flags=re.IGNORECASE)
archive_clean_df['source_name'] = archive_clean_df['source'].str.extract(r'>(.*?)<\/a>', expand=True, flags=re.IGNORECASE)
In [53]:
archive_clean_df.drop(columns=['source'], inplace=True)
Test¶
In [54]:
archive_clean_df['source_url'].head()
Out[54]:
In [55]:
archive_clean_df['source_name'].head()
Out[55]:
In [56]:
list(archive_clean_df)
Out[56]:
Code¶
In [57]:
# Extract dog stage
archive_clean_df['dog_stage'] = archive_clean_df['text'].str.extract(r'(doggo|pupper|puppo|blep|floofer|floof)',
expand=True, flags=re.IGNORECASE)
# Drop columns
archive_clean_df.drop(columns=['doggo','floofer', 'pupper', 'puppo'], inplace=True)
Test¶
In [58]:
list(archive_clean_df)
Out[58]:
In [59]:
archive_clean_df['dog_stage'].unique()
Out[59]:
In [60]:
archive_clean_df['dog_stage'].value_counts()
Out[60]:
Code¶
In [61]:
# Rename id to tweet_id
tweets_clean_df.rename(columns={'id':'tweet_id'}, inplace=True)
In [62]:
# Merge favorite_count and retweet_count
archive_clean_df = pd.merge(archive_clean_df, tweets_clean_df[['tweet_id','favorite_count','retweet_count']],
on=['tweet_id'], how='left')
Test¶
In [63]:
list(archive_clean_df)
Out[63]:
In [64]:
archive_clean_df.head()
Out[64]:
Quality¶
Code¶
In [65]:
archive_clean_df = archive_clean_df[~archive_clean_df['text'].str.contains('RT @', case=False, flags=re.IGNORECASE)]
Test¶
In [66]:
archive_clean_df.shape
Out[66]:
In [67]:
archive_clean_df[archive_clean_df['text'].str.contains('RT @', case=False, flags=re.IGNORECASE)]
Out[67]:
Code¶
In [68]:
archive_clean_df = archive_clean_df[~archive_clean_df['text'].str.contains('PUPDATE', case=False, flags=re.IGNORECASE)]
Test¶
In [69]:
archive_clean_df.shape
Out[69]:
In [70]:
archive_clean_df[archive_clean_df['text'].str.contains('PUPDATE', case=False, flags=re.IGNORECASE)]
Out[70]:
Code¶
In [71]:
# Remove tweets without images
archive_clean_df = archive_clean_df[archive_clean_df['expanded_urls'].notnull()]
Test¶
In [72]:
# Tweets without images
archive_clean_df[archive_clean_df['expanded_urls'].isnull()]
Out[72]:
In [73]:
archive_clean_df.shape
Out[73]:
Code¶
In [74]:
# removing duplicates from expanded_urls
archive_clean_df['expanded_urls'] = archive_clean_df['expanded_urls'].apply(lambda x: ','.join(list(set(x.split(',')))))
Test¶
In [75]:
archive_clean_df['expanded_urls'].sample(15)
Out[75]:
In [76]:
archive_clean_df.loc[1578].expanded_urls
Out[76]:
Code¶
In [77]:
# Extract names
a_name_is = archive_clean_df.query('name == "a"')['text'].str.extract(r'[Nn]ame is (.*?)\.', expand=True, flags=re.IGNORECASE).dropna()
In [78]:
a_name_is
Out[78]:
In [79]:
archive_clean_df.loc[a_name_is.index, 'name'] = a_name_is[0]
In [80]:
a_named = archive_clean_df.query('name == "a"')['text'].str.extract(r'[Nn]amed (.*?)\.', expand=True, flags=re.IGNORECASE).dropna()
In [81]:
a_named
Out[81]:
In [82]:
a_named
Out[82]:
In [83]:
archive_clean_df.loc[a_named.index, 'name'] = a_named[0]
# Set the name of Jacob (Yacōb) to Jacob i.e remove the attached pronunication
archive_clean_df.loc[2034, 'name'] = 'Jacob'
Test¶
In [84]:
archive_clean_df.loc[a_name_is.index]['name']
Out[84]:
In [85]:
archive_clean_df.loc[a_named.index, 'name']
Out[85]:
Name with a value of None¶
Define¶
Extract the names from the text with names that have a value of None using a regex pattern.
The names are preceeded by name is,named or This is.
For the dogs who still have a name of a set their names to None after getting all the names for the dogs with a name of None we could find from the text.
Code¶
In [86]:
none_name_is = archive_clean_df.query('name == "None"')['text'].str.extract(r'[Nn]ame is (.*?)\.', expand=True, flags=re.IGNORECASE).dropna()
none_named = archive_clean_df.query('name == "None"')['text'].str.extract(r'[Nn]amed (.*?)\.', expand=True, flags=re.IGNORECASE).dropna()
none_this_is = archive_clean_df.query('name == "None"')['text'].str.extract(r'This is (.*?)\.', expand=True, flags=re.IGNORECASE).dropna()
In [87]:
none_name_is
Out[87]:
In [88]:
archive_clean_df.loc[none_name_is.index]
Out[88]:
In [89]:
archive_clean_df.loc[none_name_is.index, 'name'] = ['Howard','Zoey','Thea','Sabertooth', 'Big Jumpy Rat']
In [90]:
none_named
Out[90]:
In [91]:
archive_clean_df.loc[none_named.index, 'name'] = none_named[0]
In [92]:
none_this_is
Out[92]:
In [93]:
archive_clean_df.loc[none_this_is.index][['text','name']]
Out[93]:
In [94]:
# Assign dog names gleaned confirmed from the text
archive_clean_df.loc[[184,349,1068,1842], 'name'] = ['Charlie','Blue', 'Bretagne','Yoshi']
In [95]:
archive_clean_df.query('name == "None"').shape
Out[95]:
In [96]:
# For the dogs who have a name of 'a' that we couldn't get their names, set it to 'None'
dog_a = archive_clean_df.query('name == "a"')
archive_clean_df.loc[dog_a.index,'name'] = 'None'
Test¶
In [97]:
archive_clean_df.loc[none_name_is.index, 'name']
Out[97]:
In [98]:
archive_clean_df.loc[none_named.index, 'name']
Out[98]:
In [99]:
archive_clean_df.loc[[184,349,1068,1842], 'name']
Out[99]:
In [100]:
archive_clean_df['name'].value_counts()
Out[100]:
Code¶
In [101]:
archive_clean_df.name = archive_clean_df.name.str.capitalize()
Test¶
In [102]:
archive_clean_df['name'].value_counts()
Out[102]:
Code¶
In [103]:
archive_clean_df['rating'] = archive_clean_df[['rating_numerator', 'rating_denominator']].astype(str).apply('/'.join, axis=1)
Test¶
In [104]:
archive_clean_df.head()
Out[104]:
In [105]:
type(archive_clean_df['rating'][0])
Out[105]:
In [106]:
archive_clean_df.info()
Code¶
In [107]:
archive_clean_df.dog_stage = archive_clean_df.dog_stage.str.lower()
Test¶
In [108]:
archive_clean_df['dog_stage'].value_counts()
Out[108]:
Code¶
In [109]:
# Get all dogs belonging to the floofer stage
clean_floofer_stage = archive_clean_df.query('dog_stage == "floofer"')
# Set the stage of the floofer dogs to floof
archive_clean_df.loc[clean_floofer_stage.index, 'dog_stage'] = 'floof'
Test¶
In [110]:
archive_clean_df.loc[clean_floofer_stage.index, 'dog_stage']
Out[110]:
In [111]:
archive_clean_df.dog_stage.value_counts()
Out[111]:
In [112]:
archive_clean_df.info()
In [113]:
archive_clean_df.head()
Out[113]:
Code¶
In [114]:
# To datetime
archive_clean_df.timestamp = pd.to_datetime(archive_clean_df.timestamp)
# To category
# Change all the NaN values to string 'None'
archive_clean_df.dog_stage.fillna('None', inplace=True)
archive_clean_df.dog_stage = archive_clean_df.dog_stage.astype('category')
# Change the 'None' back to NaN
archive_clean_df.dog_stage.replace('None',np.nan, inplace=True)
# To int
# set all NaN values to -1
archive_clean_df.favorite_count.fillna(-1, inplace=True)
archive_clean_df.retweet_count.fillna(-1, inplace=True)
archive_clean_df.favorite_count = archive_clean_df.favorite_count.astype('int64')
archive_clean_df.retweet_count = archive_clean_df.retweet_count.astype('int64')
In [115]:
archive_clean_df.dog_stage.value_counts()
Out[115]:
Test¶
In [116]:
archive_clean_df.info()
In [117]:
archive_clean_df.head()
Out[117]:
In [118]:
archive_clean_df.describe()
Out[118]:
Code¶
In [119]:
images_clean_df.p1 = images_clean_df.p1.str.lower()
images_clean_df.p2 = images_clean_df.p2.str.lower()
images_clean_df.p3 = images_clean_df.p3.str.lower()
Test¶
In [120]:
images_clean_df.head()
Out[120]:
Code¶
In [121]:
breed_predictions = []
for i in range(images_clean_df.shape[0]):
if images_clean_df['p1_dog'][i]:
breed_predictions.append(images_clean_df['p1'][i])
elif images_clean_df['p2_dog'][i]:
breed_predictions.append(images_clean_df['p2'][i])
elif images_clean_df['p3_dog'][i]:
breed_predictions.append(images_clean_df['p3'][i])
else:
breed_predictions.append(np.nan)
In [122]:
images_clean_df['predicted_breed'] = breed_predictions
In [123]:
# Join image_predictions to twitter archive
combined_clean = pd.merge(archive_clean_df, images_clean_df[['tweet_id','jpg_url','predicted_breed']],
on=['tweet_id'], how='left')
Test¶
In [124]:
combined_clean.head()
Out[124]:
In [125]:
combined_clean.info()
Visualization and Insights¶
We will answer the following questions¶
- The most popular Dog Stage
- The most popular ratings
- Popular Breed
- Details of the tweet with most retweets
- Details of the tweet/dog most favorited
- Source from which most tweets come.
In [126]:
combined_clean['dog_stage'].value_counts()
Out[126]:
In [127]:
# The Stage Most of the Dogs Belong to
stage_count = combined_clean['dog_stage'].value_counts()
plt.subplots(figsize=(25,15))
plt.bar(stage_count.index, stage_count)
plt.xlabel('Dog Stage', fontsize=30)
plt.ylabel('Number of Tweets/Dogs', fontsize=30)
plt.xticks(fontsize=30)
plt.yticks(fontsize=30)
plt.title('Number of Tweets/Dogs Per The Stage of the Dogs', fontsize=30);
In [128]:
# The Most popular ratings
combined_clean['rating'].value_counts()
Out[128]:
In [129]:
top_ratings = combined_clean['rating'].value_counts()[:14]
plt.subplots(figsize=(25,15))
plt.bar(top_ratings.index, top_ratings)
plt.xlabel('Dog Rating', fontsize=30)
plt.ylabel('Number of Tweets or Dogs', fontsize=30)
plt.xticks(fontsize=25)
plt.yticks(fontsize=20)
plt.title('Number of Tweets or Dogs Per Rating', fontsize=30);
In [130]:
# The Most Popular breed from the Predictions
combined_clean['predicted_breed'].value_counts()
Out[130]:
In [131]:
top_breeds = combined_clean['predicted_breed'].value_counts()[:10]
plt.subplots(figsize=(25,15))
plt.bar(top_breeds.index, top_breeds)
plt.xlabel('Dog Breed', fontsize=30)
plt.ylabel('Number of Tweets or Dogs', fontsize=30)
plt.xticks(fontsize=25, rotation=45)
plt.yticks(fontsize=20)
plt.title('Number of Tweets or Dogs Per Breed', fontsize=30);
In [132]:
# The most favorited tweet
combined_clean.loc[combined_clean['favorite_count'].idxmax()]
Out[132]:
In [133]:
# The most retweeted tweet
combined_clean.loc[combined_clean['retweet_count'].idxmax()]
Out[133]:
In [134]:
# source of tweet
combined_clean['source_name'].value_counts()
Out[134]:
In [135]:
tweet_source = combined_clean['source_name'].value_counts()[:10]
plt.subplots(figsize=(25,15))
plt.bar(tweet_source.index, tweet_source)
plt.xlabel('Tweet Source', fontsize=30)
plt.ylabel('Number of Tweets or Dogs', fontsize=30)
plt.xticks(fontsize=25)
plt.yticks(fontsize=20)
plt.title('Number of Tweets or Dogs By Source Name', fontsize=30);
In [136]:
# Save combined_clean to 'twitter_archive_master.csv'
combined_clean.to_csv('twitter_archive_master.csv', index=False)
Conclusions¶
- Most of the dogs were in the
pupperstage andblepbeing the least. None of the tweets indicated that a dog was at thesnootstage. - Most of the dogs were rated as
12/10. - The most popular breed found in the tweets was the
Golden Retriever. The next four in descending order areLabrador Retriever,Pembroke,ChihuahuaandPug. - Interestingly, the same tweet was most retweeted and the most favorited.
The name couldn't be found. It is a Labrador Retriever doggo which is rated 13/10, tweeted from 'Twitter with iPhone'. For the link to the tweet click here - Most of the tweets came from
Twitter for iPhoneand the least come fromTweetDeck.