[FIXED] pandas: how to create new columns based on two columns and aggregate the results

Issue

I am trying to perform a sort of aggregation, but with the creation of new columns.

Let's take the example of the dataframe below:

df = pd.DataFrame({'City':['Los Angeles', 'Denver','Denver','Los Angeles'], 
    'Car Maker': ['Ford','Toyota','Ford','Toyota'],
    'Qty': [50000,100000,80000,70000]})

That generates this:

	City	Car Maker	Qty
0	Los Angeles	Ford	50000
1	Denver	Toyota	100000
2	Denver	Ford	80000
3	Los Angeles	Toyota	70000

I would like to have one line per city and the Car Maker as a new column with the Qty related to that City:

	City	Car Maker	Ford	Toyota
0	Los Angeles	Ford	50000	70000
1	Denver	Toyota	80000	100000

Any hints on how to achieve that?

I've tried some options with transforming it on a dictionary and compressing on a function, but I am looking for a more pandas' like solution.

Solution

df.pivot(index='City', columns='Car Maker', values='Qty').reset_index()

Answered By - Khai Kim