Swap the column to one single row

user_19698290 0
Viewed 36

This is the df:

Animal Name foo
Tiger Two 3
Tiger Two 4
Tiger Two 5
Tiger Two 6
Tiger Two 7
Tiger Two 8
fish Three 31
fish Three 42
fish Three 54
fish Three 64
fish Three 74
fish Three 87

My end goal is :

Animal Name foo1 foo2 foo3 foo4 foo5 foo6
Tiger Two 3 4 5 6 7 8
fish Three 31 42 54 64 74 87

I tried it with df.pivot(index=['animal','name'], columns='foo',values='foo')

But then I end up with a huge df with alot of NaN values. Do I multiindex the 2 first columns and then tranpose the foo colum ?

pandas dataframe pivot python-3.x python
1 Answers

Try:

df[""] = "foo" + (df.groupby(["Animal", "Name"]).cumcount() + 1).astype(str)
print(
    df.pivot(index=["Animal", "Name"], columns="", values="foo").reset_index()
)

Prints:

  Animal   Name  foo1  foo2  foo3  foo4  foo5  foo6
0  Tiger    Two     3     4     5     6     7     8
1   fish  Three    31    42    54    64    74    87
user_100359850
Related