<jupyter_start><jupyter_text>Project DetailsThe tasks in this project are as follows:* Data wrangling, which consists of:    * Gathering data (downloadable file in the Resources tab in the left most panel of your classroom and linked in step 1 below).    * Assessing data    * Cleaning data    * Storing, analyzing, and visualizing your wrangled data    * Reporting on 1) your data wrangling efforts and 2) your data analyses and visualizations  1. Gathering Data for this Project: * The WeRateDogs Twitter archive. Download twitter_archive_enhanced.csv file.* The tweet image predictions, i.e., what breed of dog (or other object, animal, etc.) is present in each tweet according to a neural network. This file (image_predictions.tsv) is hosted on Udacity's servers and should be downloaded programmatically using the Requests library and the following URL: https://d17h27t6h515a5.cloudfront.net/topher/2017/August/599fd2ad_image-predictions/image-predictions.tsv* Each tweet's retweet count and favorite ("like") count at minimum, and any additional data you find interesting. Using the tweet IDs in the WeRateDogs Twitter archive, query the Twitter API for each tweet's JSON data using Python's Tweepy library and store each tweet's entire set of JSON data in a file called tweet_json.txt file. Each tweet's JSON data should be written to its own line. Then read this .txt file line by line into a pandas DataFrame with (at minimum) tweet ID, retweet count, and favorite count.<jupyter_code>import pandas as pd
import numpy as np
import requests
import matplotlib.pyplot as plt
import tweepy
from tweepy import OAuthHandler
import json
from timeit import default_timer as timer<jupyter_output><empty_output><jupyter_text>1.1 The WeRateDogs Twitter archive<jupyter_code># Read Twitter Archive Data
twitter_archive = pd.read_csv('twitter-archive-enhanced.csv')<jupyter_output><empty_output><jupyter_text>1.2 Use Requests to get image predictions data<jupyter_code># Download by using Requests and save the data as image_predictions.tsv
url = 'https://d17h27t6h515a5.cloudfront.net/topher/2017/August/599fd2ad_image-predictions/image-predictions.tsv'
r = requests.get(url)
open('image_predictions.tsv', 'wb').write(r.content)
# Read Image Predictions Data
image_predictions = pd.read_csv('image_predictions.tsv', sep = '\t')<jupyter_output><empty_output><jupyter_text>1.3 Use Twitter API and Save JSON<jupyter_code>df_1 = pd.read_csv('twitter-archive-enhanced.csv')


