Update the values of a dataframe

Question

Given some data about dishes, the location of the restaurant and its sales:

>>> import pandas
>>> df1 = pandas.DataFrame({"dish"     : ["fish", "chicken", "fish", "chicken", "chicken"],
...                         "location" : ["central", "central", "north", "north", "south"],
...                         "sales" : [1,3,5,2,4]})
>>> df1
      dish location  sales
0     fish  central      1
1  chicken  central      3
2     fish    north      5
3  chicken    north      2
4  chicken    south      4

>>> df2 = df1[["dish", "location"]]
>>> df2["sales_contrib"] = 0.0
>>> df2
      dish location  sales_contrib
0     fish  central            0.0
1  chicken  central            0.0
2     fish    north            0.0
3  chicken    north            0.0
4  chicken    south            0.0

Right now, I would like to do the following:

Iterate through each row of df2
Calculate the sales contrib. of that location for that dish . So for fish, central contributes 1/6 16.67% of total revenue and north contributes the remaining 83.3%

The resultant df is

          dish location  sales_contrib
0     fish  central            16.67
1  chicken  central            33.33
2     fish    north            83.33
3  chicken    north            22.22
4  chicken    south            44.45

I tried using iteritems() but could not get results.

Ryan · Accepted Answer · 2017-12-14 17:03:30Z

4

You can use the power of Pandas to do this...

dish_totals = df1.groupby(by="dish").sum()
df2["sales_contrib"] = df1.apply((lambda row: 100*row["sales"]/dish_totals.loc[row["dish"]]), axis=1)
print(df2)

Output:

      dish location  sales_contrib
0     fish  central      16.666667
1  chicken  central      33.333333
2     fish    north      83.333333
3  chicken    north      22.222222
4  chicken    south      44.444444

answered Dec 14, 2017 at 17:03

Ryan

2,2131 gold badge22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan Over a year ago

@Vaishali Thanks... I liked your answer too because you did it without the intermediary dish_totals dataframe. Also, I'm not sure if it was important, but I could have rounded to two decimal places as you did.

Vaishali · Accepted Answer · 2017-12-14 17:07:23Z

3

Try

(df1.groupby(['dish', 'location']).sales.sum().div(df1.groupby('dish').sales.sum()) * 100).round(2).reset_index()

    dish    location    sales
0   chicken central     33.33
1   chicken north       22.22
2   chicken south       44.44
3   fish    central     16.67
4   fish    north       83.33

answered Dec 14, 2017 at 17:07

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

Collectives™ on Stack Overflow

Update the values of a dataframe

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related