<jupyter_start><jupyter_text>Network analysis with NetworkX on Twitter hashtags around Covid19Initial dataframes for network analysis in NetworkX and Holoviews.**Input:*** da_da_mega.ndjson file with dates, id's text, entities etc**Output:*** source-target dataframes per time bins with frequency of source and target<jupyter_code>import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
import networkx as nx
import ndjson
import re
import re
import datetime
import itertools
# Data
# Load data
with open('../../data_processed/nordic_tweets/da_da/da_da_mega.ndjson') as f:
    large_df = pd.read_json(f, lines = True)
large_df.head()
data_sentiment = pd.DataFrame([dat for dat in large_df.sentiment])<jupyter_output><empty_output><jupyter_text>Chinese data<jupyter_code>data = pd.read_csv("english_preprocessed_fixed.csv")
data2 = pd.read_csv("english_preprocessed_08_26.csv")

data = pd.concat([data, data2]).reset_index(drop=True)
data
large_df = data
data_sentiment[data_sentiment["label"] == 'NEGATIVE'].describe()
data_sentiment[data_sentiment["label"] == 'POSITIVE'].describe()<jupyter_output><empty_output><jupyter_text>Check whether there are tweets where a hashtag is used twice.<jupyter_code>i = 0
rownr = 0
for entry in data.tokens:#large_df
    tweet_hashs = []
    for key in entry["ner"]:
        if entry["ner"][key] == "HASHTAG":
            #print(entry["token"][key])
            tweet_hashs.append(entry["token"][key])
    if len(tweet_hashs) > len(set(tweet_hashs)):
        print("Rownumber: ", rownr)
        print("Text: ", large_df.text[rownr])
        print("Hashtags: ", tweet_hashs)
        print("#---------------#")
        i = i + 1
    rownr = rownr+1
import json
type(large_df.iloc[20651]["tokens"])
#json.loads(data.iloc[12]["tokens"])
#json.loads(data["tokens"][0])
data["tokens"][0]<jupyter_output><empty_output><jupyter_text>104 tweets where hashtags are used more than once (jesus). There's also an issue with extracting hashtags from tokens :c I'll try on a subset how I could extract hashtags from text directly.<jupyter_code>d = large_df.iloc[0:10]
import re
list(set(re.findall(r'#\S*\w', large_df["text"][8749])))
large_df["text"][8749]
def extract_hashtags(row):
    # Extracts hashtags and attaches unique ones
    hash_list = list(set(re.findall(r'#\S*\w', row["text"])))
    return hash_list
# Create hashtags column with the actual unique hashtags
large_df["hashtags"] = large_df.apply(lambda row: extract_hashtags(row), axis = 1)
data["hashtags"] = data.apply(lambda row: extract_hashtags(row), axis = 1)
data
# Let's take a subset of necessary columns, add id
df = large_df[["created_at", "hashtags"]].reset_index().rename(columns={"index": "id"})
df = data[["created_at", "hashtags"]].reset_index().rename(columns={"index": "id"})
df
# Select only the ones where we have more than 1 hashtag per tweet
df = df[df["hashtags"].map(len) > 1].reset_index(drop=True)
df
# Hashtag per row
# convert list of pd.Series then stack it
dd = (df
 .set_index(['created_at','id'])['hashtags']
 .apply(pd.Series)
 .stack()
 .reset_index()
 .drop('level_2', axis=1)
 .rename(columns={0:'hashtag'}))
#lowercase!
dd["hashtag"] = dd["hashtag"].str.lower()
# Fix dates
# Reading the data out and in again to fix datetime
#dd.to_csv("co_hashtag_timebins/own_hashtags_large_hashtags_all.csv", index=False)
#dd = pd.read_csv("co_hashtag_timebins/own_hashtags_large_hashtags_all.csv")

dd.to_csv("co_hashtag_timebins/chinese_hashtags.csv", index=False)
dd = pd.read_csv("co_hashtag_timebins/chinese_hashtags.csv")

