0

I have a dataframe like this one:

      City          Rest ID      Price
0   New York        1            10
1   Paris           1            11
2   New York        2            13
3   Los Angeles     1            15
4   Madrid          1            9
5   New York        3            25
6   Madrid          2            15
7   New York        4            80

I want to transform it grouping by the column city and then getting a new column that counts how many registers there are of cities. I can do that with:

df.groupby(['City']).size().reset_index(name = 'Times')

Then I would get:

      City          Times
0   New York        4       
1   Paris           1       
2   Los Angeles     1       
3   Madrid          2

The point is I would need to create new columns with the different prices I showed in the first dataframe to get something like this:

      City          Times   Rest ID 1   Rest ID 2   Rest ID 3   Rest ID 4
0   New York        4       10          13          25          80
1   Paris           1       11          0           0           0 
2   Los Angeles     1       15          0           0           0 
3   Madrid          2       9           15          0           0

As you can see, the cities with less Rest IDs, would fill with 0 the columns that do not have that Rest ID... Any idea about how to do this?

nokvk
  • 171
  • 5
  • 1
    Use `df = df.groupby(['City'], sort=False).size().reset_index(name = 'Times').join(df.pivot_table(index='City', columns='Rest ID', values='Price', aggfunc='sum', fill_value=0).add_prefix('Rest ID '), on='City')` – jezrael Jan 07 '22 at 13:51

0 Answers0