1 files changed, 30 insertions, 25 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 = {}