CODEWITHSUNDEEP
python-3.xpandas
Sean Nielsen
Sean Nielsen

Reputation: 53

How do I get the daily average of measurements in pandas

I want to get the mean of several columns in pandas over time, so if I have this data:

Time                      Country        Server                Load                  
2011-01-01 00:00:00       USA            DNS                   50
2011-01-01 00:15:00       USA            HTTP                  60
2011-01-01 00:37:00       Spain          HTTP                  20
2011-01-01 01:02:00       Spain          DNS                   30
2011-01-01 01:11:00       Italy          DNS                   70
2011-01-01 23:49:00       Italy          File                  15
2011-01-02 00:00:00       USA            File                  74
2011-01-02 00:49:00       Italy          AD                    12
2011-01-02 00:31:00       Italy          AD                    11
2011-01-02 01:13:00       USA            AD                    17
2011-01-02 01:19:00       Spain          File                  18
2011-01-02 23:10:00       Spain          HTTP                  90

This is what I want to output

Country                 2011-01-01 - Mean           2011-01-02 - Mean
USA                        55                          45.5
Spain                      25                          54
Italy                      42.5                        11.5  
...

and for server

Server                 2011-01-01 - Mean           2011-01-02 - Mean
HTTP                        40                          90
DNS                         50                          NA
FILE                        15                          46  
AD                          NA                          13.3

Upvotes: 2

Views: 85

Answers (2)

Erfan
Erfan

Reputation: 42886

Use pivot_table with the mean per date by accessing the date element with dt.date:

piv1 = df.pivot_table(index='Country', columns=df['Time'].dt.date, values='Load')

Time     2011-01-01  2011-01-02
Country                        
Italy          42.5        11.5
Spain          25.0        54.0
USA            55.0        45.5

And for server:

piv2 = df.pivot_table(index='Server', columns=df['Time'].dt.date, values='Load')

Time    2011-01-01  2011-01-02
Server                        
AD             NaN   13.333333
DNS           50.0         NaN
File          15.0   46.000000
HTTP          40.0   90.000000

Upvotes: 1

jezrael
jezrael

Reputation: 862551

Use DataFrame.groupby with aggregate mean with Series.dt.date and reshape by Series.unstack:

df1 = df.groupby(['Country', df['Time'].dt.date])['Load'].mean().unstack()
print (df1)
Time     2011-01-01  2011-01-02
Country                        
Italy          42.5        11.5
Spain          25.0        54.0
USA            55.0        45.5

df2 = df.groupby(['Server', df['Time'].dt.date])['Load'].mean().unstack()
print (df2)
Time    2011-01-01  2011-01-02
Server                        
AD             NaN   13.333333
DNS           50.0         NaN
File          15.0   46.000000
HTTP          40.0   90.000000

Upvotes: 2

Related Questions