Split up rows in DataFrame (hourly) based on another column

user_7373353 0
Viewed 162

I have a DataFrame consisting of charging sessions of electric vehicles with the following scheme and content:

PlugInTime, PlugOutTime, DeliveredEnergy, ChargeDuration                    

 - 2019-01-02 05:53:00, 2019-01-02 14:38:00, 36.0, 05:57:00
 - 2019-01-02 07:53:00, 2019-01-02 16:38:00, 8.0, 03:12:00
 - ...

I want to split the individual rows based on the column ChargeDuration so that for example row one is being "exploded" to:

 - 2019-01-02 05:53:00, 2019-01-02 14:38:00, 6.05, 05:57:00
 - 2019-01-02 06:53:00, 2019-01-02 14:38:00, 6.05, 05:57:00
 - 2019-01-02 07:53:00, 2019-01-02 14:38:00, 6.05, 05:57:00
 - 2019-01-02 08:53:00, 2019-01-02 14:38:00, 6.05, 05:57:00
 - 2019-01-02 09:53:00, 2019-01-02 14:38:00, 6.05, 05:57:00
 - 2019-01-02 10:53:00, 2019-01-02 14:38:00, 5.75, 05:57:00

As you can see, I want ChargeDuration to determine into how many chunks PlugInTime is being split up and I want the DeliveredEnergy to be split accordingly with linear interpolation, so that the sum equals the original value.

After I have that, I want to resample the DataFrame hourly to so that I can determine the hourly energy demand across all the recorded charging sessions.

In my research prior to this post I came across many threads about resampling in pandas, but they are missing this split up step based on another column (here ChargeDuration).

Any help is highly appreciated.

pandas dataframe datetime python
3 Answers

I've tried to build a similar solution using dummy data

import pandas as pd
import numpy as np

tst = pd.DataFrame([{'id':1, 'start': 1, 'duration': 7, 'work_rate': 100}, {'id': 2, 'start': 1, 'duration': 4, 'work_rate': 35}])
tst

    id  start   duration    work_rate
0   1   1       7           100
1   2   1       4           35

def create_list_for_splits(_id, start, duration, work_rate):
    start_end = list(range(start, start + duration))
    work_rate_split = [round(work_rate/duration, 2)]*duration
    
    res_ls = []
    
    for s, w in zip(start_end, work_rate_split):
        res_ls.append({'id': _id, 'start': s, 'work_rate': w})
    
    return res_ls

# Test the function

create_list_for_splits(1, 1, 7, 100)

[{'id': 1, 'start': 1, 'work_rate': 14.29},
 {'id': 1, 'start': 2, 'work_rate': 14.29},
 {'id': 1, 'start': 3, 'work_rate': 14.29},
 {'id': 1, 'start': 4, 'work_rate': 14.29},
 {'id': 1, 'start': 5, 'work_rate': 14.29},
 {'id': 1, 'start': 6, 'work_rate': 14.29},
 {'id': 1, 'start': 7, 'work_rate': 14.29}]

tst['work_split'] = tst.apply(lambda x: create_list_for_splits(x['id'], x['start'], x['duration'], x['work_rate']), axis=1)

pd.DataFrame([item for subilst in tst['work_split'].values for item in subilst])

    id  start   work_rate
0   1   1       14.29
1   1   2       14.29
2   1   3       14.29
3   1   4       14.29
4   1   5       14.29
5   1   6       14.29
6   1   7       14.29
7   2   1       8.75
8   2   2       8.75
9   2   3       8.75
10  2   4       8.75
user_78625580

Its not very pretty but it does the job. Would be great to see if there are more elegant solutions to this problem.

import pandas as pd

# create df
df = pd.DataFrame({'PlugInTime': ['2019-01-02 05:53:00', '2019-01-02 07:53:00'], 'PlugOutTime': ['2019-01-02 14:38:00', '2019-01-02 16:38:00'], 'DeliveredEnergy': [36.0, 8.0], 'ChargeDuration': ['05:57:00', '03:12:00'] })

# set dtypes
df['PlugInTime'] = pd.to_datetime(df.PlugInTime)
df['PlugOutTime'] = pd.to_datetime(df.PlugOutTime)
df['ChargeDuration'] = pd.to_timedelta(df.ChargeDuration)

# determine charge time in hours
df['hours'] = df.ChargeDuration.dt.seconds / 3600

