<jupyter_start><jupyter_text>EURO2020Sentiment analysis dei giocatori delle partite dell'Italia nella fase finale ad eliminazione dai quarti alla finale* Scraping: streaming durante la partita BELITA ITAESP ITAEN* Preprocessing* ETL* Analysis Import and path<jupyter_code>from google.colab import drive
drive.mount('/content/drive')
import tweepy
import pandas as pd
pd.set_option('display.max_colwidth', None) # setta il num. di caratteri visibili per ogni cella della colonna
# es. 50 = primi 50 caratteri
# None = testo completo
import io
import os
import glob
import numpy as np
import re

import warnings
warnings.filterwarnings('ignore')
from tqdm.notebook import tqdm
drive_dir = 'drive'
root = f'/content/{drive_dir}/MyDrive/'

root_main = f'{root}challenge_master_2021/'
os.makedirs(root_main, exist_ok=True)

ingest_euro2020 = f'{root_main}ingest/'
os.makedirs(ingest_euro2020, exist_ok=True)

scrap_belita = f'{ingest_euro2020}scrap_belita/'
os.makedirs(scrap_belita, exist_ok=True)

scrap_itaesp = f'{ingest_euro2020}scrap_itaesp/'
os.makedirs(scrap_itaesp, exist_ok=True)

scrap_itaen = f'{ingest_euro2020}scrap_itaen/'
os.makedirs(scrap_itaen, exist_ok=True)

dwh_euro2020 = f'{root_main}dwh/'
os.makedirs(dwh_euro2020, exist_ok=True)

staging_area = f'{root_main}staging_area/'
os.makedirs(staging_area, exist_ok=True)<jupyter_output><empty_output><jupyter_text>Ingest (scraping)<jupyter_code>#override tweepy.StreamListener to add logic to on_status
class MyStreamListener(tweepy.StreamListener):
    data = [] #qui ci metto i tweet che restano quando interrompo lo streaming
    num = 0 #questo è un contatore settato a 0

    def store(self):
      dict_ = {'id': [], 'user': [], 'date': [], 'text': [], 
         'favorite_count': [], 'hashtags': [], 
         'location': [], 'retweet': [], 'retweet_count': [], 'followers_count': [], 'in_reply_to_status_id':[], 'user_mentions':[]}
      
      for status_j in self.data:
          status = status_j._json
          dict_['id'].append(status['id'])
          dict_['user'].append(status['user']['screen_name'])
          dict_['hashtags'].append([hashtag['text'] for hashtag in status['entities']['hashtags']])
          dict_['user_mentions'].append([user_mention['screen_name'] for user_mention in status['entities']['user_mentions']])
          dict_['location'].append(status['user']['location'])
          dict_['followers_count'].append(status['user']['followers_count'])
          dict_['in_reply_to_status_id'].append(status['in_reply_to_status_id'])
          dict_['date'].append(status['created_at'])
          dict_['text'].append(status['text'])
          dict_['favorite_count'].append(status['favorite_count'])
          dict_['retweet_count'].append(status['retweet_count'])
          dict_['retweet'].append(status['retweeted'])

      df = pd.DataFrame.from_dict(dict_, orient="columns")
      df = df.set_index("id")
      df.sort_values(by='favorite_count', inplace=True, ascending=False)
      # df.to_csv(f'{scrap_belita}sc_twitter_' + str(self.num) + '.csv') #salvo in csv BELITA
      # df.to_csv(f'{scrap_itaesp}sc_twitter_' + str(self.num) + '.csv') #salvo in csv ITAESP
      df.to_csv(f'{scrap_itaen}sc_twitter_' + str(self.num) + '.csv') #salvo in csv ITAEN
      self.num = self.num + 1
      self.data = []


    def on_status(self, status):
        self.data.append(status)
        # to pandas
        # store su google drive
        if len(self.data) > 10000:
          self.store()
#api per tweepy
from tweepy import OAuthHandler
consumer_key = 'MQXChIn29dCkxxxxxxxxxxxxxxx'
consumer_secret = 'u4n3UeFz58b4H6InWFhB<KEY>'
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)

