Data Wrangling¶
In [1]:
# import the needed libraries
import pandas as pd
import numpy as np
import requests
import zipfile
import matplotlib.pyplot as plt
from pandas.plotting import register_matplotlib_converters
register_matplotlib_converters()
import seaborn as sns
plt.style.use('bmh')
from PIL import Image
from io import BytesIO
from wordcloud import WordCloud, STOPWORDS
import requests
import numpy as np
%matplotlib inline
Gather¶
In [2]:
# read the provided csv file twitter-archive-enhanced file (file on hand) and save it
twitter_archive = pd.read_csv('twitter-archive-enhanced.csv')
In [3]:
# read the first 5 rows for data inspection
twitter_archive.head()
Out[3]:
In [4]:
# getting the image prediction file programmatically using the given url
url = 'https://d17h27t6h515a5.cloudfront.net/topher/2017/August/599fd2ad_image-predictions/image-predictions.tsv'
response = requests.get(url)
# save to .tsv file
with open('image_predictions.tsv', 'wb') as file:
file.write(response.content)
In [5]:
# read the image prediction file and save to pandas DataFrame
image_pred = pd.read_csv('image_predictions.tsv',sep='\t')
# check for the data top 5 rows
image_pred.head()
Out[5]:
I tried to set up a twitter developer account, but my application was not approved.
- The following code is the Twitter API code supported by Udacity.
- So, I will comment it as a matter of reproducibilty when rerun all the code cells in this Jupyter notebook.
In [6]:
# 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)
# NOTE TO REVIEWER: this student had mobile verification issues so the following
# Twitter API code was sent to this student from a Udacity instructor
# Tweet IDs for which to gather additional data via Twitter's API
#tweet_ids = twitter_archive.tweet_id.values
#len(tweet_ids)
# Query Twitter's API for JSON data for each tweet ID in the Twitter archive
#count = 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 will likely take 20-30 minutes to run because of Twitter's rate limit
# for tweet_id in tweet_ids:
# count += 1
# print(str(count) + ": " + str(tweet_id))
# try:
# tweet = api.get_status(tweet_id, tweet_mode='extended')
# print("Success")
# json.dump(tweet._json, outfile)
# outfile.write('\n')
# except tweepy.TweepError as e:
# print("Fail")
# fails_dict[tweet_id] = e
# pass
#end = timer()
#print(end - start)
#print(fails_dict)
The data that should be gathered by the previous code is supported in the project resources by Udacity as zip file.
In [7]:
# extract the file from the zipfile
with open('tweet-json.zip','rb') as f:
z_tweets = zipfile.ZipFile(f)
z_tweets.extractall()
# check for the extracted file
z_tweets.namelist()
Out[7]:
In [8]:
# read the file in DataFrame
with open('tweet-json copy', 'r') as f:
tweet_json = pd.read_json(f, lines= True, encoding = 'utf-8')
# check the data
tweet_json.head(3)
Out[8]:
In [9]:
# check for the columns names
tweet_json.columns
Out[9]:
In [10]:
# select the columns of interest : 'id', 'favorite_count','retweet_count'
tweet_json = tweet_json.loc[:,['id','favorite_count','retweet_count']]
# check for the top 5 rows
tweet_json.head()
Out[10]:
Assess¶
- So, Now we have Three datasets
twitter_archive,img_predandtweet_json - First let's display one by one for visual assessing
In [11]:
# display twitter archive
twitter_archive
Out[11]:
In [12]:
# display image_pred
image_pred
Out[12]:
In [13]:
# display tweet_json
tweet_json
Out[13]:
More deep¶
- Let's dive in deeper
- Assessing of the data programmatically
In [14]:
# twitter_archive data info
twitter_archive.info()
In [15]:
# statistic description of twitter archive
twitter_archive.describe()
Out[15]:
In [16]:
# data sample
twitter_archive.sample(5)
Out[16]:
In [17]:
# check for source column
twitter_archive.source.value_counts()
Out[17]:
In [18]:
# check for the dog's name written style
twitter_archive.name.str.istitle().value_counts()
Out[18]:
In [19]:
# check for those written as lowercase
lowers = twitter_archive.name.loc[twitter_archive.name.str.islower()].unique()
lowers
Out[19]:
In [20]:
# check for the unique values of those non titled
untitled = twitter_archive.name.loc[twitter_archive.name.str.istitle() == False].unique()
untitled
Out[20]:
In [21]:
# check for those mis-written
untitled_unlowers = [i for i in untitled if i not in lowers]
untitled_unlowers
Out[21]:
As we are interested in this project with the rating of Dogs so Let's focus more on the columns related to rating i.e rating_numerator and rating_denominator
In [22]:
# check for denominator values below 10
pd.set_option('display.max_colwidth',-1)
twitter_archive.loc[twitter_archive.rating_denominator <10 , ['text','rating_numerator','rating_denominator']]
Out[22]:
In [23]:
# check for rating denominator values > 10
twitter_archive.loc[twitter_archive.rating_denominator >10 ,['text','rating_numerator','rating_denominator']]
Out[23]:
- Note : the account started on 11/15/15
In [24]:
# check for rating_numerator <10
twitter_archive.loc[twitter_archive.rating_numerator < 10,['text','rating_numerator','rating_denominator']]
Out[24]:
In [25]:
# check for rating_numerator values > 10
twitter_archive.loc[twitter_archive.rating_numerator > 14,['text','rating_numerator','rating_denominator']]
Out[25]:
Important points here:
- The account has its own rating system and that is quiet clear here specially that rate of 14/10 looks normal
- form the above I found some scores as outliers 1776,420 that needs more invistigate eith to include or drop
- Also those above 100 these seems to be related to more that one dogs in a photo
- some typos as 75 instead of 9.75 , 26 instead of 11.26 ,27 instead of 11.27 11/10 instead of 50/50
- some times they are using the float numbers
- I will collect these observations for further cleaning
In [26]:
# check for the text
twitter_archive.text.sample(5).tolist()
Out[26]:
- during my check for samples from text column I noticed the sentence
We only rate dogs, Let's invistigate about this
In [27]:
# check inside the text values for non dog related tweets
twitter_archive.text[twitter_archive.text.str.match('.*only rate dogs')]
Out[27]:
In [28]:
# check the expanded urls column
twitter_archive.expanded_urls.sample(5)
Out[28]:
- So this sentence used by the account's admin to address that picture doesn't contain a dog!
In [29]:
# check for how many time this issue occur?
len(twitter_archive.text[twitter_archive.text.str.match('.*only rate dogs')])
Out[29]:
In [30]:
# image_pred data info
image_pred.info()
In [31]:
# statistic description of image_pred
image_pred.describe()
Out[31]:
In [32]:
# data sample
image_pred.sample(5)
Out[32]:
In [33]:
# number of dogs breeds
image_pred.p1.value_counts()
Out[33]:
In [34]:
# tweet_json data info
tweet_json.info()
In [35]:
# tweet_json statistics
tweet_json.describe()
Out[35]:
In [36]:
# data sample
tweet_json.sample(5)
Out[36]:
In [37]:
# check for datasets shape and completeness
twitter_archive.shape[0], tweet_json.shape[0] , image_pred.shape[0]
Out[37]:
In [38]:
# duplicate columns in the three datasets
all_columns = pd.Series(list(twitter_archive ) + list(tweet_json) +list(image_pred))
all_columns[all_columns.duplicated()]
Out[38]:
Notes¶
Qulity¶
twitter_archiveMissing Values :
- in_reply_to_status_id, in_reply_to_user_id : 78 instead of 2356
- retweeted_status_id,retweeted_status_user_id,retweeted_status_timestamp 181 instead of 2356
- expanded_urls : 2297 instead of 2356
- We are interested in the tweet ONLY not the retweet
We are interested in the tweet ONLY not the reply to the original tweet
tweet_id is saved as int datatype instead of/ "better to be" string (object)
- timestamp , retweeted_status_timestamp are saved as object datatype (str) instead of date/timestamp
- source column is writen in html containg
<a>tags column name :
- some values are not titled
untitled_unlowers('BeBe','DonDon','CeCe',, 'JD', 'DayZ') - some are inacuarte values :
lowers['such', 'a', 'quite', 'not', 'one', 'incredibly', 'mad','an', 'very', 'just', 'my', 'his', 'actually', 'getting','this', 'unacceptable', 'all', 'old', 'infuriating', 'the','by', 'officially', 'life', 'light', 'space']
- some values are not titled
rating_numerator & rating_denominator:
- datatype for rating_numerator should be float instead of int
- fix:
- @45 13.5/10 instead of 5/10
- @ 313 13/10 instead of 960/0
- @ 2335 : 9/10 instead of 1/2
- @ 1068 : 14/10 instead of 9/11
- @1165: 13/10 instead of 4/20
- @ 1202 : 11/10 instead of 50/50
- @ 1662 10/10 instead of 7/11
- @ 695 : 9.75/10 instead of 75/10
- @763 : 11.27/10 instead of 27/10
- @1712 :11.26/10 instead of 26/10
- drop:
- @ 516 no rating
- @342 inaccurate (account start date)
- invistigate(outliers):
- @ 315 https://t.co/YbEJPkg4Ag 0/10
- @979 1776/10
- @ 1634 : https://t.co/kRK51Y5ac3 143/130
- @2074 :420/10
- @1274 names
- columns doggo,floofer,pupper, puppo has None values instead of Null.
- We are interested in dogs , text column reveals the truth about that some tweets are not related to dogs
- expanded_urls is too bulky we are interested in tweet link only.
image_pred- some images are not for dogs
- tweet_id is saved as int datatype instead of object datatype
- replace the underscore in breeds values with space and title all breeds values (p1 &p2& p3)
twitter_json- column id is saved as int datatype instead of object datatype & rename as tweet_id
All_datasets- we have completeness issue not all the datasets have the same number of observation
Tidiness¶
twitter_archive- text column has two variables text and short urls,create short_urls column, drop expanded_urls
- The values of four columns (doggo,floofer,pupper,puppo) in
twitter_archivedataset should be in one column dog_stage with a category datatype. - rating_numerator and rating_denominator columns in
twitter_archivedataset should form one column dog_rating normalized out of 10. - make new columns for
day_nameandmonthfrom the timstamp column
image_pred- Columns p1, p1_dog, p1_conf , p2, p2_dog, p2_conf , p3, p3_dog, p3_conf could be condenced to two columns
dog_breedandconfidence
- Columns p1, p1_dog, p1_conf , p2, p2_dog, p2_conf , p3, p3_dog, p3_conf could be condenced to two columns
All datasets- tweet_id is present in two datasets and after renaming it will appear in all datasets
tweet_jsonandimage_preddatasets should be part of our main datasettwitter_archive.
Clean¶
In [39]:
# make a copy of the datasets
twitter_archive_clean = twitter_archive.copy()
image_pred_clean = image_pred.copy()
tweet_json_clean = tweet_json.copy()
First things first
- Let's start with the missing values
1 Missing Values¶
twitter_archive
Missing Values :
- in_reply_to_status_id, in_reply_to_user_id : 78 instead of 2356
- retweeted_status_id,retweeted_status_user_id,retweeted_status_timestamp 181 instead of 2356
- expanded_urls : 2297 instead of 2356 (to be fixed later)
#### Define
- in the
twitter_archivedataset we will keep only recodrs that: 1Are not associated with retweets.2Are not associated with reply to the original tweet.- i.e we will keep the NaN values for these columns and drop non NaN values
- Drop columns:
- in the
- in_reply_to_status_id
- in_reply_to_user_id
- retweeted_status_id
- retweeted_status_user_id
- retweeted_status_timestamp**
Code¶
In [40]:
twitter_archive_clean = twitter_archive_clean.query('in_reply_to_status_id == "NaN" &\
in_reply_to_user_id == "NaN" &\
retweeted_status_id == "NaN" &\
retweeted_status_user_id == "NaN"')
# drop columns
xcolumns = ['in_reply_to_status_id','in_reply_to_user_id','retweeted_status_id',
'retweeted_status_user_id', 'retweeted_status_timestamp']
twitter_archive_clean = twitter_archive_clean.drop(columns = xcolumns, axis=1)
Test¶
In [41]:
# check for Null values in the twitter_archive clean versions
twitter_archive_clean.info()
Tideness¶
1 twitter_archive
- text column has two variables text and short urls,create short_urls column, drop expanded_urls
Define¶
- use split method by
' 'over the text column, and apply over row - create
short_urlscolumn - drop
expanded_urlscolumn - split the text column by
https:and assign its value to the same column name
Code¶
In [42]:
# create short_urls column by use split method over the text column, and apply over row
twitter_archive_clean['short_urls'] = twitter_archive_clean.text.apply(lambda x :x.strip().split(' ')[-1])
# drop the expanded_urls
twitter_archive_clean.drop('expanded_urls', axis =1, inplace=True)
# split the text column by `https:` and assign its value to the same column name
twitter_archive_clean.text = twitter_archive_clean.text.apply(lambda x:x.split('https:')[0])
Test¶
In [43]:
twitter_archive_clean.sample()
Out[43]:
In [44]:
# check for column droping
assert 'expanded_urls' not in twitter_archive_clean.columns
Tideness¶
2 twitter_archive
- The values of four columns (doggo,floofer,pupper,puppo) in
twitter_archivedataset should be in one column dog_stage with a category datatype.
Define¶
- select the last 4 columns related to the different dog stages
- replace the 'None' string with np.nan in the selected columns
- create a dog_stage column joinig all the values in the selected values droping nan, convert to str
- convert the dog_stage column type to categorical
- drop the columns related to the previous 4 stages
Code¶
In [45]:
# select the dog stages columns from the dataset
all_dogs_type = ['doggo', 'floofer', 'pupper', 'puppo']
# replace the 'None' string with np.nan
twitter_archive_clean[all_dogs_type] = twitter_archive_clean[all_dogs_type].replace('None', np.nan)
# create the dog_stage column with joining the four columns in one column dog_stage join for more than stage
twitter_archive_clean['dog_stage'] = twitter_archive_clean[all_dogs_type].\
apply(lambda x: ', '.join(x.dropna().astype(str)),axis =1)
# replace the empty string with nan and change datatype to category
twitter_archive_clean.dog_stage = twitter_archive_clean.dog_stage.replace('', np.nan).astype('category')
# drop the 4 columns
twitter_archive_clean = twitter_archive_clean.drop(columns = all_dogs_type, axis =1)
Test¶
In [46]:
# check for the data columns and datatype
twitter_archive_clean.info()
In [47]:
# check for the values of the new column
twitter_archive_clean.dog_stage.value_counts()
Out[47]:
In [48]:
#convert the datatype of rating_numerator to float by astype('float')
twitter_archive_clean.rating_numerator = twitter_archive_clean.rating_numerator.astype('float')
Test¶
In [49]:
# check for the datatype
twitter_archive_clean.info()
Quality¶
3 rating_numerator & rating_denominator:
- fix:
- @45 13.5/10 instead of 5/10
- @ 313 13/10 instead of 960/0
- @ 2335 : 9/10 instead of 1/2
- @ 1068 : 14/10 instead of 9/11
- @1165: 13/10 instead of 4/20
- @ 1202 : 11/10 instead of 50/50
- @ 1662 10/10 instead of 7/11
- @ 695 : 9.75/10 instead of 75/10
- @763 : 11.27/10 instead of 27/10
- @1712 :11.26/10 instead of 26/10
4 rating_numerator & rating_denominator:
- drop:
- @ 516 no rating
- @342 inaccurate (account start date) retweets& replys are already droped
Define¶
- check for the above indices if exist
- get a list of the indices of the erros after check
- set a list for the correct values relative to those indices
- loop through the two lists and assign each index with the new correct value
- drop
[516]by index
Code¶
In [50]:
# check for index if exist
indices = [45,313,2335,1068,1165,1202,1662,695,763,1712,516,342]
for i in indices:
if i in list(twitter_archive_clean.index):
print('yes')
else:
print(f'No : {i} ')
In [51]:
#get a list of the indices of the erros after check
indices = [45,2335,1068,1165,1202,1662,695,763,1712]
# set a list for the correct values relative to those indices
vals = [13.5,9,14,13,11,10,9.75,11.27,11.26]
# loop through the two lists and assign each index with the new correct value
for i,val in zip(indices,vals):
twitter_archive_clean.loc[i, 'rating_numerator'] = val
twitter_archive_clean.loc[i, 'rating_denominator'] =10
# drop the index: 516
twitter_archive_clean.drop(index=516,inplace=True)
Test¶
In [52]:
# test for value for one of the entries
assert twitter_archive_clean.loc[1712,'rating_numerator'] ==11.26
# test for droping index=516
assert 516 not in list(twitter_archive_clean.index)
In [53]:
twitter_archive_clean.info()
In [54]:
# check for the rating_denominator values
twitter_archive_clean.rating_denominator.value_counts()
Out[54]:
Tideness¶
3 twitter_archive
- rating_numerator and rating_denominator columns in
twitter_archivedataset should form one column dog_rating normalized out of 10.
Define¶
- divide the rating_numerator / rating_denominator and then mulitiply by 10
- make dog_score column
- drop the columns rating_numerator & rating_denominator column
Code¶
In [55]:
#divide the rating_numerator / rating_denominator and then mulitiply by 10 & make dog_score column
twitter_archive_clean['dog_score'] = 10 * twitter_archive_clean.rating_numerator / twitter_archive_clean.rating_denominator
#drop the columns rating_numerator & rating_denominator column
twitter_archive_clean.drop(['rating_numerator','rating_denominator'],axis=1,inplace=True)
Test¶
In [56]:
# check for values in the dog_score column
twitter_archive_clean.dog_score.value_counts()
Out[56]:
In [57]:
# check for the twitter_archive_clean data
twitter_archive_clean.info()
In [58]:
# rename the id column in twitter_json to tweet_id
tweet_json_clean.columns = ['tweet_id', 'favorite_count', 'retweet_count']
# change the datatype to str(object) in all datasets
datasets = [twitter_archive_clean,image_pred_clean,tweet_json_clean]
for i in datasets:
i.tweet_id = i.tweet_id.astype('object')
Test¶
In [59]:
# check for the datatypes for tweet_id in all datasets
for i in datasets:
assert i.tweet_id.dtypes == 'object'
Tideness¶
4 image_pred dataset condence the columns p1,p1_dog_p1_conf,...etc to dog_breed, confidence
- we are interested in images of dogs only
- we are are going to select those have at least one prediction for dog among the top three prediction
Define¶
- define a dog_breed_confidence function to extract the dog_breed and confience from the top 3 predictions
- apply the function row wise
- assign the new column names
dog_breedandconfidence - drop the un needed columns now
Qulaity issues Now:¶
Define¶
- rename the No breed values with np.nan
- replace the underscore with space and title all breeds values
Code¶
In [60]:
breed = []
confidence = []
# define the function
def dog_breed_confidence(data):
if data.p1_dog:
breed.append(data.p1)
confidence.append(data.p1_conf)
elif data.p2_dog:
breed.append(data.p2)
confidence.append(data.p2_conf)
elif data.p3_dog :
breed.append(data.p3)
confidence.append(data.p3_conf)
else:
breed.append('No breed')
confidence.append(0)
# apply the function row wise
image_pred_clean.apply(dog_breed_confidence,axis =1)
# assign the new column names
image_pred_clean['dog_breed'] = breed
image_pred_clean['confidence'] = confidence
# drop the un needed columns now
image_pred_clean.drop(columns = ['p1', 'p1_dog', 'p1_conf' , 'p2', 'p2_dog',
'p2_conf' , 'p3', 'p3_dog', 'p3_conf'],axis=1, inplace =True)
# rename the No breed values with np.nan
image_pred_clean.replace('No breed',np.nan, inplace=True)
# replace the underscore with space and title all breeds values
image_pred_clean.dog_breed= image_pred_clean.dog_breed.str.replace('_',' ').str.title()
Test¶
In [61]:
# check the top 5 rows in image_pred_clean
image_pred_clean.head()
Out[61]:
Tideness¶
5 tweet_json and image_pred datasets should be part of our main dataset twitter_archive.
- we are interested in the retweet_count and favorite_count from
tweet_jsonand keeping the original data - we are interested in the tweets that have images
Define¶
- use the merge function to merge
twitter_archive_cleanandtweet_json_cleanon tweet_id column (left join) - use the merge function to merge
twitter_archive_cleanandimage_pred_cleanon tweet_id column (inner join) - make the master dataset
Code¶
In [62]:
# use the merge function to merge twitter_archive_clean and tweet_json_clean on tweet_id column (left join)
twitter_archive_clean = pd.merge(twitter_archive_clean, tweet_json_clean , how = 'left' , on = 'tweet_id')
# use the merge function to merge `twitter_archive_clean` and `image_pred_clean` on tweet_id column (inner join)
# and make master dataset
master_dataset = pd.merge(twitter_archive_clean, image_pred_clean , how = 'inner' , on = 'tweet_id')
Test¶
In [63]:
# check new dataset after merge
master_dataset.info()
In [64]:
# check that all records have an image
master_dataset.jpg_url.isnull().sum()
Out[64]:
In [65]:
# check for the unique values
master_dataset.source.unique()
Out[65]:
In [66]:
#make a function fix_source which extract the strings between tags
def fix_source(i):
'i is an html string from the source column in twitter_archive_clean dataset'
#find the first closed tag >
x= i.find('>') + 1
# find the first open tag after the previous <
y =i[x:].find('<')
# extract the text in between
return i[x:][:y]
In [67]:
# use apply function to fix the source column row wise
master_dataset.source= master_dataset.source.apply(lambda x: fix_source(x))
Test¶
In [68]:
# check for the result values in the source column
master_dataset.source.value_counts()
Out[68]:
- The Vine records are lost in merging the datasets as they haven't a jpg_url they are vedio links not passed in the model.
In [69]:
# change the datatype of timestamp column to datetime
master_dataset.timestamp = pd.to_datetime(master_dataset.timestamp)
Test¶
In [70]:
# check for the datatype
master_dataset.timestamp.dtype
Out[70]:
In [71]:
# extract the month name
master_dataset['month'] = master_dataset.timestamp.apply(lambda x: x.month_name())
#extarct the day_name
master_dataset['day_name'] = master_dataset.timestamp.apply(lambda x: x.day_name())
Test¶
In [72]:
# check for the top 5 rows in columns timestamp, day_name and month
master_dataset.loc[:5,['timestamp','day_name','month']]
Out[72]:
In [73]:
# chheck for the datatypes
master_dataset.info()
Quality¶
7 column name :
- rename to dog_name
- some values are not titled
untitled_unlowers('BeBe','DonDon','CeCe',, 'JD', 'DayZ') - some are inacuarte values :
lowers['such', 'a', 'quite', 'not', 'one', 'incredibly', 'mad','an', 'very', 'just', 'my', 'his', 'actually', 'getting','this', 'unacceptable', 'all', 'old', 'infuriating', 'the','by', 'officially', 'life', 'light', 'space']
Define¶
- rename the column to dog_name
- converted lower names to np.nan
- make all values titled
- relace 'None' values with np.nan values
Code¶
In [74]:
# rename the name column to dog_name
master_dataset.rename(columns={'name':'dog_name'},inplace=True)
# converted lower names to np.nan
lowers = master_dataset.dog_name.str.islower()
master_dataset.loc[lowers,'dog_name'] = 'None'
# make all values titled
master_dataset.dog_name = master_dataset.dog_name.apply(lambda x: x.title())
# relace 'None' with np.nan values
master_dataset.dog_name.replace('None', np.nan, inplace= True)
Test¶
In [75]:
# check for all is titled
master_dataset.dog_name.str.istitle().value_counts()
Out[75]:
In [76]:
# assert for our work
assert [i.title() in master_dataset.dog_name.unique() for i in untitled_unlowers]
assert [i in master_dataset.dog_name.unique() for i in lowers]
assert 'dog_name' in master_dataset.columns
In [77]:
# check for dog_name frequencies
master_dataset.dog_name.value_counts()
Out[77]:
In [78]:
# check for data names
master_dataset.info()
Quality¶
8 We are interested in dogs , text column reveals the truth about that some tweets are not related to dogs
Define¶
- check for the text column for
only rate dogsas it is used by the account admin to address that photo is not a dog - confirm that has no name in dog_name column
- drop the rows that contains this text using their indices
Code¶
In [79]:
# check for the text column for only rate dogs in text and null value for dog_name
not_dogs = master_dataset.loc[master_dataset.dog_name.isnull()& master_dataset.text.str.match('.*only rate dogs')]
# check for number of records
len(not_dogs)
Out[79]:
In [80]:
# explore data
not_dogs
Out[80]:
In [81]:
# collect indices
#indices = master_dataset.loc[master_dataset.dog_name.isnull()& master_dataset.text.str.match('.*only rate dogs')].index.tolist()
# drop the rows master_dataset.index[indices]
master_dataset.drop(not_dogs.index,axis= 0,inplace=True)
Test¶
In [82]:
# check for only rate dogs if still exists
len(master_dataset.loc[master_dataset.dog_name.isnull()& master_dataset.text.str.match('.*only rate dogs')])
Out[82]:
In [83]:
# check the new shape
master_dataset.shape
Out[83]:
In [84]:
# check for else
master_dataset.loc[master_dataset.text.str.match('.*only rate dogs'),['text','dog_name']]
Out[84]:
In [85]:
# So sucess! as above the text ensure that this is a real dog!
Check Outliers¶
In [86]:
master_dataset[master_dataset.dog_score >14]
Out[86]:
 |  |
