diff options
Diffstat (limited to 'python/load_globeop_report.py')
| -rw-r--r-- | python/load_globeop_report.py | 24 |
1 files changed, 21 insertions, 3 deletions
diff --git a/python/load_globeop_report.py b/python/load_globeop_report.py index 03297caa..ea150b2e 100644 --- a/python/load_globeop_report.py +++ b/python/load_globeop_report.py @@ -3,6 +3,7 @@ import os import pandas as pd from itertools import chain from dates import bus_day +from db import dbengine def get_globs(fname, years=['2013', '2014', '2015', '2016', '2017']): basedir = '/home/serenitas/Daily' @@ -30,14 +31,21 @@ def valuation_reports(): newdf = pd.read_csv(f) newdf['KnowledgeDate'] = date newdf['PeriodEndDate'] = date - bus_day + newdf['row'] = newdf.index if newdf.empty or ('PeriodEndDate' in df and \ not df[df.PeriodEndDate == newdf.PeriodEndDate.iat[0]].empty): continue df = df.append(newdf) del df['AccountingPeriod'] + + ## cleanups + df.Strat = df.Strat.str.replace("^(SERCGMAST__){1,2}(M_|SER_)?", "", 1) + df.Port = df.Port.str.replace("^(SERCGMAST__){1,2}(SERG__|SERG_)?", "", 1) for col in ['Strat', 'InvCcy', 'Fund', 'Port']: df[col] = df[col].astype('category') - df.to_hdf('globeop.hdf', 'valuation_report', format='table', complib='blosc') + df.columns = [c.lower() for c in df.columns] + + df.to_sql('val_reports', dbengine('dawndb'), if_exists='append', index=False) def pnl_reports(): df = {} @@ -48,12 +56,21 @@ def pnl_reports(): date = pd.Timestamp(f.split('/')[4]) date = date - bus_day df[date] = pd.read_csv(f) + df[date]['row'] = df[date].index df = pd.concat(df, names=['date', 'to_drop']) df.reset_index(level='to_drop', drop=True, inplace=True) - df.Strat = df.Strat.str.replace("^SERCGMAST__M_", "", 1) + df.Strat = df.Strat.str.replace("^(SERCGMAST__){1,2}(M_|SER_)?", "", 1) + df.Port = df.Port.str.replace("^(SERCGMAST__){1,2}(SERG__|SERG_)?", "", 1) for col in ['Fund', 'Strat', 'Port', 'LongShortIndicator', 'InvCcy']: df[col] = df[col].astype('category') - df.to_hdf('globeop.hdf', 'pnl', format='table', complib='blosc') + + ## cleanups + df = df.reset_index() + df.columns = [c.lower() for c in df.columns] + df['longshortindicator'] = df['longshortindicator'].str.strip() + df.columns = [c.replace(" ", "") for c in df.columns] + + df.to_sql('pnl_reports', dbengine('dawndb'), if_exists='append', index=False) def monthly_pnl_bycusip(df, strats): df = df[(df.Strat.isin(strats)) & (df.CustAcctName=='V0NSCLMAMB')] @@ -80,3 +97,4 @@ if __name__=='__main__': # clo_monthly_pnl = clo.groupby(level=0).sum()['MTD TotalBookPL'].resample('M').last() # clo.groupby(level=0).sum()['2015-12-01':'2015-12-31'] + df_val.set_index(['custacctname', 'periodenddate', 'invid', 'strat']) |