ENH: Enable DataFrame.corrwith to compute rank correlations
State: closed

This PR is to enable `DataFrame.corrwith` to calculate rank correlations in addition to Pearson's correlation (and should hopefully be fully backwards compatible), as well as clarifying functionality in the docstring .  An error message is generated if the user specifies a form of correlation that isn't implemented.  Tests were added for the new behavior.

closes #22328 
closes #21925

Labels:
name: Enhancement
name: Numeric Operations
description: Arithmetic, Comparison, and Logical operations

Comments:
I'm having some trouble finding a relevant benchmark for `corrwith` (a `grep` on the benchmarks folder returns no hits).  Should one be added to the `Correlation` class in `stats_ops.py`?
Here is one I put together which seems to suggest around a 33-50% speed-up with the new implementation.

```python
import numpy as np
from pandas import DataFrame, Series

np.random.seed(2357)

def corrwith_old(df1, df2, axis=0, drop=False):
    axis = df1._get_axis_number(axis)
    this = df1._get_numeric_data()

    if isinstance(df2, Series):
        return this.apply(df2.corr, axis=axis)

    df2 = df2._get_numeric_data()

    left, right = this.align(df2, join='inner', copy=False)

    # mask missing values
    left = left + right * 0
    right = right + left * 0

    if axis == 1:
        left = left.T
        right = right.T

    # demeaned data
    ldem = left - left.mean()
    rdem = right - right.mean()

    num = (ldem * rdem).sum()
    dom = (left.count() - 1) * left.std() * right.std()

    correl = num / dom

    if not drop:
        raxis = 1 if axis == 0 else 0
        result_index = this._get_axis(raxis).union(df2._get_axis(raxis))
        correl = correl.reindex(result_index)

    return correl


def corrwith_new(df1, df2, axis=0, drop=False, method='pearson'):
    if method not in ['pearson', 'spearman', 'kendall']:
        raise ValueError("method must be either 'pearson', "
                         "'spearman', or 'kendall', '{method}' "
                         "was supplied".format(method=method))

    axis = df1._get_axis_number(axis)
    this = df1._get_numeric_data()

    if isinstance(df2, Series):
        return this.apply(lambda x: df2.corr(x, method=method),
                          axis=axis)

    df2 = df2._get_numeric_data()
    left, right = this.align(df2, join='inner', copy=False)

    if axis == 1:
        left = left.T
        right = right.T

    correl = (left.apply(lambda x:
                         x.corr(right[x.name], method=method)))

    if drop:
        correl.dropna(inplace=True)

    return correl


data1 = DataFrame(np.random.normal(size=(10**6, 10)))
data2 = DataFrame(np.random.normal(size=(10**6, 10)))

%timeit corrwith_old(data1, data2)
%timeit corrwith_new(data1, data2)
```
can u add this as an asv

in particular i suspect you method will slow down when used with a larger number of columns 

