CODEWITHSUNDEEP
pythonpandasdataframe
smallcat31
smallcat31

Reputation: 344

Correlation between two dataframes column with matched headers

I have two dataframes from excels which look like the below. The first dataframe has a multi-index header.

I am trying to find the correlation between each column in the dataframe with the corresponding dataframe based on the currency (i.e KRW, THB, USD, INR). At the moment, I am doing a loop to iterate through each column, matching by index and corresponding header before finding the correlation. 

for stock_name in index_data.columns.get_level_values(0):
    stock_prices    = index_data.xs(stock_name, level=0, axis=1)
    stock_prices    = stock_prices.dropna()
    fx              = currency_data[stock_prices.columns.get_level_values(1).values[0]]
    fx              = fx[fx.index.isin(stock_prices.index)]

    merged_df = pd.merge(stock_prices, fx, left_index=True, right_index=True)
    merged_df[0].corr(merged_df[1])

Is there a more panda-ish way of doing this?

index_data dataframe

fx dataframe

Upvotes: 1

Views: 392

Answers (1)

Chris
Chris

Reputation: 1367

So you wish to find the correlation between the stock price and its related currency. (Or stock price correlation to all currencies?)

# dummy data
date_range = pd.date_range('2019-02-01', '2019-03-01', freq='D')

stock_prices = pd.DataFrame(
    np.random.randint(1, 20, (date_range.shape[0], 4)),
    index=date_range,
    columns=[['BYZ6DH', 'BLZGSL', 'MBT', 'BAP'],
            ['KRW', 'THB', 'USD', 'USD']])
fx = pd.DataFrame(np.random.randint(1, 20, (date_range.shape[0], 3)),
                  index=date_range, columns=['KRW', 'THB', 'USD'])

This is what it looks like, calculating correlations on this data shouldn't make much sense since it is random.

>>> print(stock_prices.head())
           BYZ6DH BLZGSL MBT BAP
              KRW    THB USD USD
2019-02-01     15     10  19  19
2019-02-02      5      9  19   5
2019-02-03     19      7  18  10
2019-02-04      1      6   7  18
2019-02-05     11     17   6   7

>>> print(fx.head())
            KRW  THB  USD
2019-02-01   15   11   10
2019-02-02    6    5    3
2019-02-03   13    1    3
2019-02-04   19    8   14
2019-02-05    6   13    2

Use apply to calculate the correlation between columns with the same currency.

def f(x, fx):
    correlation = x.corr(fx[x.name[1]])
    return correlation

correlation = stock_prices.apply(f, args=(fx,), axis=0)

>>> print(correlation)
BYZ6DH  KRW   -0.247529
BLZGSL  THB    0.043084
MBT     USD   -0.471750
BAP     USD    0.314969
dtype: float64

Upvotes: 1

Related Questions