2

I have a tidy Dataframe (which I'm not sure how to produce from scratch) such as: 

signal condition  epoch  time value  
0             A      0  -1100  0.12  
1             A      0  -1080  0.09     
2             A      0  -1060  0.08  
...

With several conditions A,B,C. I want to get a DataFrame with multiindex, and the values of value column as values in the new (and only) columns A, B, C so in the end it looks like: 

value          A     B     C
epoch time           
0    -1100   0.12   0.23   0.09
     -1080   0.09   0.22   0.10
     -1060   0.08   0.19   0.06
...

I tried to first put everything except value into the index with set_index(['condition','epoch','time']) and then transpose or pivot but can't get it right somehow (transpose gives me hierarchical columns and pivot KeyError) I tried e.g.: df.pivot(('epoch','time'),'condition')

Community
  • 1
  • 1
TNT
  • 2,431
  • 1
  • 19
  • 26

2 Answers2

1

try

df.set_index(['epoch', 'time', 'condition']).value.unstack().rename_axis('value', 1)

value           A     B     C
epoch time                   
0     -1100  0.12  0.23  0.09
      -1080  0.09  0.22  0.10
      -1060  0.08  0.19  0.06

setup 

import pandas as pd
from io import StringIO

txt = """signal condition  epoch  time value  
0             A      0  -1100  0.12  
1             A      0  -1080  0.09     
2             A      0  -1060  0.08  
0             B      0  -1100  0.23  
1             B      0  -1080  0.22     
2             B      0  -1060  0.19  
0             C      0  -1100  0.09  
1             C      0  -1080  0.10     
2             C      0  -1060  0.06  """

df = pd.read_csv(StringIO(txt), delim_whitespace=True)
piRSquared
  • 285,575
  • 57
  • 475
  • 624
  • Thanks, also for the StringIO trick, very useful; just the pivot method is less headspinning to me. – TNT Mar 02 '17 at 03:21
1

You were on the right track using pivot which is explicitly designed for such long-to-wide transformations:

# show dummy df 
print(df)

    signal  condition   epoch   time    value
0   0       A           0       -1100   0.12
1   1       A           0       -1080   0.09
2   2       A           0       -1060   0.08
3   0       B           0       -1100   0.42
4   1       B           0       -1080   0.29
5   2       B           0       -1060   0.18
6   0       C           0       -1100   0.32
7   1       C           0       -1080   0.59
8   2       C           0       -1060   0.38

# pivot
pd.pivot_table(df, values="value", columns="condition", index=["epoch", "time"])

condition       A       B       C
epoch   time            
0       -1100   0.12    0.42    0.32
        -1080   0.09    0.29    0.59
        -1060   0.08    0.18    0.38
pansen
  • 6,433
  • 4
  • 19
  • 32