CODEWITHSUNDEEP
pythonpandasdataframe
mr niblets
mr niblets

Reputation: 63

Use lookup values to create new pandas dataframe

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.

Upvotes: 2

Views: 57

Answers (2)

jpp
jpp

Reputation: 164653

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

Upvotes: 1

rafaelc
rafaelc

Reputation: 59274

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

Upvotes: 1

Related Questions