# Fix datetime - for some reason doesn't give NaT's only when you read the data in from csv
# Normalize the dates
def clean_dates(dd):
    # 0[DAY]  1[MONTH]  2[YEAR]
    datelist = [[],[],[]]
    for line in dd.created_at:
        #If string starts with letters (Mon Mar)
        if re.match(r'[A-Za-z]', line): #Tue Mar 03 20:44:19 +0000 2020
            # Day
            datelist[0].append(re.split(r'(?:[A-Za-z]{3}\s)([A-Za-z]{3})(\s\d{2})(?:.{16})(\d{4})', line)[2])
            # Month - turn month name into month number
            datelist[1].append(datetime.datetime.strptime((re.split(r'(?:[A-Za-z]{3}\s)([A-Za-z]{3})(\s\d{2})(?:.{16})(\d{4})', line)[1]), "%b").month)
            #datelist[1].append("03")
            # Year
            datelist[2].append(re.split(r'(?:[A-Za-z]{3}\s)([A-Za-z]{3})(\s\d{2})(?:.{16})(\d{4})', line)[3])
        else: #2020-02-28
            # Day
            datelist[0].append(re.split(r'(2020)-(\d*)-(\d*)', line)[3])
            # Month
            datelist[1].append(re.split(r'(2020)-(\d*)-(\d*)', line)[2])
            # Year
            datelist[2].append(re.split(r'(2020)-(\d*)-(\d*)', line)[1])

    # Change the list into DF of datetime
    d = pd.to_datetime(pd.DataFrame(datelist).transpose().rename(columns={0:"day", 1:"month", 2:"year"}))
    dd["created_at"] = d
    return(dd)

dd = clean_dates(dd)
# This is the df that is ready for the rest
dd

# Rename some hashtags
#if df["hashtag"] == "#covid19":
#    df["hashtag"]
    
def replace_hashtag(row):
    #print(row["hashtag"])
    if row["hashtag"] == "#covid19":
        new_hash = "#coronavirus"
    elif row["hashtag"] == "#covid_19":
        new_hash = "#coronavirus"
    elif row["hashtag"] == "#twosessions":
        new_hash = "#twosessions2020"
    elif row["hashtag"] == "#hk":
        new_hash = "#hongkong"
    elif row["hashtag"] == "#香港":
        new_hash = "#hongkong"
    else:
        new_hash = row["hashtag"]
    return new_hash

dd["hashtag"] = dd.apply(lambda row: replace_hashtag(row), axis = 1)
dd<jupyter_output><empty_output><jupyter_text>NOTICE: this part has become obsoleteBut I won't delete it yet<jupyter_code># Outputs a df that has id, created_at, hashtag - hashtags are clean, date is normalized to datetime
def hashtag_df_long(large_df, remove_hashtags = False):
    # Get the tokens and dates
    data_tokens = pd.DataFrame([dat for dat in large_df.tokens])
    data_dates = pd.DataFrame([dat for dat in large_df.created_at])
    data_sentiment = pd.DataFrame([dat for dat in large_df.sentiment])

    # Join them to a dataframe
    data_tokens["created_at"] = data_dates
    data_tokens['id'] = range(0, len(data_tokens))
    data_sentiment['id'] = range(0, len(data_sentiment))
    
    import re
    # Some lines have ,#
    exp = re.compile('\,#')
    # Some don't have the comma
    exp2 = re.compile('#')
    d = "#"

    # Extract the hashtags
    # Empty list for [id] [DATE],[HASHTAGS] 
    hash_list3 = [[], [],[]]
    # Loop over each tweet in data
    for tweet in range(0,len(data_tokens["created_at"])):
        for key in data_tokens.ner[tweet]:
            # If the key stands for a hashtag, append the hashtag and date to the list
            if data_tokens.ner[tweet][key] == "HASHTAG": #lst[(np.char.mod('%d', j))]]
                # If the tweet is for some reason something like this: "#PreRolledPapers,#herbStash,#420life,#stoner"
                if re.search(exp, data_tokens.token[tweet][key]):
                    # Break it into pieces and append accordingly
                    tinys = data_tokens.token[tweet][key].split(",")
                    for el in tinys:
                        hash_list3[0].append(data_tokens.id[tweet])
                        hash_list3[2].append(el)
                        hash_list3[1].append(data_tokens.created_at[tweet])
                elif re.search(exp2, data_tokens.token[tweet][key]):
                    # Break it into pieces and append accordingly
                    smalls = [d+e for e in data_tokens.token[tweet][key].split(d) if e]
                    for fl in smalls:
                        hash_list3[0].append(data_tokens.id[tweet])
                        hash_list3[2].append(fl)
                        hash_list3[1].append(data_tokens.created_at[tweet])
                else:
                    hash_list3[0].append(data_tokens.id[tweet])
                    hash_list3[2].append(data_tokens.token[tweet][key])
                    hash_list3[1].append(data_tokens.created_at[tweet])
    
    dd = pd.DataFrame(zip(hash_list3[0], hash_list3[1], hash_list3[2]), columns = ["id", "created_at", "hashtag"])

    # Lowercase the hashtags
    dd['hashtag'] = dd.hashtag.str.lower()

    if remove_hashtags:
        # Remove the meh hashtags
        rmv_hashtags = ["#covid19dk", "#dkpol", "#dkmedier", "#coronadk", "#coronavirus", "#covid19", "#coronavirusdk", 
                            "#covid2019dk", "#covid_19", "#covidー19", "#covid", "#covid2019", "#coronavirusoutbreak", "#corona",
                           "#2019coronavirus", "#2019n_cov", "#2019ncov", "#covid19denmark", "#corona19dk", "#covid-19", "#ncov2019",
                       "#covid_2019", "#ncov", "#virus", "#covid-19dk", "#威神v"]
        dd = dd[~dd['hashtag'].isin(rmv_hashtags)]
    
    # Cleaning hashtags
    # Remove the hashtags with …
    dd = dd[dd['hashtag'].str.contains('…$')==False]
    # Remove hashtags that contain .
    dd = dd[dd['hashtag'].str.contains('\.')==False]
    # Remove hashtags that have https in them:
    dd = dd[dd['hashtag'].str.contains('http')==False]
    
    # Add sentiment score based on id
    dd = pd.merge(dd, data_sentiment, on="id")
    
    return(dd)
