Complete missing data in timeseries pandas

Question

I collected data using a smartphone. The phone could run out of battery or the collection software could fail, leading to missing data.

For this example let's say I already compute the number of calls per day calls_per_day from the smartphone data. Besides the days where I actually call someone, I want to consider the days where I did not. To do this I have to consider the time where the phone was sensing and assign a zero to that day if there were no calls. In order to know if the phone was sensing, I have a reference sensor that records a tick every 25 seconds: sensed_time.

The code below does what I just described. First, it groups sensed_time by the minute, hour, and day, labelling a day as 'sensed' if it has collected at least 80% minutes of at least 80% of each of the 24 hours of a day. Then it just filters the days from calls_per_day that are labeled as sensed and replaces NaN values with 0.

import numpy as np
def count_per_minute(group):
    if group[group.columns[0]].count() == 0:
        return pd.Series({'count_per_minute':np.nan})
    else:
        return pd.Series({'count_per_minute':group[group.columns[0]].count()})

def label_sensed_days(group, hours_in_day, percentage_valid_hours, percentage_valid_minutes):
    sensed_hours_count = group['sensed_minutes'].loc[group['sensed_minutes'] > 60 * percentage_valid_minutes].count()

    if  sensed_hours_count > 24 * percentage_valid_hours:
        return pd.Series({'sensed_day':True, 'sensed_hours':sensed_hours_count})
    else:
        return pd.Series({'sensed_day':False, 'sensed_hours':sensed_hours_count})

# Create fake DF with the timestamps where the phone was sensing
index = pd.date_range("2018-01-01", "2018-01-03 23:59", freq='25S')
sensed_time = pd.DataFrame(index=index, columns=['was_sensed'])
sensed_time = sensed_time.fillna(1)
sensed_time = sensed_time.sample(frac=0.6)

# Count records sensed per minute, return nan if count == 0
sensed_minutes = sensed_time.groupby(pd.Grouper(freq='1Min')).apply(count_per_minute)

# Complete missing minutes
sensed_minutes = sensed_minutes.reindex(pd.date_range(sensed_time.index.min().date(), sensed_time.index.max().date() + pd.DateOffset(1), freq='1Min'))

# Group sensed minutes by hour
sensed_hours = sensed_minutes.groupby([pd.Grouper(freq='1H')]).count()
sensed_hours = sensed_hours.rename(columns={'count_per_minute':'sensed_minutes'})

# Group sensed hours per day but only consider a valid day the ones where at least 0.8 percent of 24 hours were sensed with at least 0.8 percent of minutes
sensed_days = sensed_hours.groupby([pd.Grouper(freq='1D')]).apply(label_sensed_days, hours_in_day=24, percentage_valid_hours= 0.8, percentage_valid_minutes=0.8)

# Create fake DF with the number of calls on every other day
index = pd.date_range("2018-01-01", "2018-01-05", freq='2D')
calls_per_day = pd.DataFrame([10,5,8], index=index, columns=['calls'])

# Only keep the days that we consider valid sensed days
calls_per_day = calls_per_day.reindex(sensed_days[sensed_days['sensed_day'] == True].index)

# All the NaN values mean that the phone was sensing but we didn't record a call, thus there were 0 calls.
calls_per_day = calls_per_day.fillna(0)

display(calls_per_day)

Answer 1

Don't forget you can chain operations (btw you missed the pandas import):

sensed_time = pd.DataFrame(index=index, columns=['was_sensed'])
sensed_time = sensed_time.fillna(1)
sensed_time = sensed_time.sample(frac=0.6)

x = pd.DataFrame(index=index, columns=['was_sensed']).fillna(1).sample(frac=0.6)

You can verify if this works by doing a sensed_time.shape == x.shape

Regarding the construction of your code, it seems like it would be a better idea to have the data object as a class, and get properties from it.

For instance, something along the lines of:

    class SmartPhoneData(object):
        def __init__(self, name=None):
            self.name = name or "Nokia6_32GB"
            self.data = pd.DataFrame(index=index, columns=['was_sensed']).fillna(1).sample(frac=0.6)

        def calls_per_day(self):
            print(f"SmartPhone Data: {self.data}")

        @property
        def minutes(self):
            return self.data.reindex(pd.date_range(sensed_time.index.min().date(), sensed_time.index.max().date() + pd.DateOffset(1), freq='1Min'))

        @property
        def hours(self):
            return self.data.groupby([pd.Grouper(freq='1H')]).count()

        @property
        def days(self):
            return self.data.groupby([pd.Grouper(freq='1D')]).apply(label_sensed_days, hours_in_day=24, percentage_valid_hours=0.8, percentage_valid_minutes=0.8)


    your_phone = SmartPhoneData()
    my_phone = SmartPhoneData("iPhone X")

    your_phone.calls_per_day()
    my_phone.calls_per_day()

Obviously I just took a single line from your code where it was similarly named (to match the function).
So if you do this - make sure you know how the data gets changed when you run each function. For instance, each of the @properties just does a return and doesn't modify the DataFrame, however some of your options might actually change the data contents (even if there is no assignment (e.g.: x = a + b doesn't change a or b, but a (not real code) self.data(column_names = ["one", "two"]) will change it). I hope that made sense?

Good Luck!