<jupyter_start><jupyter_text>Exploratory Data AnalysisThis notebook performs an initial EDA based on the sample data extracted during the data pull and etl steps.<jupyter_code>import os
from os.path import join
import eland as ed
import pandas as pd
import numpy as np
import seaborn as sns; sns.set()
import matplotlib.pyplot as plt

project_dir = join(os.getcwd(), os.pardir)
raw_dir = join(project_dir, 'data', 'raw')
interim_dir = join(project_dir, 'data', 'interim')
db_name = 'data_pull_sample.db'

%config InlineBackend.figure_format = 'svg'
ed_df = ed.read_es('localhost', 'twitter')
df_twt = ed_df.to_pandas()
df_users = df_twt.loc[
    :,
    list(df_twt.columns[df_twt.columns.str.startswith('user')]) + ['verified', 'protected']
].copy().drop_duplicates('user_id')

df_twt['is_original'] = ~df_twt[['is_retweet', 'is_quote_status', 'is_reply']].max(1)<jupyter_output><empty_output><jupyter_text>EDA Questions:- How many tweets are in the dataset?- How many unique tweets are in the dataset?- How many unique users?- Number of retweets- Locate the top N retweeted and liked tweets and the users that posted them- What are the locations? (Number of tweets by location)- What are the main languages? What are the counts and proportions of tweets by languages?- Location over time- Source analysis- Protected- Verified ___ Tweet Analysis How many tweets are in the dataset? How many unique tweets are in the dataset? Number of retweets?<jupyter_code>print(f"""
Total number of Tweets:\t\t{df_twt.shape[0]}
Number of original Tweets:\t{df_twt.is_original.sum()}
Number of retweets:\t\t{df_twt.is_retweet.sum()}
Number of replies:\t\t{df_twt.is_reply.sum()}
Number of quotes:\t\t{df_twt.is_quote_status.sum()}

Note: Many of the quote statuses/tweets are both marked as quotes and retweets.
""")<jupyter_output>Total number of Tweets:		474419
Number of original Tweets:	94018
Number of retweets:		361077
Number of replies:		14478
Number of quotes:		14190

Note: Many of the quote statuses/tweets are both marked as quotes and retweets.<jupyter_text>Locate the top N retweeted and liked tweets and the users that posted them<jupyter_code>N = 20
df_twt[df_twt.is_original==True]\
    .nlargest(N, 'retweet_count')\
    [['tweet_id', 'full_text', 'retweet_count', 'name']]\
    .reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>What are the locations? (Number of tweets by location)<jupyter_code>df_twt.columns[df_twt.columns.str.contains('loca')]
no_loc_count = df_twt['location'].isnull().sum()
print(f"""Please note that {no_loc_count} tweets (~{int(no_loc_count/df_twt.shape[0]*100)}% \
of the dataset) do not contain location info.""")

top_locations = df_twt.groupby('location')\
    .size()\
    .to_frame()\
    .reset_index()\
    .rename(columns={0:'Count', 'location':'Location'})\
    .set_index('Location')\
    .nlargest(20, 'Count')

top_locations<jupyter_output>Please note that 140614 tweets (~29% of the dataset) do not contain location info.<jupyter_text>What are the main languages? What are the counts and proportions of tweets by languages?<jupyter_code>no_loc_count = df_twt.lang.isnull().sum()
print(f"""Please note that {no_loc_count} tweets (~{int(no_loc_count/df_twt.shape[0]*100)}% \
of the dataset) do not contain tweet language info.""")

df_twt.groupby('lang')\
    .size()\
    .to_frame()\
    .reset_index()\
    .rename(columns={0:'Count', 'lang':'Language'})\
    .set_index('Language')\
    .nlargest(20, 'Count')<jupyter_output>Please note that 0 tweets (~0% of the dataset) do not contain tweet language info.<jupyter_text>Source analysis<jupyter_code>no_loc_count = df_twt.source.isnull().sum()
print(f"""Please note that {no_loc_count} tweets (~{int(no_loc_count/df_twt.shape[0]*100)}% \
of the dataset) do not contain source info.""")

main_sources = {
    '<a href="http://twitter.com/download/android" rel="nofollow">Twitter for Android</a>': 'Android',
    '<a href="https://mobile.twitter.com" rel="nofollow">Twitter Web App</a>': 'Web',
    '<a href="https://mobile.twitter.com" rel="nofollow">Mobile Web (M2)</a>': 'Web',
    '<a href="http://twitter.com/download/iphone" rel="nofollow">Twitter for iPhone</a>': 'iPhone',
    '<a href="http://twitter.com/#!/download/ipad" rel="nofollow">Twitter for iPad</a>': 'iPad',
}

