Pandas: sum Pivot Table rows for given columns

Question

I have a pivot table that I've created from a DataFrame. It basically shows number of requests in a city, for every day of the week.

          M  T  W  T  F  S  S
CITY 1     
CITY 2
CITY 3

I want to create a new column that takes Saturday's number, and divdes by the average of Monday, Tuesday, Wednesday, and Thursday. This is the code I've written, but it is clunky.

pivot_requests_per_area = df.pivot_table(values="requests_per_area", index="city name", columns="day", aggfunc=np.sum)

pivot_requests_per_area["delta_sat"] =pivot_requests_per_area["Saturday"] / ((pivot_requests_per_area["Monday"]+pivot_requests_per_area["Tuesday"]+pivot_requests_per_area["Wednesday"]+pivot_requests_per_area["Thursday"])/4)

I've tried doing this, but I get a raise KeyError(key) from err.

weekdays = pivot_requests_per_area["Monday", "Tuesday", "Wednesday", "Thursday"]

pivot_requests_per_area["delta_fri"] =pivot_requests_per_area["Friday"] / pivot_requests_per_area[weekdays].mean()

I would like to know if there is a more efficient way than adding the columns individually?

David Yi David Yi · Accepted Answer · 2020-10-08T08:05:01

The key error you get is in the first line, the proper code should be:

weekdays = pivot_requests_per_area[["Monday", "Tuesday", "Wednesday", "Thursday"]]

Replace this in and your code should work.

Pandas: sum Pivot Table rows for given columns

1 Answers