Pivot Wider Pandas DataFrame

Question

Hi I'm trying to pivot my data from long to wide in Python. I'm getting confused by some of the other answers on here with a mix of people using pivot, or set_index and unstack, and hierarchical indexing etc.

Below is a sample df with 4 columns and 9 rows. I'd like to be able to pivot the data so that there is exactly 1 row for every unique id which includes columns like Jan 2021 sales and Jan 2021 profit. So sales & profit would each have their own column for every month.

import pandas as pd
from datetime import datetime, timedelta

sample_df = pd.DataFrame({
 'id': [1, 1, 1, 2, 2, 2, 3, 3, 3],
 'month': ['Jan 2021', 'Feb 2021', 'Mar 2021', 'Jan 2021', 'Feb 2021', 'Mar 2021', 'Jan 2021', 'Feb 2021', 'Mar 2021'],
 'sales': [100, 200, 300, 400, 500, 600, 700, 800, 900],
 'profit': [10, 20, 30, 40, 50, 60, 70, 80, 90]}
)

sample_df_pivot = xxx

Ideally it would look like this

Any help would be appreciated !

Answer 1

Try this:

df = sample_df.pivot(columns='month', index='id')
df.columns = df.columns.swaplevel().map(' '.join)

Output:

>>> df
    Feb 2021 sales  Jan 2021 sales  Mar 2021 sales  Feb 2021 profit  Jan 2021 profit  Mar 2021 profit
id                                                                                                   
1              200             100             300               20               10               30
2              500             400             600               50               40               60
3              800             700             900               80               70               90