Pandas pivot using selected values as index

Question

I read this excellent guide to pivoting but I can't work out how to apply it to my case. I have tidy data like this:

>>> import pandas as pd
>>> df = pd.DataFrame({
...    'case': ['a', 'a', 'a', 'a', 'b', 'b', 'b', 'b', ],
...    'perf_var': ['num', 'time', 'num', 'time', 'num', 'time', 'num', 'time'],
...    'perf_value': [1, 10, 2, 20, 1, 30, 2, 40] 
...     }
...     )
>>>
>>> df
  case perf_var  perf_value
0    a      num           1
1    a     time          10
2    a      num           2
3    a     time          20
4    b      num           1
5    b     time          30
6    b      num           2
7    b     time          40

What I want is:

To use "case" as the columns
To use the "num" values as the index
To use the "time" values as the value.

to give:

case a   b
1.0  10  30
2.0  20  40

All the pivot examples I can see have the index and values in separate columns, but the above seems like a valid/common "tidy" data case to me (I think?). Is it possible to pivot from this?

sammywemmy · Accepted Answer · 2020-08-13T12:02:48.143

You need a bit of preprocessing to get your final result :

   (df.assign(num=np.where(df.perf_var == "num",
                           df.perf_value, 
                           np.nan),
             time=np.where(df.perf_var == "time", 
                           df.perf_value, 
                           np.nan))
      .assign(num=lambda x: x.num.ffill(),
              time=lambda x: x.time.bfill())
      .loc[:, ["case", "num", "time"]]
      .drop_duplicates()
      .pivot("num", "case", "time"))


case       a    b
num     
1.0     10.0    30.0
2.0     20.0    40.0

An alternative route to the same end point :

(
    df.set_index(["case", "perf_var"], append=True)
    .unstack()
    .droplevel(0, 1)
    .assign(num=lambda x: x.num.ffill(), 
            time=lambda x: x.time.bfill())
    .drop_duplicates()
    .droplevel(0)
    .set_index("num", append=True)
    .unstack(0)
    .rename_axis(index=None)
)

Wow. Thanks. Does this suggest my original data isn't a very sensible form? (I potentially have control over that). — lost, Aug 13 '20 at 12:52
I'd say you need to get the dataframe into the right shape before pivoting. The preprocessing shouldnt be an issue on your end — sammywemmy, Aug 13 '20 at 12:54

Pandas pivot using selected values as index

1 Answers1