#添加必要的库
import pandas as pd
import numpy as np
import requests as r
import os
import tweepy as tp
import json
%matplotlib inline
import matplotlib.pyplot as plt
import seaborn as sns

收集¶

#读取相关文件
dfTwitter_Archive = pd.read_csv('twitter-archive-enhanced.csv')

#从网络上下载文件
file_path = r'https://raw.githubusercontent.com/udacity/new-dand-advanced-china/master/%E6%95%B0%E6%8D%AE%E6%B8%85%E6%B4%97/WeRateDogs%E9%A1%B9%E7%9B%AE/image-predictions.tsv'
response = r.get(file_path)
with open(file_path.split('/')[-1],mode='wb') as file:
    file.write(response.content)
dfImage_Predictions = pd.read_csv('image-predictions.tsv',sep='\t')

#从推特下载其他数据 - 因无法访问推特所以直接下载数据

#consumer_key = 'YOUR CONSUMER KEY'
#consumer_secret = 'YOUR CONSUMER SECRET'
#access_token = 'YOUR ACCESS TOKEN'
#access_secret = 'YOUR ACCESS SECRET'

#auth = tp.OAuthHandler(consumer_key, consumer_secret)
#auth.set_access_token(access_token, access_secret)

#api = tp.API(auth)

#打印其他用户主页上的时间轴里的内容
#public_tweets = api.user_timeline('WeRateDogs')
 
#for tweet in public_tweets:
#    print(tweet.text)

#直接读取json
json_file_path = r'tweet_json.txt'
dfOrigin = pd.read_json(json_file_path,lines=True)
dfJson_Tweeter = dfOrigin[['id','retweet_count','favorite_count']]
#dfOrigin[dfOrigin['id']!=dfOrigin['id_str']].shape[0]
dfJson_Tweeter = dfJson_Tweeter.rename(columns={'id':'tweet_id'})

评估¶

dfTwitter_Archive

dfTwitter_Archive.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2356 entries, 0 to 2355
Data columns (total 17 columns):
tweet_id                      2356 non-null int64
in_reply_to_status_id         78 non-null float64
in_reply_to_user_id           78 non-null float64
timestamp                     2356 non-null object
source                        2356 non-null object
text                          2356 non-null object
retweeted_status_id           181 non-null float64
retweeted_status_user_id      181 non-null float64
retweeted_status_timestamp    181 non-null object
expanded_urls                 2297 non-null object
rating_numerator              2356 non-null int64
rating_denominator            2356 non-null int64
name                          2356 non-null object
doggo                         2356 non-null object
floofer                       2356 non-null object
pupper                        2356 non-null object
puppo                         2356 non-null object
dtypes: float64(4), int64(3), object(10)
memory usage: 313.0+ KB

#通过观察发现了rating_denominator的值并不全部为10，还有其他数字
dfTwitter_Archive['rating_denominator'].value_counts()

10     2333
11        3
50        3
80        2
20        2
2         1
16        1
40        1
70        1
15        1
90        1
110       1
120       1
130       1
150       1
170       1
7         1
0         1
Name: rating_denominator, dtype: int64

#通过观察发现了名字name列出现了诸如a,an,the的单词，这并不是正常的狗名字
dfTwitter_Archive['name'].value_counts().head(20)

None       745
a           55
Charlie     12
Oliver      11
Lucy        11
Cooper      11
Lola        10
Penny       10
Tucker      10
Winston      9
Bo           9
Sadie        8
the          8
an           7
Toby         7
Bailey       7
Daisy        7
Buddy        7
Dave         6
Scout        6
Name: name, dtype: int64

#狗的地位有缺失，有些甚至还有两种地位
(dfTwitter_Archive.iloc[:,-4:]=='None').astype(int).sum(axis=1).value_counts()

4    1976
3     366
2      14
dtype: int64

#狗的名字有大量缺失
(dfTwitter_Archive.loc[:,'name']=='None').astype(int).sum()

745

dfTwitter_Archive['tweet_id'].duplicated().sum()

0

dfImage_Predictions

