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:

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

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.

CodePudding user response：

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

CodePudding user response：

Try dataframe.pivot_table()

df.pivot_table(values='Qty', index=['City', 'Car Maker'], columns='Car Maker').reset_index()