CODEWITHSUNDEEP
pythonpandasdataframetime-seriespandas-groupby
user97662
user97662

Reputation: 970

groupby timeseries fill missing data with 0

Given a panda timeseries dataframe grouped by 'UUT'

df
Out[64]: 
                        UUT  Sum
Date_Time                       
2017-04-28 18:48:16  uut-01    2
2017-04-28 18:48:18  uut-02    2
2017-04-28 18:48:19  uut-03    2

I want to use reindex to create a time series in 1 second interval, and fill in the gaps with 0 value for column Sum only, similar to what's showing below:

df
Out[64]: 
                        UUT  Sum
Date_Time                       
2017-04-28 18:48:16  uut-01    2
2017-04-28 18:48:16  uut-02    0
2017-04-28 18:48:16  uut-03    0
2017-04-28 18:48:17  uut-01    2
2017-04-28 18:48:17  uut-02    0
2017-04-28 18:48:17  uut-03    0
2017-04-28 18:48:18  uut-01    0
2017-04-28 18:48:18  uut-02    2
2017-04-28 18:48:18  uut-03    0
2017-04-28 18:48:19  uut-01    0
2017-04-28 18:48:19  uut-02    0
2017-04-28 18:48:19  uut-03    2

I used reindex, but it filled both 'UUT' and 'Sum' with zeros. How do I fill the missing time stamp for UUT column with uut names instead of zeros, and fill zeros to 'Sum' column only?

idx = pd.date_range('2017-04-28 18:48:16', '2017-04-28 18:48:19', freq='1s')    
grouped = df.groupby('UUT')
grouped.get_group('uut-01').reindex(idx, fill_value=0)

grouped.get_group('uut-01')

2017-04-28 18:48:16  uut-01    2
2017-04-28 18:48:17  0         0
2017-04-28 18:48:18  0         0
2017-04-28 18:48:19  0         0

Based on Kyle's answer, I got it to work finally:

df = df.set_index([df.index, 'UUT'])
idx = pd.MultiIndex.from_product(df.index.levels, names=['Date_Time', 'UUT'])
df = df.reindex(index=idx, fill_value=0)
df.reset_index(level=[1]) #convert back to single index
grouped = df.groupby('UUT')

Upvotes: 0

Views: 690

Answers (1)

Kyle
Kyle

Reputation: 2904

df = df.set_index(['time', 'uut'])
idx = pd.MultiIndex.from_product([df.index, df.uut])
df.reindex(index=idx, fill_value=0)

                   sum
18:48:16 uut-01    2
         uut-02    0
         uut-03    0
18:48:18 uut-01    0
         uut-02    2
         uut-03    0
18:48:19 uut-01    0
         uut-02    0
         uut-03    2

Upvotes: 1

Related Questions