''' Altre chiavi
#api per tweepy
from tweepy import OAuthHandler
consumer_key = 'A4GRiWdUDxxxxxxxxxxxxxxx'
consumer_secret = 'nSyjpFDTr3eEmd8txxxxxxxxxxxxxxx'
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)
'''

''' Altre chiavi
#api per tweepy
from tweepy import OAuthHandler
consumer_key = 'Vzk6O5t967f1n4iz9iQcsQwhf'
consumer_secret = 'Q8sncdY1j85mwNitOqb5Spcgxxxxxxxxxxxxxxx'
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)
'''
myStreamListener = MyStreamListener()
myStream = tweepy.Stream(auth = api.auth, listener=myStreamListener)
#chiamata allo streaming
# myStream.filter(track=['belita' or 'euro2020' or 'belgioitalia' or 'italiabelgio']) #per BELITA
# myStream.filter(track=['ItaliaSpagna' or 'euro2020' or 'itaspa' or 'eurocopa2021' or 'EspanaItalia' or 'UefaEuro2020' or 'italiaespana' or 'ItalySpain']) #per ITAESP
myStream.filter(track=['Euro2020Final' or 'ITAENG' or 'EURO2020' or 'Wembley' or 'ENGITA' or 'ItalyEngland' or 'EnglandItaly' or 'InghilterraItalia' or 'ItaliaInghilterra']) #per ITAEN
#conto i tweet scaricati quando interrompo o si rompe
len(myStreamListener.data)
# Create our dict with our data, come sopra per quando stoppo manualmente lo streaming
dict_ = {'id': [], 'user': [], 'date': [], 'text': [], 
         'favorite_count': [], 'hashtags': [], 
         'location': [], 'retweet': [], 'retweet_count': [], 'followers_count': [], 'in_reply_to_status_id':[], 'user_mentions':[]}
#
for status_j in myStreamListener.data:
    status = status_j._json
    dict_['id'].append(status['id'])
    dict_['user'].append(status['user']['screen_name'])
    dict_['hashtags'].append([hashtag['text'] for hashtag in status['entities']['hashtags']])
    dict_['user_mentions'].append([user_mention['screen_name'] for user_mention in status['entities']['user_mentions']])
    dict_['location'].append(status['user']['location'])
    dict_['followers_count'].append(status['user']['followers_count'])
    dict_['in_reply_to_status_id'].append(status['in_reply_to_status_id'])
    dict_['date'].append(status['created_at'])
    dict_['text'].append(status['text'])
    dict_['favorite_count'].append(status['favorite_count'])
    dict_['retweet_count'].append(status['retweet_count'])
    dict_['retweet'].append(status['retweeted'])
#creo il dataframe dell'ultimo stream stoppato manualmente
df = pd.DataFrame.from_dict(dict_, orient="columns")
df = df.set_index("id")
df.sort_values(by='favorite_count', inplace=True, ascending=False)
df.head(5)
#scarico l'ultimo stream
# df.to_csv(f'{scrap_belita}sc_twitter_end.csv') #salvo in csv BELITA
# df.to_csv(f'{scrap_itaesp}sc_twitter_end.csv') #salvo in csv ITAESP
df.to_csv(f'{scrap_itaen}sc_twitter_end.csv') #salvo in csv ITAEN
#unisco tutti i csv

path = f'{scrap_belita}'
all_files = glob.glob(os.path.join(path, "sc_*.csv"))
df_from_each_file = (pd.read_csv(f, sep=',') for f in all_files)
df_merged = pd.concat(df_from_each_file, ignore_index=True)
df_merged.drop_duplicates(subset=['id'], inplace=True, ignore_index=False)
df_merged.insert(len(df_merged.columns), 'match', 'belita')
df_merged.to_csv(f'{staging_area}merged_belita.csv', index=False)
print(f'BELITA: {df_merged.shape[0]} records | {df_merged.shape[1]} columns')

path = f'{scrap_itaesp}'
all_files = glob.glob(os.path.join(path, "sc_*.csv"))
df_from_each_file = (pd.read_csv(f, sep=',') for f in all_files)
df_merged = pd.concat(df_from_each_file, ignore_index=True)
df_merged.drop_duplicates(subset=['id'], inplace=True, ignore_index=False)
df_merged.insert(len(df_merged.columns), 'match', 'itaesp')
df_merged.to_csv(f'{staging_area}merged_itaesp.csv', index=False)
print(f'ITAESP: {df_merged.shape[0]} records | {df_merged.shape[1]} columns')


