CODEWITHSUNDEEP
pythonpandasdataframe
lakshmen
lakshmen

Reputation: 29064

Multiply two data frames Python

I have two dataframes as such:

Margins:

margins = pd.DataFrame([{'balance_date': '2019-06-24', 'opp_pty_cd': 'GOODM','cur': 'KRW', 'amt':9714190.0,'acct': 30}, {'balance_date': '2019-06-24', 'opp_pty_cd': 'KIS','cur': 'KRW', 'amt':1858386321.,'acct': 30}])

Rate:

    dict={'CME':{u'JPY': 0.525, u'USD': 3.305, u'CNH': 4.805},
    'EUREX':{u'USD': 3.305, u'GBP': 1.545, u'EUR': 0.375},
    'GOODM':{u'KRW': 0.0},
    'HKEX':{u'USD': 3.395, u'HKD': 3.565, u'CNH': 4.895},
    'KIS':{u'KRW': 0.0},
    'NLFX':{u'USD': 3.305},
    'OSE':{u'JPY': 0.615},
    'SGX':{u'JPY': 0.725, u'USD': 2.605, u'SGD': 2.575, u'CNH': 5.005, u'CNY': 0.0},
    'TOCN':{u'JPY': 0.525}}
rate=pd.DataFrame.from_dict(dict)

I need to multiply two dataframes such the amt in margins needs to be multiplied with the correct opp_pty_cd and cur.

When I tried this:

margins['amt'] = margins['amt']* rate[margins['opp_pty_cd']][KIS_margins['cur']]

I get this error:

KeyError: "[u'KRW' u'KRW'] not in index"

Upvotes: 1

Views: 65

Answers (1)

jezrael
jezrael

Reputation: 862631

I think you need first DataFrame.reindex for add missing index and columns values (values are NaNs), so possible use DataFrame.lookup:

rate1 = rate.reindex(index=margins['cur'].unique(), columns=margins['opp_pty_cd'].unique())
margins['amt'] = margins['amt'] * rate1.lookup(margins['cur'], margins['opp_pty_cd'])
print (margins)
   acct  amt balance_date  cur opp_pty_cd
0    30  0.0   2019-06-24  KRW      GOODM
1    30  0.0   2019-06-24  KRW        KIS

Upvotes: 1

Related Questions