so pls show that as well
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/22375?src=pr&el=h1) Report
> Merging [#22375](https://codecov.io/gh/pandas-dev/pandas/pull/22375?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/aeff38d3301d4d31650f6624304259d0dfdbd69b?src=pr&el=desc) will **decrease** coverage by `60.42%`.
> The diff coverage is `0%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/22375/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/22375?src=pr&el=tree)

```diff
@@             Coverage Diff             @@
##           master   #22375       +/-   ##
===========================================
- Coverage   92.31%   31.89%   -60.43%     
===========================================
  Files         166      166               
  Lines       52335    52429       +94     
===========================================
- Hits        48313    16722    -31591     
- Misses       4022    35707    +31685
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `30.28% <0%> (-60.45%)` | :arrow_down: |
| #single | `31.89% <0%> (-11.16%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/22375?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/22375/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `27.82% <0%> (-69.1%)` | :arrow_down: |
| [pandas/io/formats/latex.py](https://codecov.io/gh/pandas-dev/pandas/pull/22375/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvbGF0ZXgucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/core/categorical.py](https://codecov.io/gh/pandas-dev/pandas/pull/22375/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY2F0ZWdvcmljYWwucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/sas/sas\_constants.py](https://codecov.io/gh/pandas-dev/pandas/pull/22375/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3Nhcy9zYXNfY29uc3RhbnRzLnB5) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/tseries/plotting.py](https://codecov.io/gh/pandas-dev/pandas/pull/22375/diff?src=pr&el=tree#diff-cGFuZGFzL3RzZXJpZXMvcGxvdHRpbmcucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/tseries/converter.py](https://codecov.io/gh/pandas-dev/pandas/pull/22375/diff?src=pr&el=tree#diff-cGFuZGFzL3RzZXJpZXMvY29udmVydGVyLnB5) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/formats/html.py](https://codecov.io/gh/pandas-dev/pandas/pull/22375/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvaHRtbC5weQ==) | `0% <0%> (-98.65%)` | :arrow_down: |
| [pandas/core/groupby/categorical.py](https://codecov.io/gh/pandas-dev/pandas/pull/22375/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ3JvdXBieS9jYXRlZ29yaWNhbC5weQ==) | `0% <0%> (-95.46%)` | :arrow_down: |
| [pandas/core/reshape/reshape.py](https://codecov.io/gh/pandas-dev/pandas/pull/22375/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvcmVzaGFwZS9yZXNoYXBlLnB5) | `8.06% <0%> (-91.51%)` | :arrow_down: |
| [pandas/io/sas/sas7bdat.py](https://codecov.io/gh/pandas-dev/pandas/pull/22375/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3Nhcy9zYXM3YmRhdC5weQ==) | `0% <0%> (-91.17%)` | :arrow_down: |
| ... and [127 more](https://codecov.io/gh/pandas-dev/pandas/pull/22375/diff?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/22375?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/22375?src=pr&el=footer). Last update [aeff38d...870d1a3](https://codecov.io/gh/pandas-dev/pandas/pull/22375?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

I've added the new benchmark function `time_corrwith` to `stats_ops.py` within the `Correlation` class.  I'll try to run the benchmark locally (although I botched the setup of `asv` on my main computer and it seems to be broken).
@jreback I think your suspicion may have been accurate regarding the dimension of the inputs.  In any case, for the tests it looks like there's an issue with `scipy` during the travis build.  Am I missing an import somewhere perhaps?
@dsaxton we test quite a few different configurations, not all of which have SciPy installed. If your tests depend on that package you should use the `skip_if_no_scipy` decorate which you'll see used in other places in that same module
@jreback If a certain benchmark is actually part of the PR, what would you say is the most straightforward way to show that there isn't a degradation in performance?  Here the `time_corrwith` function will not work for the current implementation because it uses all three correlation types.  I suppose it could temporarily be modified to look only at Pearson?
You should have for all three. The two new ones will fail since they don't exist on master but that's OK - still sets a baseline going forward and we can still get insights out of the Pearson benchmark
![screen shot 2018-08-16 at 4 31 22 pm](https://user-images.githubusercontent.com/2658661/44236661-643f9680-a173-11e8-941e-9ddb8ffd4447.png)

Here are the results I get running the `stat_ops.Correlation` benchmarks (all `DataFrames` involved are comprised of 1000 x 30 Gaussian arrays).  The Pearson calculation somehow took longer than that of `DataFrame.corr`, even though the latter should actually be doing more work since it computes the full 30 x 30 correlation matrix.  I'll need to run it on the current `corrwith` to see how that performs.
I modified the body of the function to mimic how the current calculation is being done in the special case where `method=pearson` (in the event that there was somehow an overhead associated with repeatedly calling `Series.corr`) and I saw essentially the same performance as before (shown below).  My guess then is that `DataFrame.corr` gets its speed from `nancorr` within `pandas._libs.algos`.  Could using functions from `algos` inside `corrwith` as well make sense?

```python
        if method in ['spearman', 'kendall']:
            correl = (left.apply(lambda x:
                                 x.corr(right[x.name], method=method)))
        else:
            correl = (((right - right.mean()) * (left - left.mean())).mean()
                      / right.std() / left.std())
```

<img width="448" alt="screen shot 2018-08-16 at 6 52 16 pm" src="https://user-images.githubusercontent.com/2658661/44240919-6828e400-a186-11e8-9e75-42799f03273b.png">

don't post pictures of the asv's just copy-paste the text pls.
`asv` results for the `Correlation` class (`DataFrames` are built from `np.random.randn(1000, 30)`):

```
> asv continuous -f 1.1 origin/corrwith-dev HEAD -b stat_ops.Correlation
· Creating environments
· Discovering benchmarks
·· Uninstalling from conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt.
·· Building a511a498 for conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt........................................
·· Installing into conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt..
· Running 6 total benchmarks (2 commits * 1 environments * 3 benchmarks)
[  0.00%] · For pandas commit hash a511a498:
[  0.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[  0.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[  0.00%] ··· Running (stat_ops.Correlation.time_corr--)...
[ 16.67%] ··· stat_ops.Correlation.time_corr                                                                                         ok
[ 16.67%] ··· ========== =============
                method                
              ---------- -------------
               spearman     93.6±2ms  
               kendall      219±4ms   
               pearson    2.14±0.09ms 
              ========== =============

[ 33.33%] ··· stat_ops.Correlation.time_corrwith_cols                                                                                ok
[ 33.33%] ··· ========== ============
                method               
              ---------- ------------
               spearman    51.8±1ms  
               kendall     20.6±1ms  
               pearson    17.0±0.7ms 
              ========== ============

[ 50.00%] ··· stat_ops.Correlation.time_corrwith_rows                                                                                ok
[ 50.00%] ··· ========== ==========
                method             
              ---------- ----------
               spearman   758±30ms 
               kendall    493±7ms  
               pearson    258±7ms  
              ========== ==========

[ 50.00%] · For pandas commit hash a511a498:
[ 50.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[ 50.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 50.00%] ··· Running (stat_ops.Correlation.time_corr--)...
[ 66.67%] ··· stat_ops.Correlation.time_corr                                                                                         ok
[ 66.67%] ··· ========== =============
                method                
              ---------- -------------
               spearman     101±6ms   
               kendall      226±9ms   
               pearson    2.16±0.03ms 
              ========== =============

[ 83.33%] ··· stat_ops.Correlation.time_corrwith_cols                                                                                ok
[ 83.33%] ··· ========== ============
                method               
              ---------- ------------
               spearman    52.1±4ms  
               kendall     21.1±2ms  
               pearson    16.5±0.6ms 
              ========== ============

[100.00%] ··· stat_ops.Correlation.time_corrwith_rows                                                                                ok
[100.00%] ··· ========== ==========
                method             
              ---------- ----------
               spearman   752±20ms 
               kendall    490±4ms  
               pearson    266±3ms  
              ========== ==========


BENCHMARKS NOT SIGNIFICANTLY CHANGED.
> 
```
Duplicate column names doesn't necessarily mean duplicate values.

We handle this correctly on master, so you should be able to add a test on
master that passes, and ensure it still passes on your branch.

On Thu, Aug 23, 2018 at 7:49 AM Daniel Saxton <notifications@github.com>
wrote:

> *@dsaxton* commented on this pull request.
> ------------------------------
>
> In pandas/core/frame.py
> <https://github.com/pandas-dev/pandas/pull/22375#discussion_r212295410>:
>
> >          if axis == 1:
>              left = left.T
>              right = right.T
>
> -        # demeaned data
> -        ldem = left - left.mean()
> -        rdem = right - right.mean()
> -
> -        num = (ldem * rdem).sum()
> -        dom = (left.count() - 1) * left.std() * right.std()
> +        correl = (left.apply(lambda x:
> +                             x.corr(right[x.name], method=method)))
>
> I'll test that out. What would you say the expected behavior should be in
> this case? Would it make sense to first drop duplicate columns from self
> and other?
>
> —
> You are receiving this because your review was requested.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/22375#discussion_r212295410>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/ABQHIoYTVuSsxAk89rEjHQ6FBOjIfF1aks5uTqTAgaJpZM4V_AKR>
> .
>

@TomAugspurger I just pushed some changes that include the above (`zip` / `map` instead of `apply`, more clarity in the `whatsnew`), and also added a relatively simple test to check if the method can handle duplicate columns (below).  I may have mauled the `git` history a bit with all the merging, so a `rebase` might be needed.

Regarding the test, I am creating a `DataFrame` with three columns equal to the integers `0, 1, ... , 9`, then creating another `DataFrame` which adds an additional column of the same form with a duplicate column name.  The result of `corrwith` should be a `Series` of four ones (and should not generate an error).  Please let me know if there's a more straightforward way to simply check that the method doesn't err out.

```python
    def test_corrwith_dup_cols(self):
        # GH 21925
        df1 = pd.DataFrame(np.vstack([np.arange(10)] * 3).T)
        df2 = df1.copy()
        df2 = pd.concat((df2, df2[0]), axis=1)

        result = df1.corrwith(df2).values
        expected = np.ones(4)
        tm.assert_almost_equal(result, expected)
```
can you rebase
Hello @dsaxton! Thanks for updating the PR.







Cheers ! There are no PEP8 issues in this Pull Request. :beers: 

##### Comment last updated on December 28, 2018 at 02:42 Hours UTC
see #22684, a nice alternative
@jreback  Should this PR still be implemented, or does #22684 make it obsolete?  (Since #22684 seems to be changing the implementation of `corr`, does it make sense to extend it to `corrwith`?)
pls look at the contributing docs on running the performance suite

the new impl does not scale with the number of columns - pls show this
I think you're correct that the proposed implementation is not scaling.  Could it be due to `zip` and `map` not being as efficient as pure `numpy`?

```
Daniels-MacBook-Pro:asv_bench dsaxton$ asv continuous -f 1.1 upstream/master corrwith-dev -b stat_ops.Correlation
· Creating environments
· Discovering benchmarks
·· Uninstalling from conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt.
·· Installing into conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt..
· Running 8 total benchmarks (2 commits * 1 environments * 4 benchmarks)
[  0.00%] · For pandas commit hash e19b3f62:
[  0.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[  0.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[  0.00%] ··· Running (stat_ops.Correlation.time_corr--)....
[ 12.50%] ··· stat_ops.Correlation.time_corr                                                                                                                         ok
[ 12.50%] ··· ========== ============
                method               
              ---------- ------------
               spearman    93.5±4ms  
               kendall     206±20ms  
               pearson    1.95±0.2ms 
              ========== ============

[ 25.00%] ··· stat_ops.Correlation.time_corrwith                                                                                                                     ok
[ 25.00%] ··· ========== ==========
                method             
              ---------- ----------
               spearman   39.6±5ms 
               kendall    35.0±2ms 
               pearson    35.9±3ms 
              ========== ==========

[ 37.50%] ··· stat_ops.Correlation.time_corrwith_cols                                                                                                                ok
[ 37.50%] ··· ========== ==========
                method             
              ---------- ----------
               spearman   115±6ms  
               kendall    19.5±1ms 
               pearson    36.4±2ms 
              ========== ==========

[ 50.00%] ··· stat_ops.Correlation.time_corrwith_rows                                                                                                                ok
[ 50.00%] ··· ========== ==========
                method             
              ---------- ----------
               spearman   731±20ms 
               kendall    458±30ms 
               pearson    237±7ms  
              ========== ==========

[ 50.00%] · For pandas commit hash 1a12c41d:
[ 50.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[ 50.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 50.00%] ··· Running (stat_ops.Correlation.time_corr--)....
[ 62.50%] ··· stat_ops.Correlation.time_corr                                                                                                                         ok
[ 62.50%] ··· ========== =============
                method                
              ---------- -------------
               spearman     85.9±5ms  
               kendall      200±4ms   
               pearson    2.03±0.07ms 
              ========== =============

[ 75.00%] ··· stat_ops.Correlation.time_corrwith                                                                                                                     ok
[ 75.00%] ··· ========== ============
                method               
              ---------- ------------
               spearman   19.8±0.5ms 
               kendall    19.4±0.3ms 
               pearson    19.6±0.5ms 
              ========== ============

[ 87.50%] ··· stat_ops.Correlation.time_corrwith_cols                                                                                                            failed
[ 87.50%] ··· ========== ========
                method           
              ---------- --------
               spearman   failed 
               kendall    failed 
               pearson    failed 
              ========== ========

[100.00%] ··· stat_ops.Correlation.time_corrwith_rows                                                                                                            failed
[100.00%] ··· ========== ========
                method           
              ---------- --------
               spearman   failed 
               kendall    failed 
               pearson    failed 
              ========== ========

       before           after         ratio
     [1a12c41d]       [e19b3f62]
+      19.8±0.5ms         39.6±5ms     2.00  stat_ops.Correlation.time_corrwith('spearman')
+      19.6±0.5ms         35.9±3ms     1.83  stat_ops.Correlation.time_corrwith('pearson')
+      19.4±0.3ms         35.0±2ms     1.80  stat_ops.Correlation.time_corrwith('kendall')

SOME BENCHMARKS HAVE CHANGED SIGNIFICANTLY.
```
numpy is using a fully vectorizer soln

your impl is basically linear in the number of columns and thus doesn’t scale 
Got it, I will work on a numpy replacement for map
nothing to do with map

the issue is a column by column impl is simply not scalable, that’s why it was written like this to begin with
Ok, I will likely need to look into how `DataFrame.corr` is able to efficiently implement the other correlation types in that case.
@jreback In the latest commit I'm retaining the current implementation of the Pearson calculation so there should be no difference in performance for this case, and only using the new approach for other correlation types. (While perhaps not perfectly scalable yet, I figure it will be a reasonable first effort.)  Also now that #22684 has been merged a nice side effect of this PR would be that `corrwith` can accept callable methods as well.
can you merge master and see if you can get this to pass
@jreback Just tried a rebase.  IIRC, one of the tests will likely fail because it was testing if `corrwith` works for DataFrames with duplicate columns but it was actually failing on master.
Still failing / merge conflict.

> IIRC, one of the tests will likely fail because it was testing if corrwith works for DataFrames with duplicate columns but it was actually failing on master.

Does that test need to be xfailed?

@TomAugspurger I added a commit with a merge (should I have rebased?  It seems many unrelated changes were added to the commit), a few fixes (the test failure was apparently caused by accidentally taking a transpose twice, and there was some error-catching code that wasn't accounting for callable methods) and an xfail added to the duplicate columns test