Resampling a dataframe into a new one while doing some additional operations

user_11664529 0
Viewed 979

I am working with a dataframe where each entry (row) comes with a start time, a duration and other attributes. I would like to create a new dataframe from this one where I would sort of transform each entry from the original one into 15 minutes intervals while keeping all other attributes the same. The amount of entries in the new dataframe per entry in the old one would depend on the actual duration of the original one.

At first I tried using pd.resample but it did not do exactly what I expected. I then constructed a function using itertuples() that works quite well but it took about half an hour with a dataframe of around 3000 rows. Now I want to do the same for 2 million rows so I am looking for other possibilities.

Let's say I have the following dataframe:

testdict = {'start':['2018-01-05 11:48:00', '2018-05-04 09:05:00', '2018-08-09 07:15:00', '2018-09-27 15:00:00'], 'duration':[22,8,35,2], 'Attribute_A':['abc', 'def', 'hij', 'klm'], 'id': [1,2,3,4]}
testdf = pd.DataFrame(testdict)
testdf.loc[:,['start']] = pd.to_datetime(testdf['start'])
print(testdf)

>>>testdf
                 start  duration Attribute_A  id
0  2018-01-05 11:48:00        22         abc   1
1  2018-05-04 09:05:00         8         def   2
2  2018-08-09 07:15:00        35         hij   3
3  2018-09-27 15:00:00         2         klm   4

And I would like my outcome to be like the following:

>>>resultdf
                start  duration Attribute_A  id
0 2018-01-05 11:45:00        12         abc   1
1 2018-01-05 12:00:00        10         abc   1
2 2018-05-04 09:00:00         8         def   2
3 2018-08-09 07:15:00        15         hij   3
4 2018-08-09 07:30:00        15         hij   3
5 2018-08-09 07:45:00         5         hij   3
6 2018-09-27 15:00:00         2         klm   4

This is the function that I built with itertuples which produced the desired result (the one I showed just above this):

def min15_divider(df,newdf):
for row in df.itertuples():
    orig_min = row.start.minute
    remains = orig_min % 15 # Check if it is already a multiple of 15
    if remains == 0:
        new_time = row.start.replace(second=0)
        if row.duration < 15: # if it shorter than 15 min just use that for the duration
            to_append = {'start': new_time, 'Attribute_A': row.Attribute_A,
                         'duration': row.duration, 'id':row.id}
            newdf = newdf.append(to_append, ignore_index=True)
        else: # if not, divide that in 15 min intervals until duration is exceeded
            cumu_dur = 15
            while cumu_dur < row.duration:
                to_append = {'start': new_time, 'Attribute_A': row.Attribute_A, 'id':row.id}
                if cumu_dur < 15:
                    to_append['duration'] = cumu_dur
                else:
                    to_append['duration'] = 15
                new_time = new_time + pd.Timedelta('15 minutes')
                cumu_dur = cumu_dur + 15
                newdf = newdf.append(to_append, ignore_index=True)

            else: # add the remainder in the last 15 min interval
                final_dur = row.duration - (cumu_dur - 15)
                to_append = {'start': new_time, 'Attribute_A': row.Attribute_A,'duration': final_dur, 'id':row.id}
                newdf = newdf.append(to_append, ignore_index=True)

    else: # When it is not an exact multiple of 15 min
        new_min = orig_min - remains # convert to multiple of 15
        new_time = row.start.replace(minute=new_min)
        new_time = new_time.replace(second=0)
        cumu_dur = 15 - remains # remaining minutes in the initial interval
        while cumu_dur < row.duration: # divide total in 15 min intervals until duration is exceeded
            to_append = {'start': new_time, 'Attribute_A': row.Attribute_A, 'id':row.id}
            if cumu_dur < 15:
                to_append['duration'] = cumu_dur
            else:
                to_append['duration'] = 15

            new_time = new_time + pd.Timedelta('15 minutes')
            cumu_dur = cumu_dur + 15
            newdf = newdf.append(to_append, ignore_index=True)

        else: # when we reach the last interval or the starting duration was less than the remaining minutes
            if row.duration < 15:
                final_dur = row.duration # original duration less than remaining minutes in first interval
            else:
                final_dur = row.duration - (cumu_dur - 15) # remaining duration in last interval
            to_append = {'start': new_time, 'Attribute_A': row.Attribute_A, 'duration': final_dur, 'id':row.id}
            newdf = newdf.append(to_append, ignore_index=True)
return newdf

Is there any other way to do this without using itertuples that could save me some time?

Thanks in advance.

PS. I apologize for anything that may seem a bit weird in my post as it is the first time that I have asked a question myself here in stackoverflow.

EDIT

Many entries can have the same starting time, so .groupby 'start' could be problematic. There is, however, a column with unique values for each entry called simply "id".

pandas time-series python
2 Answers

Using pd.resample is a good idea, but since you have only the starting time each row, you need to build the end row before you can use it.

The code below assumes that each starting time in 'start' column is unique, so that grouby can be used in a bit unusual way, since it will extract only one row.
I use groupby because it will automatically regroups the dataframes produced by the custom function used by apply.
Note also that the column 'duration' is converted to timedelta in minutes in order to better perform some math later.

import pandas as pd

testdict = {'start':['2018-01-05 11:48:00', '2018-05-04 09:05:00', '2018-08-09 07:15:00', '2018-09-27 15:00:00'], 'duration':[22,8,35,2], 'Attribute_A':['abc', 'def', 'hij', 'klm']}
testdf = pd.DataFrame(testdict)
testdf['start'] = pd.to_datetime(testdf['start'])
testdf['duration'] = pd.to_timedelta(testdf['duration'], 'T')
print(testdf)

