zgDeHHAg

1import sqlite3
2import pandas as pd
3
4create_sentiment_table_script = \
5"""
6CREATE TABLE IF NOT EXISTS sentiment
7(
8date TEXT, 
9symbol TEXT, 
10new_sentiment REAL,
11twitter_sentiment REAL,
12news_heat_story_flow REAL,
13news_heat_user_activity REAL
14)
15"""
16
17create_twitter_table_script = \
18"""
19CREATE TABLE IF NOT EXISTS twitter
20(
21date TEXT, 
22symbol TEXT,
23twitter_sentiment REAL,
24std_dev REAL,
25avg_ts REAL,
26z_score REAL
27)
28"""
29
30# load the csv
31df = pd.read_csv('Sentiment.csv')
32
33
34# write it to a sqlite3 table
35conn = sqlite3.connect('sentiment.db')
36conn.execute(create_sentiment_table_script)
37for row in df.iterrows():
38    try:
39        conn.execute("""
40            INSERT INTO sentiment
41            VALUES (?,?,?,?,?,?)
42            """, (row[1]['Date'], \
43              row[1]['Ticker'].split(' ')[0], \
44              float(row[1]['NEWS_SENTIMENT_RT']), \
45              float(row[1]['TWITTER_SENTIMENT_REALTIME']), \
46              float(row[1]['NEWS_HEAT_STORY_FLOW_RT']), \
47              float(row[1]['NEWS_HEAT_USER_ACTIVITY_RT'])))
48    except:
49        print 'ERROR', row
50conn.commit()
51conn.close()
52
53# create twitter stddev, mean, and zscore and insert into new table
54conn = sqlite3.connect('sentiment.db')
55conn.execute(create_twitter_table_script)
56cur = conn.cursor()
57cur.execute("SELECT DISTINCT symbol FROM sentiment")
58rows = cur.fetchall()
59df_dict = dict()
60for symbol_tuple in rows:
61    
62    symbol = symbol_tuple[0]
63
64    
65    df = pd.read_sql_query("""SELECT date, symbol, twitter_sentiment 
66                    FROM sentiment 
67                    WHERE symbol = '{}'
68        """.format(symbol), conn)
69    df['std_dev'] = df['twitter_sentiment'].shift().rolling(5, min_periods=5).std()
70    df['avg_ts'] = df['twitter_sentiment'].shift().rolling(5, min_periods=5).mean()
71    df['z_score']= (df['twitter_sentiment']-df['avg_ts'])/df['std_dev']
72    df.fillna(0, inplace=True)
73    
74    for row in df.iterrows():
75        conn.execute("INSERT INTO twitter VALUES (?,?,?,?,?,?)",(
76            str(pd.to_datetime(row[1][0]).date()),
77            row[1][1],
78            row[1][2],
79            row[1][3], 
80            row[1][4],
81            row[1][5]
82        ))
83    conn.commit()
84conn.close()