<jupyter_start><jupyter_text>Reddit comments EDA1. Setting2. data load, pre-processing3. Sentiment EDA using VADER4. Sentiment EDA using Bert5. EDA between sentiment and price  Setting<jupyter_code># try:
#   import colab
#   !pip install --upgrade pip
# except:
#   pass
# print('Installing TensorFlow Data Validation')
# !pip install --upgrade 'tensorflow_data_validation[visualization]<2'
!pip install vaderSentiment
!pip install pysentimiento
import tensorflow as tf

from google.colab import drive
import re
import os
from vaderSentiment.vaderSentiment import SentimentIntensityAnalyzer
from sklearn.metrics import confusion_matrix
import sklearn.metrics
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import shutil
from datetime import datetime, timedelta

from transformers import pipeline
from pysentimiento import create_analyzer

from tqdm.notebook import tqdm
import pickle
import time
drive.mount('/content/drive')
path_vader = '/usr/local/lib/python3.7/dist-packages/vaderSentiment/'
path_capstone = '/content/drive/MyDrive/Colab Notebooks/capstone/data/'
print(os.listdir(path_vader))
print(os.listdir(path_capstone))

#lexicon update
shutil.copy(f'{path_capstone}emoji_utf8_lexicon.txt', f'{path_vader}emoji_utf8_lexiconss.txt')
shutil.copy(f'{path_capstone}vader_lexicon_v3.txt', f'{path_vader}vader_lexicon.txt')

analyzer = SentimentIntensityAnalyzer()#vader analyzer
analyzer_bert = create_analyzer(task="sentiment", lang="en")#bert analyzer<jupyter_output>Mounted at /content/drive
['emoji_utf8_lexicon.txt', '__init__.py', 'vaderSentiment.py', '__pycache__', 'vader_lexicon.txt']
['data_reddit.csv', 'btc_day_price.csv', 'emoji_utf8_lexicon.txt', 'sentences_948.xlsx', 'new_lexicon.csv', 'all_text_sentiment.csv', 'feature.csv', 'feature_v2.csv', 'feature_v3.csv', 'feature_v4_filtering_long_sentences.csv', 'feature_v5_filter_low_abs_comp.csv', 'list_2words.pkl', 'data_final_crypto.csv', 'vader_lexicon_v3.txt', 'second_comment_bitcoin_cl.csv', 'top_comment_bitcoin_cl.csv', 'data_final_bitcoin.csv', 'ohlcv.csv', 'bert_btc.csv', 'bert_btc_top.csv', 'sent_bert_btc_top.csv', 'neutral_sentence.csv', 'top_comment_crypto_cl.xlsx', 'top_comment_crypto_cl.csv', 'sent_btc_top.csv', 'sent_btc.csv', 'sent_crypto_top.csv', 'sent_total.csv']<jupyter_text>comment count  load comment data<jupyter_code>start = '2018-01-02'
end = '2022-02-28'

df_btc = pd.read_csv(f'{path_capstone}data_final_bitcoin.csv', index_col=0)
df_btc = df_btc.loc[:,start:end]
df_btc.fillna(0, inplace=True)

df_btc_top = pd.read_csv(f'{path_capstone}top_comment_bitcoin_cl.csv', index_col=0)
df_btc_top = df_btc_top.loc[:,start:end]
df_btc_top.fillna(0, inplace=True)

df_btc_second = pd.read_csv(f'{path_capstone}second_comment_bitcoin_cl.csv', index_col=0)
df_btc_second = df_btc_second.loc[:,start:end]
df_btc_second.fillna(0, inplace=True)

df_crypto_top = pd.read_csv(f'{path_capstone}top_comment_crypto_cl.csv', index_col=0)
df_crypto_top = df_crypto_top.loc[:,start:end]
df_crypto_top.fillna(0, inplace=True)

df_total = df_btc.append(df_crypto_top, ignore_index=True)
df_total = df_total.loc[:,start:end]