path = f'{scrap_itaen}'
all_files = glob.glob(os.path.join(path, "sc_*.csv"))
df_from_each_file = (pd.read_csv(f, sep=',') for f in all_files)
df_merged = pd.concat(df_from_each_file, ignore_index=True)
df_merged.drop_duplicates(subset=['id'], inplace=True, ignore_index=False)
df_merged.insert(len(df_merged.columns), 'match', 'itaen')
print(f'ITAEN: {df_merged.shape[0]} records | {df_merged.shape[1]} columns')

path = f'{staging_area}'
all_files = glob.glob(os.path.join(path, "merged_*.csv"))
df_from_each_file = (pd.read_csv(f, sep=',') for f in all_files)
df_merged = pd.concat(df_from_each_file, ignore_index=True)
df_merged.to_csv(f'{staging_area}merge.csv', index=False)
print(f'TOT: {df_merged.shape[0]} records | {df_merged.shape[1]} columns')
print()
print(f'{df_merged.columns}')<jupyter_output>BELITA: 220647 records | 13 columns
ITAESP: 119762 records | 13 columns
ITAEN: 580111 records | 13 columns
TOT: 920528 records | 13 columns

Index(['id', 'user', 'date', 'text', 'favorite_count', 'hashtags', 'location',
       'retweet', 'retweet_count', 'followers_count', 'in_reply_to_status_id',
       'match', 'user_mentions'],
      dtype='object')<jupyter_text>ETL Drop NaN<jupyter_code>#carico in df pandas
df = pd.read_csv(f'{staging_area}merge.csv')
#diamo un occhio
df.info()
df.sample(3)
#controllo i nulli
df.isna().sum()
# location e in_reply_to_status_id non mi servono
df.drop(['location','in_reply_to_status_id'], inplace=True, axis=1)
# elimino tutti i record che contengono almeno un NaN visto che sono pochi
df.dropna(inplace=True)
#elimino tutti i duplicati
df.isna().sum()
#cosa rimane?
print(f"Number of records without duplicates and NaN {df.shape[0]} di 920528 records")
df.info()
df.to_csv(f'{staging_area}merge_no_nan_01.csv', index=False, encoding="utf-8")<jupyter_output><empty_output><jupyter_text>Drop columns<jupyter_code>#creo altro df con ciò che mi serve
p_project = df[['id', 'user', 'date', 'text', 'hashtags', 'user_mentions', 'match']]
p_project.info()
#carico il file dopo una prima pulizia
p_project.to_csv(f'{staging_area}merge_drop_col_02.csv', index=False)<jupyter_output><empty_output><jupyter_text>Cleaning<jupyter_code>import csv
with open(f'{staging_area}merge_demoji_and_cleaned_03.csv', mode='w') as csv_file_out:
  csv_writer_out = csv.writer(csv_file_out, delimiter=',', quotechar='"')
  with open(f'{staging_area}merge_drop_col_02.csv', mode='r') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    line_count = 0
    max_lines=10000000 
    monDict = {'Gen' : '01' , 'Feb' : '02', 'Mar' : '03', 'Apr' : '04', 'May' : '05', 'Jun' : '06', 'Jul' : '07', 'Aug' : '08', 'Sep' : '09', 'Oct' : '10', 'Nov' : '11', 'Dic' : '12'}
  
    for row in csv_reader:
          if line_count > 0:
              dateCur=row[2]
              monCur=monDict[dateCur[4:7]]
              dateNew=dateCur[26:30]+'-'+monDict[dateCur[4:7]]+'-'+dateCur[8:10]+' '+dateCur[11:13]+':'+dateCur[14:16]+':'+dateCur[17:19]
              timezone='z'+dateCur[20:25]
              #print (f'{dateCur} ==> {dateNew}')
              textnonl=row[3].replace("\n"," ")
              textnonl=re.sub(r"\s+"," ",textnonl) # tolgo doppi spazi
              #print (f'message {message}')
              textnonlhash=re.sub(r"#\w+ ?","",textnonl)
              textnonlhash=re.sub(r"\s+"," ",textnonlhash)
              textnonlhashmen=re.sub(r"@\w+ ?","",textnonlhash)
              textnonlhashmen=re.sub(r"\s+"," ",textnonlhashmen)
              textnonlhashmenhttp=re.sub(r"http\S+","",textnonlhashmen)
              textnonlhashmenhttp=re.sub(r"\s+"," ",textnonlhashmenhttp)
              textnonlhashmenhttpnum=re.sub(r"\d+", "", textnonlhashmenhttp)    
              textnonlhashmenhttpnum=re.sub(r"\s+"," ",textnonlhashmenhttpnum)
              textnonlhashmenhttpnumsimb=re.sub(r"[^\w\s]"," ",textnonlhashmenhttpnum)
              textnonlhashmenhttpnumsimb=re.sub(r"\s+"," ",textnonlhashmenhttpnumsimb)
              row2=row
              row2.extend([dateNew,timezone,textnonl,textnonlhash,textnonlhashmen,textnonlhashmenhttp,textnonlhashmenhttpnum,textnonlhashmenhttpnumsimb])
              csv_writer_out.writerow(row2)
              line_count += 1
          else:
            row2=row
            row2.extend(['date_norm','time_zone','text_nonl','text_nonl_hash','text_nonl_hash_men','text_nonl_hash_men_http','text_nonl_hash_men_http_num','text_nonl_hash_men_http_num_simb'])
            csv_writer_out.writerow(row2)
            line_count += 1
          if line_count > max_lines:
              print (f'Exit')
              break      
    print(f'Processed {line_count} lines.')
