diff options
| -rw-r--r-- | python/database_consistency.py | 13 |
1 files changed, 9 insertions, 4 deletions
diff --git a/python/database_consistency.py b/python/database_consistency.py index b05e9d81..df9dad32 100644 --- a/python/database_consistency.py +++ b/python/database_consistency.py @@ -11,8 +11,13 @@ if __name__=="__main__": data2 = pd.read_sql("""SELECT dealname, sum(currentbalance) AS bal, updatedate AS date FROM et_collateral GROUP BY dealname, updatedate""", engine, parse_dates=["date"], index_col=["dealname", "date"]) - data3 = pd.read_sql("""SELECT dealname, sum(curr_balance) AS bal, updatedate AS date - FROM cusip_universe GROUP BY dealname, updatedate""", engine, index_col=["dealname", "date"]) + data3 = pd.read_sql("""with cte AS (SELECT distinct on (dealname, updatedate, tranche) +dealname, updatedate, curr_balance FROM cusip_universe +WHERE type not in ('MODELING', 'Combination', 'Unspecified', 'JUN_FIX_IO') + ORDER BY dealname, updatedate, tranche) +SELECT dealname, updatedate AS date, SUM(curr_balance) AS bal FROM cte + GROUP BY dealname, updatedate ORDER BY dealname, updatedate""", engine, + parse_dates=["date"], index_col=["dealname", "date"]) data12 = pd.merge(data1, data2, left_index=True, right_index=True) - #data13 = pd.merge(data1bis, data3, left_index=True, right_index=True) - print(data12.query('bal_x-bal_y>1|bal_x-bal_y<-1').sortlevel(level=1)) + data13 = pd.merge(data1bis, data3, left_index=True, right_index=True) + print(data13.query('bal_x-bal_y>1|bal_x-bal_y<-1').sortlevel(level=1)) |