2
$\begingroup$

I want to be able to correlate values from various IDs, where the date is the same with one another.

The data looks something like this;

ID      Time(secs)   Date
AAAA    1            01/01/1990
AAAA    6            02/01/1990
AAAA    5            03/01/1990
AAAA    2            04/01/1990
AAAA    4            05/01/1990
BBBB    2            01/01/1990
BBBB    4            02/01/1990
BBBB    6            03/01/1990
BBBB    3            04/01/1990
CCCC    3            01/01/1990
CCCC    4            02/01/1990
CCCC    1            03/01/1990
CCCC    6            04/01/1990
DDDD    7            01/01/1990
DDDD    4            02/01/1990
DDDD    5            03/01/1990
DDDD    3            04/01/1990

I want to find the correlation coefficient between each combination of these IDs, where there are matching dates. N.B. Not all of the IDs have the same date;

ID             CorrCoef
AAAA>BBBB      ????
AAAA>CCCC
AAAA>DDDD
BBBB>CCCC
BBBB>DDDD
CCCC>DDDD

I think I need to feed the data from each ID into a variable and then run the following;

data1.corr(data2)
$\endgroup$

1 Answer 1

0
$\begingroup$

If i understand correctly you can do something like this:

In [42]: df
Out[42]:
      ID  Time(secs)        Date
0   AAAA           1  01/01/1990
1   AAAA           6  02/01/1990
2   AAAA           5  03/01/1990
3   AAAA           2  04/01/1990
4   AAAA           4  05/01/1990
5   BBBB           2  01/01/1990
6   BBBB           4  02/01/1990
7   BBBB           6  03/01/1990
8   BBBB           3  04/01/1990
9   CCCC           3  01/01/1990
10  CCCC           4  02/01/1990
11  CCCC           1  03/01/1990
12  CCCC           6  04/01/1990
13  DDDD           7  01/01/1990
14  DDDD           4  02/01/1990
15  DDDD           5  03/01/1990
16  DDDD           3  04/01/1990

In [43]: df.pivot(index='Date', columns='ID', values='Time(secs)').corr()
Out[43]:
ID        AAAA      BBBB      CCCC      DDDD
ID
AAAA  1.000000  0.778924 -0.336336 -0.368964
BBBB  0.778924  1.000000 -0.609449 -0.257143
CCCC -0.336336 -0.609449  1.000000 -0.609449
DDDD -0.368964 -0.257143 -0.609449  1.000000

Virtual helper DF:

In [44]: df.pivot(index='Date', columns='ID', values='Time(secs)')
Out[44]:
ID          AAAA  BBBB  CCCC  DDDD
Date
01/01/1990   1.0   2.0   3.0   7.0
02/01/1990   6.0   4.0   4.0   4.0
03/01/1990   5.0   6.0   1.0   5.0
04/01/1990   2.0   3.0   6.0   3.0
05/01/1990   4.0   NaN   NaN   NaN
$\endgroup$
1
  • $\begingroup$ I am finding an error with this that states ValueError: Index contains duplicate entries, cannot reshape $\endgroup$
    – Taylrl
    Oct 17, 2017 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.