df = pd.read_csv(f'{staging_area}merge_demoji_and_cleaned_03.csv', sep=',')
df.dropna(inplace=True)
df.to_csv(f'{staging_area}merge_demoji_and_cleaned_03.csv', index=False)
df.info()
df.head()<jupyter_output><empty_output><jupyter_text>Language detection<jupyter_code>!pip install polyglot
!pip install PyICU
!pip install pycld2
!pip install morfessor
import polyglot
import icu
from polyglot.detect import Detector
from icu import *
p_project_cleaned = pd.read_csv(f'{staging_area}merge_demoji_and_cleaned_03.csv', sep=',')
p_project_cleaned.sample(3)
# problema: servono almeno 3 parole per tweet altrimenti polyglot si rompe dopo un po
#nuova colonna con testo splittato
p_project_cleaned['split_text'] = p_project_cleaned['text_nonl_hash_men_http_num_simb'].str.split()
p_project_cleaned.info()
#nuova colonna con la conta delle parole
p_project_cleaned['length'] = p_project_cleaned['split_text'].apply(lambda x: len(x))
#vediamo
p_project_cleaned.head(3)
#ci sono valori 0
min(p_project_cleaned['length'])
#tengo solo tweet con 3 parole almeno
p_project_cleaned = p_project_cleaned[p_project_cleaned.length > 2]
p_project_cleaned.info()
#dividere il subset in 3 perchè la RAM di Colab non è sufficiente
p_project_cleaned_1 = p_project_cleaned.iloc[:100000,:]
p_project_cleaned_2 = p_project_cleaned.iloc[100000:200000,:]
p_project_cleaned_3 = p_project_cleaned.iloc[200000:300000,:]
p_project_cleaned_4 = p_project_cleaned.iloc[300000:400000,:]
p_project_cleaned_5 = p_project_cleaned.iloc[400000:500000,:]
p_project_cleaned_6 = p_project_cleaned.iloc[500000:600000,:]
p_project_cleaned_7 = p_project_cleaned.iloc[600000:700000,:]
p_project_cleaned_8 = p_project_cleaned.iloc[700000:800000,:]
p_project_cleaned_9 = p_project_cleaned.iloc[800000:,:]
# apply polyglot
p_project_cleaned_1['poly_obj'] = p_project_cleaned_1['text_nonl_hash_men_http_num_simb'].apply(lambda x: Detector(x, quiet=True))
p_project_cleaned_1['text_lang'] = p_project_cleaned_1.poly_obj.apply(lambda x: icu.Locale.getDisplayName(x.language.locale))
p_project_cleaned_1['text_lan_code'] = p_project_cleaned_1.poly_obj.apply( lambda x: x.language.code)
p_project_cleaned_1['lang_confidence'] = p_project_cleaned_1.poly_obj.apply( lambda x: x.language.confidence)
p_project_cleaned_1.to_csv(f'{staging_area}subset_lan_det_01.csv', index=False)
p_project_cleaned_2['poly_obj'] = p_project_cleaned_2['text_nonl_hash_men_http_num_simb'].apply(lambda x: Detector(x, quiet=True))
p_project_cleaned_2['text_lang'] = p_project_cleaned_2.poly_obj.apply(lambda x: icu.Locale.getDisplayName(x.language.locale))
p_project_cleaned_2['text_lan_code'] = p_project_cleaned_2.poly_obj.apply( lambda x: x.language.code)
p_project_cleaned_2['lang_confidence'] = p_project_cleaned_2.poly_obj.apply( lambda x: x.language.confidence)
p_project_cleaned_2.to_csv(f'{staging_area}subset_lan_det_02.csv', index=False)

