diff options
| -rw-r--r-- | python/bbg_newids.py | 107 |
1 files changed, 57 insertions, 50 deletions
diff --git a/python/bbg_newids.py b/python/bbg_newids.py index b0b1cd40..9f670eab 100644 --- a/python/bbg_newids.py +++ b/python/bbg_newids.py @@ -1,13 +1,46 @@ -from sqlalchemy import create_engine +from sqlalchemy import create_engine, MetaData import pandas as pd from db import conn import numpy as np from bbg_helpers import init_bbg_session, retreive_data, process_msgs +from psycopg2 import IntegrityError -engine = create_engine('postgresql://et_user@debian/ET') +engine = create_engine('postgresql://et_user@debian/ET', echo=True) +meta = MetaData(bind=engine) +meta.reflect(only = ['bloomberg_corp_ref', 'bloomberg_corp', 'deal_indicative']) -session = init_bbg_session('192.168.1.108', 8194) +deal_indicative = meta.tables['deal_indicative'] +bloomberg_corp_ref = meta.tables['bloomberg_corp_ref'] +bloomberg_corp = meta.tables['bloomberg_corp'] + +s = select([Column('cusip'), Column('loanxid')]).\ + select_from(func.et_latestdealinfo(bindparam('dealname'))).where(Column('cusip')!=None) + +# we build a dictionary with cusips as keys and values is a set of lonxids mapped to this cusip +result = select([deal_indicative.c.dealname]).execute() +d = defaultdict(set) +for r in result: + result2 = engine.execute(s, dealname = r.dealname) + for t in result2: + d[t.cusip].add(t.loanxid) + +clean_mapping = ((cusip, loanxid - {None}) for cusip, loanxid in d.items()) + +def f(s): + if s: + return "{%s}" % ",".join(s) + else: + return "" + +clean_mapping = {cusip: f(loanxid) for cusip, loanxid in clean_mapping} +mapping = pd.DataFrame.from_dict(clean_mapping, orient='index') +mapping.index.name = 'cusip' +mapping.columns = ['loanxid'] +currentdata = pd.read_sql_query("select id_bb_unique, cusip from bloomberg_corp_ref", + engine, index_col='cusip') +mapping = mapping.ix[mapping.index.difference(currentdata.index)] +session = init_bbg_session('192.168.1.108', 8194) all_fields = ["ISSUE_DT", "LN_ISSUE_STATUS", "ID_CUSIP", "ID_BB_UNIQUE", "SECURITY_TYP", "AMT_OUTSTANDING", "PX_LAST","LAST_UPDATE_DT", "ISSUER", "MATURITY","CPN","CPN_TYP", "CPN_FREQ","FLT_SPREAD", @@ -15,57 +48,31 @@ all_fields = ["ISSUE_DT", "LN_ISSUE_STATUS", "ID_CUSIP", "ID_BB_UNIQUE", "LN_COVENANT_LITE","SECOND_LIEN_INDICATOR","DEFAULTED", "DEFAULT_DATE", "CALLED", "CALLED_DT", "PRICING_SOURCE"] -# append securities to request -currentdata = pd.read_sql_query("select id_bb_unique, substring(id_bb_unique from 3) as cusip " \ - "from bloomberg_corp_ref", engine, index_col='cusip') - -mapping = pd.read_csv("/home/share/CorpCDOs/data/bbg_loanxid.csv", index_col=0) -mapping = mapping.ix[mapping.index.difference(currentdata.index)] - securities = ["{0} Corp".format(cusip) for cusip in mapping.index] data = retreive_data(session, securities, all_fields) df = process_msgs(data, all_fields) df.security = df.security.str.slice(0,9) df.set_index('security', inplace=True) -df['loanxid'] = mapping['loanxid'] df.reset_index(inplace=True) +df['loanxid'] = mapping.loanxid +df.dropna(subset=['ID_BB_UNIQUE'], inplace=True) +df.loc[df.LN_TRANCHE_SIZE.isnull(),'LN_TRANCHE_SIZE'] = df[df.LN_TRANCHE_SIZE.isnull()].AMT_ISSUED.values +df.set_index('ID_BB_UNIQUE', inplace=True, verify_integrity=True) +currentdata.set_index('id_bb_unique', inplace=True) +df = df.ix[df.index.difference(currentdata.index)] +df.drop_duplicates(subset='ID_BB_UNIQUE', inplace=True) +sql_colnames = [c.name for c in bloomberg_corp_ref.columns] +to_insert = df[['ID_BB_UNIQUE', 'ID_CUSIP', 'ISSUER', 'MATURITY', 'CPN', 'CPN_TYP', + 'CPN_FREQ', 'FLT_SPREAD', 'LIBOR_FLOOR', 'LN_TRANCHE_SIZE', 'LN_COVENANT_LITE', + 'SECOND_LIEN_INDICATOR', 'SECURITY_TYP', 'ISSUE_DT', 'DEFAULTED', + 'DEFAULT_DATE', 'CALLED', 'CALLED_DT', 'LN_ISSUE_STATUS', 'loanxid']] +to_insert.columns = sql_colnames +to_insert.to_sql("bloomberg_corp_ref", engine, if_exists='append', index=False) -sqlstr = "INSERT INTO bloomberg_corp_ref VALUES({0})".format(",".join(["%s"]*20)) -with conn.cursor() as c: - for i in range(df.shape[0]): - issue_size = df.iloc[i]['LN_TRANCHE_SIZE'] - if np.isnan(issue_size): - issue_size = df.iloc[i]['AMT_ISSUED'] - if np.isnan(issue_size): - issue_size = None - try: - c.execute(sqlstr, - (df.iloc[i]['ID_BB_UNIQUE'], df.iloc[i]['ID_CUSIP'], df.iloc[i]['ISSUER'], - df.iloc[i]['MATURITY'], df.iloc[i]['CPN'], df.iloc[i]['CPN_TYP'], - df.iloc[i]['CPN_FREQ'], df.iloc[i]['FLT_SPREAD'], df.iloc[i]['LIBOR_FLOOR'], - issue_size, df.iloc[i]["LN_COVENANT_LITE"], df.iloc[i]["SECOND_LIEN_INDICATOR"], - df.iloc[i]["SECURITY_TYP"], df.iloc[i]["ISSUE_DT"], df.iloc[i]["DEFAULTED"], - df.iloc[i]["DEFAULT_DATE"], df.iloc[i]["CALLED"], df.iloc[i]["CALLED_DT"], - df.iloc[i]["LN_ISSUE_STATUS"], [df.iloc[i]['loanxid']])) - conn.commit() - except IntegrityError: - conn.rollback() - -sqlstr = "INSERT INTO bloomberg_corp VALUES(%s, %s, %s, %s, %s, %s)" -with conn.cursor() as c: - for i in range(df.shape[0]): - margin = df.iloc[i]['LN_CURRENT_MARGIN'] - if np.isnan(margin): - margin = None - amt_outstanding = df.iloc[i]['AMT_OUTSTANDING'] - if np.isnan(amt_outstanding): - amt_outstanding = None - try: - c.execute(sqlstr, (df.iloc[i]['ID_BB_UNIQUE'], df.iloc[i]['LAST_UPDATE_DT'], - df.iloc[i]['PX_LAST'], margin, amt_outstanding, - df.iloc[i]['PRICING_SOURCE'])) - conn.commit() - except IntegrityError: - conn.rollback() - -conn.close() +to_insert2 = df[['ID_BB_UNIQUE','LAST_UPDATE_DT','PX_LAST','LN_CURRENT_MARGIN', + 'AMT_OUTSTANDING','PRICING_SOURCE']] +sql_colnames = [c.name for c in bloomberg_corp.columns] +to_insert2.columns = sql_colnames +to_insert2.dropna(subset=['pricingdate'], inplace=True) +to_insert2.set_index(['id_bb_unique', 'pricingdate'], inplace=True) +to_insert2.to_sql("bloomberg_corp", engine, if_exists='append', index=True) |