id_hashtag = hashtag_df_long(large_df, remove_hashtags = True)
id_hashtag
# Reading the data out and in again to fix datetime
#id_hashtag.to_csv("co_hashtag_timebins/DEBUGGING_large_hashtags_all.csv", index=False)
id_hashtag.to_csv("co_hashtag_timebins/all_sentiment_large_hashtags_all.csv", index=False)
#dd = pd.read_csv("co_hashtag_timebins/DEBUGGING_large_hashtags_all.csv")
dd = pd.read_csv("co_hashtag_timebins/all_sentiment_large_hashtags_all.csv")
# Fix datetime - for some reason doesn't give NaT's only when you read the data in from csv
# Normalize the dates
def clean_dates(dd):
    # 0[DAY]  1[MONTH]  2[YEAR]
    datelist = [[],[],[]]
    for line in dd.created_at:
        #If string starts with letters (Mon Mar)
        if re.match(r'[A-Za-z]', line): #Tue Mar 03 20:44:19 +0000 2020
            # Day
            datelist[0].append(re.split(r'(?:[A-Za-z]{3}\s)([A-Za-z]{3})(\s\d{2})(?:.{16})(\d{4})', line)[2])
            # Month - turn month name into month number
            datelist[1].append(datetime.datetime.strptime((re.split(r'(?:[A-Za-z]{3}\s)([A-Za-z]{3})(\s\d{2})(?:.{16})(\d{4})', line)[1]), "%b").month)
            #datelist[1].append("03")
            # Year
            datelist[2].append(re.split(r'(?:[A-Za-z]{3}\s)([A-Za-z]{3})(\s\d{2})(?:.{16})(\d{4})', line)[3])
        else: #2020-02-28
            # Day
            datelist[0].append(re.split(r'(2020)-(\d*)-(\d*)', line)[3])
            # Month
            datelist[1].append(re.split(r'(2020)-(\d*)-(\d*)', line)[2])
            # Year
            datelist[2].append(re.split(r'(2020)-(\d*)-(\d*)', line)[1])

    # Change the list into DF of datetime
    d = pd.to_datetime(pd.DataFrame(datelist).transpose().rename(columns={0:"day", 1:"month", 2:"year"}))
    dd["created_at"] = d
    return(dd)
dd = clean_dates(dd)
bin0['points'] = bin0.apply(f, axis=1)
#pd.DataFrame({'score' : bin0.groupby(['id']).mean()}).reset_index()