#### 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 = 'xxxxxxxxxxxxxxx'
consumer_secret = 'xxxxxxxxxxxxxxx'
access_token = '<KEY>'
access_secret = '<KEY>'

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 STUDENT WITH MOBILE VERIFICATION ISSUES:
#### df_1 is a DataFrame with the twitter_archive_enhanced.csv file. You may have to
#### change line 17 to match the name of your DataFrame with twitter_archive_enhanced.csv
#### 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 = df_1.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)<jupyter_output><empty_output><jupyter_text>2. Assessing Data for this Project Key PointsKey points to keep in mind when data wrangling for this project:You only want original ratings (no retweets) that have images. Though there are 5000+ tweets in the dataset, not all are dog ratings and some are retweets.Assessing and cleaning the entire dataset completely would require a lot of time, and is not necessary to practice and demonstrate your skills in data wrangling. Therefore, the requirements of this project are only to assess and clean at least 8 quality issues and at least 2 tidiness issues in this dataset.Cleaning includes merging individual pieces of data according to the rules of tidy data.The fact that the rating numerators are greater than the denominators does not need to be cleaned. This unique rating system is a big part of the popularity of WeRateDogs.You do not need to gather the tweets beyond August 1st, 2017. You can, but note that you won't be able to gather the image predictions for these tweets since you don't have access to the algorithm used.  2.1.1 Twitter archive data:<jupyter_code>twitter_archive.info()
twitter_archive.head()
twitter_archive.isnull().sum()
# we don't want retweets, so 181 retweets with retweeted_status_id shall removed
# also, 78 tweet replies need to be removed
twitter_archive.notnull().sum()
#in_reply_to_status_id,in_reply_to_user_id,retweeted_status_id,retweeted_status_user_id shall be string, not float
twitter_archive.dtypes
# numerator of rating <10 shall be removed
twitter_archive.rating_numerator.value_counts()
#Denominator of not 10 shall be removed
twitter_archive.rating_denominator.value_counts()
twitter_archive.groupby('source').count()
pd.set_option('display.max_colwidth', 1000)
# check content in text column to see if there are any rating numerators or denominators contain decimals
twitter_archive[twitter_archive.text.str.contains(r'\d+\.\d+\/(?:\d+\.)?\d+|(?:\d+\.)\d+\/\d+\.\d+')]
# we can see that the rating_numerators are not correct for the text with decimals, we need to include the score
# on the left hand side of the decimal as well
pd.set_option('display.max_colwidth', 24)<jupyter_output><empty_output><jupyter_text>2.1.2 Assessment for Quality Issues 1. There are "<a href=" and other characters in the source column, they shall be removed2. "+0000" in the timestamp column does not provide any value, shall be removed3. tweet_id, in_reply_to_status_id,in_reply_to_user_id,retweeted_status_id,retweeted_status_user_id shall be string, not float.4. In the text, there are ratings with decimal numbers, right now only numbers after the decimal points are captured and loaded into the rating_numerator column.5. As we don’t want retweets and replies, “retweeted_status_timestamp” and other retweets and reply ralated columns becomes irrelevant , this column shall be removed.6. Rows with 'None' in the name column has no dog name in the text  2.1.3 Assessment for Tidiness Issues 1. There are 181 retweets and 78 tweet replies, those rows of entries shall be removed to ensure no duplication of the same tweet. This is following the rule of tidy data, i.e. Each observation forms a row. Therefore, we want each row to only represent an unique entry. 2. Dog types are currently in the form of wide columns, i.e. "doggo", "floofer", "pupper", and "puppo" columns, we shall combine these dog types into one single column as they are one type of variables.   2.2.1 Image prediction data:<jupyter_code>image_predictions.info()
image_predictions.head()
image_predictions.img_num.value_counts()
image_predictions.isnull().sum()
image_predictions.dtypes<jupyter_output><empty_output><jupyter_text>2.2.2 Assessment for Quality Issues 7. Entries with a False “P1_dog” value will be removed, as the model has no confidence to determine the type of dog. 8. “P1_dog” column will be removed as it does not provide information after the False “P1_dog” entries are removed.  2.3.1 Twitter API's JSON file (Retweet vs. Favorite Count Data)<jupyter_code># saving json data to the dataframe
tweets_list =[]

with open('tweet_json.txt') as json_file:
    for line in json_file:
    
        t_dict = {}
        t_json = json.loads(line)
        
        #to handle exceptions, as some of id is na
        try:
            t_dict['tweet_id'] = t_json['extended_entities']['media'][0]['id']
        except:
            t_dict['tweet_id'] = 'na'

        t_dict['retweet_count'] = t_json['retweet_count']
        t_dict['favorite_count'] = t_json['favorite_count']
        
        tweets_list.append(t_dict)
