WeRateDogs Data Wrangling Project¶
Introduction¶
The purpose of this project is practising data wrangling techniques on three separate files.
All wrangled files are related to the twitter account @dog_rates (WeRateDogs).
The project follows the wrangling workflow of gather, assess, clean.
Data Wrangling - Gather¶
There are three sources of data to gather:
- twitter enhanced archive file - csv downloaded from udacity course site
- tweet image predictions file - tsv requested from udacity site
- tweet details file - json file downloaded from twitter using API
In [1160]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns
import requests
import tweepy
from tweepy import OAuthHandler
import json
from timeit import default_timer as timer
pd.set_option('display.max_colwidth', None) # to display the whole strings and not collapse them
pd.set_option('display.max_columns', None) # to not collapse columns when viewing data
pd.set_option('display.max_rows', None) # to display all rows in the output
1. twitter enhanced archive file (local file)¶
In [1161]:
# load csv file
df_archive = pd.read_csv('twitter-archive-enhanced.csv')
df_archive.head(2)
Out[1161]:
2. tweet image predictions file (url)¶
In [1162]:
# request tsv file
url = 'https://d17h27t6h515a5.cloudfront.net/topher/2017/August/599fd2ad_image-predictions/image-predictions.tsv'
response = requests.get(url)
# save the file
with open('image_predictions.tsv', mode = 'wb') as file:
file.write(response.content)
# load the tab separated file
df_preds = pd.read_csv('image_predictions.tsv', sep='\t')
df_preds.head(2)
Out[1162]:
3. tweet details file (API)¶
In [ ]:
# Query Twitter API for each tweet in the Twitter archive and save JSON in a text file
# These are hidden to comply with Twitter's API terms and conditions
consumer_key = 'HIDDEN'
consumer_secret = 'HIDDEN'
access_token = 'HIDDEN'
access_secret = 'HIDDEN'
auth = 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)
# Tweet IDs for which to gather additional data via Twitter's API
tweet_ids = df_archive.tweet_id.values
print('Tweets to search for:', len(tweet_ids))
# Query Twitter's API for JSON data for each tweet ID in the Twitter archive
count = 0
success = 0
fails_dict = {}
start = timer()
# Save each tweet's returned JSON as a new line in a .txt file
with open('tweet_json.txt', 'w') as outfile:
# This loop takes 20-30 minutes to run because of Twitter's rate limit
for tweet_id in tweet_ids:
count += 1
if count % 100 == 0:
print('#',count, 'processed tweet IDs')
try:
tweet = api.get_status(tweet_id, tweet_mode='extended')
success += 1
json.dump(tweet._json, outfile)
outfile.write('\n')
except tweepy.TweepError as e:
print("Fail")
fails_dict[tweet_id] = e
pass
end = timer()
In [ ]:
print('Time to compose the file:', end - start)
print('Tweets found:', success)
print('Tweet IDs not found:', fails_dict)
In [1163]:
# read the just created tweet_json.txt file line by line to create a pandas DataFrame
# get tweet ID, retweet count, and like count
# a list to be converted to pd df
json_list = []
with open('tweet_json.txt', 'r') as json_file:
json_row = json_file.readline()
while json_row:
json_dict = json.loads(json_row)
# each tweet is written in its own dictionary because that's how the file was created
# we know the column names from visual inspection of the txt file or by reading
# https://developer.twitter.com/en/docs/tweets/data-dictionary/overview/tweet-object
tweet_id = json_dict['id']
retweet_count = json_dict['retweet_count']
like_count = json_dict['favorite_count']
# let's also get info about retweets to compare it with df_archive
# retweeted_status is only included when the tweet is a retweet:
retweeted = json_dict.get('retweeted_status', 0)
if retweeted != 0:
retweeted = 1
json_list.append({'tweet_id': tweet_id, 'retweets': retweet_count, 'likes': like_count, 'retweeted': retweeted})
# new json row, i.e. next tweet
json_row = json_file.readline()
# convert a list of dictionaries to df
df_likes = pd.DataFrame(json_list, columns = ['tweet_id', 'retweets', 'likes', 'retweeted'])
df_likes.head(2)
Out[1163]:
Data Wrangling - Assess¶
We only want to work with:
1) original tweets
2) tweets with an image of a dog
The end results after assessing and cleaning should be one master file (if that will make sense based on tidiness rules), which includes only the relevant tweets (based on the above definition) and has all the required columns with corrected data in it.
Let's visually and programatically analyze all three data frames separately:
1. twitter enhanced archive (df_archive)¶
In [1164]:
df_archive.sample(10)
Out[1164]:
In [1165]:
df_archive.info()
# in_reply and retweeted columns have nulls for all tweets that were not a reply or a retweet
In [1166]:
df_archive.describe()
Out[1166]:
In [1167]:
# there are some extreme values, some of which are wrong based on the text
df_archive.rating_denominator.value_counts()
Out[1167]:
In [1168]:
df_archive.query('rating_denominator == 2').text
Out[1168]:
In [1169]:
# there are some extreme values, some of which are wrong based on the text
df_archive.rating_numerator.value_counts()
Out[1169]:
In [1170]:
df_archive.query('rating_numerator == 960').text
Out[1170]:
In [1171]:
# decimal rating numerator: the actual rating is 13.5/10, but 5/10 is extracted
# df_archive.iloc[45] ... the below code has a more leggible output
df_archive[df_archive.index==45]
Out[1171]:
In [1172]:
# some names are clearly not correctly extracted
df_archive.name.value_counts().nlargest(20)
Out[1172]:
In [1173]:
# stages check
df_archive.query('doggo == "doggo"').sample(5)
Out[1173]:
In [1286]:
# expanded url indicates tweets with a photo and there are some missing values, i.e. not all tweets have pictures
df_archive.expanded_urls.sample(10)
Out[1286]:
In [1175]:
df_archive.expanded_urls.nunique()
Out[1175]:
In [1176]:
df_archive.tweet_id.nunique()
Out[1176]:
df_archive issues¶
Quality¶
Validity:
- we should exclude REtweets (those with non-empty values in retweeted columns)
- we should exclude tweets without images (those with missing expanded_urls)
Accuracy:
- ratings of more than 10/10 are acceptable, but there are some really extreme or decimal values of rating_numerator, which are wrongly extracted based on the text
- ratings can have various bases, but some rating_denominator values are inaccurate based on the text
- some names and stages have incorrect values
Consistency:
- incorrect data types - timestamps should be datetime, rating columns should be floats to allow for decimals
Tidiness¶
- columns doggo, floofer, pupper, puppo are actually values of a single column dog_stage
- rating would make more sense in its own column combining the numerator and denominator columns
2. tweet image predictions (df_preds)¶
In [1177]:
df_preds.sample(15)
Out[1177]:
In [1178]:
# no non-nulls
df_preds.info()
In [1179]:
# probability values are valid
df_preds.describe()
Out[1179]:
In [1180]:
# 26% are not predictions of dogs (in p1)
1 - df_preds.p1_dog.mean()
Out[1180]:
In [1181]:
# some dog names start with a lower case, some with an uppercase letter
df_preds.p1.value_counts().nlargest(20)
Out[1181]:
In [1182]:
df_preds.tweet_id.nunique()
Out[1182]:
df_preds issues¶
Quality - Validity¶
- we only want ratings of dogs, i.e. where px_dog is True
Quality - Consistency¶
- some dog names start with an uppercase, some with a lowercase letter
Tidiness¶
- the columns relevant for analysis can be merged with the main df_archive file, they do not have to be in a separate table
- some columns will not be interesting for the analysis and can be deleted
3. tweet details (df_likes)¶
In [1183]:
df_likes.info()
Issues identified for cleaning¶
Visual and programmatic inspection revealed the following issues:
Quality¶
Completeness:
- -
Validity:
- df_archive: we should exclude REtweets
- df_archive: we should exclude tweets without images
- df_preds: we only want ratings of dogs, i.e. where px_dog is True
Accuracy:
- df_archive: ratings of more than 10/10 are acceptable, but there are some really extreme or decimal values of rating_numerator, which are wrongly extracted based on the text
- df_archive: ratings can have various bases, but some rating_denominator values are inaccurate based on the text
- df_archive: some names and stages have incorrect values
Consistency:
- df_archive: incorrect data types - timestamp should be datetime, rating columns should be floats to allow for decimals
- df_preds: some dog names start with an uppercase, some with a lowercase letter
Tidiness¶
- df_archive: columns doggo, floofer, pupper, puppo are actually values of a single column dog_stage
- df_archive: rating would make more sense in its own column combining the numerator and denominator columns
- df_preds: the columns relevant for analysis can be merged with the main df_archive file, they do not have to be in a separate table
- df_preds: some columns will not be interesting for the analysis and can be deleted
- df_likes: information should be included in the main archive table, there is no reason to have it in a separate data frame
Data Wrangling - Clean¶
The cleaning part will be done in define-code-test chunks in order which I deem as logical given the data structure and the goal.
In [1184]:
# safe copies of all files
df_archive_clean = df_archive.copy()
df_preds_clean = df_preds.copy()
df_likes_clean = df_likes.copy()
In [1185]:
# merging into one file
df_clean = pd.merge(df_archive_clean, df_preds_clean, on='tweet_id', how='left')
df_clean = pd.merge(df_clean, df_likes_clean, on='tweet_id', how='left')
Test #1¶
In [1186]:
df_clean.sample(2)
Out[1186]:
In [1187]:
# data types will need to be adjusted, but let's leave it for later when we eliminate some of the columns
df_clean.info()
In [1188]:
df_clean.retweeted.value_counts()
Out[1188]:
In [1189]:
# all retweeted == 1 are also retweeted_status_id non null
df_clean[(df_clean['retweeted'] == 1) & (df_clean['retweeted_status_id'].isnull()==False)].shape
Out[1189]:
In [1190]:
# delete 179 retweeted tweet rows and test
df_clean.drop(df_clean[df_clean['retweeted'] == 1].index, inplace=True)
df_clean.retweeted.value_counts()
Out[1190]:
In [1191]:
df_clean.retweeted_status_id.isnull().value_counts()
Out[1191]:
In [1192]:
# 2 remaining rows are retweets, but these were not included in df_likes (i.e. these tweet_ids were not processed with API)
df_clean[df_clean['retweeted_status_id'].isnull()==False]
Out[1192]:
Test #2¶
In [1193]:
df_clean.retweeted_status_id.isnull().value_counts()
Out[1193]:
In [1194]:
# these should be deleted, too
df_clean.drop(df_clean[df_clean['retweeted_status_id'].isnull()==False].index, inplace=True)
# we eliminated all retweets and will not need related columns anymore
df_clean.drop(['retweeted', 'retweeted_status_id', 'retweeted_status_user_id', 'retweeted_status_timestamp'], axis=1, inplace=True)
In [1195]:
list(df_clean)
Out[1195]:
In [1196]:
# 58 rows have no picture information, let's delete them
df_clean[(df_clean['expanded_urls'].isnull()==True) & (df_clean['jpg_url'].isnull()==True)].shape
Out[1196]:
In [1197]:
df_clean.drop(df_clean[(df_clean['expanded_urls'].isnull()==True) & (df_clean['jpg_url'].isnull()==True)].index, inplace=True)
df_clean.shape
Out[1197]:
In [1198]:
# test for no remaining missing images
df_clean.expanded_urls.isnull().value_counts()
Out[1198]:
In [1199]:
# there are still 123 missing jpg_urls, which is because these tweets were not included in the predictions file
df_clean.jpg_url.isnull().value_counts()
Out[1199]:
In [1200]:
# upon visual inspection, these tweets are not tweets with an image after all because there is no 'photo' string
# included in the expanded_urls
df_clean[df_clean['jpg_url'].isnull()==True].sample(10)
Out[1200]:
In [1201]:
# these should be deleted, too
df_clean.drop(df_clean[df_clean['jpg_url'].isnull()==True].index, inplace=True)
Test #3¶
In [1202]:
df_clean.jpg_url.isnull().value_counts()
Out[1202]:
In [1203]:
# drop one of the columns because it will no longer be needed
df_clean.drop(['expanded_urls'], axis=1, inplace=True)
In [1204]:
list(df_clean)
Out[1204]:
In [1205]:
# so I will keep in True-False-False and True-True-False predictions
df_clean.groupby(['p1_dog', 'p2_dog', 'p3_dog']).count()['tweet_id']
Out[1205]:
In [1206]:
df_clean.drop(df_clean[df_clean['p1_dog']==False].index, inplace=True)
Test #4¶
In [1207]:
df_clean.groupby(['p1_dog', 'p2_dog', 'p3_dog']).count()['tweet_id']
Out[1207]:
In [1208]:
# we can delete some more information from df_clean coming initially from df_preds:
# img_num, p1_dog, p2_dog, p3_dog, p2_conf, p3_conf
df_clean.drop(['img_num', 'p1_dog', 'p2_dog', 'p3_dog', 'p2_conf', 'p3_conf'], axis=1, inplace=True)
In [1209]:
# df now has fewer observations and columns after identifying retweets and tweets without images and then eliminating
# these columns and other columns, which will not be interesting for further analysis
df_clean.shape
Out[1209]:
In [1210]:
list(df_clean)
Out[1210]:
In [1211]:
df_clean['dog_stage'] = ''
def find_dog_stage(row):
dog_stage = []
if row['doggo'] == 'doggo':
dog_stage.append('doggo')
if row['floofer'] == 'floofer':
dog_stage.append('floofer')
if row['pupper'] == 'pupper':
dog_stage.append('pupper')
if row['puppo'] == 'puppo':
dog_stage.append('puppo')
if len(dog_stage) < 1:
row['dog_stage'] = 'None'
else:
# update the row (multiple stages per row are allowed by the if construction)
row['dog_stage'] = ','.join(dog_stage)
# return updated column
return row
# apply to all rows
df_clean = df_clean.apply(find_dog_stage, axis=1)
# drop redundant columns
df_clean = df_clean.drop(['doggo', 'floofer', 'pupper', 'puppo'], axis=1)
Test #5¶
In [1212]:
# there are 10 dogs that have more than one stage defined, let's clean these manually
df_clean.groupby('dog_stage').count()['tweet_id']
Out[1212]:
In [1213]:
# should be floofer
df_clean[df_clean['dog_stage']=='doggo,floofer'][['tweet_id', 'text', 'dog_stage']]
Out[1213]:
In [1214]:
# puppo
df_clean[df_clean['dog_stage']=='doggo,puppo'][['tweet_id', 'text', 'dog_stage']]
Out[1214]:
In [1215]:
# there seem to be actually two dogs on these pictures
# but for the simplicity, let's make them all a doggo
df_clean[df_clean['dog_stage']=='doggo,pupper'][['tweet_id', 'text', 'dog_stage']]
Out[1215]:
In [1216]:
df_clean['dog_stage'].replace({'doggo,floofer': 'floofer', 'doggo,puppo': 'puppo', 'doggo,pupper': 'doggo'}, inplace=True)
Test #6¶
In [1217]:
df_clean.groupby('dog_stage').count()['tweet_id']
Out[1217]:
In [1218]:
# change data types first to allow storing all correct numbers including decimals
df_clean['rating_numerator'] = df_clean['rating_numerator'].astype(float)
df_clean['rating_denominator'] = df_clean['rating_denominator'].astype(float)
In [1219]:
# to not display warnings when looking at refex results and not extracting them
import warnings
warnings.simplefilter('ignore')
In [1220]:
# find decimal numerator (where denominator could be decimal, too) - there are 4 cases
df_clean[df_clean.text.str.contains(r"(\d+\.\d+\/\d+\.?\d*)")][['tweet_id', 'text', 'rating_numerator']]
Out[1220]:
In [1221]:
# these can be cleaned manually
df_clean.loc[df_clean['tweet_id'] == 883482846933004288, ['rating_numerator']] = 13.5
df_clean.loc[df_clean['tweet_id'] == 786709082849828864, ['rating_numerator']] = 9.75
df_clean.loc[df_clean['tweet_id'] == 778027034220126208, ['rating_numerator']] = 11.27
df_clean.loc[df_clean['tweet_id'] == 680494726643068929, ['rating_numerator']] = 11.26
In [1222]:
# find decimal denominators - there are not any
# (we already know that these will not have a decimanl numerator, because we found all these cases above)
df_clean[df_clean.text.str.contains(r"(\d+\/\d+\.\d+)")][['tweet_id', 'text', 'rating_numerator', 'rating_denominator']]
Out[1222]:
In [ ]:
In [1223]:
# check if any tweet has more than one "numeric/numeric" pattern in its text
df_clean[df_clean.text.str.contains( r"(\d+\.?\d*\/\d+\.?\d*\D+\d+\.?\d*\/\d+\.?\d*)")][['tweet_id', 'text', 'rating_numerator', 'rating_denominator']]
Out[1223]:
There are quite a few cases with more than one "rating" in the text:
- some of them are two ratings for two dogs: for simplicity, whenever this is the case, the first rating which the regex extracts is fine and can be considered correct
- some "ratings" are not the actual ratings but other fractions, these have to be corrected:
In [1224]:
df_clean.loc[df_clean['tweet_id'] == 740373189193256964, ['rating_numerator']] = 14
df_clean.loc[df_clean['tweet_id'] == 740373189193256964, ['rating_denominator']] = 10
df_clean.loc[df_clean['tweet_id'] == 722974582966214656, ['rating_numerator']] = 13
df_clean.loc[df_clean['tweet_id'] == 722974582966214656, ['rating_denominator']] = 10
df_clean.loc[df_clean['tweet_id'] == 716439118184652801, ['rating_numerator']] = 11
df_clean.loc[df_clean['tweet_id'] == 716439118184652801, ['rating_denominator']] = 10
df_clean.loc[df_clean['tweet_id'] == 666287406224695296, ['rating_numerator']] = 9
df_clean.loc[df_clean['tweet_id'] == 666287406224695296, ['rating_denominator']] = 10
Test #7¶
In [1225]:
# no wrong extracted decimal numerators remain
df_clean[df_clean.text.str.contains(r"(\d+\.\d+\/\d+\.?\d*)")][['tweet_id', 'text', 'rating_numerator', 'rating_denominator']]
Out[1225]:
In [1226]:
# no wrong extracted double fractions remain
df_clean.loc[(df_clean['tweet_id'] == 740373189193256964) | (df_clean['tweet_id'] == 722974582966214656) |\
(df_clean['tweet_id'] == 716439118184652801) | (df_clean['tweet_id'] == 666287406224695296)]\
[['tweet_id', 'text', 'rating_numerator', 'rating_denominator']]
Out[1226]:
In [1227]:
df_clean['rating'] = ''
def merge_rating(row):
rating = []
rating.append(str(row['rating_numerator']))
rating.append(str(row['rating_denominator']))
row['rating'] = '/'.join(rating)
# return updated column
return row
# apply to all rows
df_clean = df_clean.apply(merge_rating, axis=1)
# drop the separate rating columns
df_clean = df_clean.drop(['rating_numerator', 'rating_denominator'], axis=1)
Test #8¶
In [1228]:
# rating is now stored in one column, so we solved the tidiness issue
# most of the ratings which occur only once in the data seem plausible
# however, let's check some of the values for accuracy
df_clean.groupby('rating').count()['tweet_id'].nlargest(40)
Out[1228]:
Note: Rating column is intentionally kept as a string column to keep the unique rating system readable and comparable.
In [1229]:
# let's check the couple of ratings that still look suspicious even after the cleaning we did in #7
# this is wrongly extracted and there is actually no real rating included in the text
df_clean.query('rating == "24.0/7.0"').text
Out[1229]:
In [1230]:
df_clean['rating'].replace({'24.0/7.0': None}, inplace=True)
In [1231]:
# this one is correct
df_clean.query('rating == "2.0/10.0"').text
Out[1231]:
In [1232]:
# this one is correct
df_clean.query('rating == "165.0/150.0"').text
Out[1232]:
In [1233]:
# this one is correct
df_clean.query('rating == "143.0/130.0"').text
Out[1233]:
In [1234]:
# this one is correct
df_clean.query('rating == "121.0/110.0"').text
Out[1234]:
Test #8¶
In [1235]:
df_clean.groupby('rating').count()['tweet_id'].nlargest(30)
Out[1235]:
In [1236]:
df_clean.info()
In [1237]:
# let's make tweet_id the index of the df
df_clean.set_index('tweet_id', inplace=True)
In [1238]:
# in_reply_to_status_id, in_reply_to_user_id only have 14 items, which will not be interesting for the analysis
df_clean = df_clean.drop(['in_reply_to_status_id', 'in_reply_to_user_id'], axis=1)
Test #9¶
In [1239]:
df_clean.info()
In [1240]:
# timestamp as datetime
# retweets and likes as integers
df_clean['timestamp'] = pd.to_datetime(df_clean['timestamp'])
df_clean['retweets'] = df_clean['retweets'].astype(int)
df_clean['likes'] = df_clean['likes'].astype(int)
Test #10¶
In [1241]:
df_clean.info()
In [1242]:
# this one is now easier to do on the final cleaned data than if we tried it in the beginning
df_clean.name.value_counts().nlargest(50)
Out[1242]:
In [1243]:
# first, let's change "None" to real None
df_clean.name.replace('None', inplace=True)
In [1244]:
# it looks like all names starting with lowercase letter seem not to be real names
df_clean.loc[df_clean['name'].str.islower()][['text','name']]
Out[1244]:
Displaying all lowercase names (which are actually not names) reveals several things:
- most of these tweet texts actually do not include any name - these names should be changed to None
- some actually do have a name, which was not extracted correctly
- we also found some texts which suggest that we still have some non-dog tweets included (which can happen because we only dropped tweets where the first prediction was not a dog) - let's drop these tweets manually
In [1245]:
# let's start with 3. and drop non-dog tweets
df_clean.drop(855459453768019968, inplace=True)
df_clean.drop(806219024703037440, inplace=True)
df_clean.drop(772581559778025472, inplace=True)
df_clean.drop(748977405889503236, inplace=True)
df_clean.drop(746872823977771008, inplace=True)
df_clean.drop(746369468511756288, inplace=True)
df_clean.drop(745422732645535745, inplace=True)
df_clean.drop(740214038584557568, inplace=True)
df_clean.drop(736225175608430592, inplace=True)
df_clean.drop(717537687239008257, inplace=True)
df_clean.drop(715733265223708672, inplace=True)
df_clean.drop(710272297844797440, inplace=True)
df_clean.drop(700747788515020802, inplace=True)
df_clean.drop(697259378236399616, inplace=True)
df_clean.drop(690360449368465409, inplace=True)
df_clean.drop(681297372102656000, inplace=True)
df_clean.drop(679530280114372609, inplace=True)
df_clean.drop(675534494439489536, inplace=True)
df_clean.drop(675109292475830276, inplace=True)
df_clean.drop(880872448815771648, inplace=True)
df_clean.drop(702217446468493312, inplace=True)
df_clean.drop(667793409583771648, inplace=True)
df_clean.drop(666373753744588802, inplace=True)
In [1246]:
# next, let's check for real names in the remaining tweets
df_clean.loc[df_clean['name'].str.islower()][['text','name']]
Out[1246]:
In [1247]:
df_clean.loc[828650029636317184, ['name']] = 'Grace'
df_clean.loc[765395769549590528, ['name']] = 'Zoey'
df_clean.loc[675706639471788032, ['name']] = 'Wylie'
df_clean.loc[671743150407421952, ['name']] = 'Jacob'
df_clean.loc[671147085991960577, ['name']] = 'Rufus'
df_clean.loc[670303360680108032, ['name']] = 'Hemry'
df_clean.loc[669564461267722241, ['name']] = 'Alfredo'
df_clean.loc[668955713004314625, ['name']] = 'Leroi'
df_clean.loc[668636665813057536, ['name']] = 'Berta'
df_clean.loc[668507509523615744, ['name']] = 'Chuk'
df_clean.loc[668171859951755264, ['name']] = 'Alfonso'
df_clean.loc[667861340749471744, ['name']] = 'Cheryl'
df_clean.loc[667773195014021121, ['name']] = 'Jessiga'
df_clean.loc[667538891197542400, ['name']] = 'Klint'
df_clean.loc[667470559035432960, ['name']] = 'Kohl'
df_clean.loc[667177989038297088, ['name']] = 'Daryl'
df_clean.loc[666781792255496192, ['name']] = 'Octaviath'
df_clean.loc[666701168228331520, ['name']] = 'Johm'
In [1248]:
# finally, let's return to the remaining lowercase "names" that are still left and change them to none
df_clean.loc[df_clean['name'].str.islower()]['name'].value_counts()
Out[1248]:
In [1249]:
# not names to null
df_clean.name.replace('a', inplace=True)
df_clean.name.replace('the', inplace=True)
df_clean.name.replace('an', inplace=True)
df_clean.name.replace('one', inplace=True)
df_clean.name.replace('just', inplace=True)
df_clean.name.replace('officially', inplace=True)
df_clean.name.replace('space', inplace=True)
df_clean.name.replace('light', inplace=True)
df_clean.name.replace('very', inplace=True)
df_clean.name.replace('infuriating', inplace=True)
Test #11¶
In [1250]:
# no lowercase names remain
df_clean.loc[df_clean['name'].str.islower()]['name']
Out[1250]:
In [1251]:
df_clean.p1 = df_clean.p1.str.lower()
df_clean.p2 = df_clean.p2.str.lower()
df_clean.p3 = df_clean.p3.str.lower()
Test #12¶
In [1252]:
df_clean.sample(7)
Out[1252]:
In [1253]:
# we end up with 1454 cleaned rows
df_clean.info()
The following data issues were corrected in the cleaning process:¶
- tidiness: merging all files together, creating a dog stage column, creating a rating column
- quality: deleting retweets, deleting tweets without an image, deleting tweets with an image not of a dog, cleaning dog stage column, cleaning rating columns, changing datatypes, deleting columns, cleaning name column, making all predicted breed names lowercase
Let's store the final cleaned data:
In [1254]:
df_clean.to_csv('twitter_archive_master.csv', index=False)
Analyze & Visualize¶
The stored twitter_archive_master file is the cleaned data which we will now use for the analysis.
In [1255]:
df = pd.read_csv('twitter_archive_master.csv')
In [1256]:
# we can see the expected positive correlation of number of retweets and likes
sns.pairplot(df);
In [1257]:
df.describe()
Out[1257]:
In [1258]:
# likes and retweets scatter
plt.figure(figsize=(20,8))
plt.subplot(1,3,1)
g1 = sns.regplot(x = df.likes, y = df.retweets, color='blue', scatter_kws={'alpha':0.3})
g1.set(title = 'Correlation of likes and retweets')
plt.subplot(1,3,2)
g2 = sns.regplot(x = df.likes, y = df.retweets, fit_reg=False, color='blue', scatter_kws={'alpha':0.3})
g2.set(title = 'Correlation of likes and retweets', xlim=(0,15000), ylim=(0,4000))
plt.subplot(1,3,3)
g3 = sns.regplot(x = df.likes, y = df.retweets, fit_reg=False, color='blue', scatter_kws={'alpha':0.3})
g3.set(title = 'Correlation of likes and retweets', xlim=(0,6000), ylim=(0,2000))
plt.show()
In [1259]:
df['likes'].corr(df['retweets'])
Out[1259]:
In [1260]:
df_pred_breeds = pd.DataFrame(df.groupby('p1')['p1_conf'].mean().nlargest(15))
df_pred_breeds
Out[1260]:
In [1261]:
breed_count = df.groupby('p1')['p1_conf'].count()
breed_count
Out[1261]:
In [1262]:
# some of the breeds predicted with the highest probability have low counts among the tweets
df_pred_breeds['breed_count'] = breed_count
df_pred_breeds
Out[1262]:
In [1263]:
df_pred_breeds.breed_count.sum()
Out[1263]:
In [1264]:
# top 15 best predicted breeds covers 25% of all tweets
df_pred_breeds.breed_count.sum() / len(df)
Out[1264]:
In [1265]:
# find what komondor, the breed predicted with the highest probability, looks like
df.query('p1 == "komondor"')['jpg_url']
Out[1265]:
In [1266]:
# no wonder it is easy to recognize
from IPython.display import Image
Image(url='https://pbs.twimg.com/media/DBg_HT9WAAEeIMM.jpg')
Out[1266]:
In [1267]:
# now let's look at the most frequently predicted breeds
df_frequent_breeds = df.groupby('p1').count()['likes'].nlargest(15)
df_frequent_breeds
Out[1267]:
In [1268]:
# top 15 most frequently predicted breeds covers 51% of all tweets
df.groupby('p1').count()['likes'].nlargest(15).sum() / len(df)
Out[1268]:
In [1269]:
# 5 of these breeds are also predicted with high probability
df_breeds_intersect = pd.merge(df_frequent_breeds, df_pred_breeds, on='p1', how='inner')
df_breeds_intersect.index
Out[1269]:
In [1270]:
# likes and retweets scatter by breeds which are both most frequently predicted and with the highest probability
# separate dfs for each breed
df_golden_retriever = df[df['p1'] == 'golden_retriever']
df_pug = df[df['p1'] == 'pug']
df_samoyed = df[df['p1'] == 'samoyed']
df_pomeranian = df[df['p1'] == 'pomeranian']
df_french_bulldog = df[df['p1'] == 'french_bulldog']
# legend
import matplotlib.patches as mpatches
ret = mpatches.Patch(label='Golden retriever', color='blue')
pug = mpatches.Patch(label='Pug', color='green')
sam = mpatches.Patch(label='Samoyed', color='red')
pom = mpatches.Patch(label='Pomeranian', color='yellow')
bull = mpatches.Patch(label='French bulldog', color='black')
plt.figure(figsize=(20,12))
plt.subplot(1,3,1)
def reg_plot(x_lim, y_lim):
g = sns.regplot(x = df_golden_retriever.likes, y = df_golden_retriever.retweets, color='blue', scatter_kws={'alpha':0.5, 's':80}, fit_reg=False)
sns.regplot(x = df_pug.likes, y = df_pug.retweets, color='green', scatter_kws={'alpha':0.5, 's':80}, ax=g, fit_reg=False)
sns.regplot(x = df_samoyed.likes, y = df_samoyed.retweets, color='red', scatter_kws={'alpha':0.5, 's':80}, ax=g, fit_reg=False)
sns.regplot(x = df_pomeranian.likes, y = df_pomeranian.retweets, color='yellow', scatter_kws={'alpha':0.5, 's':80}, ax=g, fit_reg=False)
sns.regplot(x = df_french_bulldog.likes, y = df_french_bulldog.retweets, color='black', scatter_kws={'alpha':0.5, 's':80}, ax=g, fit_reg=False)
g.set(title = 'Correlation of likes and retweets', xlim=(0,x_lim), ylim=(0,y_lim))
plt.legend(handles=[ret, pug, sam, pom, bull])
return g
reg_plot(120000, 35000)
plt.subplot(1,3,2)
reg_plot(15000, 4000)
plt.subplot(1,3,3)
reg_plot(6000, 2000)
plt.show()
In [1271]:
df.likes.mean()
Out[1271]:
In [1272]:
# all ways below get the same result
# df['likes'].groupby(df['dog_stage']).mean()
# df.groupby('dog_stage').mean()['likes']
# df[['likes', 'dog_stage']].groupby(df['dog_stage']).mean() ... this one creates a dataframe instead of a series
df.groupby('dog_stage')['likes'].mean()
Out[1272]:
In [1273]:
sns.barplot(x = df.dog_stage, y = df.likes, order = ['puppo', 'doggo', 'floofer', 'pupper'], color = 'blue', errwidth = 0)
plt.axhline(8729, color='black')
non = mpatches.Patch(label='No stage mentioned', color='black')
cover = mpatches.Patch(label='Covers 16% of all tweets', color='blue')
plt.legend(handles=[non, cover])
plt.xlabel('Dog stage')
plt.ylabel('Average likes per tweet')
plt.title('Tweets popularity by dog stage')
plt.ylim((0,25000))
plt.show()
In [1274]:
# there are not many tweets with identified dog stage
df.groupby('dog_stage')['likes'].count()
Out[1274]:
In [1275]:
# shares
shares = df.groupby('dog_stage').agg({'dog_stage': 'count'})
shares.apply(lambda x: x / len(df))
Out[1275]:
In [1276]:
# only 16% of tweets have a dog stagementioned in them
1 - shares.loc['None'].apply(lambda x: x / len(df))
Out[1276]:
In [1277]:
# check the high likes numbers
df.query('dog_stage == "puppo"').head(5)
Out[1277]:
In [1278]:
# ratings by popularity
df.groupby('rating').count()['likes'].nlargest(10)
Out[1278]:
In [1279]:
df.groupby('rating').count()['likes'].nlargest(4).sum() / len(df)
Out[1279]:
In [1280]:
df.groupby('rating').count()['likes'].nlargest(10).sum() / len(df)
Out[1280]:
In [1281]:
plt.figure(figsize=(12,4))
plt.subplot(1,2,1)
g = sns.barplot(x = ['12/10', '10/10', '11/10', '13/10'], \
y = df.groupby('rating').count()['likes'].nlargest(4), color = 'blue');
g.set(title = 'Top 4 most popular ratings', xlabel = 'Rating', ylabel = 'Number of tweets')
cover = mpatches.Patch(label='Covers 83% of all tweets', color='blue')
plt.legend(handles=[cover])
plt.subplot(1,2,2)
g = sns.barplot(x = ['12/10', '10/10', '11/10', '13/10', '9/10', '8/10', '7/10', '14/10', '6/10', '5/10'], \
y = df.groupby('rating').count()['likes'].nlargest(10), color = 'blue');
g.set(title = 'Top 10 most popular ratings', xlabel = 'Rating', ylabel = 'Number of tweets')
cover = mpatches.Patch(label='Covers 98% of all tweets', color='blue')
plt.legend(handles=[cover])
plt.show()
In [1282]:
# names by popularity
df.groupby('name').count()['likes'].nlargest(10)
Out[1282]:
In [1283]:
# top 10 names cover only 9% of tweets
df.groupby('name').count()['likes'].nlargest(10).sum() / len(df)
Out[1283]:
In [1284]:
# because dog names are very diverse
df.name.nunique()
Out[1284]:
In [1285]:
plt.figure(figsize=(8,4))
g = sns.barplot(x = ['Walter', 'Daisy', 'Cooper', 'Sadie', 'Charlie', 'Lucy', 'Oliver', 'Koda', 'Penny', 'Bella'], \
y = df.groupby('name').count()['likes'].nlargest(10), color = 'blue')
g.set(title = 'Top 10 most popular dog names', xlabel = 'Dog name', ylabel = 'Number of tweets')
cover = mpatches.Patch(label='Covers 9% of all tweets', color='blue')
plt.legend(handles=[cover])
plt.show()