p_project_cleaned_3['poly_obj'] = p_project_cleaned_3['text_nonl_hash_men_http_num_simb'].apply(lambda x: Detector(x, quiet=True))
p_project_cleaned_3['text_lang'] = p_project_cleaned_3.poly_obj.apply(lambda x: icu.Locale.getDisplayName(x.language.locale))
p_project_cleaned_3['text_lan_code'] = p_project_cleaned_3.poly_obj.apply( lambda x: x.language.code)
p_project_cleaned_3['lang_confidence'] = p_project_cleaned_3.poly_obj.apply( lambda x: x.language.confidence)
p_project_cleaned_3.to_csv(f'{staging_area}subset_lan_det_03.csv', index=False)

p_project_cleaned_4['poly_obj'] = p_project_cleaned_4['text_nonl_hash_men_http_num_simb'].apply(lambda x: Detector(x, quiet=True))
p_project_cleaned_4['text_lang'] = p_project_cleaned_4.poly_obj.apply(lambda x: icu.Locale.getDisplayName(x.language.locale))
p_project_cleaned_4['text_lan_code'] = p_project_cleaned_4.poly_obj.apply( lambda x: x.language.code)
p_project_cleaned_4['lang_confidence'] = p_project_cleaned_4.poly_obj.apply( lambda x: x.language.confidence)
p_project_cleaned_4.to_csv(f'{staging_area}subset_lan_det_04.csv', index=False)

p_project_cleaned_5['poly_obj'] = p_project_cleaned_5['text_nonl_hash_men_http_num_simb'].apply(lambda x: Detector(x, quiet=True))
p_project_cleaned_5['text_lang'] = p_project_cleaned_5.poly_obj.apply(lambda x: icu.Locale.getDisplayName(x.language.locale))
p_project_cleaned_5['text_lan_code'] = p_project_cleaned_5.poly_obj.apply( lambda x: x.language.code)
p_project_cleaned_5['lang_confidence'] = p_project_cleaned_5.poly_obj.apply( lambda x: x.language.confidence)
p_project_cleaned_5.to_csv(f'{staging_area}subset_lan_det_05.csv', index=False)

p_project_cleaned_6['poly_obj'] = p_project_cleaned_6['text_nonl_hash_men_http_num_simb'].apply(lambda x: Detector(x, quiet=True))
p_project_cleaned_6['text_lang'] = p_project_cleaned_6.poly_obj.apply(lambda x: icu.Locale.getDisplayName(x.language.locale))
p_project_cleaned_6['text_lan_code'] = p_project_cleaned_6.poly_obj.apply( lambda x: x.language.code)
p_project_cleaned_6['lang_confidence'] = p_project_cleaned_6.poly_obj.apply( lambda x: x.language.confidence)
p_project_cleaned_6.to_csv(f'{staging_area}subset_lan_det_06.csv', index=False)
p_project_cleaned_7['poly_obj'] = p_project_cleaned_7['text_nonl_hash_men_http_num_simb'].apply(lambda x: Detector(x, quiet=True))
p_project_cleaned_7['text_lang'] = p_project_cleaned_7.poly_obj.apply(lambda x: icu.Locale.getDisplayName(x.language.locale))
p_project_cleaned_7['text_lan_code'] = p_project_cleaned_7.poly_obj.apply( lambda x: x.language.code)
p_project_cleaned_7['lang_confidence'] = p_project_cleaned_7.poly_obj.apply( lambda x: x.language.confidence)
p_project_cleaned_7.to_csv(f'{staging_area}subset_lan_det_07.csv', index=False)