tweets_df = pd.DataFrame(tweets_list)
tweets_df.head()
tweets_df.info()
tweets_df.isnull().sum()<jupyter_output><empty_output><jupyter_text>3. Cleaning Data for this Project  3.1 Twitter Archive data:<jupyter_code>twitter_archive_cleaned = twitter_archive.copy()<jupyter_output><empty_output><jupyter_text>Tidiness Issues: Recap:1. There are 181 retweets and 78 tweet replies, those rows of entries shall be removed to ensure no duplication of the same tweet. This is following the rule of tidy data, i.e. Each observation forms a row. Therefore, we want each row to only represent an unique entry.2. Dog types are currently in the form of wide columns, i.e. "doggo", "floofer", "pupper", and "puppo" columns, we shall combine these dog types into one single column as they are one type of variables.  Define: 1. There are 181 retweets and 78 tweet replies, those rows of entries shall be removed to ensure no duplication of the same tweet. This is following the rule of tidy data, i.e. Each observation forms a row. Therefore, we want each row to only represent an unique entry.  Code:<jupyter_code>twitter_archive_cleaned.drop(twitter_archive_cleaned[twitter_archive_cleaned.retweeted_status_id.notnull()].index, inplace = True)<jupyter_output><empty_output><jupyter_text>Test:<jupyter_code># Test:
twitter_archive_cleaned.retweeted_status_id.notnull().sum()<jupyter_output><empty_output><jupyter_text>Define: 2.Dog types are currently in the form of wide columns, i.e. "doggo", "floofer", "pupper", and "puppo" columns, we shall combine these dog types into one single column as they are one type of variables.  Code:<jupyter_code># First, use lambda to join all types into one value

twitter_archive_cleaned['dogStage'] = twitter_archive_cleaned[['doggo', 'floofer','pupper','puppo']].apply(lambda x: ''.join(x), axis=1)

# single stage
twitter_archive_cleaned['dogStage'].replace("NoneNoneNoneNone","None ", inplace=True)
twitter_archive_cleaned['dogStage'].replace("doggoNoneNoneNone","doggo", inplace=True)
twitter_archive_cleaned['dogStage'].replace("NoneflooferNoneNone","floofer", inplace=True)
twitter_archive_cleaned['dogStage'].replace("NoneNonepupperNone","pupper", inplace=True)
twitter_archive_cleaned['dogStage'].replace("NoneNoneNonepuppo","puppo", inplace=True) 

# mix stage
twitter_archive_cleaned['dogStage'].replace("doggoNonepupperNone","multiple", inplace=True) 
twitter_archive_cleaned['dogStage'].replace("doggoNoneNonepuppo","multiple", inplace=True) 
twitter_archive_cleaned['dogStage'].replace("doggoflooferNoneNone","multiple", inplace=True)<jupyter_output><empty_output><jupyter_text>Test<jupyter_code># Test:
twitter_archive_cleaned.head()
# Test:
twitter_archive_cleaned.dogStage.value_counts()
# To drop old stage columns, they are no longer useful
ToDrop = ['doggo','floofer','pupper','puppo',]
twitter_archive_cleaned.drop(ToDrop,inplace=True,axis=1)
twitter_archive_cleaned.columns<jupyter_output><empty_output><jupyter_text>Quality Issues: Recap all the quality issues:1. There are "<a href=" and other characters in the source column, they shall be removed2. "+0000" in the timestamp column does not provide any value, shall be removed3. tweet_id, in_reply_to_status_id,in_reply_to_user_id,retweeted_status_id,retweeted_status_user_id shall be string, not float.4. In the text, there are ratings with decimal numbers, right now only numbers after the decimal points are captured and loaded into the rating_numerator column.5. As we don’t want retweets and replies, “retweeted_status_timestamp” and other retweets and reply ralated columns becomes irrelevant , this column shall be removed.6. Rows with 'None' in the name column has no dog name in the text  Define: 1. Remove extra parts of urls in source column using split() function  Code<jupyter_code>twitter_archive_cleaned.source = twitter_archive_cleaned.source.str.replace(r'<[^>]*>', '')<jupyter_output><empty_output><jupyter_text>Test:<jupyter_code>twitter_archive_cleaned.head()<jupyter_output><empty_output><jupyter_text>Define: 2. "+0000" in the timestamp column does not provide any value, shall be removed  Code:<jupyter_code>twitter_archive_cleaned.timestamp=twitter_archive_cleaned.timestamp.str.rstrip('+0000')<jupyter_output><empty_output><jupyter_text>Test:<jupyter_code># Test:
twitter_archive_cleaned.head()<jupyter_output><empty_output><jupyter_text>Define: 3.tweet_id, in_reply_to_status_id,in_reply_to_user_id,retweeted_status_id,retweeted_status_user_id shall be string, not float.  Code:<jupyter_code># Code:
ListOfID= ['tweet_id', 'in_reply_to_status_id','in_reply_to_user_id','retweeted_status_id','retweeted_status_user_id']
twitter_archive_cleaned[ListOfID] = twitter_archive_cleaned[ListOfID].astype(str)
# Test:
twitter_archive_cleaned.dtypes<jupyter_output><empty_output><jupyter_text>Define: 4. In the text, there are ratings with decimal numbers, right now only numbers after the decimal points are captured and loaded into the rating_numerator column.  we can see that the rating_numerators are not correct for the text with decimals, we need to include the score on the left hand side of the decimal as well. So let's re-extract the rating numerator with the correct regular expression  Code:<jupyter_code>twitter_archive_cleaned.rating_numerator = twitter_archive_cleaned.text.str.extract(r'((?:\d+\.)?\d+)\/\d+',
                                                                                expand=True).astype('float')<jupyter_output><empty_output><jupyter_text>Test:<jupyter_code>twitter_archive_cleaned.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 2175 entries, 0 to 2355