dfImage_Predictions.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2075 entries, 0 to 2074
Data columns (total 12 columns):
tweet_id    2075 non-null int64
jpg_url     2075 non-null object
img_num     2075 non-null int64
p1          2075 non-null object
p1_conf     2075 non-null float64
p1_dog      2075 non-null bool
p2          2075 non-null object
p2_conf     2075 non-null float64
p2_dog      2075 non-null bool
p3          2075 non-null object
p3_conf     2075 non-null float64
p3_dog      2075 non-null bool
dtypes: bool(3), float64(3), int64(2), object(4)
memory usage: 152.1+ KB

dfImage_Predictions['jpg_url'].duplicated().sum()

66

dfJson_Tweeter

dfJson_Tweeter.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 2352 entries, 0 to 2351
Data columns (total 3 columns):
tweet_id          2352 non-null int64
retweet_count     2352 non-null int64
favorite_count    2352 non-null int64
dtypes: int64(3)
memory usage: 73.5 KB

dfJson_Tweeter['tweet_id'].duplicated().sum()

0

问题记录¶

质量问题¶

dfTwitter_Archive中包含了转发、回复的数据

dfTwitter_Archive的expanded_urls列有缺失

dfTwitter_Archive中，狗的地位有缺失，有些甚至还有两种地位

dfTwitter_Archive的name列含有a，an，the等异常名字

dfTwitter_Archive中，基准分rating_denominator列不全部为10

dfTwitter_Archive的source列包含了HTML格式的代码

dfImage_Predictions中图片链接jpg_url存在重复值

dfImage_Predictions中对于品种的写法不规范，存在大小写混搭，分隔有用空格、减号、下划线

整洁度问题¶

dfTwitter_Archive中，狗的“地位”使用了doggo,floofer,pupper,puppo四列进行统计

三个Dataframe的观察对象相同，应放在同一Dataframe内

清理¶

#准备工作
dfTwitter_Archive_Clean = dfTwitter_Archive.copy()
dfImage_Predictions_Clean = dfImage_Predictions.copy()
dfJson_Tweeter_Clean = dfJson_Tweeter.copy()

dfTwitter_Archive中包含了转发、回复的数据¶

对转发、回复的数据执行删除操作

dfTwitter_Archive_Clean = dfTwitter_Archive_Clean[dfTwitter_Archive_Clean['retweeted_status_id'].isnull()]
dfTwitter_Archive_Clean = dfTwitter_Archive_Clean[dfTwitter_Archive_Clean['in_reply_to_user_id'].isnull()]

#测试
dfTwitter_Archive_Clean.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 2097 entries, 0 to 2355
Data columns (total 17 columns):
tweet_id                      2097 non-null int64
in_reply_to_status_id         0 non-null float64
in_reply_to_user_id           0 non-null float64
timestamp                     2097 non-null object
source                        2097 non-null object
text                          2097 non-null object
retweeted_status_id           0 non-null float64
retweeted_status_user_id      0 non-null float64
retweeted_status_timestamp    0 non-null object
expanded_urls                 2094 non-null object
rating_numerator              2097 non-null int64
rating_denominator            2097 non-null int64
name                          2097 non-null object
doggo                         2097 non-null object
floofer                       2097 non-null object
pupper                        2097 non-null object
puppo                         2097 non-null object
dtypes: float64(4), int64(3), object(10)
memory usage: 294.9+ KB

#将多余列删除
dfTwitter_Archive_Clean.drop(['in_reply_to_status_id','in_reply_to_user_id','retweeted_status_id','retweeted_status_user_id','retweeted_status_timestamp'],axis=1,inplace=True)
dfTwitter_Archive_Clean.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 2097 entries, 0 to 2355
Data columns (total 12 columns):
tweet_id              2097 non-null int64
timestamp             2097 non-null object
source                2097 non-null object
text                  2097 non-null object
expanded_urls         2094 non-null object
rating_numerator      2097 non-null int64
rating_denominator    2097 non-null int64
name                  2097 non-null object
doggo                 2097 non-null object
floofer               2097 non-null object
pupper                2097 non-null object
puppo                 2097 non-null object
dtypes: int64(3), object(9)
memory usage: 213.0+ KB

dfTwitter_Archive的expanded_urls列有缺失¶

将expanded_urls列缺失的数据删除

dfTwitter_Archive_Clean = dfTwitter_Archive_Clean[dfTwitter_Archive_Clean['expanded_urls'].notnull()]

