1

I'm trying to reshape my data based on column values and then transpose the dataframe so that groups of column values become column headings. For example I'd like to go from:

# Creating an illustrative dataframe:

import pandas as pd
import numpy as np

categories = np.repeat(list('abc'), 3)
data = np.arange(len(categories))
df = pd.DataFrame({'Data': data, 'Data2': data*2, 'Category': categories})

   Data  Data2 Category
0     0      0        a
1     1      2        a
2     2      4        a
3     3      6        b
4     4      8        b
5     5     10        b
6     6     12        c
7     7     14        c
8     8     16        c

to look like:

   a  b  c
0  0  3  6
1  1  4  7
2  2  5  8

I've tried an approach using pd.DataFrame.pivot():

[In]
df.pivot(columns='Category', values='Data')

[Out]
Category    a    b    c
0         0.0  NaN  NaN
1         1.0  NaN  NaN
2         2.0  NaN  NaN
3         NaN  3.0  NaN
4         NaN  4.0  NaN
5         NaN  5.0  NaN
6         NaN  NaN  6.0
7         NaN  NaN  7.0
8         NaN  NaN  8.0

Is there a way to 'ignore' the index when using pd.DataFrame.pivot() or is there an alternative approach to producing the desired output?

Jason
  • 4,346
  • 10
  • 49
  • 75
  • 1
    Specifically, see question 10 in the duplicate. First create the `index` with `groupby` + `cumcount` then you pivot. – ALollz May 01 '20 at 14:48
  • 2
    You'll need to help `pivot_table` with the index, `df.pivot_table(index = df.groupby('Category').cumcount(), columns='Category', values='Data')` – yatu May 01 '20 at 14:49

0 Answers0