pd.DataFrame(bin0.groupby('hashtag')['points'].mean()).reset_index()<jupyter_output><empty_output><jupyter_text>---------------------------------------------------------------------------------- OBSOLITE PART OVER<jupyter_code>def df_to_timebins(id_hashtag):
    
    # Add freq of hashtags by themselves in the dataset
    hashtag_freq = pd.DataFrame({'whole_freq' : id_hashtag.groupby(['hashtag']).size()}).reset_index()

    # Add the whole_frew to id_hashtag - THIS SEEMS BETTER THAN mm
    freq_hashtags = pd.merge(id_hashtag, hashtag_freq, how='left', on=['hashtag'])#, 'id', 'created_at'])
    
    df0 = freq_hashtags
    # Extract initial timebin 0
    #bin0 = df0[df0['created_at'] <= #"2020-03-10"]
    # Add hashtag freq in this timebin

    #bin01 = pd.merge(bin0, pd.DataFrame({'bin_freq' : bin0.groupby(['hashtag']).size()}).reset_index(), on=['hashtag'])

    # Extract initial timebin X and remove values higher than last bin
    #bin1a = df0[df0['created_at'] <= "2020-03-17"]# - bin0
    #bin1 = bin1a[bin1a.created_at > "2020-03-10"]
    #bin11 = pd.merge(bin1, pd.DataFrame({'bin_freq' : bin1.groupby(['id', 'created_at', 'hashtag']).size()}).reset_index(), on=['id', 'created_at', 'hashtag'])

    #bin2a = df0[df0['created_at'] <= "2020-03-22"]# - bin0
    #bin2 = bin2a[bin2a.created_at > "2020-03-17"]
    #bin21 = pd.merge(bin2, pd.DataFrame({'bin_freq' : bin2.groupby(['id', 'created_at', 'hashtag']).size()}).reset_index(), on=['id', 'created_at', 'hashtag'])

    #bin3a = df0[df0['created_at'] <= "2020-03-31"]# - bin0
    #bin3 = bin3a[bin3a.created_at > "2020-03-22"]
    #bin31 = pd.merge(bin3, pd.DataFrame({'bin_freq' : bin3.groupby(['id', 'created_at', 'hashtag']).size()}).reset_index(), on=['id', 'created_at', 'hashtag'])

    #bin4a = df0[df0['created_at'] <= "2020-05-31"]# - bin0
    #bin4 = bin4a[bin4a.created_at > "2020-03-31"]
    #bin41 = pd.merge(bin4, pd.DataFrame({'bin_freq' : bin4.groupby(['id', 'created_at', 'hashtag']).size()}).reset_index(), on=['id', 'created_at', 'hashtag'])
    
    return(freq_hashtags)
    #return(df0, bin0, bin1, bin2, bin3, bin4)
f = df_to_timebins(dd)
f
#f.to_csv('co_hashtag_timebins/networkx-to-d3-input/prebin_4_chinese.csv', index=False)
start_date = "2020-05-20"
end_date = "2020-05-31"
mask = (f['created_at'] >= start_date) & (f['created_at'] <= end_date)
bin0 = f.loc[mask].reset_index(drop=True)

start_date = "2020-06-01"
end_date = "2020-06-30"
mask = (f['created_at'] >= start_date) & (f['created_at'] <= end_date)
bin1 = f.loc[mask].reset_index(drop=True)

start_date = "2020-07-01"
end_date = "2020-07-31"
mask = (f['created_at'] >= start_date) & (f['created_at'] <= end_date)
bin2 = f.loc[mask].reset_index(drop=True)

start_date = "2020-08-01"
end_date = "2020-08-05"
mask = (f['created_at'] >= start_date) & (f['created_at'] <= end_date)
bin3 = f.loc[mask].reset_index(drop=True)
bin1
bin0.to_csv('co_hashtag_timebins/networkx-to-d3-input/prebin_0_chinese.csv', index=False)
bin1.to_csv('co_hashtag_timebins/networkx-to-d3-input/prebin_1_chinese.csv', index=False)
bin2.to_csv('co_hashtag_timebins/networkx-to-d3-input/prebin_2_chinese.csv', index=False)
bin3.to_csv('co_hashtag_timebins/networkx-to-d3-input/prebin_3_chinese.csv', index=False)
# Chinese:
#f = df_to_timebins(dd)
f["created_at"].describe()

# split data to month bins
#months = [g for n, g in f.set_index('created_at')

# Actual time bins for CHCAA data:
all_bin, bin0, bin1, bin2, bin3, bin4 = df_to_timebins(dd)

# Individual lengths of bins
print(len(bin0))
print(len(bin1))
print(len(bin2))
print(len(bin3))
print(len(bin4))

print("These two should be the same: ")
print("Bins added together:         ", len(bin0) + len(bin1) + len(bin2) + len(bin3) + len(bin4))
print("Length before making bins:   ", len(all_bin))
bin1<jupyter_output><empty_output><jupyter_text>Outputting input Files for NetworkX-to-d3<jupyter_code>bin0.to_csv('co_hashtag_timebins/networkx-to-d3-input/prebin_0_co_hashtags.csv', index=False)
bin1.to_csv('co_hashtag_timebins/networkx-to-d3-input/prebin_1_co_hashtags.csv', index=False)
bin2.to_csv('co_hashtag_timebins/networkx-to-d3-input/prebin_2_co_hashtags.csv', index=False)
bin3.to_csv('co_hashtag_timebins/networkx-to-d3-input/prebin_3_co_hashtags.csv', index=False)
bin4.to_csv('co_hashtag_timebins/networkx-to-d3-input/prebin_4_co_hashtags.csv', index=False)<jupyter_output><empty_output><jupyter_text>Converting time bins to source-target (for HoloViews visuals)<jupyter_code>def f(x):
  if x['label'] == 'NEGATIVE': return -x['score']
  elif x['label'] == 'POSITIVE': return x['score']
  else: return 0
