Merge multiple rows into one with additional columns in pandas

Question

Say I have the dataframe below:

import pandas as pd
   
rankings = {'Team': ['A', 'A', 'B',
                            'B', 'C', 'C', 'C'],
              'Description': ['Aggressive', 'Strong', 'Passive',
                            'Strong', 'Bad', 'Loser Streak', 'Injured'],
           'Description Continued': ['Aggressive ...', 'Strong ...', 'Passive ...',
                            'Strong ...', 'Bad ...', 'Loser Streak ...', 'Injured ...']}
rankings_pd = pd.DataFrame(rankings)

There are multiple rows for each team. I want to have one row against each team and add additional columns in the dataframe to contain the extra information. This is the desired output:

How can I achieve this?

How to pivot a dataframe? This doesn't work for multiple columns as in the example above.

Answer 1

The key is to create numeric index per group before pivoting

rankings_pd["count"] = rankings_pd.groupby("Team").cumcount() + 1

df = rankings_pd.pivot(
    index="Team", columns="count", values=["Description", "Description Continued"]
)

df.columns = [f"{x}{y}" for x, y in df.columns]