def calcduration(df, starttime):
    if len(df) == 1:
        return
    elif len(df) == 2:
        df['duration'].iloc[0] = pd.Timedelta(15, 'T') - (starttime - df.index[0])
        df['duration'].iloc[1] = df['duration'].iloc[1] - df['duration'].iloc[0]
    elif len(df) > 2:
        df['duration'].iloc[0] = pd.Timedelta(15, 'T') - (starttime - df.index[0])
        df['duration'].iloc[1:-1] = pd.Timedelta(15, 'T')
        df['duration'].iloc[-1] = df['duration'].iloc[-1] - df['duration'].iloc[:-1].sum()

def expandtime(x):
    frow = x.copy()
    frow['start'] = frow['start'] + frow['duration']
    gdf = pd.concat([x, frow], axis=0)
    gdf = gdf.set_index('start')
    resdf = gdf.resample('15T').nearest()
    calcduration(resdf, x['start'].iloc[0])
    return resdf

findf = testdf.groupby('start', as_index=False).apply(expandtime)
print(findf)

This code produces:

                      duration Attribute_A
  start                                   
0 2018-01-05 11:45:00 00:12:00         abc
  2018-01-05 12:00:00 00:10:00         abc
1 2018-05-04 09:00:00 00:08:00         def
2 2018-08-09 07:15:00 00:15:00         hij
  2018-08-09 07:30:00 00:15:00         hij
  2018-08-09 07:45:00 00:05:00         hij
3 2018-09-27 15:00:00 00:02:00         klm

A bit of explanation

expandtime is the first custom function. It takes a dataframe of one row (because we assume that 'start' values are uniques), builds a second row whose 'start' is equal to 'start' of the first row + duration and then uses resample to sample it in time intervals of 15 minutes. Values of all other columns are duplicated.

calcduration is used to do some math on the column 'duration' in order to calculate the correct duration of each row.

user_104260370

So, starting with your df:

testdict = {'start':['2018-01-05 11:48:00', '2018-05-04 09:05:00', '2018-08-09 07:15:00', '2018-09-27 15:00:00'], 'duration':[22,8,35,2], 'Attribute_A':['abc', 'def', 'hij', 'klm']}
df = pd.DataFrame(testdict)
df.loc[:,['start']] = pd.to_datetime(df['start'])
print(df)

First calculate an ending time for each row:

df['dur'] = pd.to_timedelta(df['duration'], unit='m')
df['end'] = df['start'] + df['dur']

Then create two new columns that hold the regular interval (15 minute) start and end dates:

df['start15'] = df['start'].dt.floor('15min')
df['end15'] = df['end'].dt.floor('15min')

At this point, the dataframe looks like:

  Attribute_A  duration               start      dur                 end start15               end15
0         abc        22 2018-01-05 11:48:00 00:22:00 2018-01-05 12:10:00 2018-01-05 11:45:00 2018-01-05 12:00:00  
1         def         8 2018-05-04 09:05:00 00:08:00 2018-05-04 09:13:00 2018-05-04 09:00:00 2018-05-04 09:00:00     
2         hij        35 2018-08-09 07:15:00 00:35:00 2018-08-09 07:50:00 2018-08-09 07:15:00 2018-08-09 07:45:00   
3         klm         2 2018-09-27 15:00:00 00:02:00 2018-09-27 15:02:00 2018-09-27 15:00:00 2018-09-27 15:00:00

The start15 and end15 columns combine to have the right times, but you need to merge them:

df = pd.melt(df, ['dur', 'start', 'Attribute_A', 'end'], ['start15', 'end15'], value_name='start15')
df = df.drop('variable', 1).drop_duplicates('start15').sort_values('start15').set_index('start15')

Output:

                         dur               start Attribute_A
start15                                                     
2018-01-05 11:45:00 00:22:00 2018-01-05 11:48:00         abc
2018-01-05 12:00:00 00:22:00 2018-01-05 11:48:00         abc
2018-05-04 09:00:00 00:08:00 2018-05-04 09:05:00         def
2018-08-09 07:15:00 00:35:00 2018-08-09 07:15:00         hij
2018-08-09 07:45:00 00:35:00 2018-08-09 07:15:00         hij
2018-09-27 15:00:00 00:02:00 2018-09-27 15:00:00         klm

Looking good, but the 2018-08-09 07:30:00 row is missing. Fill in this and any other missing rows with groupby and resample:

df = df.groupby('start').resample('15min').ffill().reset_index(0, drop=True).reset_index()

Get the end15 column back, it was dropped during the melt operation earlier:

df['end15'] = df['end'].dt.floor('15min')

Then calculate the correct durations for each row. I split this into two calculations (durations that spread across multiple timesteps, and ones that don't) to keep it readable:

df.loc[df['start15'] != df['end15'], 'duration'] = np.minimum(df['end15'] - df['start'], pd.Timedelta('15min').to_timedelta64())
df.loc[df['start15'] == df['end15'], 'duration'] = np.minimum(df['end'] - df['end15'], df['end'] - df['start'])

Then just some clean-up to make it look like you wanted:

df['duration'] = (df['duration'].dt.seconds/60).astype(int)
print(df)
df = df[['start15', 'duration', 'Attribute_A']].copy()

Result:

              start15  duration Attribute_A
0 2018-01-05 11:45:00        12         abc
1 2018-01-05 12:00:00        10         abc
2 2018-05-04 09:00:00         8         def
3 2018-08-09 07:15:00        15         hij
4 2018-08-09 07:30:00        15         hij
5 2018-08-09 07:45:00         5         hij
6 2018-09-27 15:00:00         2         klm

Please note, portions of this answer were based on this answer

user_59382900
Related