Adding row and column total to pivot table fails

Question

I want to display row and column total. I am using margin=True but the output does not show row total as below code and output:

import pandas as pd
df2 = pd.read_csv("https://www.dropbox.com/s/90y07129zn351z9/test_data.csv?dl=1",encoding="latin-1")

df2['received'] = pd.to_datetime(df2['received'])
df2['sent'] = pd.to_datetime(df2['sent'])


pvt_all = df2.dropna(axis=0, how='all', subset=['received', 'sent'])\
    .pivot_table(index=['site'], values=['received','sent'],\
    aggfunc='count', margins=True, dropna=False)
pvt_all['to_send']= pvt_all['received']-pvt_all['sent'] 
pvt_all=pvt_all[['received','sent','to_send']] 
pvt_all

received    sent    to_send
site            
2   32.0    27.0    5.0
3   20.0    17.0    3.0
4   33.0    31.0    2.0
5   40.0    31.0    9.0
All 125.0   106.0   19.0

Sample data below to allow easiness for you besides it is a long one. You can also find in url provided in df vector above. The DataFrame consists of four variables: date, site, received and sent.

date    site    received    sent
7/10/2018   2       
7/10/2018   2       
7/11/2018   2       
7/11/2018   2       
7/11/2018   2       
7/12/2018   2       
7/12/2018   2       
7/12/2018   2       
7/13/2018   2   7/13/2018 12:50 7/18/2018 14:44
7/13/2018   2       
7/18/2018   2       
7/19/2018   2       
7/19/2018   2       
7/23/2018   2       
7/23/2018   2       
7/12/2018   2       
7/12/2018   2       
7/12/2018   2       
7/12/2018   2       
7/12/2018   2       
7/13/2018   2       
7/13/2018   2       
7/13/2018   2       
7/16/2018   2       
7/16/2018   2       
7/17/2018   2       
7/17/2018   2       
7/18/2018   2       
7/18/2018   2       
7/18/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/19/2018   2       
7/19/2018   2       
7/19/2018   2       
7/26/2018   2       
7/26/2018   2       
7/25/2018   2       
7/24/2018   2       
7/23/2018   2   7/23/2018 15:53 7/25/2018 10:35
7/24/2018   2       
7/23/2018   2       
7/23/2018   2   7/23/2018 15:53 7/25/2018 10:35
7/24/2018   2       
7/24/2018   2       
7/24/2018   2       
7/23/2018   2   7/23/2018 15:53 7/25/2018 10:35
7/23/2018   2   7/23/2018 15:53 7/25/2018 10:35
7/23/2018   2       
7/23/2018   2   7/23/2018 15:53 7/25/2018 10:35
7/24/2018   2       
7/9/2018    2       
7/10/2018   2       
7/9/2018    2   7/9/2018 15:19  7/11/2018 10:25
7/10/2018   2   7/10/2018 12:26 7/11/2018 10:25
7/10/2018   2       
7/19/2018   2       
7/19/2018   2       
7/19/2018   2   7/19/2018 14:22 7/25/2018 10:35
7/23/2018   2       
7/23/2018   2   7/23/2018 15:53 7/25/2018 10:35
7/23/2018   2       
7/19/2018   2       
7/19/2018   5       
7/23/2018   2       
7/23/2018   2       
7/16/2018   2       
7/16/2018   2       
7/16/2018   2       
7/17/2018   2       
7/17/2018   2       
7/17/2018   2       
7/17/2018   2       
7/17/2018   2       
7/17/2018   2       
7/17/2018   2       
7/6/2018    2       
7/6/2018    2       
7/6/2018    2       
7/9/2018    2       
7/9/2018    2       
7/24/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/2/2018    2       
7/2/2018    2       
7/3/2018    2       
7/3/2018    2       
7/3/2018    2       
6/29/2018   2       
6/29/2018   2       
6/29/2018   2       
7/2/2018    2       
7/2/2018    2       
7/11/2018   2       
7/12/2018   2       
7/12/2018   2       
7/12/2018   2       
7/12/2018   2       
7/9/2018    2       
7/9/2018    2       
7/9/2018    2       
7/10/2018   2   7/10/2018 12:26 7/11/2018 10:25
7/10/2018   2       
7/10/2018   2       
7/10/2018   2       
7/10/2018   2       
7/11/2018   2   7/11/2018 14:54 7/18/2018 14:44
7/11/2018   2       
7/13/2018   2       
7/12/2018   2       
7/13/2018   2       
7/13/2018   2       
7/13/2018   2       
7/13/2018   2       
7/16/2018   2       
7/16/2018   2       
7/16/2018   2       
7/16/2018   2       
7/18/2018   2   7/18/2018 14:35 7/25/2018 10:35
7/19/2018   2       
7/18/2018   2       
7/19/2018   2       
7/19/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/18/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2   7/26/2018 15:35 
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/23/2018   2       
7/24/2018   2       
7/24/2018   2       
7/24/2018   2   7/24/2018 15:31 7/25/2018 10:35
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2   7/25/2018 15:34 
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/26/2018   2       
7/26/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/26/2018   2   7/26/2018 15:55 
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/27/2018   2       
7/24/2018   2       
7/24/2018   2       
7/24/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
6/29/2018   2       
6/29/2018   2       
7/2/2018    2       
7/2/2018    2       
7/2/2018    2       
6/29/2018   2       
7/6/2018    2       
7/6/2018    2       
7/6/2018    2       
7/9/2018    2       
7/2/2018    2       
7/3/2018    2       
7/3/2018    2       
7/3/2018    2       
7/3/2018    2       
7/3/2018    2       
7/3/2018    2   7/3/2018 15:20  7/4/2018 11:35
7/4/2018    2       
7/5/2018    2       
7/6/2018    2       
7/4/2018    2       
7/25/2018   2       
7/25/2018   2       
7/25/2018   2       
7/26/2018   2       
7/5/2018    2   7/5/2018 15:15  7/11/2018 10:25
7/6/2018    2       
7/9/2018    2   7/9/2018 15:19  7/11/2018 10:25
7/9/2018    2       
7/10/2018   2       
7/5/2018    2   7/5/2018 15:15  7/11/2018 10:25
7/6/2018    2       
7/6/2018    2   7/6/2018 13:30  7/11/2018 10:25
7/6/2018    2       
7/6/2018    2   7/6/2018 13:30  7/11/2018 10:25
7/10/2018   2       
7/10/2018   2       
7/10/2018   2       
7/10/2018   2       
7/11/2018   2       
7/10/2018   2       
7/13/2018   2       
7/13/2018   2   7/13/2018 12:50 
7/13/2018   2   7/13/2018 12:50 7/18/2018 14:44
7/12/2018   2   7/12/2018 15:30 7/18/2018 14:44
7/11/2018   2       
7/11/2018   2       
7/12/2018   2       
7/12/2018   2       
7/12/2018   2       
7/27/2018   2       
7/27/2018   2       
7/26/2018   2       
7/26/2018   2   7/26/2018 15:55 
7/26/2018   2       
7/26/2018   2       
7/27/2018   2       
7/16/2018   2       
7/16/2018   2       
7/16/2018   2       
7/16/2018   2       
7/16/2018   2       
7/19/2018   2       
7/17/2018   2       
7/17/2018   2       
7/17/2018   2       
7/17/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
7/26/2018   2       
6/29/2018   2       
7/2/2018    2       
7/2/2018    2       
7/2/2018    2       
7/2/2018    2       
7/2/2018    2       
7/6/2018    2       
7/6/2018    2       
7/9/2018    2       
7/9/2018    2       
7/2/2018    2   7/2/2018 15:38  7/4/2018 11:35
7/2/2018    2       
7/3/2018    2       
7/5/2018    2       
7/3/2018    2   7/3/2018 14:15  7/4/2018 11:35
7/10/2018   2       
7/10/2018   2       
7/10/2018   2       
7/10/2018   2       
7/10/2018   2       
7/10/2018   2       
7/10/2018   2       
7/11/2018   2       
7/11/2018   2       
7/11/2018   2       
7/12/2018   2       
7/12/2018   2       
7/12/2018   2       
7/13/2018   2       
7/13/2018   2       
7/13/2018   2       
7/16/2018   2   7/16/2018 14:25 7/4/2018 15:30
7/16/2018   2       
7/16/2018   2       
7/17/2018   2   7/17/2018 14:50 7/18/2018 14:44
7/17/2018   2       
7/17/2018   2       
7/18/2018   2       
7/17/2018   2       
7/18/2018   2       
7/18/2018   2   7/18/2018 14:35 7/25/2018 10:35
7/27/2018   2       
7/27/2018   2       
7/27/2018   2       
7/27/2018   2       
7/27/2018   2       
7/27/2018   2       
7/27/2018   2       
6/20/2018   5       
6/20/2018   5       
6/22/2018   5       
6/19/2018   5   6/19/2018 14:20 6/28/2018 14:20
6/19/2018   5   6/19/2018 14:20 6/28/2018 14:20
6/27/2018   5       
6/28/2018   5       
6/28/2018   5   6/28/2018 11:30 7/4/2018 15:30
6/28/2018   5       
6/28/2018   5       
7/24/2018   5       
7/24/2018   5       
6/28/2018   5       
7/2/2018    5       
7/2/2018    5       
7/2/2018    5       
7/2/2018    5       
6/19/2018   5       
6/20/2018   5   6/19/2018 14:20 6/28/2018 14:20
6/20/2018   5       
6/19/2018   5       
6/19/2018   5       
7/12/2018   5       
7/12/2018   5       
7/12/2018   5       
7/12/2018   5       
7/12/2018   5       
6/26/2018   5   6/26/2018 11:40 6/28/2018 14:20
6/27/2018   5       
6/27/2018   5   6/27/2018 14:36 6/28/2018 14:20
6/27/2018   5       
6/19/2018   5       
6/19/2018   5       
6/19/2018   5       
6/22/2018   5       
6/20/2018   5   6/19/2018 12:40 6/28/2018 14:20
6/20/2018   5       
6/20/2018   5       
6/20/2018   5   6/20/2018 11:10 6/28/2018 14:20
6/21/2018   5       
6/21/2018   5       
6/21/2018   5       
6/21/2018   5       
6/21/2018   5       
6/21/2018   5       
6/21/2018   5       
6/21/2018   5   6/21/2018 1:26  6/28/2018 14:20
6/22/2018   5       
6/22/2018   5       
6/22/2018   5       
6/22/2018   5   6/22/2018 2:30  6/28/2018 14:20
6/26/2018   5       
6/26/2018   5       
6/26/2018   5   6/20/2018 11:10 6/28/2018 14:20
6/26/2018   5   6/26/2018 2:36  6/28/2018 14:20
6/26/2018   5       
6/26/2018   5       
6/26/2018   5       
6/27/2018   5   6/27/2018 14:36 6/28/2018 14:20
6/27/2018   5       
6/27/2018   5       
6/28/2018   5       
6/28/2018   5       
6/28/2018   5       
6/28/2018   5       
6/28/2018   5       
6/19/2018   5       
6/19/2018   5       
6/19/2018   5       
6/19/2018   5       
6/19/2018   5   6/19/2018 2:20  6/28/2018 14:20
6/20/2018   5   6/20/2018 2:15  6/28/2018 14:20
6/20/2018   5       
6/20/2018   5       
6/20/2018   5       
6/22/2018   5       
6/22/2018   5       
6/22/2018   5       
6/22/2018   5       
6/22/2018   5       
7/25/2018   5       
7/25/2018   5       
7/25/2018   5   7/25/2018 14:45 
7/25/2018   5       
7/25/2018   5       
7/24/2018   5       
7/24/2018   5       
7/25/2018   5   7/25/2018 14:45 
7/25/2018   5       
7/25/2018   5       
7/25/2018   5       
7/25/2018   5       
7/25/2018   5       
7/25/2018   5       
6/22/2018   5   6/22/2018 12:00 6/28/2018 14:20
6/20/2018   5   6/21/2018 2:35  6/28/2018 14:20
6/20/2018   5       
6/20/2018   5       
6/20/2018   5       
6/26/2018   5       
6/26/2018   5       
6/26/2018   5       
6/21/2018   5       
6/21/2018   5       
6/21/2018   5       
6/21/2018   5       
6/21/2018   5       
6/22/2018   5       
6/22/2018   5   6/22/2018 14:30 6/28/2018 14:20
6/22/2018   5       
6/22/2018   5       
6/26/2018   5       
6/26/2018   5       
6/26/2018   5       
6/26/2018   5       
6/26/2018   5       
6/21/2018   5       
6/21/2018   5       
6/21/2018   5       
6/21/2018   5       
7/3/2018    5       
7/3/2018    5       
7/3/2018    5       
7/3/2018    5       
7/3/2018    5   7/3/2018 14:45  7/4/2018 15:30
7/2/2018    5       
7/2/2018    5       
7/2/2018    5       
7/2/2018    5       
7/2/2018    5       
7/24/2018   5       
7/24/2018   5       
7/25/2018   5       
7/24/2018   5       
7/24/2018   5       
7/24/2018   5       
7/25/2018   5       
7/24/2018   5   7/25/2018 14:45 
7/24/2018   5       
7/24/2018   5       
7/23/2018   5       
7/24/2018   5       
7/24/2018   5       
7/3/2018    5       
7/3/2018    5   7/3/2018 14:45  7/4/2018 15:30
7/3/2018    5       
7/23/2018   5       
7/3/2018    5       
7/3/2018    5   7/3/2018 14:45  7/4/2018 15:30
7/3/2018    5       
7/3/2018    5       
7/11/2018   5       
7/10/2018   5       
7/11/2018   5   7/11/2018 12:47 7/13/2018 15:30
7/11/2018   5       
7/11/2018   5       
7/24/2018   5       
7/23/2018   5       
7/24/2018   5       
7/24/2018   5       
7/23/2018   5       
7/24/2018   5   7/24/2018 13:40 
7/23/2018   5       
7/24/2018   5       
7/24/2018   5       
7/23/2018   5       
7/24/2018   5       
7/23/2018   5   7/23/2018 16:20 
7/23/2018   5       
7/24/2018   5       
7/24/2018   5       
7/23/2018   5       
7/23/2018   5       
7/23/2018   5       
7/24/2018   5       
7/10/2018   5       
7/10/2018   5       
7/10/2018   5   7/10/2018 16:10 7/13/2018 15:30
7/10/2018   5       
7/10/2018   5       
7/10/2018   5       
7/10/2018   5       
7/10/2018   5       
7/10/2018   5       
7/10/2018   5       
7/10/2018   5       
7/10/2018   5       
7/10/2018   5       
7/10/2018   5       
7/10/2018   5       
7/16/2018   5       
7/10/2018   5   7/10/2018 16:20 7/13/2018 15:30
7/10/2018   5       
7/10/2018   5       
7/10/2018   5       
7/11/2018   5       
7/11/2018   5       
7/11/2018   5       
7/11/2018   5       
7/11/2018   5       
7/11/2018   5       
7/18/2018   5       
7/18/2018   5   7/18/2018 12:35 7/21/2018 11:20
7/11/2018   5       
7/11/2018   5       
7/11/2018   5       
7/11/2018   5       
7/11/2018   5       
7/11/2018   5       
7/11/2018   5       
7/11/2018   5       
7/11/2018   5       
7/11/2018   5       
7/12/2018   5       
7/12/2018   5       
7/12/2018   5       
7/12/2018   5       
7/12/2018   5       
7/12/2018   5       
7/12/2018   5       
7/12/2018   5       
7/12/2018   5       
7/18/2018   5       
7/18/2018   5       
7/18/2018   5       
7/18/2018   5       
7/12/2018   5       
7/12/2018   5       
7/12/2018   5   7/12/2018 16:38 7/13/2018 15:30
7/12/2018   5       
7/12/2018   5       
7/13/2018   5       
7/13/2018   5       
7/13/2018   5       
7/20/2018   5       
7/23/2018   5       
7/23/2018   5       
7/16/2018   5       
7/23/2018   5       
7/19/2018   5       
7/19/2018   5       
7/19/2018   5       
7/18/2018   5       
7/18/2018   5       
7/18/2018   5       
7/18/2018   5       
7/18/2018   5       
7/18/2018   5       
7/20/2018   5       
7/20/2018   5       
7/20/2018   5       
7/20/2018   5       
7/18/2018   5       
7/23/2018   5       
7/23/2018   5       
7/19/2018   5       
7/19/2018   5       
7/19/2018   5       
7/19/2018   5       
7/20/2018   5       
7/20/2018   5       
7/20/2018   5       
7/20/2018   5       
7/23/2018   5       
7/23/2018   5       
7/23/2018   5       
7/23/2018   5       
7/23/2018   5       
7/24/2018   5       
7/24/2018   5       
7/24/2018   5       
7/20/2018   5       
7/20/2018   5       
7/20/2018   5       
7/18/2018   5       
7/24/2018   5       
7/18/2018   5       
7/18/2018   5   7/18/2018 13:00 7/21/2018 11:20
7/18/2018   5   7/18/2018 10:30 7/21/2018 11:20
7/18/2018   5       
7/18/2018   5       
7/18/2018   5       
7/18/2018   5       
7/13/2018   5       
7/13/2018   5       
7/13/2018   5       
7/13/2018   5       
7/16/2018   5       
7/16/2018   5       
7/19/2018   5       
7/20/2018   5       
7/20/2018   5   7/20/2018 14:30 7/21/2018 11:20
7/20/2018   5       
7/20/2018   5       
7/19/2018   5       
7/16/2018   5       
7/16/2018   5       
7/16/2018   5       
7/16/2018   5       
7/16/2018   5       
7/19/2018   5       
7/19/2018   5       
7/19/2018   5   7/19/2018 11:50 7/21/2018 11:20
7/19/2018   5       
7/19/2018   5       
7/23/2018   5       
7/23/2018   5   7/23/2018 16:20 
7/23/2018   5       
7/13/2018   5       
7/25/2018   5       
7/25/2018   5   7/25/2018 14:45 
7/25/2018   5   7/25/2018 14:45 
7/19/2018   5       
7/19/2018   5       
7/19/2018   5       
7/19/2018   5       
7/19/2018   5       
7/13/2018   5       
7/13/2018   5       
7/13/2018   5       
7/13/2018   5       
7/13/2018   5       
7/18/2018   5       
7/18/2018   5       
7/18/2018   5       
7/23/2018   5   7/23/2018 16:20 
7/19/2018   5       
7/19/2018   5   7/19/2018 13:30 7/21/2018 11:20
7/19/2018   5       
7/19/2018   5       
7/20/2018   5       
6/26/2018   4       
6/26/2018   4       
6/27/2018   4       
6/27/2018   4       
6/27/2018   4       
6/26/2018   4       
6/27/2018   4       
6/27/2018   4       
6/27/2018   4       
6/27/2018   4       
7/3/2018    4       
6/28/2018   4       
6/28/2018   4       
7/3/2018    4       
7/4/2018    4       
6/28/2018   4       
6/29/2018   4       
6/28/2018   4       
6/28/2018   4       
6/29/2018   4       
6/28/2018   4       
6/28/2018   4   6/28/2018 15:00 7/4/2018 15:45
6/28/2018   4       
6/28/2018   4       
6/29/2018   4       
7/11/2018   4       
7/11/2018   4       
7/6/2018    4       
7/6/2018    4       
7/6/2018    4       
7/11/2018   4       
7/11/2018   4       
7/11/2018   4       
7/11/2018   4       
7/11/2018   4       
7/12/2018   4       
7/12/2018   4   7/12/2018 14:09 7/17/2018 15:00
7/12/2018   4       
7/12/2018   4       
7/12/2018   4       
7/12/2018   4       
7/12/2018   4       
7/12/2018   4       
7/12/2018   4       
7/12/2018   4       
7/12/2018   4       
7/13/2018   4       
7/13/2018   4       
7/13/2018   4       
7/13/2018   4   7/13/2018 10:52 7/17/2018 15:00
7/13/2018   4       
7/12/2018   4   7/12/2018 16:28 7/17/2018 15:00
7/13/2018   4   7/13/2018 10:52 7/17/2018 15:00
7/13/2018   4       
7/13/2018   4       
7/18/2018   4       
7/18/2018   4       
7/18/2018   4       
7/18/2018   4       
7/13/2018   4       
7/18/2018   4       
7/18/2018   4       
7/18/2018   4   7/18/2018 16:21 7/24/2018 14:30
7/19/2018   4       
7/16/2018   4       
7/13/2018   4       
7/13/2018   4       
7/13/2018   4       
7/16/2018   4       
7/17/2018   4       
7/13/2018   4       
7/16/2018   4       
7/16/2018   4       
7/13/2018   4   7/13/2018 14:15 7/17/2018 15:00
7/12/2018   4       
7/11/2018   4       
7/12/2018   4       
7/12/2018   4       
7/12/2018   4       
7/11/2018   4       
7/12/2018   4       
7/11/2018   4       
7/11/2018   4   7/11/2018 13:40 7/17/2018 15:00
7/12/2018   4       
7/11/2018   4       
7/11/2018   4       
7/11/2018   4       
7/13/2018   4       
7/5/2018    4       
7/5/2018    4       
7/6/2018    4       
7/6/2018    4   7/6/2018 13:00  7/10/2018 13:30
7/6/2018    4   7/6/2018 15:30  7/10/2018 13:30
7/5/2018    4       
7/5/2018    4       
7/5/2018    4       
7/5/2018    4       
7/5/2018    4       
7/5/2018    4       
7/4/2018    4       
7/4/2018    4       
7/4/2018    4   7/4/2018 11:20  7/10/2018 13:30
7/3/2018    4       
7/18/2018   4       
7/19/2018   4       
7/18/2018   4       
7/4/2018    4   7/4/2018 11:20  7/10/2018 13:30
7/17/2018   4       
7/19/2018   4       
7/20/2018   4       
7/4/2018    4   7/4/2018 11:20  7/10/2018 13:30
7/4/2018    4   7/4/2018 13:48  7/10/2018 13:30
7/5/2018    4       
7/5/2018    4       
7/5/2018    4       
7/3/2018    4   7/3/2018 12:50  7/4/2018 15:45
7/3/2018    4   7/3/2018 12:50  7/4/2018 15:45
7/3/2018    4       
7/3/2018    4   7/3/2018 12:50  7/4/2018 15:45
7/3/2018    4   7/3/2018 14:30  7/4/2018 15:45
7/3/2018    4       
7/3/2018    4   7/3/2018 12:50  7/4/2018 15:45
7/3/2018    4       
7/3/2018    4   7/3/2018 12:50  7/4/2018 15:45
7/3/2018    4       
7/6/2018    4       
7/6/2018    4       
7/5/2018    4       
7/5/2018    4   7/5/2018 16:20  7/10/2018 13:30
7/5/2018    4       
6/26/2018   4       
6/27/2018   4       
6/28/2018   4       
7/11/2018   4       
6/28/2018   4       
7/18/2018   4   7/18/2018 10:56 7/24/2018 14:30
7/18/2018   4       
7/18/2018   4       
7/18/2018   4       
7/18/2018   4       
7/18/2018   4       
7/18/2018   4       
7/18/2018   4       
7/18/2018   4       
7/18/2018   4       
7/17/2018   4       
7/17/2018   4       
7/17/2018   4   7/17/2018 13:20 7/24/2018 14:30
7/17/2018   4       
7/17/2018   4   7/17/2018 16:30 7/24/2018 14:30
7/17/2018   4       
7/17/2018   4       
7/17/2018   4       
7/17/2018   4       
7/17/2018   4       
7/19/2018   4   7/19/2018 12:50 7/24/2018 14:30
7/19/2018   4       
7/19/2018   4       
7/19/2018   4       
7/19/2018   4       
7/19/2018   4   7/19/2018 14:04 7/24/2018 14:30
7/19/2018   4       
7/19/2018   4       
7/20/2018   4       
7/20/2018   4       
7/19/2018   4   7/19/2018 12:50 7/24/2018 14:30
7/19/2018   4       
7/19/2018   4       
7/19/2018   4       
7/19/2018   4       
7/20/2018   4       
7/20/2018   4       
7/20/2018   4       
7/20/2018   4       
7/20/2018   4   7/20/2018 12:40 7/24/2018 14:30
7/19/2018   4       
7/20/2018   4       
7/20/2018   4       
7/20/2018   4       
7/20/2018   4       
7/24/2018   4       
7/24/2018   4       
7/24/2018   4       
7/25/2018   4       
7/24/2018   4       
7/25/2018   4       
7/25/2018   4       
7/25/2018   4       
7/25/2018   4   7/25/2018 13:00 
7/20/2018   4       
7/20/2018   4       
7/20/2018   4   7/20/2018 13:55 7/24/2018 14:30
7/23/2018   4   7/23/2018 12:48 7/24/2018 14:30
7/23/2018   4       
7/20/2018   4       
7/20/2018   4       
7/20/2018   4       
7/23/2018   4       
7/24/2018   4       
7/20/2018   4       
7/20/2018   4   7/20/2018 12:40 7/24/2018 14:30
7/20/2018   4       
7/23/2018   4       
7/25/2018   4       
7/26/2018   4       
7/25/2018   4       
7/26/2018   4       
7/25/2018   4       
7/26/2018   4       
7/27/2018   4       
7/26/2018   4       
7/27/2018   4       
7/26/2018   4       
7/26/2018   4       
7/26/2018   4       
7/26/2018   4       
7/26/2018   4       
7/26/2018   4   7/26/2018 14:02 
7/27/2018   4       
7/26/2018   4       
7/27/2018   4       
7/26/2018   4       
7/27/2018   4       
7/27/2018   4       
7/27/2018   4       
7/27/2018   4       
7/27/2018   4       
7/25/2018   4       
7/25/2018   4       
7/26/2018   4       
7/26/2018   4       
7/26/2018   4       
7/26/2018   4       
7/26/2018   4       
6/19/2018   3   6/18/2018 15:00 6/28/2018 11:35
6/19/2018   3       
6/19/2018   3       
6/20/2018   3       
6/20/2018   3       
6/20/2018   3       
6/20/2018   3       
6/20/2018   3       
6/21/2018   3       
6/21/2018   3       
6/21/2018   3       
6/22/2018   3       
6/22/2018   3       
6/22/2018   3       
6/22/2018   3       
6/22/2018   3       
6/26/2018   3   6/26/2018 12:35 6/28/2018 11:35
6/26/2018   3       
6/26/2018   3       
6/26/2018   3       
6/20/2018   3       
6/21/2018   3       
6/21/2018   3       
6/21/2018   3       
6/20/2018   3       
7/5/2018    3       
7/5/2018    3       
7/5/2018    3       
7/5/2018    3       
7/5/2018    3       
6/21/2018   3       
6/21/2018   3       
6/21/2018   3       
6/22/2018   3       
6/22/2018   3       
6/22/2018   3       
6/22/2018   3       
6/22/2018   3       
6/26/2018   3       
6/26/2018   3       
6/22/2018   3       
6/22/2018   3   6/22/2018 14:10 6/28/2018 11:35
6/22/2018   3       
6/26/2018   3       
6/26/2018   3   6/26/2018 12:35 6/28/2018 11:35
6/26/2018   3       
6/26/2018   3       
6/26/2018   3       
6/27/2018   3   6/27/2018 13:20 6/28/2018 11:35
6/27/2018   3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
6/27/2018   3       
6/27/2018   3       
6/27/2018   3       
6/27/2018   3       
6/27/2018   3       
6/19/2018   3       
6/19/2018   3       
6/19/2018   3       
6/20/2018   3       
6/20/2018   3       
6/26/2018   3       
6/26/2018   3       
6/26/2018   3       
6/27/2018   3   6/27/2018 13:20 6/28/2018 11:35
6/27/2018   3       
6/27/2018   3       
6/27/2018   3       
6/27/2018   3       
6/27/2018   3       
6/27/2018   3       
6/27/2018   3       
6/27/2018   3       
6/28/2018   3   6/28/2018 12:15 7/4/2018 10:50
6/28/2018   3       
6/28/2018   3       
6/28/2018   3       
6/28/2018   3   6/28/2018 15:22 7/4/2018 10:50
6/28/2018   3       
6/28/2018   3       
6/28/2018   3       
6/26/2018   3   6/26/2018 13:35 6/28/2018 11:35
6/26/2018   3       
6/26/2018   3       
6/26/2018   3       
6/27/2018   3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
6/27/2018   3       
6/27/2018   3       
6/28/2018   3   6/28/2018 12:15 7/4/2018 10:50
6/28/2018   3       
6/28/2018   3       
6/28/2018   3       
6/28/2018   3       
6/28/2018   3       
6/28/2018   3       
6/28/2018   3       
6/29/2018   3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
6/29/2018   3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/3/2018    3       
7/3/2018    3       
7/3/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/2/2018    3       
7/3/2018    3       
7/3/2018    3       
7/3/2018    3       
7/3/2018    3       
7/3/2018    3       
7/3/2018    3       
7/3/2018    3       
7/3/2018    3       
7/3/2018    3       
7/4/2018    3       
7/4/2018    3       
7/4/2018    3       
7/4/2018    3       
7/4/2018    3       
7/4/2018    3       
7/3/2018    3       
7/3/2018    3       
7/3/2018    3       
7/3/2018    3       
7/3/2018    3       
7/4/2018    3       
7/4/2018    3       
7/4/2018    3       
7/4/2018    3       
7/4/2018    3   7/4/2018 13:20  7/10/2018 11:15
7/4/2018    3       
7/5/2018    3       
7/5/2018    3       
7/5/2018    3       
7/5/2018    3       
7/5/2018    3       
7/5/2018    3       
7/5/2018    3       
7/9/2018    3       
7/9/2018    3       
7/5/2018    3       
7/5/2018    3       
7/5/2018    3       
7/5/2018    3       
7/5/2018    3       
7/9/2018    3       
7/9/2018    3       
7/9/2018    3       
7/9/2018    3       
7/9/2018    3       
7/10/2018   3       
7/10/2018   3       
7/10/2018   3       
7/11/2018   3       
7/11/2018   3       
7/5/2018    3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/9/2018    3       
7/9/2018    3       
7/9/2018    3       
7/10/2018   3       
7/10/2018   3       
7/10/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/11/2018   3       
7/12/2018   3       
7/12/2018   3       
7/12/2018   3       
7/12/2018   3       
7/12/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/18/2018   3       
7/18/2018   3       
7/18/2018   3       
7/18/2018   3       
7/20/2018   3       
7/20/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/13/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/18/2018   3       
7/20/2018   3       
7/20/2018   3       
7/20/2018   3       
7/20/2018   3       
7/20/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/17/2018   3       
7/18/2018   3       
7/18/2018   3       
7/18/2018   3       
7/23/2018   3       
7/23/2018   3   7/23/2018 15:10 7/24/2018 13:00
7/23/2018   3       
7/18/2018   3   7/18/2018 14:30 7/24/2018 13:00
7/18/2018   3       
7/18/2018   3       
7/18/2018   3       
7/20/2018   3       
7/20/2018   3       
7/20/2018   3       
7/20/2018   3       
7/20/2018   3       
7/20/2018   3       
7/23/2018   3       
7/23/2018   3       
7/23/2018   3       
7/23/2018   3       
7/23/2018   3       
7/23/2018   3       
7/23/2018   3       
7/23/2018   3   7/23/2018 15:10 7/24/2018 13:00
7/24/2018   3   7/24/2018 14:00 
7/24/2018   3       
7/23/2018   3       
7/23/2018   3       
7/23/2018   3   7/23/2018 15:10 7/24/2018 13:00
7/23/2018   3   7/23/2018 15:10 7/24/2018 13:00
7/23/2018   3   7/23/2018 15:10 7/24/2018 13:00
7/23/2018   3       
7/25/2018   3       
7/24/2018   3       
7/25/2018   3       
7/24/2018   3       
7/24/2018   3       
7/24/2018   3   7/24/2018 14:00 
7/24/2018   3       
7/24/2018   3       
7/25/2018   3   7/25/2018 15:00 
7/25/2018   3       
7/25/2018   3       
7/25/2018   3       
7/25/2018   3       
7/26/2018   3       
7/24/2018   3       
7/25/2018   3       
7/24/2018   3       
7/25/2018   3       
7/24/2018   3       
7/25/2018   3       
7/24/2018   3       
7/25/2018   3       
7/25/2018   3       
7/25/2018   3       
7/26/2018   3       
7/23/2018   3       
7/26/2018   3       
7/27/2018   3       
7/27/2018   3       
7/27/2018   3       
7/27/2018   3       
7/23/2018   3       
7/23/2018   3       
7/23/2018   3       
7/23/2018   3       
7/27/2018   3       
7/27/2018   3       
7/27/2018   3       
7/27/2018   3       
7/27/2018   3       
7/27/2018   3       
7/25/2018   3       
7/26/2018   3       
7/26/2018   3       
7/26/2018   3       
7/27/2018   3       
7/27/2018   3

would you be able to post the sample data? and you may want to look at [this post](https://stackoverflow.com/questions/15570099/pandas-pivot-tables-row-subtotals) — MattR, Jan 10 '19 at 21:03
@MattR, sample data are already in the code here: df2 = pd.read_csv("https://www.dropbox.com/s/90y07129zn351z9/test_data.csv?dl=1",encoding="latin-1") — MGB.py, Jan 10 '19 at 21:05
@MattR, I already had read that podt you shared and thought not applicable to my case.Thanks — MGB.py, Jan 10 '19 at 21:08
the reason why you will want to add sample data instead of dropbox is two fold. Some people do not want to go to external sites to help answer your question. Also, if that link breaks in the future, those who come to this post won't be able to follow along. I suggest you add the sample data and read [this post](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) on creating good pandas examples. The easier you make it for those to help you, the more help you may receive :) — MattR, Jan 10 '19 at 21:23
@MattR, thanks for your explanation and clarification. Now, I understand the reasons. Please, check again, I have updated my question. — MGB.py, Jan 10 '19 at 22:28
The reason why row totals aren't show is because you have two single columns for two separate "values". — Scott Boston, Jan 10 '19 at 22:38
You could do this: `df2.dropna(axis=0, how='all', subset=['received', 'sent'])\ .pivot_table(index='site', values=['received','sent'],\ aggfunc='count', margins=True, dropna=False).eval('Total = received + sent')` — Scott Boston, Jan 10 '19 at 22:39

score 1 · Answer 1 · answered Jan 10 '19 at 22:40

1

Try this, using eval:

df2.dropna(axis=0, how='all', subset=['received', 'sent'])\
    .pivot_table(index='site', values=['received','sent'],\
    aggfunc='count', margins=True, dropna=False).eval('Total = received + sent')

Output:

      received  sent  Total
site                       
2           32    27     59
3           20    17     37
4           33    31     64
5           40    31     71
All        125   106    231

answered Jan 10 '19 at 22:40

Scott Boston

147,308
15
139
187

Thanks but doesnt work for me. 8 pvt_all = df2.dropna(axis=0, how='all', subset=['received', 'sent']) .pivot_table(index='site', values=['received','sent'], aggfunc='count', margins=True, dropna=False).eval('Total = received + sent') ----> 9 pvt_all['to_send']= pvt_all['received']-pvt_all['sent'] 10 pvt_all=pvt_all[['received','sent','to_send']] 11 pvt_all TypeError: 'NoneType' object is not subscriptable – MGB.py Jan 10 '19 at 23:07

score 0 · Accepted Answer · answered Jan 18 '19 at 23:39

df2 = pd.read_csv("https://www.dropbox.com/s/90y07129zn351z9/test_data.csv?dl=1",encoding="latin-1")

df2['received'] = pd.to_datetime(df2['received'])
df2['sent'] = pd.to_datetime(df2['sent'])

import pandas as pd
pvt_all = df2.dropna(axis=0, how='all', subset=['received', 'sent'])\
    .pivot_table(index=['site'], values=['received','sent'],\
    aggfunc='count', margins=True, dropna=False)
pvt_all['to_send']= pvt_all['received']-pvt_all['sent'] 
#pvt_all['Total'] = pvt_all.received + pvt_all.sent + pvt_all.to_send
pvt_all['Total'] = pvt_all.sum(axis=1) #Add Total column for row subtotal \
#and Total
pvt_all=pvt_all[['received','sent','to_send','Total']] 
pvt_all


received    sent    to_send Total
site                
2   32.0    27.0    5.0 64.0
3   20.0    17.0    3.0 40.0
4   33.0    31.0    2.0 66.0
5   40.0    31.0    9.0 80.0
All 125.0   106.0   19.0    250.0

Adding row and column total to pivot table fails

2 Answers2