How would you flip and fold diagonaly a matrix with pandas?

user_15843385 0
Viewed 114

I have some datas I would like to organize for visualization and statistics but I don't know how to proceed.

The data are in 3 columns (stimA, stimB and subjectAnswer) and 10 rows (numero of pairs) and they are from a pairwise comparison test, in panda's dataFrame format. Example :

stimA stimB subjectAnswer
1 2 36
3 1 55
5 3 98
... ... ...

My goal is to organize them as a matrix with each row and column corresponding to one stimulus with the subjectAnswer data grouped to the left side of the matrix' diagonal (in my example, the subjectAnswer 36 corresponding to stimA 1 and stimB 2 should go to the index [2][1]), like this :

stimA/stimB 1 2 3 4 5
1 ...
2 36
3 55
4 ...
5 ... ... 98

I succeeded in pivoting the first table to the matrix but I couldn't succeed the arrangement on the left side of the diag of my datas, here is my code :

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np


session1 = pd.read_csv(filepath, names=['stimA', 'stimB', 'subjectAnswer'])


pivoted = session1.pivot('stimA','stimB','subjectAnswer')

Which gives :

session1 :

   stimA  stimB  subjectAnswer
0      1      3              6
1      4      3             21
2      4      5             26
3      2      3             10
4      1      2              6
5      1      5              6
6      4      1              6
7      5      2             13
8      3      5             15
9      2      4             26

pivoted :

stimB    1     2     3     4     5
stimA                             
1      NaN   6.0   6.0   NaN   6.0
2      NaN   NaN  10.0  26.0   NaN
3      NaN   NaN   NaN   NaN  15.0
4      6.0   NaN  21.0   NaN  26.0
5      NaN  13.0   NaN   NaN   NaN

The expected output for pivoted :

stimB    1     2     3     4     5
stimA                             
1      NaN   NaN   Nan   NaN   NaN
2      6.0   NaN  Nan  NaN   NaN
3      6.0   10.0   NaN   NaN  NaN
4      6.0   26.0  21.0   NaN  NaN
5      6.0  13.0   15.0   26.0   NaN

Thanks a lot for your help !

pandas statistics visualization numpy python
2 Answers

If I understand you correctly, the stimuli A and B are interchangeable. So to get the matrix layout you want, you can swap A with B in those rows where A is smaller than B. In other words, you don't use the original A and B for the pivot table, but the maximum and minimum of A and B:

session1['stim_min'] = np.min(session1[['stimA', 'stimB']], axis=1)
session1['stim_max'] = np.max(session1[['stimA', 'stimB']], axis=1)

pivoted = session1.pivot('stim_max', 'stim_min', 'subjectAnswer')
pivoted

  stim_min  1       2       3       4
stim_max                
2           6.0     NaN     NaN     NaN
3           6.0     10.0    NaN     NaN
4           6.0     26.0    21.0    NaN
5           6.0     13.0    15.0    26.0
user_130141720

sort the columns stimA and stimB along the columns axis and assign two temporary columns namely x and y in the dataframe. Here sorting is required because we need to ensure that the resulting matrix clipped on the upper right side.

Pivot the dataframe with index as y, columns as x and values as subjectanswer, then reindex the reshaped frame in order to ensure that all the available unique stim names are present in the index and columns of the matrix

session1[['x', 'y']] = np.sort(session1[['stimA', 'stimB']], axis=1)

i = np.union1d(session1['x'], session1['y'])
session1.pivot('y', 'x','subjectAnswer').reindex(i, i)

x    1     2     3     4   5
y                           
1  NaN   NaN   NaN   NaN NaN
2  6.0   NaN   NaN   NaN NaN
3  6.0  10.0   NaN   NaN NaN
4  6.0  26.0  21.0   NaN NaN
5  6.0  13.0  15.0  26.0 NaN
user_128331660
Related