Corrwith when "other" has different columns

[olgabot]

It would be great if corrwith could calculate correlations between dataframes that have different column names, but the same index.

For example, take the two (10, 5) df1 and df2 dataframes below.

import pandas as pd
import numpy as np
import string

nrow = 10
ncol = 5

axis = 0

index = list(string.ascii_lowercase[:nrow])
columns = list(string.ascii_uppercase[:ncol])

df1 = pd.DataFrame(np.random.randn(nrow, ncol), index=index, columns=columns)
df2 = pd.DataFrame(np.random.randn(nrow, ncol), index=index)

df1 and df2 have different columns, and I'd like to create a 5x5 matrix of the correlations of their columns, on the values in each row.

I've implemented a stopgap measure here: https://github.com/YeoLab/flotilla/blob/d9e53c219320c5d5dbbbfa41769abb2ab6f25574/flotilla/compute/generic.py#L429

Is this a planned feature for future releases?

Probably also related to the method issue: #9490

[shoyer]

I don't think this feature is currently planned, but we're always open to new contributions!

This certainly seems like a useful feature in some form, but I'm not sure it should be squeezed into corrwith, which currently pairs columns in different dataframes instead of considering the outer product, e.g.,

In [20]: df1.corrwith(df1 ** 1.3)
Out[20]:
A    0.997052
B    0.999656
C    0.994640
D    0.997947
E    0.996514
dtype: float64

This might make more sense as an optional other argument to DataFrame.corr, which already returns a matrix of correlations:

In [21]: df1.corr()
Out[21]:
          A         B         C         D         E
A  1.000000  0.333927  0.174839 -0.202988  0.071950
B  0.333927  1.000000 -0.012221 -0.407869  0.258222
C  0.174839 -0.012221  1.000000 -0.332829 -0.028436
D -0.202988 -0.407869 -0.332829  1.000000  0.434950
E  0.071950  0.258222 -0.028436  0.434950  1.000000

[jreback]

This would be trivial if .set_index() took an axis argument

In [51]: df1.T.corrwith(df2.T.set_index(df1.columns))  
Out[51]: 
a    0.072140
b   -0.025166
c    0.355389
d    0.461372
e    0.051302
f   -0.031054
g    0.761556
h   -0.960473
i    0.858428
j    0.159165
dtype: float64

[shoyer]

@jreback that's not quite what @olgabot is looking for, I think -- she wants a 5x5 matrix of cross correlations, if understand her correctly

[olgabot]

@shoyer that's correct - in this example, I want a 5x5 of the cross correls. Generally speaking, if df1 is (n, m) and df2 is (n, p), then I want a (m, p) matrix of correlations

[olgabot]

that's a solution I've used before, but the issue is that it takes a long
time if the matrices are large (thousands of columns), and I'm not
interested in the df1,df2 correlations, necessarily.

On Mon, Apr 6, 2015 at 4:58 PM jreback notifications@github.com wrote:

In [58]: pd.concat([df1,df2],axis=1).corr()
Out[58]:
A B C D E 0 1 2 3 4
A 1.000000 0.160876 0.368649 0.252098 0.108760 0.566828 -0.466817 -0.314223 -0.131133 0.590715
B 0.160876 1.000000 0.545311 0.056020 -0.460356 -0.091174 0.354608 -0.378924 -0.003633 -0.048977
C 0.368649 0.545311 1.000000 -0.451701 -0.165686 -0.036762 0.253964 0.036151 0.125651 0.145658
D 0.252098 0.056020 -0.451701 1.000000 -0.257068 0.735342 -0.185773 -0.589945 0.138441 0.006919
E 0.108760 -0.460356 -0.165686 -0.257068 1.000000 0.035644 -0.570018 -0.012733 -0.293044 0.464915
0 0.566828 -0.091174 -0.036762 0.735342 0.035644 1.000000 -0.188574 -0.525456 0.359605 0.347547
1 -0.466817 0.354608 0.253964 -0.185773 -0.570018 -0.188574 1.000000 -0.056621 0.627198 -0.289038
2 -0.314223 -0.378924 0.036151 -0.589945 -0.012733 -0.525456 -0.056621 1.000000 0.041765 -0.069550
3 -0.131133 -0.003633 0.125651 0.138441 -0.293044 0.359605 0.627198 0.041765 1.000000 0.287847
4 0.590715 -0.048977 0.145658 0.006919 0.464915 0.347547 -0.289038 -0.069550 0.287847 1.000000

—
Reply to this email directly or view it on GitHub
#9823 (comment).

[pstjohn]

This is an old issue, but it doesn't seem like pandas has this functionality yet. here's a code sample that works for me, when left and right are two dataframes with the same columns but different indices.

def corrwith(left, right):
    # demeaned data
    left_tiled = np.repeat(left.values[:, np.newaxis, :], right.shape[0], 1)
    right_tiled = np.repeat(right.values[np.newaxis, :, :], left.shape[0], 0)

    ldem = left_tiled - left_tiled.mean(-1)[:, :, np.newaxis]
    rdem = right_tiled - right_tiled.mean(-1)[:, :, np.newaxis]

    num = (ldem * rdem).sum(-1)

    dom = np.sqrt((ldem**2).sum(-1)) * np.sqrt((rdem**2).sum(-1))
    correl = num / dom

    return pd.DataFrame(correl, index=left.index, columns=right.index)