Python Dataframe table manipulation

Question

I have a dataframe like this:

Account	Effective_Date
1	01-Jan-2022
1	10-Jan-2022
1	21-Jan-2022
2	05-Jan-2022
2	21-Jan-2022
3	10-Jan-2022
4	01-Jan-2022
4	10-Jan-2022
5	01-Jan-2022
5	02-Jan-2022
5	03-Jan-2022
5	04-Jan-2022

The max Account# can repeat is 4. I want to change it to a table to something like this

Account	Effective_Date	Effective_Date1	Effective_Date2	Effective_Date3
1	01-Jan-2022	10-Jan-2022	21-Jan-2022
2	05-Jan-2022	21-Jan-2022
3	10-Jan-2022
4	01-Jan-2022	10-Jan-2022
5	01-Jan-2022	02-Jan-2022	03-Jan-2022	04-Jan-2022

The transformed table has unique columns, the repeated row value gets added to the column.

Appreciate if someone can give me a python snippet for this.

score 0 · Answer 1 · answered Feb 27 '22 at 17:41

Not really sure why you didn't know your columns Effective_Date1, Effective_Date2, Effective_Date3, Effective_Date4. But this is a job for pivot:

df['Rank'] = df.groupby('Account').cumcount()
result = df.pivot(index='Account', columns='Rank')
result.columns = [f'{i}{"" if j == 0 else j}' for i, j in result.columns.to_flat_index()]

result = result.reset_index().fillna('')

Python Dataframe table manipulation

1 Answers1