# split the hours into a list
df['split'] = df.ChargeDuration.apply(lambda x: [1 for _ in range(int(x.total_seconds() // 3600))] + [(x.total_seconds() % 3600 / 3600 or None)])

# explode the list
df = df.explode('split')

# calculate the energy per hour
df['DeliveredEnergy'] = df.DeliveredEnergy / df.hours * df.split

# update PlugInTime
df['PlugInTime'] = df.PlugInTime + pd.to_timedelta(df.PlugInTime.groupby(level=0).cumcount(), unit='h')

print(df)

           PlugInTime         PlugOutTime DeliveredEnergy ChargeDuration  hours split
0 2019-01-02 05:53:00 2019-01-02 14:38:00         6.05042       05:57:00   5.95     1
0 2019-01-02 06:53:00 2019-01-02 14:38:00         6.05042       05:57:00   5.95     1
0 2019-01-02 07:53:00 2019-01-02 14:38:00         6.05042       05:57:00   5.95     1
0 2019-01-02 08:53:00 2019-01-02 14:38:00         6.05042       05:57:00   5.95     1
0 2019-01-02 09:53:00 2019-01-02 14:38:00         6.05042       05:57:00   5.95     1
0 2019-01-02 10:53:00 2019-01-02 14:38:00          5.7479       05:57:00   5.95  0.95
1 2019-01-02 07:53:00 2019-01-02 16:38:00             2.5       03:12:00   3.20     1
1 2019-01-02 08:53:00 2019-01-02 16:38:00             2.5       03:12:00   3.20     1
1 2019-01-02 09:53:00 2019-01-02 16:38:00             2.5       03:12:00   3.20     1
1 2019-01-02 10:53:00 2019-01-02 16:38:00             0.5       03:12:00   3.20   0.2
user_47003270

Try this. Used a naive method for some things, if you want to change feel free to do so:

import datetime
import time
import pandas as pd

def func(PlugInTime, PlugOutTime, DeliveredEnergy, ChargeDuration):

    # inTime calculation
    inTime = str(PlugInTime).split(' ')
    d = inTime[0].split('-')
    t = inTime[1].split(':')
    Timein = int(datetime.datetime(int(d[0]), int(d[1]), int(d[2]), int(t[0]), int(t[1]), int(t[2])).strftime('%s'))

    # outTime calculation
    outTime = str(PlugOutTime).split(' ')
    d = outTime[0].split('-')
    t = outTime[1].split(':')
    Timeout = int(datetime.datetime(int(d[0]), int(d[1]), int(d[2]), int(t[0]), int(t[1]), int(t[2])).strftime('%s'))

    # energy
    energy = float(DeliveredEnergy)

    # charge
    h, m, s = str(ChargeDuration).split(':')
    charge = int(h) * 3600 + int(m) * 60 + int(s)
    val = charge

    # hourly
    add = 3600

    # chargeval
    if add < val:
        chargeval = (add/val)*energy


    list_ = []
    while charge - add >= 0:
        Timein += add
        t_ = time.localtime(Timein)
        lt = [time.strftime("%Y-%m-%d %H:%M:%S", t_), PlugOutTime, chargeval, ChargeDuration]
        list_.append(lt)
        charge -= add

    if charge:
        t_ = time.localtime(Timein+charge)
        lt = [time.strftime("%Y-%m-%d %H:%M:%S", t_), PlugOutTime, (charge/val) * energy, ChargeDuration]
        list_.append(lt)

    return list_


if __name__ == '__main__':

    l = [['2019-01-02 05:53:00', '2019-01-02 14:38:00', '36.0', '05:57:00']]

    # input dataframe
    df = pd.DataFrame(l, columns = ['PlugInTime', 'PlugOutTime', 'DeliveredEnergy', 'ChargeDuration'])

    list_ = []
    for ind, row in df.iterrows():
        l = func(row['PlugInTime'], row['PlugOutTime'], row['DeliveredEnergy'], row['ChargeDuration'])
        list_.extend(l)

        # printing the output for a row
        for i in l:
            print(i)

    # output dataframe
    df_out = pd.DataFrame(list_, columns = ['PlugInTime', 'PlugOutTime', 'DeliveredEnergy', 'ChargeDuration'])

output :

['2019-01-02 06:53:00', '2019-01-02 14:38:00', 6.050420168067228, '05:57:00']
['2019-01-02 07:53:00', '2019-01-02 14:38:00', 6.050420168067228, '05:57:00']
['2019-01-02 08:53:00', '2019-01-02 14:38:00', 6.050420168067228, '05:57:00']
['2019-01-02 09:53:00', '2019-01-02 14:38:00', 6.050420168067228, '05:57:00']
['2019-01-02 10:53:00', '2019-01-02 14:38:00', 6.050420168067228, '05:57:00']
['2019-01-02 11:50:00', '2019-01-02 14:38:00', 5.747899159663865, '05:57:00']
user_107586540
Related