pythonpandasrowsumcolumn-sum

Adding row and column total to pivot table fails


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       

Solution

  • 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