Pivoting without numerical aggregation/ a numerical column

Question

I have a dataframe that looks like this

d = {'Name': ['Sally', 'Sally', 'Sally', 'James', 'James', 'James'], 'Sports': ['Tennis', 'Track & field', 'Dance', 'Dance', 'MMA', 'Crosscountry']}
df = pd.DataFrame(data=d)

Name	Sports
Sally	Tennis
Sally	Track & field
Sally	Dance
James	Dance
James	MMA
James	Crosscountry

It seems that pandas' pivot_table only allows reshaping with numerical aggregation, but I want to reshape it to wide format such that the strings are in the "values":

Name	First_sport	Second_sport	Third_sport
Sally	Tennis	Track & field	Dance
James	Dance	MMA	Crosscountry

Is there a method in pandas that can help me do this? Thanks!

Answer 1

Another solution:

print(
    df.groupby("Name")
    .agg(list)["Sports"]
    .apply(pd.Series)
    .rename(columns={0: "First", 1: "Second", 2: "Third"})
    .add_suffix("_sport")
    .reset_index()
)

Prints:

    Name First_sport   Second_sport   Third_sport
0  James       Dance            MMA  Crosscountry
1  Sally      Tennis  Track & field         Dance