Summing all values from one day in a time series in pandas pivot

Question

how to calculate the sum of all values from one day in a time series in pd pivot? My pandas pivot looks like this:

       Date    2019-10-01         2019-10-02          2019-10-03       ....          2019-12-01 
Hospital_name                                                         

Hospital1          12                 15                  16           ....              12                                                              
Hospital2          10                 17                  14           ....              12 
Hospital3          15                 20                  12           ....              12

and I want to pivot such like this:

       Date    2019-10-01         2019-10-02           2019-10-03      ....          2019-12-01 

Sum                37                 52                   42          ....               36

My data type is:

type(df.columns[0])
out: str

type(df.columns[1])
out: pandas._libs.tslibs.timestamps.Timestamp

Thanks for your help!

Is the first dataframe your original dataframe or is it a pivot? — davidbilla, Dec 12 '19 at 10:24
Did you try `.sum()` on your pivot table? Can you post a sample of your actual data frame before pivot? — davidbilla, Dec 12 '19 at 10:48

score 1 · Answer 1 · answered Dec 12 '19 at 11:15

sum is your friend here, as stated in the comments. Using dummy df:

              2019-10-01 2019-10-02 2019-10-03
Hospital_name       John       Maya      Robin
h1                    12         12         42
h2                    15         55         13
h3                    14         42         22

You simply ignore the first row and use sum:

df.loc[df.index!='Hospital_name'].sum()

2019-10-01     41.0
2019-10-02    109.0
2019-10-03     77.0
dtype: float64

EDIT: It looks like you have multiindex columns. You can drop this using:

df.columns = df.columns.droplevel()

(taken from this answer)

score 1 · Answer 2 · answered Dec 12 '19 at 11:17

new_df = df.transpose()
new_df["Total"] = df[0:].sum()
df = new_df.transpose()

new_df is assigned as df but a transposed version
new_df["Total"] = df[0:].sum() adds the Total columns
df = new_df.transpose() brings back the table as it was in the first place

For a better experience you can always try each line in a jupyter notebook or lab to see what happens. And please if you are satisfied with the answer, mark it as accepted

Thank you

Summing all values ​from one day in a time series in pandas pivot

2 Answers2

Summing all values from one day in a time series in pandas pivot