#测试
dfTwitter_Archive_Clean.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 2094 entries, 0 to 2355
Data columns (total 12 columns):
tweet_id              2094 non-null int64
timestamp             2094 non-null object
source                2094 non-null object
text                  2094 non-null object
expanded_urls         2094 non-null object
rating_numerator      2094 non-null int64
rating_denominator    2094 non-null int64
name                  2094 non-null object
doggo                 2094 non-null object
floofer               2094 non-null object
pupper                2094 non-null object
puppo                 2094 non-null object
dtypes: int64(3), object(9)
memory usage: 212.7+ KB

dfTwitter_Archive中，狗的地位有缺失，有些甚至还有两种地位¶

dfTwitter_Archive中，狗的“地位”使用了doggo,floofer,pupper,puppo四列进行统计¶

从原始数据中，发现很多推文并未明确给出狗的地位，因此缺失的数据暂时没有办法填充

将doggo,floofer,pupper,puppo这4列合并，统计在新列stage，删除这4列

#查看原始推文
dfTwitter_Archive[(dfTwitter_Archive.iloc[:,-4:]=='None').astype(int).sum(axis=1)==4].sample(1).text

1649    Meet Sebastian. He's a womanizer. Romantic af....
Name: text, dtype: object

#合并原四列
dfTwitter_Archive_Clean['stage'] = dfTwitter_Archive_Clean['doggo']+dfTwitter_Archive_Clean['floofer']+dfTwitter_Archive_Clean['pupper']+dfTwitter_Archive_Clean['puppo']
dfTwitter_Archive_Clean['stage'] = dfTwitter_Archive_Clean['stage'].str.replace('None','')
dfTwitter_Archive_Clean = dfTwitter_Archive_Clean.replace(({'stage':{'':np.nan}}))

#删除四列
dfTwitter_Archive_Clean.drop(['doggo','floofer','pupper','puppo'],axis=1,inplace=True)
dfTwitter_Archive_Clean[dfTwitter_Archive_Clean['stage'].notnull()]

#测试
dfTwitter_Archive_Clean['stage'].value_counts()

pupper          220
doggo            72
puppo            23
floofer           9
doggopupper       9
doggofloofer      1
doggopuppo        1
Name: stage, dtype: int64

dfTwitter_Archive的name列含有a，an，the等异常名字¶

推测是取名字的时候，直接取了this is后面的第一个单词

通过正则表达式从推文中重新获取

dfTwitter_Archive_Clean['name'] = dfTwitter_Archive_Clean['text'].str.extract(r'\S*[This is|Here is|Here\'s|named|Meet|Say hello to|Here we have]\s([A-Z][a-z]+).+',expand=True)

#测试
dfTwitter_Archive_Clean['name'].value_counts()

Charlie           11
Lucy              11
Oliver            11
Cooper            10
Tucker             9
Penny              9
Lola               8
Winston            8
Christmas          8
Sadie              8
Toby               8
Bo                 7
Daisy              7
Jax                6
Oscar              6
Bailey             6
Koda               6
Bella              6
Stanley            6
Rusty              5
Leo                5
Bentley            5
Louis              5
Chester            5
Dave               5
Zoey               5
Boomer             5
Milo               5
Buddy              5
Scout              5
                  ..
Hercules           1
Piers              1
Emma               1
Pixar              1
Carbon             1
Venezuelan         1
Fabio              1
Striker            1
Skittles           1
Geno               1
Teddy              1
Michelangelope     1
Forrest            1
Harper             1
Gromit             1
Parthenon          1
Am                 1
Cermet             1
Valentine          1
Apollo             1
Skye               1
Margo              1
Tuscaloosa         1
Bowie              1
Simba              1
Ralpher            1
Stark              1
Mitsubishi         1
Karll              1
Bobb               1
Name: name, Length: 1063, dtype: int64

dfTwitter_Archive中，基准分rating_denominator列不全部为10¶

从原始数据中重新提取。对于一条推特中存在两条评分记录的情况，取前一组评分。

dfTwitter_Archive_Clean.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 2094 entries, 0 to 2355
Data columns (total 9 columns):
tweet_id              2094 non-null int64
timestamp             2094 non-null object
source                2094 non-null object
text                  2094 non-null object
expanded_urls         2094 non-null object
rating_numerator      2094 non-null int64
rating_denominator    2094 non-null int64
name                  1566 non-null object
stage                 335 non-null object
dtypes: int64(3), object(6)
memory usage: 163.6+ KB

