Can only compare identically-labeled Series objects Pandas Python

Question

I am trying to convert certain values in a dataframe to USD dollar. Hence i have an dataframe for currency conversions and another is the data.

Currency dataframe:

Identifier  Price
USDJPY  110.42
USDHKD  7.8483
USDCNH  6.7837
USDUSD  1.0

The data looks like this:

    date    acct    type    currency    isM pbc spanReq exchange
    20190215    20  S   JPY INIT    CORE    14872000.0  SGX
    20190215    40  S   USD INIT    CORE    2987340.93  SGX
    20190215    60  S   USD INIT    CORE    2030260.39  SGX
    20190215    70  S   JPY INIT    CORE    57172391.0  SGX
    20190215    20  S   JPY INIT    CORE    106516141.0 OSE
    20190215    70  S   JPY INIT    CORE    1800000.0   OSE
    20190215    10  S   CNH INIT    CORE    0.0 HKEX
    20190215    40  S   HKD INIT    CORE    36071131.4  HKEX
    20190215    60  S   HKD INIT    CORE    3459377.95  HKEX
    20190215    70  S   HKD INIT    CORE    81300.0 HKEX
    20190215    80  S   HKD INIT    CORE    23698214.0  HKEX
    20190215    10  S   USD INIT    CORE    1728005.0   CME
    20190215    20  S   USD INIT    CORE    83671.0 CME
    20190215    30  S   USD INIT    CORE    6237.0  CME
    20190215    40  S   USD INIT    CORE    857120.0    CME
    20190215    60  S   USD INIT    CORE    2803385.0   CME
    20190215    70  S   USD INIT    CORE    9007666.0   CME
    20190215    90  S   USD INIT    CORE    119644.0    CME

Expected Output:

date    acct    type    currency    isM pbc spanReq exchange
20190215    20  S   JPY INIT    CORE    134551.70541934317  SGX
20190215    40  S   USD INIT    CORE    2987340.93  SGX
20190215    60  S   USD INIT    CORE    2030260.39  SGX
20190215    70  S   JPY INIT    CORE    517256.77191712655  SGX
20190215    10  S   CNH INIT    CORE    0.0 HKEX
20190215    40  S   HKD INIT    CORE    4624504.025641026   HKEX
20190215    60  S   HKD INIT    CORE    443509.9935897436   HKEX
20190215    70  S   HKD INIT    CORE    10423.076923076924  HKEX
20190215    80  S   HKD INIT    CORE    3038232.564102564   HKEX
20190215    20  S   JPY INIT    CORE    963685.343345698    OSE
20190215    70  S   JPY INIT    CORE    16285.171446666063  OSE
20190215    10  S   USD INIT    CORE    1728005.0   CME
20190215    20  S   USD INIT    CORE    83671.0 CME
20190215    30  S   USD INIT    CORE    6237.0  CME
20190215    40  S   USD INIT    CORE    857120.0    CME
20190215    60  S   USD INIT    CORE    2803385.0   CME
20190215    70  S   USD INIT    CORE    9007666.0   CME
20190215    90  S   USD INIT    CORE    119644.0    CME

My code looks like this:

initial_margin_data['spanReq'] =  initial_margin_data['spanReq'].astype(float)/d.loc[d['Identifier'] == initial_margin_data['currency'], 'Price']

The second line gives me this error:

Can only compare identically-labeled Series objects

Need some guidance on this.

Thanks for the data, but is there any way you can print the data without the ascii borders? Makes it very tough to parse your data through clipboard that way. — cs95
– cs95, Commented Feb 19, 2019 at 1:58
@coldspeed dont the changes to the tables. any idea to solve it? — lakshmen
– lakshmen, Commented Feb 19, 2019 at 2:12
The first issue is that you are comparing differently sized objects with non-aligned indexes. What is your expected output? — cs95
– cs95, Commented Feb 19, 2019 at 2:13
the expected output should be initial margin data with the spanReq be changed according to the currency — lakshmen
– lakshmen, Commented Feb 19, 2019 at 2:16

cs95 · Accepted Answer · 2019-02-19 02:35:01Z

2

You can initialise a currency mapping like so,

m = dict(zip(currency['Identifier'].str[-3:], currency['Price']))
m
# {'CNH': 6.7837, 'HKD': 7.8483, 'JPY': 110.42, 'USD': 1.0}

Now, you can map your currencies to exchange rates and divide:

df['spanReq'] /= df['currency'].map(m)

answered Feb 19, 2019 at 2:35

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bravosierra99 · Accepted Answer · 2019-02-19 02:12:43Z

This part of your code is not going to do what you think it's going to do.

d.loc[d['Identifier'] == initial_margin_data['currency'], 'Price']

Because both sides of your "==" are series, it's trying to compare if they are equal. And it cannot not. You are hoping that initial_margin_data['currency'] is going to give you the type of currency for a given line. What you probably need to do is some form of apply. It might be easier to have d actually be a dictionary for lookups.

I mocked up some code with a toy example that might explain better.

import pandas as pd
d = {'A': 1, 'B' : 2}
df = pd.DataFrame([[20, "A"],[30,"B"]])
df.apply(lambda x: x[0] / d[x[1]], axis=1)

#results
0    20.0
1    15.0
dtype: float64

I'm using apply to iterate over each row (because axis=1) and each row is passed to the lambda function. In my case I'm using 0 to index and get the account value, and 1 to index and get the letter (which represents your Identifier). I then use that to lookup the "price" in my dictionary "d". I then do the math that you want to do.

You could also index these columns by name, my toy example simply doesn't have column names.

also you might find this function handy for creating your dictionary pandas.DataFrame.to_dict

Collectives™ on Stack Overflow

Can only compare identically-labeled Series objects Pandas Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related