import pandas as pd

df1 = pd.read_csv('data/PFE.csv')
df1['id'] = 'PFE'
df1.head(3)

df2 = pd.read_csv('data/GSK.csv')
df2['id'] = 'GSK'
df2.head(3)

df = pd.concat([df1, df2])
df.head(3)

df.tail(3)

print(df1.shape, df2.shape, df.shape)

del df1
del df2

df.sort_values(by=['id', 'Date'], ascending=[True, True], inplace=True)
df.head(3)

df.tail(3)

sel_cols = ['id', 'Date', 'Adj Close', 'Volume']
df = df[sel_cols].copy(deep=True)

df.head(3)

from tsfresh.utilities.dataframe_functions import roll_time_series
from tsfresh import extract_features

roll_rows = 21
df_rolled = roll_time_series(df, column_id="id", column_sort="Date", max_timeshift=roll_rows, min_timeshift=roll_rows, disable_progressbar=True)
df_features = extract_features(df_rolled, column_id="id", column_sort="Date")

print(df.shape, df_rolled.shape, df_features.shape, df_features.shape[0]-df.shape[0])

df_rolled.head(3)

df_rolled[df_rolled['Date']=='2009-02-03']

df_features.head(2)

na_cnt = df_features.isna().sum().sort_values(ascending=False)
na_cnt.head()

ratio = 0.1
print(ratio*df_features.shape[0])
drop_cols = na_cnt[na_cnt>ratio*df_features.shape[0]].index.tolist()
print('before dropping columns: ', df_features.shape)
df_features.drop(columns=drop_cols, inplace=True)
print('after dropping columns: ', df_features.shape)

na_cnt = df_features.isna().sum().sort_values(ascending=False)
na_cnt.head()

from tsfresh.utilities.dataframe_functions import impute

impute(df_features)
na_cnt = df_features.isna().sum().sort_values(ascending=False)
na_cnt.head()

df_features.reset_index(inplace=True)
df_features.head(2)

df_features.rename(columns={'level_0':'id', 'level_1':'Date'}, inplace=True)
df_features.head(2)

df.head()

roll_rows = 30
df_rolled2 = roll_time_series(df, column_id="id", column_sort="Date", 
                             max_timeshift=roll_rows, min_timeshift=roll_rows, 
                              rolling_direction=-1, disable_progressbar=True)

df_rolled2.head()

df_features2 = extract_features(df_rolled2, column_id="id", column_sort="Date", column_value = 'Adj Close')

df_features2.head()

sel_cols = ['Adj Close__maximum']
df_target = df_features2[sel_cols].copy(deep=True)

df_target.head(10)

df_target['max_adj_close_30'] = df_target['Adj Close__maximum'].shift(-1)

df_target.head(10)

for i in range(0,10):
    print(df.iloc[i,1], df.iloc[i:(30+i),]['Adj Close'].max())

df_target.reset_index(inplace=True)
df_target.rename(columns={'level_0':'id', 'level_1':'Date'}, inplace=True)
df_target.head(2)

print(df_target.shape, df.shape)
df_target = df_target.merge(df, on=['id', 'Date'], how='inner')
print(df_target.shape, df.shape)
df_target.head(2)

df_target['delta_pct'] = (df_target['max_adj_close_30'] - df_target['Adj Close'] )/df_target['Adj Close'] *100
df_target.head(2)

df_target['target'] = 0
df_target.loc[df_target['delta_pct']>=5, 'target'] = 1
df_target['target'].value_counts()

df_target.head()

from tsfresh.feature_selection.selection import select_features

df_target.columns

sel_cols = ['id', 'Date', 'target', 'max_adj_close_30', 'delta_pct', 'Adj Close','Volume']

#combine features with target variable
print(df_target.shape, df_features.shape)
df_final = df_target[sel_cols].merge(df_features, on = ['id', 'Date'], how='inner')
print(df_final.shape, df_target.shape, df_features.shape)

df_final.head()

df_final.isna().sum().sort_values(ascending=False).head(5)

print(df_final.shape)
df_final.dropna(how='any', inplace=True)
print(df_final.shape)
df_final.isna().sum().sort_values(ascending=False).head(5)

X = df_final.iloc[:, 5:].copy(deep=True)
y = df_final['target']
print(y[:3])
X.head(3)

y = df_final['target']
print(X.shape)
for fdr_level in [0.05, 0.01, 0.005, 0.001, 0.0005, 0.0001, 0.00005, 0.00001, 0.000005, 0.000001, 0.0000005, 0.0000001]:
    df_filtered_binary = select_features(X, y, fdr_level=fdr_level, ml_task='classification')
    print(fdr_level, df_filtered_binary.shape)

y = df_final['delta_pct']

for fdr_level in [0.05, 0.01, 0.005, 0.001, 0.0005, 0.0001, 0.00005, 0.00001, 0.000005, 0.000001, 0.0000005, 0.0000001]:
    df_filtered_reg = select_features(X, y, fdr_level=fdr_level, ml_task='regression')
    print(fdr_level, df_filtered_reg.shape)

binary_cols = df_filtered_binary.columns.tolist()
reg_cols = df_filtered_reg.columns.tolist()

both_cols = list(set(binary_cols+reg_cols))

print(len(binary_cols), len(reg_cols), len(both_cols))

'Adj Close' in both_cols, 'Volume'in both_cols

y_cols = ['id', 'Date', 'target', 'delta_pct']

df_final[y_cols + both_cols].head(3)

file_name = 'PFE_GSK_final.csv'
df_final[y_cols + both_cols].to_csv('data/'+file_name, sep='|', compression='gzip')