dfTwitter_Archive_Clean['rating_numerator'],dfTwitter_Archive_Clean['rating_denominator'] = dfTwitter_Archive_Clean['text'].str.extract(r'([0-9]+\.?[0-9]*\/[0-9]+0)',expand=True)[0].str.split("/",1).str

#dfTwitter_Archive_Clean[dfTwitter_Archive_Clean['rating_numerator'].str.find('.')!=-1]

#其中有一条特殊情况，评分为24/7，手工修改数据
modified_index = dfTwitter_Archive_Clean[dfTwitter_Archive_Clean['rating_numerator'].isnull()].index[0]
dfTwitter_Archive_Clean.loc[modified_index,'rating_numerator']=24
dfTwitter_Archive_Clean.loc[modified_index,'rating_denominator']=7

#修改类型为float
dfTwitter_Archive_Clean['rating_numerator'] = dfTwitter_Archive_Clean['rating_numerator'].astype(float)
dfTwitter_Archive_Clean['rating_denominator'] = dfTwitter_Archive_Clean['rating_denominator'].astype(float)

#测试
dfTwitter_Archive_Clean['rating_denominator'].value_counts()

10.0     2080
50.0        3
80.0        2
150.0       1
110.0       1
90.0        1
70.0        1
170.0       1
120.0       1
40.0        1
20.0        1
7.0         1
Name: rating_denominator, dtype: int64

dfTwitter_Archive_Clean['rating_numerator'].value_counts()

12.00      485
10.00      435
11.00      413
13.00      287
9.00       153
8.00        98
7.00        51
14.00       39
5.00        33
6.00        32
3.00        19
4.00        16
2.00         9
1.00         4
13.50        1
0.00         1
24.00        1
84.00        1
420.00       1
1776.00      1
80.00        1
60.00        1
44.00        1
144.00       1
88.00        1
11.26        1
11.27        1
121.00       1
9.75         1
99.00        1
204.00       1
45.00        1
165.00       1
50.00        1
Name: rating_numerator, dtype: int64

dfTwitter_Archive的source列包含了HTML格式的代码¶

使用正则表达式提取URL

dfTwitter_Archive_Clean['source'] = dfTwitter_Archive_Clean['source'].str.extract(r'>(.+)<',expand=True)

#测试
dfTwitter_Archive_Clean.head()

dfImage_Predictions中图片链接jpg_url存在重复值¶

删除这些重复值

dfImage_Predictions_Clean.drop_duplicates(subset='jpg_url',inplace=True)

#测试
dfImage_Predictions_Clean['jpg_url'].duplicated().sum()

0

dfImage_Predictions中对于品种的写法不规范，存在大小写混搭，分隔有用空格、减号、下划线¶

字母全部使用小写。分隔则全部使用下划线。

dfImage_Predictions_Clean[['p1','p2','p3']] = dfImage_Predictions_Clean[['p1','p2','p3']].applymap(str.lower)
dfImage_Predictions_Clean[['p1','p2','p3']] = dfImage_Predictions_Clean[['p1','p2','p3']].replace(' ','_').replace('-','_')

#测试
dfImage_Predictions_Clean.head(5)

三个Dataframe的观察对象相同，应放在同一Dataframe内¶

使用merge合并三个Dataframe

#合并Dataframe
dfClean = pd.merge(dfTwitter_Archive_Clean,dfImage_Predictions_Clean,how='inner',on='tweet_id').merge(dfJson_Tweeter_Clean,how='left',on='tweet_id')

dfClean.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 1971 entries, 0 to 1970
Data columns (total 22 columns):
tweet_id              1971 non-null int64
timestamp             1971 non-null object
source                1971 non-null object
text                  1971 non-null object
expanded_urls         1971 non-null object
rating_numerator      1971 non-null float64
rating_denominator    1971 non-null float64
name                  1520 non-null object
stage                 303 non-null object
jpg_url               1971 non-null object
img_num               1971 non-null int64
p1                    1971 non-null object
p1_conf               1971 non-null float64
p1_dog                1971 non-null bool
p2                    1971 non-null object
p2_conf               1971 non-null float64
p2_dog                1971 non-null bool
p3                    1971 non-null object
p3_conf               1971 non-null float64
p3_dog                1971 non-null bool
retweet_count         1971 non-null int64
favorite_count        1971 non-null int64
dtypes: bool(3), float64(5), int64(4), object(10)
memory usage: 313.7+ KB