# Convert timebins to source-target
def timebin(timebin_dataframe, with_count_1 = True):
    # Aggregate the dataframe based on ID
    hashtags_per_tweet = timebin_dataframe.groupby(['id'])["hashtag"].apply(lambda x: ', '.join(x)).reset_index()

    cols = [[],[]]
    for index, row in hashtags_per_tweet.iterrows():
        hashtags=row['hashtag'].split()
        hashtags_len = len(hashtags)
        for n in list(itertools.combinations(hashtags, 2)):
            cols[0].append(n[0])
            cols[1].append(n[1])

    # Make the data frame from the source and target columns, remove commas
    df3 = pd.DataFrame(list(zip(cols[0], cols[1]))).rename(columns={0:"source",1:"target"}).replace(',','', regex=True)
    
    # Aggregate repeated edges
    df10 = pd.DataFrame({'count' : df3.groupby(['source', 'target']).size()}).reset_index()
    
    if with_count_1 == False:
        df10 = df10[df10["count"] != 1]
    
    # Add the frequencies of hashtags per whole data + per timebin
    time1 = timebin_dataframe.rename(columns={'hashtag':'source', 'whole_freq': 'source_whole_freq', 'bin_freq':'source_bin_freq'})
    time1 = time1.drop(columns=['id', 'created_at'])

    time2 = timebin_dataframe.rename(columns={'hashtag':'target', 'whole_freq': 'target_whole_freq', 'bin_freq':'target_bin_freq'})
    time2 = time2.drop(columns=['id', 'created_at'])#, 'label', 'score'])

    df11 = pd.merge(df10, time1, how = 'left', on='source')
    df11 = pd.merge(df11, time2, how = 'left', on='target')
    
    # Drop duplicates if any!
    df11 = df11.drop_duplicates().reset_index(drop=True)
    
    return(df11)
# Make the timebins
#time0 = timebin(bin0)
#time1 = timebin(bin1)
#time2 = timebin(bin2)
#time3 = timebin(bin3)
time4 = timebin(f)
time4
bin0
bin0

def f(x):
  if x['label'] == 'NEGATIVE': return -x['score']
  elif x['label'] == 'POSITIVE': return x['score']
  else: return 0

bin0['points'] = bin0.apply(f, axis=1)
#pd.DataFrame({'score' : bin0.groupby(['id']).mean()}).reset_index()

pd.DataFrame(bin0.groupby('hashtag')['points'].mean()).reset_index()
print(len(time0) + len(time1) + len(time2) + len(time3) + len(time4))
# Chinese
#time0.to_csv('co_hashtag_timebins/networkx-to-hv-input/chinese/timebin_0_co_hashtags_rmv.csv', index=False)
#time1.to_csv('co_hashtag_timebins/networkx-to-hv-input/chinese/timebin_1_co_hashtags_rmv.csv', index=False)
#time2.to_csv('co_hashtag_timebins/networkx-to-hv-input/chinese/timebin_2_co_hashtags_rmv.csv', index=False)
#time3.to_csv('co_hashtag_timebins/networkx-to-hv-input/chinese/timebin_3_co_hashtags_rmv.csv', index=False)
time4.to_csv('co_hashtag_timebins/networkx-to-hv-input/chinese/timebin_4_co_hashtags_rmv.csv', index=False)
time0.to_csv('co_hashtag_timebins/networkx-to-hv-input/timebin_0_co_hashtags_sentiment_rmv.csv', index=False)
time1.to_csv('co_hashtag_timebins/networkx-to-hv-input/timebin_1_co_hashtags_sentiment_rmv.csv', index=False)
time2.to_csv('co_hashtag_timebins/networkx-to-hv-input/timebin_2_co_hashtags_sentiment_rmv.csv', index=False)
time3.to_csv('co_hashtag_timebins/networkx-to-hv-input/timebin_3_co_hashtags_sentiment_rmv.csv', index=False)
time4.to_csv('co_hashtag_timebins/networkx-to-hv-input/timebin_4_co_hashtags_sentiment_rmv.csv', index=False)<jupyter_output><empty_output><jupyter_text>Chinese hashtags - users to network analysis<jupyter_code>data = pd.read_csv("english_preprocessed_fixed.csv")
data2 = pd.read_csv("english_preprocessed_08_26.csv")

