diff options
| -rw-r--r-- | python/load_globeop_report.py | 55 | ||||
| -rw-r--r-- | python/task_server/globeop.py | 5 |
2 files changed, 33 insertions, 27 deletions
diff --git a/python/load_globeop_report.py b/python/load_globeop_report.py index d45c3f85..5c0ccffc 100644 --- a/python/load_globeop_report.py +++ b/python/load_globeop_report.py @@ -66,40 +66,45 @@ def pnl_reports(): df = pd.concat(df, names=['date', 'row']).reset_index() df.to_sql('pnl_reports', dbengine('dawndb'), if_exists='append', index=False) -def read_cds_report(f, old_report=False): +def read_cds_report(f): df = pd.read_csv(f) - for col in df.columns: - vc = len(df[col].value_counts()) - if vc == 0: - del df[col] - continue - if 'Contractual Definition' in df: - contract = df['Contractual Definition'] - contract = contract.where(contract.isin(['ISDA2014', 'ISDA2003Cred']), 'ISDA2014') - df['Contractual Definition'] = contract - df = df.drop(['Bloomberg Yellow key', 'Created User', 'Last Modified User', - 'Last Modified Date', 'Fund Long Name', 'Instrument Sub Type', - 'Netting Id', 'Client', 'Trade Status', 'Position Status', - 'Clearing Broker', 'Settle Mode', 'Off Price', 'On Price', - 'Price Ccy', 'VAT', 'SEC Fee', 'Clearing Fee', - 'Remaining Notional', 'Trading Notional', 'BBGID'], + df2 = pd.read_csv(f.parent / "All_Report.csv") + def drop_zero_count(df): + for col in df: + vc = len(df[col].value_counts()) + if vc == 0: + del df[col] + continue + drop_zero_count(df) + drop_zero_count(df2) + contract = df['Contractual Definition'] + contract = contract.where(contract.isin(['ISDA2014', 'ISDA2003Cred']), 'ISDA2014') + df['Contractual Definition'] = contract + to_drop = ['Bloomberg Yellow key', 'Created User', 'Last Modified User', + 'Last Modified Date', 'Fund Long Name', 'Instrument Sub Type', + 'Netting Id', 'Client', 'Trade Status', 'Position Status', + 'Clearing Broker', 'Settle Mode', 'Off Price', 'On Price', + 'Price Ccy', 'VAT', 'SEC Fee', 'Clearing Fee', + 'Trading Notional', 'BBGID'] + df = df.drop(to_drop, axis=1, errors='ignore') + df2 = df2.drop(to_drop, + axis=1, errors='ignore') df.columns = df.columns.str.lower().str.replace(" ", "_") - if old_report: - df.calendar = df.calendar.str.replace(" ", "") - df = df.rename(columns={'direction': 'buy/sell'}) - df.roll_convention = df.roll_convention.str.title() + df2.columns = df2.columns.str.lower().str.replace(" ", "_") + df.calendar = df.calendar.str.replace(" ", "") + df = df.rename(columns={'direction': 'buy/sell'}) + df.roll_convention = df.roll_convention.str.title() df = df[df.strategy != 'SER_TEST'] df.loc[df.strategy == 'SERCGMAST__MBSCDS', 'strategy'] = 'MBSCDS' df.strategy = df.strategy.str.replace("SER_", "") df['buy/sell'] = df['buy/sell'].astype('category') df['buy/sell'].cat.categories = ['Buyer', 'Seller'] + del df['independent_%'] + df2 = df2.rename(columns={'independent_%': 'independent_perc'}) df.prime_broker = df.prime_broker.where(df.prime_broker != 'NONE') - df.loc[df.executing_broker.isnull(),'executing_broker'] = df[df.executing_broker.isnull()].counterparty - del df['counterparty'] - df = df.rename(columns={'executing_broker': 'counterparty', - 'independent_%': 'independent_perc'}) - return df + return df.set_index('gtid').join(df2.set_index('gtid')[ + df2.columns.difference(df.columns)]).reset_index() def cds_reports(): df = {} diff --git a/python/task_server/globeop.py b/python/task_server/globeop.py index 7a26f170..e95823c8 100644 --- a/python/task_server/globeop.py +++ b/python/task_server/globeop.py @@ -65,10 +65,11 @@ def get_gpg(): return gpg def convert_to_csv(f): + mapping = (('Credit Default Swap', 'CDS'), ('Swaption', 'Swaption'), ('ALL', 'All')) if f.exists(): - for sheet in ["Credit Default Swap", "Swaption"]: + for sheet, name in mapping: df = pd.read_excel(f, sheet_name=sheet, skiprows=[0, 1, 2, 3]) - df.to_csv(f.parent / f"{sheet}.csv", index=False) + df.to_csv(f.parent / f"{name}_Report.csv", index=False) f.unlink() def download_data(workdate): |