df_twt['main_sources'] = df_twt['source'].apply(
    lambda x: main_sources[x] 
    if x in main_sources.keys() 
    else 'Others/Unknown'
)

df_twt.groupby('main_sources')\
    .size()\
    .to_frame()\
    .reset_index()\
    .rename(columns={0:'Count', 'main_sources':'Source'})\
    .set_index('Source')\
    .nlargest(20, 'Count')<jupyter_output>Please note that 0 tweets (~0% of the dataset) do not contain source info.<jupyter_text>Location over time<jupyter_code>pd.options.mode.chained_assignment = None

top_locations_list = top_locations.iloc[:8]\
    .index.tolist()

df_places = df_twt[['tweet_created_at', 'location']]
df_places.loc[:,'created_at'] = pd.to_datetime(df_places.tweet_created_at).dt.date
df_places.loc[:,'location'] = df_places['location'].apply(
    lambda x: x 
    if x in top_locations_list
    else 'Others/Unknown'
)

df_places = df_places\
    .drop(columns='tweet_created_at')\
    .groupby(['created_at', 'location']).size()\
    .to_frame().reset_index()\
    .rename(columns={0:'Counts', 'location':'Location', 'created_at': 'Date'})\
    .sort_index()

sns.set(style="darkgrid", context="talk")
sns.lineplot(
    data=df_places, hue='Country', x='Date', y='Counts'
).legend(loc='center left', bbox_to_anchor=(1, 0.5))

plt.xticks(
    rotation=45, 
    horizontalalignment='right',
    fontsize='small'  
)


plt.show()
pd.options.mode.chained_assignment = None

top_locations_list = top_locations.iloc[:8]\
    .index.tolist()

df_places = df_twt[['tweet_created_at', 'location']]
df_places.loc[:,'created_at'] = pd.to_datetime(df_places.tweet_created_at).dt.date
df_places.loc[:,'derived.locations.country'] = df_places['location'].apply(
    lambda x: x 
    if x in top_locations_list
    else 'Others/Unknown'
)

df_places = df_places[~df_places['location'].isin(['Others/Unknown', 'India'])]

df_places = df_places\
    .drop(columns='tweet_created_at')\
    .groupby(['created_at', 'location']).size()\
    .to_frame().reset_index()\
    .rename(columns={0:'Counts', 'location':'Location', 'created_at': 'Date'})\
    .sort_index()

sns.set(style="darkgrid", context="talk")
sns.lineplot(
    data=df_places, hue='Country', x='Date', y='Counts'
).legend(loc='center left', bbox_to_anchor=(1, 0.5))

plt.xticks(
    rotation=45, 
    horizontalalignment='right',
    fontsize='small'  
)


plt.show()<jupyter_output><empty_output><jupyter_text>Based on User Location<jupyter_code>pd.options.mode.chained_assignment = None

top_locations_list = top_locations.iloc[:8]\
    .index.tolist()

df_places = df_twt[['tweet_created_at', 'users_derived_country']]
df_places.loc[:,'created_at'] = pd.to_datetime(df_places.tweet_created_at).dt.date
df_places.loc[:,'users_derived_country'] = df_places['users_derived_country'].apply(
    lambda x: x 
    if x in top_locations_list
    else 'Others/Unknown'
)

df_places = df_places[~df_places['users_derived_country'].isin(['Others/Unknown', 'India'])]

df_places = df_places\
    .drop(columns='tweet_created_at')\
    .groupby(['created_at', 'users_derived_country']).size()\
    .to_frame().reset_index()\
    .rename(columns={0:'Counts', 'users_derived_country':'Country', 'created_at': 'Date'})\
    .sort_index()

sns.set(style="darkgrid", context="talk")
sns.lineplot(
    data=df_places, hue='Country', x='Date', y='Counts'
).legend(loc='center left', bbox_to_anchor=(1, 0.5))

plt.xticks(
    rotation=45, 
    horizontalalignment='right',
    fontsize='small'  
)


plt.show()<jupyter_output><empty_output><jupyter_text>___ User Analysis How many unique users? Verified and Protected Users Analysis<jupyter_code>print(f"""
Number of unique users: {df_users.drop_duplicates(subset='user_id').shape[0]}
Number of verified users: {df_users.verified.sum()}
Number of protected users: {df_users.protected.sum()}
""")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15725359911406422, 'fraction_numerical': 0.010705057216685123, 'mean_word_length': 5.435471100554236, 'pattern_counts': {'":': 0, '<': 43, '<?xml version=': 0, '>': 43, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '616637', 'license': 'MIT', 'n_tokens_mistral': 3060, 'n_tokens_neox': 2900, 'n_words': 614, 'path': 'notebooks/2.1-jf-EDA.ipynb', 'repo_name': 'joaopfonseca/solve-iwmi', 'size': '8127'}