data = pd.concat([data, data2]).reset_index(drop=True)
data
def extract_users(row):
    # Extracts users and attaches unique ones
    ori = [row["user"]]
    user_list = ori + list(set(re.findall(r'@(\S*\w)', row["text"])))
    return user_list
# Create hashtags column with the actual unique hashtags
large_df["users"] = large_df.apply(lambda row: extract_users(row), axis = 1)
data["users"] = data.apply(lambda row: extract_users(row), axis = 1)
data
# Let's take a subset of necessary columns, add id
df = large_df[["created_at", "users"]].reset_index().rename(columns={"index": "id"})
df = data[["created_at", "users"]].reset_index().rename(columns={"index": "id"})
df

# Select only the ones where we have more than 1 hashtag per tweet
df = df[df["users"].map(len) > 1].reset_index(drop=True)

# Hashtag per row
# convert list of pd.Series then stack it
dd = (df
 .set_index(['created_at','id'])['users']
 .apply(pd.Series)
 .stack()
 .reset_index()
 .drop('level_2', axis=1)
 .rename(columns={0:'user_solo'}))
#lowercase!
dd["user_solo"] = dd["user_solo"].str.lower()

# Fix dates
# Reading the data out and in again to fix datetime
#dd.to_csv("co_hashtag_timebins/own_hashtags_large_hashtags_all.csv", index=False)
#dd = pd.read_csv("co_hashtag_timebins/own_hashtags_large_hashtags_all.csv")

dd.to_csv("co_hashtag_timebins/chinese_users.csv", index=False)
dd = pd.read_csv("co_hashtag_timebins/chinese_users.csv")

# Fix datetime - for some reason doesn't give NaT's only when you read the data in from csv
# Normalize the dates
def clean_dates(dd):
    # 0[DAY]  1[MONTH]  2[YEAR]
    datelist = [[],[],[]]
    for line in dd.created_at:
        #If string starts with letters (Mon Mar)
        if re.match(r'[A-Za-z]', line): #Tue Mar 03 20:44:19 +0000 2020
            # Day
            datelist[0].append(re.split(r'(?:[A-Za-z]{3}\s)([A-Za-z]{3})(\s\d{2})(?:.{16})(\d{4})', line)[2])
            # Month - turn month name into month number
            datelist[1].append(datetime.datetime.strptime((re.split(r'(?:[A-Za-z]{3}\s)([A-Za-z]{3})(\s\d{2})(?:.{16})(\d{4})', line)[1]), "%b").month)
            #datelist[1].append("03")
            # Year
            datelist[2].append(re.split(r'(?:[A-Za-z]{3}\s)([A-Za-z]{3})(\s\d{2})(?:.{16})(\d{4})', line)[3])
        else: #2020-02-28
            # Day
            datelist[0].append(re.split(r'(2020)-(\d*)-(\d*)', line)[3])
            # Month
            datelist[1].append(re.split(r'(2020)-(\d*)-(\d*)', line)[2])
            # Year
            datelist[2].append(re.split(r'(2020)-(\d*)-(\d*)', line)[1])

    # Change the list into DF of datetime
    d = pd.to_datetime(pd.DataFrame(datelist).transpose().rename(columns={0:"day", 1:"month", 2:"year"}))
    dd["created_at"] = d
    return(dd)

dd = clean_dates(dd)
dd
def df_to_timebins(id_hashtag):
    
    # Add freq of hashtags by themselves in the dataset
    hashtag_freq = pd.DataFrame({'whole_freq' : id_hashtag.groupby(['user_solo']).size()}).reset_index()

    # Add the whole_frew to id_hashtag - THIS SEEMS BETTER THAN mm
    freq_hashtags = pd.merge(id_hashtag, hashtag_freq, how='left', on=['user_solo'])#, 'id', 'created_at'])
    
    df0 = freq_hashtags
    
    return(freq_hashtags)

f = df_to_timebins(dd)

start_date = "2020-05-20"
end_date = "2020-05-31"
mask = (f['created_at'] >= start_date) & (f['created_at'] <= end_date)
bin0 = f.loc[mask].reset_index(drop=True)

start_date = "2020-06-01"
end_date = "2020-06-30"
mask = (f['created_at'] >= start_date) & (f['created_at'] <= end_date)
bin1 = f.loc[mask].reset_index(drop=True)

start_date = "2020-07-01"
end_date = "2020-07-31"
mask = (f['created_at'] >= start_date) & (f['created_at'] <= end_date)
bin2 = f.loc[mask].reset_index(drop=True)

start_date = "2020-08-01"
end_date = "2020-08-05"
mask = (f['created_at'] >= start_date) & (f['created_at'] <= end_date)
bin3 = f.loc[mask].reset_index(drop=True)