Data columns (total 14 columns):
tweet_id                      2175 non-null object
in_reply_to_status_id         2175 non-null object
in_reply_to_user_id           2175 non-null object
timestamp                     2175 non-null object
source                        2175 non-null object
text                          2175 non-null object
retweeted_status_id           2175 non-null object
retweeted_status_user_id      2175 non-null object
retweeted_status_timestamp    0 non-null object
expanded_urls                 2117 non-null object
rating_numerator              2175 non-null float64
rating_denominator            2175 non-null int64
name                          2175 non-null object
dogStage                      2175 non-null object
dtypes: float64(1), int64(1), object(12)
memory usage: 254.9+ KB<jupyter_text>Define: 5. all columns relate to retweets and replies will be removed  Code:<jupyter_code>ToDrop = ['in_reply_to_status_id','in_reply_to_user_id','retweeted_status_id','retweeted_status_user_id','retweeted_status_timestamp']
twitter_archive_cleaned.drop(ToDrop,inplace=True,axis=1)<jupyter_output><empty_output><jupyter_text>Test:<jupyter_code>#Test:
twitter_archive_cleaned.head()<jupyter_output><empty_output><jupyter_text>Define: 6. Rows with 'None' in the name column has no dog name in the text  Code:<jupyter_code># Code:
twitter_archive_cleaned.name = twitter_archive_cleaned.name.replace('None', np.nan)<jupyter_output><empty_output><jupyter_text>Test:<jupyter_code># Test:
twitter_archive_cleaned.query('name == "None"')<jupyter_output><empty_output><jupyter_text>3.2 Image Prediction Data:<jupyter_code>image_preds_cleaned = image_predictions.copy()<jupyter_output><empty_output><jupyter_text>Quality Issues: 7. Entries with a False “P1_dog” value will be removed, as the model has no confidence to determine the type of dog.8. “P1_dog” column will be removed as it does not provide information after the False “P1_dog” entries are removed.<jupyter_code>image_preds_cleaned.p1_dog.value_counts()<jupyter_output><empty_output><jupyter_text>Define: 7. Entries with a False “P1_dog” value will be removed, as the model has no confidence to determine the type of dog.  Code:<jupyter_code>image_preds_cleaned.drop(image_preds_cleaned[image_preds_cleaned.p1_dog == False].index, inplace=True)<jupyter_output><empty_output><jupyter_text>Test:<jupyter_code># Test:
image_preds_cleaned.p1_dog.value_counts()<jupyter_output><empty_output><jupyter_text>Define: 8. “P1_dog” column will be removed as it does not provide information after the False “P1_dog” entries are removed.  Code:<jupyter_code>Drop_C = ['p1_dog']
image_preds_cleaned.drop(Drop_C, inplace=True, axis=1)<jupyter_output><empty_output><jupyter_text>Test:<jupyter_code># Test
image_preds_cleaned.head()
image_preds_cleaned.info()
image_preds_cleaned['tweet_id'] = image_preds_cleaned['tweet_id'].astype(str)
image_preds_cleaned.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 1532 entries, 0 to 2073
Data columns (total 11 columns):
tweet_id    1532 non-null object
jpg_url     1532 non-null object
img_num     1532 non-null int64
p1          1532 non-null object
p1_conf     1532 non-null float64
p2          1532 non-null object
p2_conf     1532 non-null float64
p2_dog      1532 non-null bool
p3          1532 non-null object
p3_conf     1532 non-null float64
p3_dog      1532 non-null bool
dtypes: bool(2), float64(3), int64(1), object(5)
memory usage: 122.7+ KB<jupyter_text>3.3 Retweet vs Fav. Data<jupyter_code>tweets_df_cleaned=tweets_df.copy()
tweets_df_cleaned.info()
tweets_df_cleaned.describe()<jupyter_output><empty_output><jupyter_text>Quality and Tidiness seem Ok  4. Storing, Analyzing, and Visualizing Data for this Project  4.1 Combining and Storing<jupyter_code>twitter_archive_master = pd.merge(twitter_archive_cleaned,image_preds_cleaned,on = 'tweet_id' ,how = 'left')
twitter_archive_master = pd.merge(twitter_archive_master,tweets_df_cleaned,on = 'tweet_id' ,how = 'left')
twitter_archive_master.head()
twitter_archive_master.to_csv('twitter_archive_master.csv')
twitter_archive_cleaned.to_csv('twitter_archive_cleaned.csv')
image_preds_cleaned.to_csv('image_preds_cleaned.csv')
tweets_df_cleaned.to_csv('tweets_df_cleaned.csv')<jupyter_output><empty_output><jupyter_text>4.2 Analyzing<jupyter_code>twitter_archive_master.dogStage.value_counts()
import seaborn as sns
# Plotting a bar graph of the number of ooccurrence of dog in each dog stage
dog_count  = twitter_archive_master.dogStage.value_counts()
dog_count = dog_count[1:]
plt.figure(figsize=(10,5))
sns.barplot(dog_count.index, dog_count.values, alpha=0.8)
plt.title('Most popular dog stages in twitter data')
plt.ylabel('Number of dogs in the same stage', fontsize=12)
plt.xlabel('dog stage', fontsize=12)
plt.show()