- It seems that we have funny outliers here the Atticuss gets the highest score in celebration of Indenpence day, so the score here related to the occassion and his dress
- The second one is also a funny joke as this is not a pic of a real dog, this singer nickname is snoop dogg #### Define
- So we are going to drop these outliers #### Code
In [87]:
# drop outliers
outliers = master_dataset[master_dataset.dog_score >14].index.tolist()
master_dataset.drop(outliers,axis = 0, inplace=True)
Test¶
In [88]:
# check for the master data shape
master_dataset.shape
Out[88]:
In [89]:
master_dataset[master_dataset.dog_score>14]
Out[89]:
Final Check to the Tidy Master Dataset¶
In [90]:
#check for the final master
master_dataset.info()
Store Data¶
In [91]:
# Store the data after combinig and cleaning
master_dataset.to_csv('twitter_archive_master.csv',encoding='utf-8',index=False)
Visualization & Analysis¶
1 What's the most source used by followers to share their dog's nice photo ?¶
In [92]:
plt.title("Distribution of Tweets'Source")
master_dataset.source.value_counts().sort_values().plot(kind ='barh')
plt.xlabel('Total Tweets')
plt.ylabel('Source');
In [93]:
# percentage of sources
master_dataset.source.value_counts() / master_dataset.source.value_counts().sum()
Out[93]:
It is clear from the above that Twitter app in Iphone has the most share 98% which is better explained by :
- The ease of use to take a shot for a dog from the app
- The high resolution of cameras.
- Nice to mention here that around 91 records of Vine source was omitted in the cleaning process as they contain no jpg_url and those counts for around only 5% so the above insight is still valid.
2 Which is the most popular day/month to post a dog photo?¶
In [94]:
master_dataset.day_name.value_counts().plot('bar')
plt.title("Distribution of Tweets over Days")
plt.xlabel('Week Days')
plt.ylabel('Frequency');
In [95]:
master_dataset.month.value_counts().plot('bar')
plt.title("Distribution of Tweets over Months")
plt.xlabel('Month')
plt.ylabel('Frequency');
- It is quiet clear that people tend to post their dogs photos in Mondy/ December
- Interestingly, most day is Mondy, may indicate that most of followers is out of stress (may be not workers)
- The top month is December may intrepreted as the time of Christmas and New Year vacations, so people tends to go out with their dogs and take shots
- These interpretations needs more data and investigation to be confirmed
In [96]:
# select the month and day from timestamp e.g 01/07 will be 107
master_dataset.timestamp.apply(lambda x: x.day*100 + x.month ).value_counts().sort_values(ascending =False)[:15].plot('bar')
plt.title("Distribution of Tweets over Day/Month")
plt.xlabel('Day/Month "ddmm"')
plt.xticks(rotation = 90)
plt.ylabel('Frequency');
- Viola ! It's quiet clear now , The most common Day/Month in our sample dataset indicate that most posts related to the end of November , which is matching with Thanksgiving Holidays, as it comes at the the Fourth Thursday of November after that Black Fridays which is also a holiday
3 Which is the most common dog name?¶
In [97]:
# rank the names frequency in a descending order
master_dataset.dog_name.value_counts().sort_values(ascending =False)[:10].plot('barh')
plt.title("Most Common Dogs' Names")
plt.xlabel('Frequency')
plt.ylabel("Dog's Name");
- It is obvious here, that most used dog name in our sample is Charlie, followed by Oliver and Lucy.!
- May be that people tend to use real names for their dogs
4 What is the most common dog stage?¶
In [98]:
# select the dog_ stage frequencies
master_dataset.dog_stage.value_counts().plot('bar')
plt.title("Distribution of Dog Stages")
plt.xlabel('Dog Stage')
plt.ylabel('Frequency');
- As per the dogotionary Pupper is : a small doggo, here Pupper is the most common dog stage in our dataset
- This may be due to dogs at this stage/age prefered by owners.
Caveats: We need to note that some values for the dog stage is missed may be because not known by the account itself and/or the dog's owner
5 How do @WeRateDogs account rate dogs?¶
In [99]:
# histogram for the dog score
master_dataset.dog_score.hist(bins=15)
plt.title('Distribution of Dog scores')
plt.xlabel('Scores')
plt.ylabel('Count')
plt.axvline(x= master_dataset.dog_score.mean(),color='orange', linestyle='--',label ='Mean')
plt.xticks(np.arange(15))
plt.legend(loc=0);
In [100]:
# descriptive stats
master_dataset.dog_score.describe()
Out[100]:
- We can notice from the above plot that the most frequent score is arround 12, and the maximun is 14
- Although the rating system for the account is /10 but actually the average rating is 10.55!
- One of the most causes for this account poularity they tend to give higher scores i.e above 10, Brent was right!
6 Which is the most common breed?¶
- Note : I need to iterate here over the cleaning process to fix the name of breeds (remove underscore and titled breed names)
In [101]:
# frequency for dog breeds
master_dataset.dog_breed.value_counts()[:10].plot('bar')
plt.title('Distribution of Dog Breeds')
plt.xlabel('Breeds')
plt.ylabel('Count');
- Here, The most common breed in our sample is Golden Retriever
Caveats:
- The breeds data contains a lot of null values
- Also to take into consideration that this data is given by a neural network model
7 What is the account performance over time?¶
- Here I need to answer the question if this twitter account getting more popularity over time or followers interest is going to decline
- So, I used the rolling average to get more insight about performance on 30 days rolling average
In [102]:
# set a 30 days rolling average for favorite count
y1= master_dataset.favorite_count.rolling(window = 30).mean()
# set a 30 days rolling average for retweet count
y2= master_dataset.retweet_count.rolling(window = 30).mean()
x = master_dataset.timestamp
plt.plot(x,y1)
plt.plot(x,y2)
plt.xticks(rotation = 90)
plt.title('30 days Rolling Average Account Performance')
plt.xlabel('Dates')
plt.ylabel('30 days average')
plt.legend(loc=0);
It's quiet clear here that the @WeRateDogs account is getting more popular overtime, noticed by the increase number of likes(favorite_count)
- Also it is quiet clear that folowers tends to like more than to retweet
8 Who is the top retweeted and/or favorite dog?¶
In [103]:
def get_photo(param):
"""
get photo and numbers of the top of param after sorting descendingly.
INPUT: param as one of our dataset columns
----------------------------------------------
OUTPUT:
image saved from the jpg_url link
print out the numbers for the top
"""
winner = master_dataset.loc[master_dataset[param].sort_values(ascending =False).index[0]]
r = requests.get(winner['jpg_url'])
i =Image.open(BytesIO(r.content))
i.save('.\images/'+f'top_of_{param}.jpg')
print(f'Top {param} is: {winner[param]}')
In [104]:
get_photo('favorite_count')
In [105]:
get_photo('retweet_count')
Top Favorite count winner with 132,810 likes |
Top Retweet count winner with 79,515 retweets |
In [106]:
# final winner
# get the winner who has the largest retweet_count and the favorite_count
max_retweet , max_favorite = master_dataset.favorite_count.groupby(master_dataset['retweet_count']).value_counts().\
sort_values(ascending=False).index[0]
winner = master_dataset.query('favorite_count == @max_favorite & retweet_count == @max_retweet')
r = requests.get(winner['jpg_url'].item())
i =Image.open(BytesIO(r.content))
i.save('.\images\winner.jpg')
# prin the final result
print(f"No of retweets is : {winner['retweet_count'].item()}, \nNo of favorite_count is {winner['favorite_count'].item()}")
In [107]:
# winner dog-score
winner['dog_score']
Out[107]:
This awesome dogs prove his capability to swim in a pool so, he catched the attenion of followers either by retweet or give him a like
9 How @WeRateDogs account write their posts? (DogCloud)¶
In [108]:
text = master_dataset.text.to_string(index =False).replace('/','').strip()
In [109]:
#select the text
def wordzcloud(text):
#text =df_sql.review[0]
# choose the mask from a google dog pictures
url = 'https://cdn.pixabay.com/photo/2013/11/28/11/32/dog-220324_960_720.jpg'
r = requests.get(url)
mask = np.array(Image.open(BytesIO(r.content)))
# set stopwords
stopwords = ('This','and','is','the','to')#set(STOPWORDS)
# set other parameters
wc = WordCloud(background_color= 'white',
mask = mask,
stopwords=stopwords,
max_words=100,
contour_color='blue')
# generate the word cloud
wc.generate(text)
# show the image
wc.to_file('.\images\dog_cloud.jpg')
return wc.to_image()
In [110]:
# generate the word cloud from text
wordzcloud(text)
Out[110]:
Intersting Notes Here:
- The admin uses friendly words as Meet, Say Hello, Here ,love,like...
- The appearance of He is more than She which indicates the gender of dog is more for male dogs
- The presence of Pupper dog stage is much larger than doggo, which is matching with my analysis.