# Convert timebins to source-target
def timebin(timebin_dataframe, with_count_1 = True):
    # Aggregate the dataframe based on ID
    hashtags_per_tweet = timebin_dataframe.groupby(['id'])["user_solo"].apply(lambda x: ', '.join(x)).reset_index()

    cols = [[],[]]
    for index, row in hashtags_per_tweet.iterrows():
        hashtags=row['user_solo'].split()
        hashtags_len = len(hashtags)
        for n in list(itertools.combinations(hashtags, 2)):
            cols[0].append(n[0])
            cols[1].append(n[1])

    # Make the data frame from the source and target columns, remove commas
    df3 = pd.DataFrame(list(zip(cols[0], cols[1]))).rename(columns={0:"source",1:"target"}).replace(',','', regex=True)
    
    # Aggregate repeated edges
    df10 = pd.DataFrame({'count' : df3.groupby(['source', 'target']).size()}).reset_index()
    
    if with_count_1 == False:
        df10 = df10[df10["count"] != 1]
    
    # Add the frequencies of hashtags per whole data + per timebin
    time1 = timebin_dataframe.rename(columns={'user_solo':'source', 'whole_freq': 'source_whole_freq', 'bin_freq':'source_bin_freq'})
    time1 = time1.drop(columns=['id', 'created_at'])

    time2 = timebin_dataframe.rename(columns={'user_solo':'target', 'whole_freq': 'target_whole_freq', 'bin_freq':'target_bin_freq'})
    time2 = time2.drop(columns=['id', 'created_at'])#, 'label', 'score'])

    df11 = pd.merge(df10, time1, how = 'left', on='source')
    df11 = pd.merge(df11, time2, how = 'left', on='target')
    
    # Drop duplicates if any!
    df11 = df11.drop_duplicates().reset_index(drop=True)
    
    return(df11)

# Make the timebins
time0 = timebin(bin0)
time1 = timebin(bin1)
time2 = timebin(bin2)
time3 = timebin(bin3)
#time4 = timebin(f)
time3

# Chinese
time0.to_csv('co_hashtag_timebins/networkx-to-hv-input/chinese/timebin_0_users_rmv.csv', index=False)
time1.to_csv('co_hashtag_timebins/networkx-to-hv-input/chinese/timebin_1_users_rmv.csv', index=False)
time2.to_csv('co_hashtag_timebins/networkx-to-hv-input/chinese/timebin_2_users_rmv.csv', index=False)
time3.to_csv('co_hashtag_timebins/networkx-to-hv-input/chinese/timebin_3_users_rmv.csv', index=False)
#time4.to_csv('co_hashtag_timebins/networkx-to-hv-input/chinese/timebin_4_co_hashtags_rmv.csv', index=False)<jupyter_output><empty_output><jupyter_text>April-May data analysis<jupyter_code>with open('../../data_processed/megafiles/da_da_mega1.ndjson') as f:
    da_da_mega1 = pd.read_json(f, lines = True)
da_da_mega1
with open('../../data_processed/megafiles/da_da_mega2.ndjson') as f:
    da_da_mega2 = pd.read_json(f, lines = True)
da_da_mega2
id_hashtag = hashtag_df_long(da_da_mega2, remove_hashtags = True)
id_hashtag
# Reading the data out and in again to fix datetime
id_hashtag.to_csv("co_hashtag_timebins/da_da_mega2_large_hashtags_all.csv", index=False)
dd = pd.read_csv("co_hashtag_timebins/da_da_mega2_large_hashtags_all.csv")
dd = clean_dates(dd)
df = dd.copy()
df[df["created_at"] == "2020-06-01"]
df['month'] = pd.DatetimeIndex(df['created_at']).month
df['day'] = pd.DatetimeIndex(df['created_at']).day
dd
dd.to_csv("co_hashtag_timebins/da_da_mega2_cleaned_date.csv", index=False)
# WORK WITH THIS HERE
dd = pd.read_csv("co_hashtag_timebins/da_da_mega2_cleaned_date.csv")
dd[dd["month"] == 6]

dd["month"] = dd["month"].replace(6, 0)
dd["month"] = dd["month"].replace(5, 1)

dd_june = dd[dd["month"] == 0]
dd_may = dd[dd["month"] == 1]
dd_june = dd_june.reset_index(drop=True)
dd_june
d1 = pd.DataFrame()
for i in range(0,len(dd_june)):
    for row in dd_june.day:
        is_between = 1 <= row <= 7
        if is_between:
            d1.append(dd_june.loc[i])
            
        #dd_june[[dd_june["day"] == row]]

