Use lookup values to create new pandas dataframe

Question

I have a pandas DataFrame containing stocks that looks like this:

    Name                Ticker        Currency
0   TOTAL SA            FP FP         EUR
1   APPLE INC           AAPL US       USD
2   ALPHABET INC-CL A   GOOGL US      USD

I have another containing prices:

             AAPL US   FP FP    GOOGL US
Date
2018-08-01   200.0000  55.0000  1235.0000     
2018-08-02   201.0000  56.0000  1236.0000
2018-08-03   202.0000  57.0000  1237.0000

and another containing FXs:

             EUR        USD
Date
2018-08-01   1.1300     1.3000  
2018-08-02   1.1400     1.3100
2018-08-03   1.1500     1.3200

I want to create a new DataFrame with the same columns as the price one containing FX rates. I want to do that by using the currency column of the stocks DataFrame as a lookup and selecting the correct values from the FX DataFrame so I get something like this:

             AAPL US   FP FP    GOOGL US
Date
2018-08-01   1.3000    1.1300   1.3000    
2018-08-02   1.3100    1.1400   1.3100
2018-08-03   1.3200    1.1500   1.3200

I cannot seem to find the right function that allows me to do this.

jpp · Accepted Answer · 2018-10-14 17:03:30Z

1

Let's say you begin with 3 dataframes: stocks, prices, and fx. You can then use pd.DataFrame.loc with a series mapping:-

# create mapping from ticker to currency
s = stocks.set_index('Ticker')['Currency']

# reindex fx with data from prices, then rename columns
res = fx.loc[prices.index, prices.columns.map(s)]\
        .set_axis(prices.columns, axis=1, inplace=False)

print(res)

            AAPL US  FP FP  GOOGL US
2018-08-01     1.30   1.13      1.30
2018-08-02     1.31   1.14      1.31
2018-08-03     1.32   1.15      1.32

answered Oct 14, 2018 at 17:03

jpp

166k37 gold badges301 silver badges363 bronze badges

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

1 Comment

mr niblets Over a year ago

This did the trick. However as a side note, it is worth noting that I received the following warning when running this. 'FutureWarning: Passing list-likes to .loc or [] with any missing label will raise KeyError in the future, you can use .reindex() as an alternative'. I checked and all labels matched so not sure why this warning appeared. I marked this as the correct answer as it was considerably quicker than the alternative solution.

rafaelc · Accepted Answer · 2018-10-14 16:57:14Z

1

Use update

final_df = df_prices.copy()
cols = final_df.columns

final_df.columns = final_df.columns.map(df_stocks[['Ticker', 'Currency']].set_index('Ticker').to_dict()['Currency'])

final_df.update(df_fx)
final_df.columns = cols

Outputs

>>> print(final_df)

           AAPL US  FP FP   GOOGL US
Date            
2018-08-01  1.30    1.13    1.30
2018-08-02  1.31    1.14    1.31
2018-08-03  1.32    1.15    1.32

answered Oct 14, 2018 at 16:57

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

1 Comment

mr niblets Over a year ago

This solution worked and the function seems to be for the specific reason, but was just slower than the alternative suggestion on my dataset - 6s vs 55ms

Collectives™ on Stack Overflow

Use lookup values to create new pandas dataframe

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related