CODEWITHSUNDEEP
pythonpandas
Faenatek
Faenatek

Reputation: 13

How to partially pivot a table in pandas

I'm not sure if "pivot" is the right term for this, but I have something like

ID type date
123 A 1-2-2020
123 B 1-7-2020
123 C 1-14-2020
456 A 5-18-2019
456 B 5-30-2019
789 A 8-7-2021
789 B 8-16-2021
789 C 8-23-2021
789 D 8-30-2021

And I want to transform it into something like:

ID type_1 date_1 type_2 date_2
123 A 1-2-2020 B 1-7-2020
123 B 1-7-2020 C 1-14-2020
456 A 5-18-2019 B 5-30-2019
789 A 8-7-2021 B 8-16-2021
789 B 8-16-2021 C 8-23-2021
789 C 8-23-2021 D 8-30-2021

So that they types are paired and every row has exactly two types and dates.

Upvotes: 0

Views: 69

Answers (1)

user17242583
user17242583

Reputation:

Try this:

new_df = pd.concat([
    df.groupby('ID').apply(lambda g: g.head(g.shape[0] - 1)).reset_index(drop=True),
    df.groupby('ID').apply(lambda g: g.tail(g.shape[0] - 1)).reset_index(drop=True).drop('ID', axis=1)
], axis=1)

Output:

>>> new_df
    ID type       date type       date
0  123    A   1-2-2020    B   1-7-2020
1  123    B   1-7-2020    C  1-14-2020
2  456    A  5-18-2019    B  5-30-2019
3  789    A   8-7-2021    B  8-16-2021
4  789    B  8-16-2021    C  8-23-2021
5  789    C  8-23-2021    D  8-30-2021

More compact version:

new_df = pd.concat([df.groupby('ID').apply(lambda g:g.head(g.shape[0]-1)).reset_index(drop=True),df.groupby('ID').apply(lambda g:g.tail(g.shape[0]-1)).reset_index(drop=True).drop('ID', axis=1)],axis=1)

Upvotes: 1

Related Questions