How to get transformed dataframe by grouping by one column and matching on another in Pandas

user_534617 0
Viewed 47

I have a Pandas dataframe like the following

col_a            col_b     col_c
2021-05-01        1           30
2021-05-01        3           40
2021-05-01        2           60
2021-05-02        1           70
2021-05-02        2           10
2021-05-02        3           20

And I want to make it into the following dataframe (i.e. adding n new columns based on how many different types in the col_b column in the original dataframe, and then matching based on col_a to fill in values)

col_a                 type_1   type_2     type_3
2021-05-01                30      60        40   
2021-05-02                70      10        20

Could you help me please?

pandas python
2 Answers

you can use pivot:

df = df.pivot(*df).add_prefix('type_')

To further transform into required structure use:

df = df.pivot(*df).add_prefix('type_').rename_axis(None, axis= 1).reset_index()

OUTPUT:

        col_a  type_1  type_2  type_3
0  2021-05-01      30      60      40
1  2021-05-02      70      10      20

One more set_index/unstack option:

df = df.set_index(['col_a','col_b']).unstack().add_prefix('type_')
df.columns = df.columns.droplevel()
user_154380330

You can use .pivot() and then .add_prefix() for the column names. Clean up the column axis name by .rename_axis() and finally .reset_index(), as follows:

df = (df.pivot(index='col_a', columns='col_b', values='col_c')
        .add_prefix('type_')
        .rename_axis(columns=None)
        .reset_index()
     )

Result:

        col_a  type_1  type_2  type_3
0  2021-05-01      30      60      40
1  2021-05-02      70      10      20
user_150706970
Related