dfClean.tail()

#保存文件
dfClean.to_csv('twitter_archive_master.csv', index=False)

数据分析¶

提出问题：¶

1、哪些名字在狗起名之中比较热门？

2、哪些品种的狗能获得较多的转发和点赞？

3、狗的评分是否会影响转发与点赞？

1、哪些名字在狗起名之中比较热门？¶

#取姓名的排名
dfNameRanking = pd.DataFrame(dfClean.groupby('name',as_index=False)['tweet_id'].count().sort_values(by='tweet_id',ascending=False))
dfNameRanking = dfNameRanking.rename(columns={'tweet_id':'counts'})
dfNameRanking.reset_index(drop=True,inplace=True)
dfNameRanking.head(10)

lbName = np.full(len(dfNameRanking.name.value_counts()),"",dtype=object)
lbName[0]='Oliver'
lbName[1]='Charlie'
dfNameRanking.counts.plot(kind='pie',labels=lbName);

结论：狗的名字五花八门，但是像Charlie和Oliver这样的名字是比较热门的。¶

2、哪些品种的狗能获得较多的转发和点赞？¶

#选取所有狗的品种
dfVariety = pd.DataFrame(columns=['tweet_id','variety'])
for i in dfClean.index:
    if dfClean.p1_dog.loc[i] == True:
        dfVariety = dfVariety.append({'tweet_id':str(dfClean.tweet_id.loc[i]),'variety':dfClean.p1.loc[i]},ignore_index=True)
    elif dfClean.p2_dog.loc[i] == True:
        dfVariety = dfVariety.append({'tweet_id':str(dfClean.tweet_id.loc[i]),'variety':dfClean.p2.loc[i]},ignore_index=True)
    elif dfClean.p3_dog.loc[i] == True:
        dfVariety = dfVariety.append({'tweet_id':str(dfClean.tweet_id.loc[i]),'variety':dfClean.p3.loc[i]},ignore_index=True)
    else:
        dfVariety = dfVariety.append({'tweet_id':str(dfClean.tweet_id.loc[i]),'variety':np.nan},ignore_index=True)

dfVariety['tweet_id'] = dfVariety['tweet_id'].astype(int)
#去除空值
dfVariety.dropna(subset=['variety'],inplace=True)
        
#合并获得转发和点赞
dfVariety = pd.merge(dfVariety,dfClean[['tweet_id','retweet_count','favorite_count']],how='left',on='tweet_id')
dfVariety.head()

#计算种类的排名，通过转推和喜欢数相加来计算
dfRanking = dfVariety.groupby('variety').sum().dropna()[['retweet_count','favorite_count']]
dfRanking['total'] = dfRanking['retweet_count']+dfRanking['favorite_count']

dfCountTweet = dfVariety.groupby('variety').size().rename('counts')

dfTempRanking = dfRanking.join(dfCountTweet).sort_values(by='total',ascending=False).head(10)
#dfTempRanking.reset_index(drop=True,inplace=True)
dfTempRanking

dfTempRanking['total'].plot(kind='bar',figsize=(8,5),color='#e597b2');

dfTempRanking['counts'].plot(kind='bar',figsize=(8,5),color='#00a3af');

结论：根据转发和喜欢数，可以看出金毛寻回犬（golden_retriever）、拉布拉多（Labrador_retriever）和彭布罗克威尔士柯基犬（Pembroke）占据了热门中的前三。看起来人们比较喜欢容易养并且外观可爱的狗。¶

3、狗的评分是否会影响转发与点赞？¶

#通过回归来探索相关性
#因为存在不为10的基准评分（如多条狗的情况），通过rating_numerator / rating_denominator来计算
dfTempRate = dfClean[['rating_numerator','rating_denominator','retweet_count','favorite_count']].dropna()
dfTempRate['Rate']=dfTempRate['rating_numerator'] / dfTempRate['rating_denominator']

#去掉异常值
dfTempRate = dfTempRate[dfTempRate['Rate']<2]

sns.lmplot(x='Rate', y='favorite_count',data=dfTempRate,size=7);

sns.lmplot(x='Rate', y='retweet_count',data=dfTempRate,size=7);

收集¶

评估¶