In [1]:
import pandas as pd
import requests
import os
import tweepy
import json
import re
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
In [2]:
##### Gathering data #####
In [3]:
# Manually download and open the file
df_twitter_enhanced = pd.read_csv('twitter-archive-enhanced.csv')
df_twitter_enhanced.head(3)
Out[3]:
In [4]:
# Download image.predictions.tsv file using request library
url = 'https://d17h27t6h515a5.cloudfront.net/topher/2017/August/599fd2ad_image-predictions/image-predictions.tsv'
r = requests.get(url)
In [5]:
# write the downloaded file in to image.predictions.tsv
file_name = url.split('/')[-1]
with open(file_name,'wb') as f:
f.write(r.content)
In [6]:
# open and read the downloaded image.predictions.tsv file
df_image_predictions = pd.read_csv(file_name,sep='\t')
df_image_predictions.head()
Out[6]:
In [7]:
# get the consumer and acces token and keys
consumer_key = ''
consumer_secret = ''
access_token = ''
access_secret = ''
auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_secret)
api = tweepy.API(auth,wait_on_rate_limit=True,wait_on_rate_limit_notify=True)
In [8]:
#Get tweet id's in a list
tweet_id = []
for items in df_twitter_enhanced.values:
tweet_id.append(items[0])
print(len(tweet_id))
In [9]:
# Get the json data for the tweet id's and store the data in the form of list
json_file = 'tweet_json.txt'
err= {}
tlist=[]
for t in tweet_id:
try:
ranking = tweet_id.index(t) + 1
tweet = api.get_status(t,tweet_mode = 'extended')
tweet = tweet._json
tlist.append({'ranking': int(ranking),
'tweet_id': t,
'json_data': tweet})
except tweepy.TweepError as e:
print(e)
In [10]:
# Dump the list in to a text file tweet_json.txt
with open(json_file,'w+') as f:
json.dump(tlist,f,indent=4)
In [11]:
# Read the text file tweet_json.txt
with open('tweet_json.txt') as j_file:
data = json.load(j_file)
In [12]:
# Extract rank,tweet_id,retweet_count and favourite count from the json.txt file and create a dictionary
new_list=[]
for d in data:
new_list.append({'ranking':d['ranking'],
'tweet_id':d['tweet_id'],
'retweet_count':d['json_data']['retweet_count'],
'favourite_count':d['json_data']['favorite_count'],
'timestamp':d['json_data']['created_at']
})
In [13]:
# convert the dictionary in to a dataframe and save.
df_tweet_api = pd.DataFrame(new_list,columns=['ranking','tweet_id','retweet_count','favourite_count','timestamp'])
df_tweet_api = df_tweet_api.sort_values('ranking').reset_index(drop=True)
df_tweet_api.tail()
Out[13]:
In [14]:
##### Assesing data #####
In [15]:
df_twitter_enhanced.info()
In [16]:
df_twitter_enhanced.describe()
Out[16]:
In [17]:
df_twitter_enhanced['rating_numerator'].value_counts()
Out[17]:
In [18]:
df_image_predictions.info()
In [19]:
df_image_predictions.describe()
Out[19]:
In [22]:
df_tweet_api.info()
In [23]:
df_tweet_api.describe()
Out[23]:
In [25]:
#let's look at the duplicated values
all = pd.Series(list(df_twitter_enhanced)+list(df_image_predictions)+list(df_tweet_api))
all[all.duplicated()]
Out[25]:
In [18]:
# Quality:
#Twitter_enhanced table:
#1. Rating Numerator data type should be float
#2. Rating Numerator values needs to get modified
#3. Timestamp datatype should get converted to datetime.date
#4. Type of dog datatype needs to be category type
#5. Name column has some incorrect values like a,an,The,This.
#6. Combine different types of dog stage in to a single Dog_Stage column
#Image_predictions table:
#7. Incorrect names are displayed Eg: paper_towel is not a dog breed
#8. confidence intervals columns can be round-off to 2 decimals to the right.
#Tweet_API table:
#9. Convert the timestamp datatype to datetime.date
#Tidiness:
#1. Tweet_api table can be merged with twitter enhanced table
#2. Image predictions table can be merged with twitter enhanced table
#3. Drop the duplicated and the unwanted columns
#4. Realign the columns in order starting from Ranking column
#5. Rename the column names for better understanding in visualization
#6. save the combined master dataframe to twitter_archive_master.csv
In [26]:
#### CLEANING THE DATA ####
In [27]:
#Copy the a temporary dataset for the cleaning purpose.
df_twitter_enhanced_clean = df_twitter_enhanced.copy()
df_image_predictions_clean = df_image_predictions.copy()
df_tweet_api_clean = df_tweet_api.copy()
In [28]:
#::::: QUALITY ::::
# 1. Rating Numerator data type should be float
#Define:
#Change the Rating Numerator datatype to float using astype
#Code:
df_twitter_enhanced_clean.rating_numerator = df_twitter_enhanced_clean.rating_numerator.astype(float)
#Test:
df_twitter_enhanced_clean.info()
#we are able to see the int data type has been changed to float
In [29]:
# 2. Rating Numerator needs to get modified
#Define:
#Take the text column from twitter_enhanced table and extract only the digits and save them in a sepreate list.And then use
#a loop to extract the first digit from the list.this will give us the rating_numerator.Finally replace the
#existing rating_numerator with the new list.
#Code:
sf_test = df_twitter_enhanced_clean.text #Take the text column seperately to work split
# Extract the digits from the text and save it in a temporary list
temp = []
new_rating = []
for s in sf_test:
temp.append(re.findall(r'\d+(?:\.\d+)?', s))
#Now, Extract only the rating numerator.
for a in temp:
new_rating.append(a[0])
#Drop the exiting rating_numerator column
df_twitter_enhanced_clean = df_twitter_enhanced_clean.drop('rating_numerator',axis=1)
# Add the new list to the dataframe using numpy
n = np.asarray(new_rating)
df_twitter_enhanced_clean['rating_numerator'] = n
df_twitter_enhanced_clean.query('rating_numerator == "007"')
# modify value in rating_numerator column to 7 from 007
df_twitter_enhanced_clean.loc[df_twitter_enhanced_clean.tweet_id == 826598799820865537, 'rating_numerator'] = 7
#Test:
df_twitter_enhanced_clean['rating_numerator'].unique()
#Now,we are able to see the rating_numerato dispalyed the float values as well.
Out[29]:
In [30]:
# 3. Convert timestamp column to datetime.date
#Define:
#use datetime.date to convert the timestamp column.
#Code:
df_twitter_enhanced_clean['timestamp'] = pd.to_datetime(df_twitter_enhanced_clean['timestamp']).apply(lambda x:x.date())
#Test:
df_twitter_enhanced_clean.timestamp.unique()
#Now, we can confirm that there are no tweet_id's in twitter_enhanced dataframe that are greater than 01-Aug'17'
Out[30]:
In [31]:
#4. Type of dog datatype needs to be category type
#Define:
#Change the datatype of dog type columns to category using astype
#Code:
df_twitter_enhanced_clean.doggo = df_twitter_enhanced_clean.doggo.astype('category')
df_twitter_enhanced_clean.floofer = df_twitter_enhanced_clean.floofer.astype('category')
df_twitter_enhanced_clean.pupper = df_twitter_enhanced_clean.pupper.astype('category')
df_twitter_enhanced_clean.puppo = df_twitter_enhanced_clean.puppo.astype('category')
#Test:
df_twitter_enhanced_clean.info()
#Dog type columns are moved to Category datatype
In [32]:
#5. Name column has some in-correct values
#Define:
#Name column has some incorrect values like (a,the,an,this,All). This can be replaced with None using str.replace command
#Code:
df_twitter_enhanced_clean.name = df_twitter_enhanced_clean.name.str.replace('a', 'None')
df_twitter_enhanced_clean.name = df_twitter_enhanced_clean.name.str.replace('an', 'None')
df_twitter_enhanced_clean.name = df_twitter_enhanced_clean.name.str.replace('the', 'None')
df_twitter_enhanced_clean.name = df_twitter_enhanced_clean.name.str.replace('This', 'None')
df_twitter_enhanced_clean.name = df_twitter_enhanced_clean.name.str.replace('All', 'None')
#Test:
df_twitter_enhanced_clean.query('name =="a"' or 'name =="an"' or'name =="the"' or 'name =="This"'or 'name =="All"')
#Now, all the incorrect name values has been replaced with None
Out[32]:
In [33]:
#6. Combine different types of dog stage in to a single Dog_Stage column
#Define:
# Combine different types of dog stages using fill.na option
#Code:
df_twitter_enhanced_clean.doggo = df_twitter_enhanced_clean.doggo.str.replace('None','')
df_twitter_enhanced_clean.floofer = df_twitter_enhanced_clean.floofer.str.replace('None','')
df_twitter_enhanced_clean.pupper = df_twitter_enhanced_clean.pupper.str.replace('None','')
df_twitter_enhanced_clean.puppo = df_twitter_enhanced_clean.puppo.str.replace('None','')
df_twitter_enhanced_clean['Dog_Stage'] = df_twitter_enhanced_clean[['doggo','floofer','pupper','puppo']].fillna('').sum(axis=1)
df_twitter_enhanced_clean.Dog_Stage = df_twitter_enhanced_clean.Dog_Stage.str.replace('doggopuppo','doggo')
df_twitter_enhanced_clean.Dog_Stage = df_twitter_enhanced_clean.Dog_Stage.str.replace('doggofloofer','floofer')
df_twitter_enhanced_clean.Dog_Stage = df_twitter_enhanced_clean.Dog_Stage.str.replace('doggopupper','pupper')
#Test:
df_twitter_enhanced_clean['Dog_Stage'].unique()
Out[33]:
In [34]:
#6. Incorrect names are displayed Eg: paper_towel is not a dog breed in Image_Predictions file
#Define:
#Perdictions column has some invalid predictions which needs to replaced
#Code:
df_image_predictions_clean.p1 = df_image_predictions_clean.p1.str.replace('electric_fan', 'None')
df_image_predictions_clean.p1 = df_image_predictions_clean.p1.str.replace('web_site', 'None')
df_image_predictions_clean.p1 = df_image_predictions_clean.p1.str.replace('fire_engine', 'None')
df_image_predictions_clean.p1 = df_image_predictions_clean.p1.str.replace('orange', 'None')
df_image_predictions_clean.p1 = df_image_predictions_clean.p1.str.replace('pencil_box', 'None')
df_image_predictions_clean.p1 = df_image_predictions_clean.p1.str.replace('paper_towl', 'None')
df_image_predictions_clean.p2 = df_image_predictions_clean.p2.str.replace('cup', 'None')
df_image_predictions_clean.p2 = df_image_predictions_clean.p2.str.replace('sports_car', 'None')
df_image_predictions_clean.p2 = df_image_predictions_clean.p2.str.replace('purse', 'None')
df_image_predictions_clean.p3= df_image_predictions_clean.p3.str.replace('car_wheel', 'None')
df_image_predictions_clean.p3 = df_image_predictions_clean.p3.str.replace('can_opener', 'None')
#Test
df_image_predictions_clean.query('p1 =="electric_fan"' or 'p1 =="web_site"')
df_image_predictions_clean.query('p2 =="sports_car"' or 'p2 =="purse"')
df_image_predictions_clean.query('p2 =="car_wheel"' or 'p2 =="can_opener"')
#Now, All those invlaid predications have been programmtically get converted to None
Out[34]:
In [35]:
#7.Confidence intervals columns can be round-off to 2 decimals
#Define
#Round-Off the confidence intervals to 2 decimal digits using numpy
#Code
df_image_predictions_clean.p1_conf = np.round(df_image_predictions_clean.p1_conf,decimals=2)
df_image_predictions_clean.p2_conf = np.round(df_image_predictions_clean.p2_conf,decimals=2)
df_image_predictions_clean.p3_conf = np.round(df_image_predictions_clean.p3_conf,decimals=2)
#Test
df_image_predictions_clean.head()
#Now,All the confidence intervals are round off to 2 decimal points
Out[35]:
In [36]:
#8. Convert timestamp column of tweet_api table to datetime.date
#Define:
#use datetime.date to convert the timestamp column.
#Code:
df_tweet_api_clean['timestamp'] = pd.to_datetime(df_tweet_api_clean['timestamp']).apply(lambda x:x.date())
#Test:
df_tweet_api_clean.tail(5)
#Now, we can confirm that there are no tweet_id's in tweet_api dataframe that are greater than 01-Aug'17'
Out[36]:
In [37]:
#::::: TIDINESS ::::
In [38]:
#1,2. Merge all the 3 dataframes in to a single master dataframe twitter_archive_master
#Define:
#Merge all the 3 dataframes() in to a single master dataframe twitter_archive_master using merge command
#with tweet_id as the key
#Code:
#Merge df_tweet_api_clean dataframe on df_twitter_enhanced_clean dataframe using tweet_id
twitter_archive_master = pd.merge(df_twitter_enhanced_clean, df_tweet_api_clean,
on=['tweet_id'], how='left')
#Merge twitter_archive_master dataframe on df_image_predictions_clean dataframe using tweet_id
twitter_archive_master = pd.merge(twitter_archive_master, df_image_predictions_clean,
on=['tweet_id'], how='left')
#Change the Rating_numerator to float
twitter_archive_master.rating_numerator = twitter_archive_master.rating_numerator.astype('float')
#Test:
twitter_archive_master.info()
#Now we sucessfully created the master data frame called twitter_archive_master. Now, lets to tidiness work on the master
#dataframe and remove unwanted and duplicate columns.
In [39]:
#3. Drop the Duplicate and unwanted columns for our Visualization analysis
#Define:
#Let's remove duplicate columns (timestamp_y),different dog stages and unwanted columns(in_reply_to_status_id,in_reply_to_user_id,
#retweeted_status_id,retweeted_status_user_id,retweeted_status_timestamp,img_num) using drop.na command
#Code:
#twitter_archive_master.drop(twitter_archive_master.columns['in_reply_to_status_id','in_reply_to_user_id','retweeted_status_id','retweeted_status_user_id','retweeted_status_timestamp','img_num','timestamp_y'],axis=1,inplace=True)
twitter_archive_master = twitter_archive_master.drop('in_reply_to_status_id', axis=1)
twitter_archive_master = twitter_archive_master.drop('in_reply_to_user_id', axis=1)
twitter_archive_master = twitter_archive_master.drop('retweeted_status_id', axis=1)
twitter_archive_master = twitter_archive_master.drop('retweeted_status_user_id', axis=1)
twitter_archive_master = twitter_archive_master.drop('retweeted_status_timestamp', axis=1)
twitter_archive_master = twitter_archive_master.drop('img_num', axis=1)
twitter_archive_master = twitter_archive_master.drop('timestamp_y', axis=1)
twitter_archive_master = twitter_archive_master.drop('ranking', axis=1)
twitter_archive_master = twitter_archive_master.drop('doggo', axis=1)
twitter_archive_master = twitter_archive_master.drop('puppo', axis=1)
twitter_archive_master = twitter_archive_master.drop('floofer', axis=1)
twitter_archive_master = twitter_archive_master.drop('pupper', axis=1)
#Test:
twitter_archive_master.info()
#Now, we have dropped all the unwanted columns.Let's re-align the columns accordingly
In [40]:
#Let's check for Duplicated before proceeding
twitter_archive_master[twitter_archive_master.duplicated()]
Out[40]:
In [41]:
#Looks like there are no duplicates, so let's proceed to our final cleaning process.
In [42]:
#4. Re-align the columns
#Define:
#Re-align the columns for better readbility
#Code:
twitter_archive_master = pd.DataFrame(twitter_archive_master,columns=['tweet_id','name','rating_numerator','rating_denominator','retweet_count','favourite_count','p1','p1_conf','p1_dog','p2','p2_conf','p2_dog','p3','p3_conf','p3_dog','Dog_Stage','timestamp_x','text','source','jpg_url','expanded_urls'])
#Test:
twitter_archive_master.tail(2)
#Finally, Let's rename few of the column for better user understanding during visualization
Out[42]:
In [44]:
#5. Rename the master dataframe
#Define:
# tweet_id --> Tweet_id
# name --> Name
# rating_numerator --> Rating_Numerator
# rating_denominator --> Rating_Denominator
# retweet_count --> Retweet_Count
# favourite_count --> Favourite_count
# p1,p2,p3 --> Breed_Prediction_1,Breed_Prediction_2,Breed_Prediction_3
# p1_conf,p2_conf,p3_conf --> Breed_Conf_Intrvl_1,Breed_Conf_Intrvl_2,Breed_Conf_Intrvl_3
# p1_dog,p2_dog,p3_dog --> Breed_Prediction_Result_1,Breed_Prediction_Result_2,Breed_Prediction_Result_3
#doggo --> Doggo
#floofer --> Floofer
#pupper --> Pupper
#puppo --> Puppo
#timestamp_x --> Timestamp
#text --> Text
#source --> Source
#jpg_url --> Jpg_url
#expanded_urls --> Expanded_urls
#Code:
twitter_archive_master.columns = ['Tweet_id','Name','Rating_Numerator','Rating_Denominator','Retweet_Count','Favourite_count','Breed_Prediction_1','Breed_Conf_Intrvl_1','Breed_Prediction_Result_1','Breed_Prediction_2','Breed_Conf_Intrvl_2','Breed_Prediction_Result_2','Breed_Prediction_3','Breed_Conf_Intrvl_3','Breed_Prediction_Result_3','Dog_Stage','Timestamp','Text','Source','Jpg_url','Expanded_urls']
#Test:
twitter_archive_master.head(2)
Out[44]:
In [45]:
#Save the twitter_archive_master dataframe
twitter_archive_master.to_csv('twitter_archive_master.csv')
In [46]:
#Conclusion:
#In this project, we took a 'We rate dogs' page and have used Data Wrangling processs like Gathering the data
#from various sources and Assessed the data to find out the Quality and Tidiness issues and then cleaning all the data
#issues identified to perform a better visualization Analysis.
In [47]:
#::::: VISUALIZATION ::::
#In the Visualization section, we can cover lots of insights between the variables as we have lots of information about dogs
#like ratings,breed type,dog type,retweet counts,favourite counts etc.
#But, In our section we gonna take 3 insights and for one of the insight, we gonna plot a bar graph to visualize analayze it.
#Below are the Insights we are going to perform below
#INSIGHT1 ---- Rating Numerator vs Tweet_id with Visualization
#INSIGHT 2 --- Most Re-tweeted dog
#INSIGHT 3 --- Most Favourite dog
In [48]:
#INSIGHT1 ---- Rating Numerator vs Tweet_id with Visualization
#Intro :
#Let's do a analysis on 'We rate dogs',Here we are going to do a insight about how often the page
#is giving same rating for the dog breeds which will help us predicting how many dogs got lower ratings and
#how many dogs got higher ratings etc.
twitter_archive_master = pd.read_csv('twitter_archive_master.csv') #Open the saved master dataframe
df = twitter_archive_master[twitter_archive_master['Rating_Numerator'] <= 18]
df_group = df.groupby(['Rating_Numerator'])['Tweet_id'].count()
r1 = df_group[0]
r2 = df_group[1]
r3 = df_group[2]
r4 = df_group[3]
r5 = df_group[4]
r6 = df_group[5]
r7 = df_group[6]
r8 = df_group[7]
r9 = df_group[8]
r10 = df_group[9]
r11 = df_group[10]
r12 = df_group[11]
r13 = df_group[12]
r14 = df_group[13]
r15 = df_group[14]
r16 = df_group[15]
r17 = df_group[17]
#Visualization
N=17
locations = np.arange(N)
rating = [r1,r2,r3,r4,r5,r6,r7,r8,r9,r10,r11,r12,r13,r14,r15,r16,r17]
labels = ['0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','17']
plt.bar(locations,rating,tick_label=labels),
plt.xticks(locations)
plt.title('Rating Numerator vs Tweet_id')
plt.xlabel('Rating Numerator')
plt.ylabel('Tweet_Id')
#By Looking at the bar plot between the Rating_Numerator and the Tweet_id. 'We rate dogs' page gave rating as 12 for most of the dogs,
#followed by ranks 10 and 11 and then followed by 13.If you look at the distribution of the plot.. it's more left skewed. Also,
#since we have only plot the graph for rank with whole digits till 17, we cannot really predict the distribution here.But this plot
#give us a good information about the relation between Rating_Numerator and Tweet_id
Out[48]:
In [49]:
#INSIGHT 2 --- Most Re-tweeted dog
#Let's find out which breed of dog has been retweeted most number of times.
twitter_archive_master.Retweet_Count.max() #get the max. retweet count
twitter_archive_master.query('Tweet_id==744234799360020481') # Fetch the row details using query method
#So, The most retweeted dog in our dataset is Labrador_retriever with a confidence interval of 0.83 from image predictions analysis
#using neural netwrok.Also it has a impressive Favourite counts of 128452 and it's in doggo stage.we rate dogs gave a rating of 13/10
# for this dog
Out[49]:
In [50]:
#INSIGHT 3 --- Most Favourite dog
#This time let's take a look at the dog that has the most favourite in our dataset.
twitter_archive_master.Favourite_count.max() #get the max. favourite count
twitter_archive_master.query('Tweet_id==822872901745569793') # Fetch the row details using query method
#Here, the suprising factor is predicting the breed of the dog. Becoz if you see the confidence interval
#for all the three predictions are very less 0.2 > 0.16 > 0.07. Out of 3, let's consider the maximun confidence level so this
# breed of dog is Lakeland_terrier.It also has a good retweet_counts of 49K. But unlike the most retweeted dog,our most
#favourite dog is a puppo but both get rated by the page as 13/10.
Out[50]:
In [51]:
#Conclusion:
#In this Visualization section, we took pretty interesting insights like How the 'we rate dogs' rated the dogs and
#the visualization plot gave us more detailed information,the most re-tweeted dog, the most favoutire dog.
In [52]:
from subprocess import call
call(['python', '-m', 'nbconvert', 'wrangle_act.ipynb'])
Out[52]: