1

I have a dataframe of type:

a = ['a','b','c','a','b','c','a','b','c']
b = [0,1,2,3,4,5,6,7,8]
df = pd.DataFrame({'key':a,'values':b})

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

I want to move the values in the "values" column to new columns where they have the same "key".

So result:

  key  values0  values1  values2
0   a        0        3        6
1   b        1        4        7
2   c        2        5        8

From this question How can I pivot a dataframe? I've tried:

a=d1.pivot_table(index='key',values='values',aggfunc=list).squeeze()
pd.DataFrame(a.tolist(),index=a.index)

Which gives

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

But I don't want the index to be 'key', I want the index to stay the same.

Mitch
  • 553
  • 1
  • 9
  • 24

2 Answers2

4

You can use reset_index.

a = df.pivot_table(index='key',values='values',aggfunc=list).squeeze()
out = pd.DataFrame(a.tolist(),index=a.index).add_prefix('values').reset_index()
print(out)

# Output
  key  values0  values1  values2
0   a        0        3        6
1   b        1        4        7
2   c        2        5        8

Another way to do it:

out = (df.pivot_table('values', 'key', df.index // 3)
         .add_prefix('values').reset_index())
print(out)

# Output
  key  values0  values1  values2
0   a        0        3        6
1   b        1        4        7
2   c        2        5        8
Corralien
  • 109,409
  • 8
  • 28
  • 52
  • That works I just thought there would be a way to do it without changing the index in the first place. Thanks – Mitch May 02 '22 at 18:17
3
df["id"] = df.groupby("key").cumcount()
df.pivot(columns="id", index="key").reset_index()
#    key values      
# id          0  1  2
# 0    a      0  3  6
# 1    b      1  4  7
# 2    c      2  5  8
d.b
  • 32,245
  • 6
  • 36
  • 77