#dd_june["chunk"] = 
grouped = dd_june.groupby(dd_june.created_at)

grouped.get_group("2020-06-01")

for name, group in grouped:
    path = "co_hashtag_timebins/may_june/" + str(name) + ".csv"
    group.to_csv(path, index=False)
print(concat("hello", 1))
df0
import pandas as pd

df1 = pd.DataFrame({
    'atable':     ['Users', 'Users', 'Domains', 'Domains', 'Locks'],
    'column':     ['col_1', 'col_2', 'col_a', 'col_b', 'col'],
    'column_type':['varchar', 'varchar', 'int', 'varchar', 'varchar'],
    'is_null':    ['No', 'No', 'Yes', 'No', 'Yes'],
})

df1_grouped = df1.groupby('atable')

# iterate over each group
for group_name, df_group in df1_grouped:
    print('\nCREATE TABLE {}('.format(group_name))

    for row_index, row in df_group.iterrows():
        col = row['column']
        column_type = row['column_type']
        is_null = 'NOT NULL' if row['is_null'] == 'NO' else ''
        print('\t{} {} {},'.format(col, column_type, is_null))

    print(");")
# sort the dataframe
df.sort_values(by='name', axis=1, inplace=True)
# set the index to be this and don't drop
df.set_index(keys=['name'], drop=False,inplace=True)
# get a list of names
names=df['name'].unique().tolist()
# now we can perform a lookup on a 'view' of the dataframe
joe = df.loc[df.name=='joe']
# now you can query all 'joes'
def df_to_timebins(id_hashtag):
    
    # Add freq of hashtags by themselves in the dataset
    hashtag_freq = pd.DataFrame({'whole_freq' : id_hashtag.groupby(['hashtag']).size()}).reset_index()

    # Add the whole_frew to id_hashtag - THIS SEEMS BETTER THAN mm
    freq_hashtags = pd.merge(id_hashtag, hashtag_freq, how='left', on=['hashtag'])#, 'id', 'created_at'])
    
    df0 = freq_hashtags
    # Extract initial timebin 0
    bin0 = df0[df0['created_at'] <= "2020-03-10"]
    # Add hashtag freq in this timebin

    bin01 = pd.merge(bin0, pd.DataFrame({'bin_freq' : bin0.groupby(['hashtag']).size()}).reset_index(), on=['hashtag'])

    # Extract initial timebin X and remove values higher than last bin
    bin1a = df0[df0['created_at'] <= "2020-03-17"]# - bin0
    bin1 = bin1a[bin1a.created_at > "2020-03-10"]
    bin11 = pd.merge(bin1, pd.DataFrame({'bin_freq' : bin1.groupby(['id', 'created_at', 'hashtag']).size()}).reset_index(), on=['id', 'created_at', 'hashtag'])

    bin2a = df0[df0['created_at'] <= "2020-03-22"]# - bin0
    bin2 = bin2a[bin2a.created_at > "2020-03-17"]
    bin21 = pd.merge(bin2, pd.DataFrame({'bin_freq' : bin2.groupby(['id', 'created_at', 'hashtag']).size()}).reset_index(), on=['id', 'created_at', 'hashtag'])

    bin3a = df0[df0['created_at'] <= "2020-03-31"]# - bin0
    bin3 = bin3a[bin3a.created_at > "2020-03-22"]
    bin31 = pd.merge(bin3, pd.DataFrame({'bin_freq' : bin3.groupby(['id', 'created_at', 'hashtag']).size()}).reset_index(), on=['id', 'created_at', 'hashtag'])

    bin4a = df0[df0['created_at'] <= "2020-05-31"]# - bin0
    bin4 = bin4a[bin4a.created_at > "2020-03-31"]
    bin41 = pd.merge(bin4, pd.DataFrame({'bin_freq' : bin4.groupby(['id', 'created_at', 'hashtag']).size()}).reset_index(), on=['id', 'created_at', 'hashtag'])
    
    return(df0, bin0, bin1, bin2, bin3, bin4)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17916805324459234, 'fraction_numerical': 0.03391014975041597, 'mean_word_length': 4.129032258064516, 'pattern_counts': {'":': 12, '<': 61, '<?xml version=': 0, '>': 60, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '279464', 'license': 'MIT', 'n_tokens_mistral': 12178, 'n_tokens_neox': 11448, 'n_words': 2062, 'path': '.ipynb_checkpoints/Ori_df-to-NetworkX-checkpoint.ipynb', 'repo_name': 'centre-for-humanities-computing/chinese-twitter', 'size': '30050'}