eggbert eggbert - 1 month ago 12
Python Question

Add datetime in pandas dataframe to existing row based on column

I'm new to pandas and am having a problem with this. I have a simple table:

date period_number
0 2016-10-26 1
1 2016-10-26 3
2 2016-10-26 5
3 2016-10-27 1


I want to create 3 equal sized period with start and end times, like this:

date period_number start end
0 2016-10-26 1 2016-10-26 00:00 2016-10-26 08:00
1 2016-10-26 3 2016-10-26 08:00 2016-10-26 16:00
2 2016-10-26 5 2016-10-26 16:00 2016-10-27 00:00
3 2016-10-27 1 2016-10-27 00:00 2016-10-27 08:00


I tried to do something like this:

for (period, group) in df.groupby('period'):
if period == 1:
group['start'] = group['date']
group['end'] = group['date'] + timedelta(hours=8)
if period == 3:
group['start'] = group['date'] + timedelta(hours=8)
group['end'] = group['date'] + timedelta(hours=16)
if period == 5:
group['start'] = group['date'] + timedelta(hours=16)
group['end'] = group['date'] + timedelta(days=1)


But I'm getting errors:

A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead


Also tried this but obviously wrong and getting same error:

df[df['period'] == 1]['end'] = df['date'] + timedelta(hours=8)

Answer

My approach is to use an apply that looks at the period integer and uses that to add a variable number of hours to the date. It's a bit hacky but does the trick. It would be cleaner if the period numbers would be simply 1 2 3.

# Initialise dataframe
df = pd.DataFrame([['2016-10-26', '2016-10-26', '2016-10-26', '2016-10-27'], [1, 3, 5, 1]]).T
df.columns = ['date', 'period_number']
df['date'] = df['date'].apply(pd.to_datetime)

# Make start column
df['start'] = df.apply(lambda x: x.date + timedelta(hours=8 * (x.period_number - 1) / 2), 1)

# End column is just start column + 8 hours
df['end'] = df.start + timedelta(hours=8)

This returns:

date    period_number   start   end
0   2016-10-26  1   2016-10-26 00:00:00 2016-10-26 08:00:00
1   2016-10-26  3   2016-10-26 08:00:00 2016-10-26 16:00:00
2   2016-10-26  5   2016-10-26 16:00:00 2016-10-27 00:00:00
3   2016-10-27  1   2016-10-27 00:00:00 2016-10-27 08:00:00
Comments