Reshaping Pandas dataframe based on repeating values in a column

Question

Very new to Pandas and probably been answered somewhere but I can't seem to find exactly what I'm looking for. Assuming my dataset has this type of structure

Animal |  Age  |  Color  | Length

Cat       1       Brown       50cm
Cat       2       White       60cm
Cat       3       Brown       55cm
Dog       1       White       99cm
Dog       2       White       129cm
Dog       3       White       105cm

How can I most easily transform it to this format where the existing columns are appended horizontally rather than being ordered vertical for a specific animal

Animal |  Age_1  |  Color_1  | Length_1 |  Age_2 | Color_2 | Length_2 | Age_3 | Color_3 | Length_3

Cat       1        Brown       50cm         2       White      60cm       3       Brown      55cm
Dog       1        White       99cm         2       White      129cm      3       White      105cm

Maybe not the best example labels to use, but hopefully gets the point across I greatly appreciate links to answers too.

@jezrael Question 10: How to pivot by two columns. and there's another question on renaming. — Quang Hoang
– Quang Hoang, Commented May 19, 2020 at 13:28
@jezrael Why? both contents are provided in the dupe! But sure... — Quang Hoang
– Quang Hoang, Commented May 19, 2020 at 13:29
@QuangHoang - It is not 100% dupe, so I think it should be not closed. — jezrael
– jezrael, Commented May 19, 2020 at 13:31
@jezrael Pivoting by set_index and unstack is also mentioned in that question/answer. But then again, that's just my opinion, you already said you disagreed. Please do not tag me again on this. — Quang Hoang
– Quang Hoang, Commented May 19, 2020 at 13:33

jezrael · Accepted Answer · 2020-05-19 13:09:49Z

2

Create MultiIndex by GroupBy.cumcount and DataFrame.set_index, then reshape by DataFrame.unstack and sortinf second level of MultiIndex in columns, then flatten it with f-strings and convert index to column:

df1 = (df.set_index(['Animal', df.groupby('Animal').cumcount().add(1)])
         .unstack()
         .sort_index(axis=1, level=1))
df1.columns = [f'{a}_{b}' for a, b in df1.columns]
df1 = df1.reset_index()
print (df1)
  Animal  Age_1 Color_1 Length_1  Age_2 Color_2 Length_2  Age_3 Color_3  \
0    Cat      1   Brown     50cm      2   White     60cm      3   Brown   
1    Dog      1   White     99cm      2   White    129cm      3   White   

  Length_3  
0     55cm  
1    105cm

answered May 19, 2020 at 13:09

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Mark Wang Over a year ago

I never find a nice way to method chain 'flatten multiindex into a single one'. Any suggestion?

jezrael Over a year ago

@MarkWang - The best should be if support rename, but it working with all columns names separately...

Mark Wang Over a year ago

Also expand single header to multindex index header. It used to work with rename in the order version of pandas, now it just gives you a tuple..

Mark Wang Over a year ago

Awesome, though multiindex to_flat_index only gives you tuple. But I guess you can chain it with df.rename(columns='{0[0]}_{0[1]}'.format) to flatten the tuple

Mark Wang Over a year ago

Try the following?

index = pd.MultiIndex.from_product( [['foo', 'bar'], ['baz', 'qux']],names=['a', 'b'])  pd.Series([1,2,3,3], index=index.to_flat_index()).rename('{0[0]}_{0[1]}'.format)

|

Collectives™ on Stack Overflow

Reshaping Pandas dataframe based on repeating values in a column

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related