df_top = df_btc_top.append(df_crypto_top, ignore_index = True)
df_top = df_top.loc[:,start:end]
df_btc.head()<jupyter_output>/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2882: DtypeWarning: Columns (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252[...]<jupyter_text>count daily comment number<jupyter_code>def count_text_num(df):#count daily comment num
  day_cnt = []
  for i in range(len(df.columns)):
    cnt = 0
    for j in range(len(df)):
      text = df.iloc[j,i]
      if text =='[deleted]' or text == 0: continue #NaN, [deleted]되있는 문장 스킵
      cnt +=1
    if cnt==0:
      print(f'{df.columns[i]}<- comment 0개')
    day_cnt.append(cnt)
  return day_cnt
print('btc_total')
btc_cnt = count_text_num(df_btc)
print('btc_top')
btc_cnt_top = count_text_num(df_btc_top)
print('btc_second')
btc_cnt_second = count_text_num(df_btc_second)
print('crypto_top')
crypto_cnt_top = count_text_num(df_crypto_top)
print('total')
total_cnt = count_text_num(df_total)
plt.subplot(5, 1, 1)
plt.hist(btc_cnt, bins= range(0,1000,10))
plt.subplot(5, 1, 2)
plt.hist(btc_cnt_top, bins= range(0,1000,10))
plt.subplot(5, 1, 3)
plt.hist(btc_cnt_second, bins= range(0,1000,10))
plt.subplot(5, 1, 4)
plt.hist(crypto_cnt_top, bins= range(0,1000,10))
plt.subplot(5, 1, 5)
plt.hist(total_cnt, bins= range(0,1000,10))
plt.show()<jupyter_output><empty_output><jupyter_text>sentiment EDA  def<jupyter_code>def sent(x):#vader tri-class
    if x > 0:
        return 'positive'
    elif x == 0:
        return 'neutral'
    else:
        return 'negative'

def sentiment(sentence):#bert tri-class
    if sentence == 'pos':
        return 'positive'
    elif sentence == 'neg':
        return 'negative'
    else :
        return 'neutral'        
def sentiment_bi(probas):#bert bi-class
    if probas['POS'] > probas['NEG']:
        return 'positive'
    else:
        return 'negative'
def filtering(series): #text filtering
  cleaned = []
  for text in series: 
    if text =='[deleted]' or text == 0: 
      continue #NaN, [deleted]되있는 문장 스킵
    if re.match('http', text) or text.find('!gif') != -1:
      continue
    length = len(text.split())#20단어 이상 문장들, 물음표로 끝나는 문장 제거
    if length >20 or text[-1]=='?': 
      # print('long sentence and question mark')
      continue
    cleaned.append(text)
  return cleaned
#감성점수 비율 계산
def ratio(li):
  neu = 0
  pos = 0
  neg = 0
  for s in li:
    if s == 'neutral':
      neu += 1
    elif s== 'positive':
      pos += 1
    else:
      neg += 1
  total = pos + neu + neg
  if total!=0:
    return [pos/total, neu/total, neg/total]
  else:
    return [0,0,0]
def make_features(df):
  df['EMA_short'] = df.pos.ewm(span = 12).mean()
  df['EMA_long'] = df.pos.ewm(span = 26).mean()
  df['MACD'] = df.EMA_short - df.EMA_long
  df['MACD_OSC'] = df.MACD - df.pos.ewm(span = 9).mean()#macd osc
  df['pos-neg'] = df.pos - df.neg
  return df
def make_day_score(df):
  day = []
  date = []
  for i in range(1,len(df.columns)):
    cleaned = filtering(df.iloc[:,i])
    date.append(df.columns[i].replace('-',''))
    sentiments = []
    compounds = []
    for text in cleaned:# 정제된 데이터에서 긍/중/부 판단
      comp = analyzer.polarity_scores(text)['compound']
      sentiments.append(sent(comp))
      compounds.append(comp)
    sent_features = ratio(sentiments)
    sent_features.append(np.mean(compounds))
    sent_features.append(np.var(compounds))
    day.append(sent_features)
  day = pd.DataFrame(day, columns=['pos', 'neu', 'neg', 'mean', 'var'])
  day['day'] = date
  for i in range(len(day)):
    day.loc[i,'day'] = datetime.strptime(day.loc[i,'day'], '20%y%m%d')
  day.set_index('day', inplace =True)

  return make_features(day)
def make_day_score_bert(df):
  day = []
  date = []
  for i in tqdm(range(1,len(df.columns))):
    cleaned = filtering(df.iloc[:,i])
    date.append(df.columns[i].replace('-',''))
    sentiments = []
    for text in cleaned:# 정제된 데이터에서 긍/중/부 판단
      # sentiments.append(sentiment_bi(analyzer_bert.predict(text).probas))
      sentiments.append(sentiment(analyzer_bert.predict(text).output.lower()))
    day.append(ratio(sentiments))
  day = pd.DataFrame(day, columns=['pos', 'neu','neg'])
  day['day'] = date
  for i in range(len(day)):
    day.loc[i,'day'] = datetime.strptime(day.loc[i,'day'], '20%y%m%d')
  day.set_index('day', inplace =True)

  return make_features(day)

def is_it_neu(df):
  neu_list = []
  for i in range(1,200):
    cleaned = filtering(df.iloc[:,i])
    for text in cleaned:
      a = analyzer.polarity_scores(text)
      if sorted(a.items(), key = lambda x: -x[1])[0][0] == 'neu':
        neu_list.append(text)
  neu_df = pd.DataFrame({'neu': neu_list})
  return neu_df
    
# neu_df = is_it_neu(df_btc_top)
# neu_df.to_csv(f'{path_capstone}neutral_sentence.csv')
# neu_df<jupyter_output><empty_output><jupyter_text>sentiment distribution Bert 가 Vader보다 수백배 느리다 약 2시간 소요<jupyter_code>df_total.fillna(0, inplace = True)
sent_btc = make_day_score(df_btc)
sent_btc_top = make_day_score(df_btc_top)
sent_btc_second = make_day_score(df_btc_second)

sent_crypto_top =  make_day_score(df_crypto_top)
sent_total = make_day_score(df_total)

print(sent_btc.describe())
print(sent_btc_top.describe())
print(sent_btc_second.describe())
print(sent_crypto_top.describe())
print(sent_total.describe())
sent_btc_top.to_csv(f'{path_capstone}sent_btc_top.csv')
sent_btc.to_csv(f'{path_capstone}sent_btc.csv')
sent_crypto_top.to_csv(f'{path_capstone}sent_crypto_top.csv')
sent_crypto_top.to_csv(f'{path_capstone}sent_total.csv')
# sent_bert_btc_top = make_day_score_bert(df_btc_top)
# print(sent_bert_btc_top.describe())
# sent_bert_btc_top.to_csv(f'{path_capstone}sent_bert_btc_top.csv')
# sent_bert_btc = make_day_score_bert(df_btc)
# print(sent_bert_btc.describe())
# sent_bert_btc_top.to_csv(f'{path_capstone}sent_bert_btc.csv')
# f, ax = plt.subplots(figsize=(30,10))
# # ax1 = ax.plot(sent_btc.pos, label ='total')
# ax2 = ax.plot(sent_btc_top.pos, label = 'VADER')
# ax3 = ax.plot(sent_bert_btc_top.pos, label = 'BERT')
# plt.legend()
# plt.ylabel('pos_ratio')
# plt.show()<jupyter_output><empty_output><jupyter_text>Price data load<jupyter_code>!pip install ccxt

import ccxt
import time
t = '2017-12-01 09:00:00' #btc 시작
start = int(time.mktime(datetime.strptime(t, '%Y-%m-%d %H:%M:%S').timetuple())*1000)#처음 데이터 가져올 때
def timestamp_to_str(time):  # 타임스탬프 문자열로 변환
    return str(time.year * 100000000 + time.month * 1000000 + time.day * 10000 + time.hour * 100 + time.minute)
days = 86400000
binance = ccxt.binance()
btc_ohlcv = binance.fetch_ohlcv("BTC/USDT", '1d', since = start, limit = 1000)
btc_ohlcv2 = binance.fetch_ohlcv("BTC/USDT", '1d', since = start + days*1000, limit = 1000)
df = pd.DataFrame(btc_ohlcv, columns=['datetime', 'open', 'high', 'low', 'close', 'volume'])
df['datetime'] = pd.to_datetime(df['datetime'], unit='ms')
df2 = pd.DataFrame(btc_ohlcv2, columns=['datetime', 'open', 'high', 'low', 'close', 'volume'])
df2['datetime'] = pd.to_datetime(df2['datetime'], unit='ms')
df.set_index('datetime', inplace=True)
df2.set_index('datetime', inplace=True)

hist = df.append(df2)
hist.head()<jupyter_output><empty_output><jupyter_text>Relation between sentiment and price 결측치 처리<jupyter_code>def missing_day(day):
  last_day = day.index[-1]
  for i in range(len(day)):
    if day.index[i] == last_day:break
    if day.index[i]  + timedelta(days=1) != day.index[i+1]: #다음 날짜가 없다
      day.loc[day.index[i]  + timedelta(days=1)] = [day.iloc[i,0],day.iloc[i,1],day.iloc[i,2],day.iloc[i,3],day.iloc[i,4],
                                                    day.iloc[i,5],day.iloc[i,6],day.iloc[i,7],day.iloc[i,8],day.iloc[i,9]]
  day = day.sort_index()
  return day
def missing_bert_day(day):
  last_day = day.index[-1]
  for i in range(len(day)):
    if day.index[i] == last_day:break
    if day.index[i]  + timedelta(days=1) != day.index[i+1]: #다음 날짜가 없다
      day.loc[day.index[i]  + timedelta(days=1)] = [day.iloc[i,0],day.iloc[i,1],day.iloc[i,2]]
  day = day.sort_index()
  return day
while True:
  first = sent_crypto_top.shape
  print(sent_btc.shape)
  print(sent_btc_top.shape)
  print(sent_btc_second.shape)
  print(sent_crypto_top.shape)
  sent_btc = missing_day(sent_btc)
  sent_btc_top = missing_day(sent_btc_top)
  sent_btc_second = missing_day(sent_btc_second)
  sent_crypto_top = missing_day(sent_crypto_top)
  sent_total = missing_day(sent_total)
  second = sent_crypto_top.shape
  print(sent_btc.shape)
  print(sent_btc_top.shape)
  print(sent_btc_second.shape)
  print(sent_crypto_top.shape)
  if first == second: break

# while True:
#   first = sent_bert_btc_top.shape
#   print(first)
#   # sent_bert_btc = missing_bert_day(sent_bert_btc)
#   sent_bert_btc_top = missing_bert_day(sent_bert_btc_top)
#   second = sent_bert_btc_top.shape
#   print(second)
#   if first[0] == second[0]: break<jupyter_output><empty_output><jupyter_text>price data section fit<jupyter_code>print(sent_btc.shape)
print(hist.shape)
hist = hist.loc[sent_btc.index[0]:sent_btc.index[-1],:] # sent_btc와 start date, end date 통합
print(sent_crypto_top.shape)
print(hist.shape)
hist.join(sent_btc) #sent_df의 2022-01-02 ~ 2022-01-04까지 NaN 값<jupyter_output>(1518, 10)
(1588, 5)
(1518, 10)
(1518, 5)<jupyter_text>Visualization<jupyter_code>fig, ax1 = plt.subplots(figsize=(30,10))
x = sent_btc.index
y1 = sent_btc.pos
y2 = hist['close']
ax1.plot(x, y1, color='green')

ax2 = ax1.twinx()
ax2.plot(x, y2, color='deeppink')
plt.title('price/pos-VADER')
plt.show()
fig, ax1 = plt.subplots(figsize=(30,10))
x = sent_crypto_top.index
y1 = sent_crypto_top.pos.rolling(window=20, min_periods=20).mean()
y2 = hist['close']
ax1.plot(x, y1, color='green')

ax2 = ax1.twinx()
ax2.plot(x, y2, color='deeppink')
plt.title('price/crypto-posMA20-VADER')
plt.show()
fig, ax1 = plt.subplots(figsize=(30,10))
x = sent_crypto_top.index
y1 = sent_crypto_top.pos
y2 = hist['close']
ax1.plot(x, y1, color='green')

ax2 = ax1.twinx()
ax2.plot(x, y2, color='deeppink')
plt.title('price/crypto-pos-VADER')
plt.show()
fig, ax1 = plt.subplots(figsize=(30,10))
x = sent_btc.index
y1 = sent_btc.pos.rolling(window=20, min_periods=20).mean()
y2 = hist['close']
ax1.plot(x, y1, color='green')

ax2 = ax1.twinx()
ax2.plot(x, y2, color='deeppink')
plt.title('MAprice/pos - VADER')
plt.show()
fig, ax1 = plt.subplots(figsize=(30,10))
x = sent_bert_btc_top.index
y1 = sent_bert_btc_top.pos
y2 = hist['close']
ax1.plot(x, y1, color='green')

ax2 = ax1.twinx()
ax2.plot(x, y2, color='deeppink')
plt.title('price/pos-BERT')
plt.show()
fig, ax1 = plt.subplots(figsize=(30,10))
x = sent_bert_btc_top.index
y1 = sent_bert_btc_top.pos.rolling(window=20, min_periods=20).mean()
y2 = hist['close']
ax1.plot(x, y1, color='green')

ax2 = ax1.twinx()
ax2.plot(x, y2, color='deeppink')
plt.title('MAprice/pos - BERT')
plt.show()<jupyter_output><empty_output><jupyter_text>Correlation<jupyter_code># print('VADER-BERT',np.corrcoef(sent_bert_btc_top['pos'], sent_btc_top['pos'])[0,1])
# sent_bert_btc_top['diff'] = sent_bert_btc_top.pos - sent_bert_btc_top.pos.shift(1)
# sent_bert_btc_top.fillna(0, inplace = True)
# sent_bert_btc_top

# sent_btc_top['diff'] = sent_btc_top.pos - sent_btc_top.pos.shift(1)
# sent_btc_top.fillna(0, inplace = True)
# sent_btc['diff'] = sent_btc.pos - sent_btc.pos.shift(1)
# sent_btc.fillna(0, inplace = True)
# sent_btc_second['diff'] = sent_btc_second.pos - sent_btc_second.pos.shift(1)
# sent_btc_second.fillna(0, inplace = True)
#상관계수
def correlation(day, price):
  pos = np.corrcoef(day['pos'], price['close'])[0,1]
  neu = np.corrcoef(day['neu'], price['close'])[0,1]
  neg = np.corrcoef(day['neg'], price['close'])[0,1]
  pos_neg = np.corrcoef(day['pos-neg'], price['close'])[0,1]
  mean = np.corrcoef(day['mean'], price['close'])[0,1]
  var = np.corrcoef(day['var'], price['close'])[0,1]
  posMA20 = np.corrcoef(day.pos.rolling(window=20, min_periods=1).mean(), price['close'])[0,1]
  negMA20 = np.corrcoef(day.neg.rolling(window=20, min_periods=1).mean(), price['close'])[0,1]
  EMA_short = np.corrcoef(day['EMA_short'], price['close'])[0,1]
  EMA_long = np.corrcoef(day['EMA_long'], price['close'])[0,1]
  MACD = np.corrcoef(day['MACD'], price['close'])[0,1]
  MACD_OSC = np.corrcoef(day['MACD_OSC'], price['close'])[0,1]
  return pos, neu, neg, pos_neg,mean, var, posMA20, negMA20, EMA_short, EMA_long, MACD, MACD_OSC
sent_btc = sent_btc.fillna(0)
sent_btc_top = sent_btc_top.fillna(0)
sent_btc_second = sent_btc_second.fillna(0)
sent_crypto_top = sent_crypto_top.fillna(0)
# sent_bert_btc = sent_bert_btc.fillna(0)
# sent_bert_btc_top = sent_bert_btc_top.fillna(0)
matrix_df = pd.DataFrame({
    'btc' : correlation(sent_btc, hist),
    'btc_top' :correlation(sent_btc_top, hist),
    'btc_second' : correlation(sent_btc_second, hist),
    'crypto_top' : correlation(sent_crypto_top, hist),
    'total' : correlation(sent_total, hist)
})
matrix_df['sent_feature'] = ['pos', 'neu', 'neg', 'pos-neg','mean', 'var', 'posMA20', 'negMA20', 'EMA_short',
                             'EMA_long', 'MACD', 'MACD_OSC']
matrix_df.set_index('sent_feature', inplace = True)
print('correlation between sentiment and price')
matrix_df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16844935414109533, 'fraction_numerical': 0.0634169150739377, 'mean_word_length': 5.841263494602159, 'pattern_counts': {'":': 0, '<': 40, '<?xml version=': 0, '>': 41, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '633833', 'license': 'MIT', 'n_tokens_mistral': 7929, 'n_tokens_neox': 7384, 'n_words': 1088, 'path': 'comments_EDA.ipynb', 'repo_name': 'CryptoNLP/bitcoin_price_prediction_with_sentiment-1', 'size': '17109'}