Column-wise Correlation Between All Pairs Of Columns Of Two Data Frame

Hi so I have created a function to check the correlation between 2 variables, anyone knows how can I create a new data frame from this? In [1]:from scipy.stats import pearsonr for

Solution 1:

I would avoid using two for loops. Depending on the size of your dataset this will be very slow.

Pandas provides a correlation function with might come in hand here:

import pandas as pd

df = pd.DataFrame({'A': range(4), 'B': [2*i for i inrange(4)]})

using corr() will give you the pairwise correlations then and returns a new dataframe as well:

df.corr()

For more infos you can check the manual: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.corr.html

Solution 2:

You can just do the following.

df = pd.DataFrame(index=X.columns, columns=Y.columns)

#In your loop
df[colY][colX] = corr

Your loop would then be

for colY in Y.columns:
    for colX in X.columns:
    #print('Pearson Correlation')
        corr, _ = pearsonr(numerical_cols_target[colX], numerical_cols_target[colY])
        alpha = 0.05print('Pearson Correlation', (alpha, corr))
        df[colY][colX] = corr
        if corr <= alpha:
            print(colX +' and ' +colY+ ' two ariables are not correlated ')
        else:
            print(colX +' and ' +colY+ ' two variables are highly correlated ')
        print('\n')
    print('\n')

Solution 3:

I think you are looking for this: This will get a column-wise correlation of every two pairs of columns between X and Y dataframes and create another dataframe that keeps all the correlations and whether they pass a threshold alpha: This assumes Y has less or equal number of columns as X. If not simply switch X and Y places:

import collections
corr_df = pd.DataFrame(columns=['col_X', 'col_Y', 'corr', 'is_correlated'])
d = collections.deque(X.columns)
Y_cols = Y.columns
alpha = 0.05for i in range(len(d)):
  d.rotate(i)
  X = X[d]
  corr = Y.corrwith(X, axis=0)
  corr_df = corr_df.append(pd.DataFrame({'col_X':list(d)[:len(Y_cols)], 'col_Y':Y.columns, 'corr':corr[:len(Y_cols)], 'is_correlated':corr[:len(Y_cols)]>alpha}))
print(corr_df.reset_index())

sample input and output:

X:
   A  B   C
02210140228013008Y:
   B   C
0210102201308


correlation(X, Y):

  col_X col_Y  corr is_correlated
0     A     B   1.0True1     B     C   1.0True2     C     B   1.0True3     A     C   1.0True4     A     B   1.0True5     B     C   1.0True