1 files changed, 10 insertions, 15 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py
index bf6159be..feaeb18c 100644
--- a/python/globeop_reports.py
+++ b/python/globeop_reports.py
@@ -169,7 +169,8 @@ def get_rmbs_pos_df(date=None):
 
     return calc_df.reset_index().set_index('timestamp').sort_index()
 
-def get_clo_pos_df(date = None):
+
+def get_clo_pos_df(date=None):
 
     etengine = dbengine('etdb')
     dawnengine = dbengine('dawndb')
@@ -185,23 +186,17 @@ def get_clo_pos_df(date = None):
             (df['invid'].str.len() >= 9)]
     df = df[['endbookmv', 'endlocalmarketprice', 'identifier']]
     sql_string = "select distinct cusip, identifier from bonds where asset_class = 'CLO'"
-    cusip_map = pd.read_sql_query(sql_string, dawnengine)
-
+    cusip_map = {r['identifier']: r['cusip'] for r in dawnengine.execute(sql_string)}
+    df['cusip'] = df['identifier'].replace(cusip_map)
     r = {}
     for d, g in df.groupby(pd.Grouper(freq='M')):
-        cusip_list = g.loc[g.index[-1]].identifier
-        if isinstance(cusip_list, str):
-            pos = pd.merge(pd.DataFrame(g.loc[g.index[-1]]).T, cusip_map, on='identifier')
-        else:
-            pos = pd.merge(g.loc[g.index[-1]], cusip_map, on='identifier')
-        cusip_list = pos.cusip.tolist()
-        placeholders = ",".join(["%s"] * (1+len(cusip_list)))
+        cusips = g.loc[[g.index[-1]], 'cusip']
+        placeholders = ",".join(["%s"] * (1 + len(cusips)))
         sql_string = f"SELECT * FROM historical_cusip_risk({placeholders})"
-        model = pd.read_sql_query(sql_string, etengine, parse_dates = ['pricingdate'],
-                                params=[d.date()] + cusip_list)
-        model = pd.concat([model, pd.Series(cusip_list, name='cusip')], axis=1)
-        model = model.dropna().set_index('pricingdate')
-        r[d] = pd.merge(model, pos, on='cusip').set_index('cusip')
+        model = pd.read_sql_query(sql_string, etengine, parse_dates=['pricingdate'],
+                                  params=(d.date(), *cusips))
+        model.index = cusips
+        r[d] = g.loc[[g.index[-1]]].set_index('cusip').join(model)
     calc_df = pd.concat(r, names=['date', 'cusip'])
     calc_df['hy_equiv'] = calc_df.delta * calc_df.endbookmv
     return calc_df