Calculate Mean Sales Per Minute Accross 24-hour Cycles As Per Local-time (HH:MM)
In this example we have two days of data sampled at a resolution of 1min, giving us 2880 measurements. The measurements are collected across multiple timezones sequentially: the fi
Solution 1:
Here is an approach to get what I think you want. This requires pandas 0.17.0
Create the data as you have aboe
import pandas as pd
import numpy as np
pd.options.display.max_rows=12
np.random.seed(1234)
df=pd.DataFrame(index=pd.DatetimeIndex(pd.date_range('2015-03-29 00:00','2015-03-30 23:59',freq='1min',tz='UTC')))
df.loc['2015-03-29 00:00':'2015-03-29 04:00','timezone']='Europe/London'
df.loc['2015-03-29 04:00':'2015-03-30 23:59','timezone']='America/Los_Angeles'
df['sales1']=np.random.random_integers(100,size=len(df))
df['sales2']=np.random.random_integers(10,size=len(df))
In [79]: df
Out[79]:
timezone sales1 sales2
2015-03-29 00:00:00+00:00 Europe/London 48 6
2015-03-29 00:01:00+00:00 Europe/London 84 1
2015-03-29 00:02:00+00:00 Europe/London 39 1
2015-03-29 00:03:00+00:00 Europe/London 54 10
2015-03-29 00:04:00+00:00 Europe/London 77 5
2015-03-29 00:05:00+00:00 Europe/London 25 9
... ... ... ...
2015-03-30 23:54:00+00:00 America/Los_Angeles 77 8
2015-03-30 23:55:00+00:00 America/Los_Angeles 16 4
2015-03-30 23:56:00+00:00 America/Los_Angeles 55 3
2015-03-30 23:57:00+00:00 America/Los_Angeles 18 1
2015-03-30 23:58:00+00:00 America/Los_Angeles 3 2
2015-03-30 23:59:00+00:00 America/Los_Angeles 52 2
[2880 rows x 3 columns]
Pivot according to the timezone; this creates a multi-index with the timezone separated
x = pd.pivot_table(df.reset_index(),values=['sales1','sales2'],index='index',columns='timezone').swaplevel(0,1,axis=1)
x.columns.names = ['timezone','sales']
In [82]: x
Out[82]:
timezone America/Los_Angeles Europe/London America/Los_Angeles Europe/London
sales sales1 sales1 sales2 sales2
index
2015-03-29 00:00:00+00:00 NaN 48 NaN 6
2015-03-29 00:01:00+00:00 NaN 84 NaN 1
2015-03-29 00:02:00+00:00 NaN 39 NaN 1
2015-03-29 00:03:00+00:00 NaN 54 NaN 10
2015-03-29 00:04:00+00:00 NaN 77 NaN 5
2015-03-29 00:05:00+00:00 NaN 25 NaN 9
... ... ... ... ...
2015-03-30 23:54:00+00:00 77 NaN 8 NaN
2015-03-30 23:55:00+00:00 16 NaN 4 NaN
2015-03-30 23:56:00+00:00 55 NaN 3 NaN
2015-03-30 23:57:00+00:00 18 NaN 1 NaN
2015-03-30 23:58:00+00:00 3 NaN 2 NaN
2015-03-30 23:59:00+00:00 52 NaN 2 NaN
[2880 rows x 4 columns]
Create the groupers that we want to use, namely hours and minutes in the local zone. We are going to populate them according to the mask, IOW. where both sales1/sales2 are notnull, we will use the hours/minutes for that (local) zone
hours = pd.Series(index=x.index)
minutes = pd.Series(index=x.index)
for tz in ['America/Los_Angeles', 'Europe/London' ]:
local = df.index.tz_convert(tz)
x[(tz,'tz')] = local
mask = x[(tz,'sales1')].notnull() & x[(tz,'sales2')].notnull()
hours.iloc[mask.values] = local.hour[mask.values]
minutes.iloc[mask.values] = local.minute[mask.values]
x = x.sortlevel(axis=1)
After the above. (Note this could be a bit simplified, meaning that we don't need to actually record the local timezone, just compute hours/minutes).
Out[84]:
timezone America/Los_Angeles Europe/London
sales sales1 sales2 tz sales1 sales2 tz
index
2015-03-29 00:00:00+00:00 NaN NaN 2015-03-28 17:00:00-07:00 48 6 2015-03-29 00:00:00+00:00
2015-03-29 00:01:00+00:00 NaN NaN 2015-03-28 17:01:00-07:00 84 1 2015-03-29 00:01:00+00:00
2015-03-29 00:02:00+00:00 NaN NaN 2015-03-28 17:02:00-07:00 39 1 2015-03-29 00:02:00+00:00
2015-03-29 00:03:00+00:00 NaN NaN 2015-03-28 17:03:00-07:00 54 10 2015-03-29 00:03:00+00:00
2015-03-29 00:04:00+00:00 NaN NaN 2015-03-28 17:04:00-07:00 77 5 2015-03-29 00:04:00+00:00
2015-03-29 00:05:00+00:00 NaN NaN 2015-03-28 17:05:00-07:00 25 9 2015-03-29 00:05:00+00:00
... ... ... ... ... ... ...
2015-03-30 23:54:00+00:00 77 8 2015-03-30 16:54:00-07:00 NaN NaN 2015-03-31 00:54:00+01:00
2015-03-30 23:55:00+00:00 16 4 2015-03-30 16:55:00-07:00 NaN NaN 2015-03-31 00:55:00+01:00
2015-03-30 23:56:00+00:00 55 3 2015-03-30 16:56:00-07:00 NaN NaN 2015-03-31 00:56:00+01:00
2015-03-30 23:57:00+00:00 18 1 2015-03-30 16:57:00-07:00 NaN NaN 2015-03-31 00:57:00+01:00
2015-03-30 23:58:00+00:00 3 2 2015-03-30 16:58:00-07:00 NaN NaN 2015-03-31 00:58:00+01:00
2015-03-30 23:59:00+00:00 52 2 2015-03-30 16:59:00-07:00 NaN NaN 2015-03-31 00:59:00+01:00
[2880 rows x 6 columns]
This uses the new representation for timezones (in 0.17.0).
In [85]: x.dtypes
Out[85]:
timezone sales
America/Los_Angeles sales1 float64
sales2 float64
tz datetime64[ns, America/Los_Angeles]
Europe/London sales1 float64
sales2 float64
tz datetime64[ns, Europe/London]
dtype: object
Results
x.groupby([hours,minutes]).mean()
timezone America/Los_Angeles Europe/London
sales sales1 sales2 sales1 sales2
0 0 62.5 5.5 48 6
1 52.0 7.0 84 1
2 89.0 3.5 39 1
3 67.5 6.5 54 10
4 41.0 5.5 77 5
5 81.0 5.5 25 9
... ... ... ... ...
23 54 76.5 4.5 NaN NaN
55 37.5 5.0 NaN NaN
56 60.5 8.0 NaN NaN
57 87.5 7.0 NaN NaN
58 77.5 6.0 NaN NaN
59 31.0 5.5 NaN NaN
[1440 rows x 4 columns]
Post a Comment for "Calculate Mean Sales Per Minute Accross 24-hour Cycles As Per Local-time (HH:MM)"