p_project_cleaned_8['poly_obj'] = p_project_cleaned_8['text_nonl_hash_men_http_num_simb'].apply(lambda x: Detector(x, quiet=True))
p_project_cleaned_8['text_lang'] = p_project_cleaned_8.poly_obj.apply(lambda x: icu.Locale.getDisplayName(x.language.locale))
p_project_cleaned_8['text_lan_code'] = p_project_cleaned_8.poly_obj.apply( lambda x: x.language.code)
p_project_cleaned_8['lang_confidence'] = p_project_cleaned_8.poly_obj.apply( lambda x: x.language.confidence)
p_project_cleaned_8.to_csv(f'{staging_area}subset_lan_det_08.csv', index=False)

p_project_cleaned_9['poly_obj'] = p_project_cleaned_9['text_nonl_hash_men_http_num_simb'].apply(lambda x: Detector(x, quiet=True))
p_project_cleaned_9['text_lang'] = p_project_cleaned_9.poly_obj.apply(lambda x: icu.Locale.getDisplayName(x.language.locale))
p_project_cleaned_9['text_lan_code'] = p_project_cleaned_9.poly_obj.apply( lambda x: x.language.code)
p_project_cleaned_9['lang_confidence'] = p_project_cleaned_9.poly_obj.apply( lambda x: x.language.confidence)
p_project_cleaned_9.to_csv(f'{staging_area}subset_lan_det_09.csv', index=False)
path = f'{staging_area}'
all_files = glob.glob(os.path.join(path, "subset_*.csv"))
df_from_each_file = (pd.read_csv(f, sep=',') for f in all_files)
df_merged = pd.concat(df_from_each_file, ignore_index=True)
df_merged.to_csv(f'{staging_area}merge_lan_det_04.csv', index=False)
print(f'TOT: {df_merged.shape[0]} records | {df_merged.shape[1]} columns')
print()
print(f'{df_merged.columns}')
#vediamo
df_merged.info()
df_merged.head(3)
# vediamo che roba c'è dentro in una riga a caso
print(df_merged['poly_obj'][1])<jupyter_output>Prediction is reliable: False
Language 1: name: Breton      code: br       confidence:  95.0 read bytes:   558
Language 2: name: un          code: un       confidence:   0.0 read bytes:     0
Language 3: name: un          code: un       confidence:   0.0 read bytes:     0<jupyter_text>Prepare pattern for sentiment<jupyter_code>#riprendiamo
result_project = pd.read_csv(f'{staging_area}merge_lan_det_04.csv')
#definisco un pattern di insulti
mylist = ['cazzo', 'dio', 'madonna', 'merda', 'minchia', 'culo', 'stronzo', 'troia', 'figlio di', 'scarpone', 'puttana', 'cornuto', 'porco', 'cagare', 'coglione', 'incazzare', 'figa', 'bastardo', 'venduto', 'cazzata', 'stronzata', 'negro', 'vacca', 'pirla', 'frocio', 'pippa', 'terrone', 'suca', 'coleroso', 'tumorato']
pattern = '|'.join(mylist)
pattern
'(?i)cazzo|(?i)dio|(?i)madonna|(?i)merda|(?i)minchia|(?i)culo|(?i)stronzo|(?i)troia|(?i)figlio di|(?i)scarpone|(?i)puttana|(?i)cornuto|(?i)porco|(?i)cagare|(?i)coglione|(?i)incazzare|(?i)figa|(?i)bastardo|(?i)venduto|(?i)cazzata|(?i)stronzata|(?i)negro|(?i)vacca|(?i)pirla|(?i)frocio|(?i)pippa|(?i)terrone|(?i)suca|(?i)coleroso|(?i)tumorato'
#creo una nuova colonna che mi dice se il tweet contiene o no qualcosa del pattern
result_project['insulto_ita'] = result_project.text_nonl_hash_men_http_num_simb.str.contains(pattern)
#vediamo
result_project.head(2)
result_project.to_csv(f'{staging_area}merge_insult_05.csv', index=False)<jupyter_output><empty_output><jupyter_text>Analysis<jupyter_code>#importo file
p = pd.read_csv(f'{staging_area}merge_insult_05.csv')
#divido in 3 subset per ogni match perchè Gepi fa schifo
belita = p[p["match"] == 'belita']
itaesp = p[p["match"] == 'itaesp']
itaen = p[p["match"] == 'itaen']
#conto gli insulti in lingua italiana, tutti, anche quelli che hanno prediction della lingua false
p['insulto_ita'].value_counts()
#faccio un groupby sulle lingue e una count per sapere quante lingue ci sono e quale è la frequenza
language = p.groupby(['text_lang']).size()
language
#esporto quanto sopra così ci posso lavorare in google sheet e fare eventuali grafici
belita.to_excel(f'{dwh_euro2020}belita.xlsx')
itaesp.to_excel(f'{dwh_euro2020}itaesp.xlsx')
itaen.to_excel(f'{dwh_euro2020}itaen.xlsx')
#creo un subset solo di tweet in italiano
belita = p.loc[p['text_lang'] == 'Italian']
belita.head()
###### DAI QUI IN POI, RISULTATO PER RISULTATO, COPIAVO A MANO SU GOOGLESHEET
### 22 GIOCATORI + VAR E ARBITRO + 2 ALLENATORI = 26 PERSONE DA TROVARE
# FATTIBILE FARE I PASSAGGI A MANO MA FOSSERO STATE MIGLIAIA DI PERSONE DA ANALIZZARE?
# CI IMPIEGHEREI TROPPO TEMPO A CAPIRE COME FARE E HO TAGLIATO CORTO
# MAGARI LA SOLUZIONE è PIù semplice dle previsto. amen
#conto gli insulti in lingua italiana nei tweet che risultano in italiano partendo dal subset precedente
belita['insulto_ita'].value_counts()
#conto gli insulti in lingua italiana nei tweet che risultano in italiano dove la predizione è vera
#cioè l'assegnazione della lingua è data per certa
insult_it = belita.loc[belita['insulto_ita'] == True] #creo subset di insulti true da language ita
len(insult_it) #conto
# controllo quante volte viene citato qualcuno in qualsiasi tweet
belita['text'].str.contains('ourtois').value_counts()
# selezionio il subset delle volte in cui viene citato il giocatore in tutti i tweet con qualsiasi predizione
insulti = belita.loc[belita['text'].str.contains('ourtois')]
#controllo quante volte è stato insultato in italiano in tutti i tweet di tutte le lingue con qualsiasi predizione
insulti['insulto_ita'].value_counts()
#ora restringo l'anasili a citazioni e insulti solo per tweet con predizione true e in lingua italiana
# riprendo il sub set linguaggio in italiano e seleziono solo le volte in cui qualcuno è citato in tweet che risultano con prediction true
insult_it = insulti.loc[insulti['text'].str.contains('ourtois')]
insult_it['text'].value_counts()
#vedo che ci sono 14 + 1 retweet. non distingue e metto tutto insieme
len(insult_it) #conto
# controllo quante volte viene insultato in italiano il citato solo nei tweet sopra (in ling ita e validati)
insult_it['insulto_ita'].value_counts()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.139288259211133, 'fraction_numerical': 0.020664794399966466, 'mean_word_length': 6.569162436548224, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 43, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 5, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '402297', 'license': 'MIT', 'n_tokens_mistral': 9532, 'n_tokens_neox': 9060, 'n_words': 1575, 'path': 'EURO2020.ipynb', 'repo_name': 'AntonelloManenti/challange_master_2021', 'size': '24041'}