# Pupper is the most popular stage of dogs<jupyter_output><empty_output><jupyter_text>Insight 1: Pupper is the most popular dog type<jupyter_code>valueCounts=twitter_archive_master.p1.value_counts().reset_index()
valueCounts.rename(columns={'p1':'counts','index':'type'},inplace=True)
#Create a dogRating which is numerator/denominator*10 (score out of 10)
twitter_archive_master['dogRating']=twitter_archive_master['rating_numerator']/twitter_archive_master['rating_denominator']*10
twitter_archive_master.head()
rating=twitter_archive_master.groupby('p1').dogRating.mean().reset_index()
rating.sort_values('dogRating', ascending=False)
rating.rename(columns={'p1':'type'},inplace=True)
valueCounts.head()
valueCounts.info()
rating.info()
rate_table=pd.merge(valueCounts,rating)
# Golden Retriever is the most popular breed
rate_table.head(20)<jupyter_output><empty_output><jupyter_text>Insight 2: Golden Retriever is the most popular breed with 139 counts<jupyter_code># Golden Retriever has the highest rating among top 20 most popular breeds 
rate_table[rate_table['counts']>20].sort_values('dogRating', ascending=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08863122727719368, 'fraction_numerical': 0.019111722511545644, 'mean_word_length': 5.658488714425908, 'pattern_counts': {'":': 1, '<': 143, '<?xml version=': 0, '>': 142, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '34', 'dir': 'jupyter-structured-clean-dedup', 'id': '26985', 'license': 'MIT', 'n_tokens_mistral': 6691, 'n_tokens_neox': 6327, 'n_words': 1941, 'path': 'wrangle_act.ipynb', 'repo_name': 'jrhe90/Udacity_Data_Analyst_Nanodegree', 'size': '20374'}