Resample pandas dataframe and return start time and end time

user_11863394 0
Viewed 155

I have a pandas dataframe that I want to resample by 10 seconds per each id. However, I also want to extend the output to return sampling start-time and end-time of each id. Dataframe, expected output and what I have tried is below.

Dataframe:

id,date,value
1,2012-01-01 00:09:45,1
1,2012-01-01 00:09:46,1
2,2012-01-01 00:09:47,1
1,2012-01-01 00:09:47,1
2,2012-01-01 00:09:48,1
1,2012-01-01 00:09:51,1
1,2012-01-01 00:09:52,1
1,2012-01-01 00:09:53,1
2,2012-01-01 00:10:00,1
2,2012-01-01 00:10:01,1
2,2012-01-01 00:10:04,1
2,2012-01-01 00:10:05,1
2,2012-01-01 00:10:06,1
3,2012-01-01 00:30:04,1
3,2012-01-01 00:30:05,1
3,2012-01-01 00:30:06,1
3,2012-01-01 00:30:08,1
3,2012-01-01 00:30:09,1
2,2012-01-01 00:30:18,1
2,2012-01-01 00:30:19,1
2,2012-01-01 00:30:23,1
2,2012-01-01 00:30:24,1
3,2012-01-01 00:30:25,1
3,2012-01-01 00:30:26,1
3,2012-01-01 00:30:29,1
3,2012-01-01 00:30:30,1
3,2012-01-01 00:30:32,1
3,2012-01-01 00:30:33,1

Expected output:

id,date,value,start-time,end-time
1,2012-01-01 00:09:40,3,2012-01-01 00:09:45,2012-01-01 00:09:47
2,2012-01-01 00:09:40,2,2012-01-01 00:09:47,2012-01-01 00:09:48
1,2012-01-01 00:09:50,3,2012-01-01 00:09:51,2012-01-01 00:09:53
2,2012-01-01 00:10:00,5,2012-01-01 00:10:00,2012-01-01 00:10:06
3,2012-01-01 00:30:00,5,2012-01-01 00:30:04,2012-01-01 00:30:09
2,2012-01-01 00:30:10,2,2012-01-01 00:30:18,2012-01-01 00:30:19
2,2012-01-01 00:30:20,2,2012-01-01 00:30:23,2012-01-01 00:30:24
3,2012-01-01 00:30:20,3,2012-01-01 00:30:25,2012-01-01 00:30:29
3,2012-01-01 00:30:30,3,2012-01-01 00:30:30,2012-01-01 00:30:33

Below is what I have done together with the output:

import pandas as pd
df = pd.read_csv('df.csv')
df['date'] = pd.to_datetime(df['date'])
df_resampled = df.set_index('date').groupby('id').resample('10s')['value'].sum().reset_index()
df = df_resampled[df_resampled['value']!=0]
print(df.sort_values(['date']))

Output so far:

id,date,value
1,2012-01-01 00:09:40,3
2,2012-01-01 00:09:40,2
1,2012-01-01 00:09:50,3
2,2012-01-01 00:10:00,5
3,2012-01-01 00:30:00,5
2,2012-01-01 00:30:10,2
2,2012-01-01 00:30:20,2
3,2012-01-01 00:30:20,3
3,2012-01-01 00:30:30,3

How can I extend the current simple code to include the start and end time of the 10 seconds sampling for each id.

pandas resampling dataframe python
1 Answers

Try:

df["date"] = pd.to_datetime(df["date"])
df["date2"] = df["date"]

x = (
    df.groupby(["id", pd.Grouper(freq="10s", key="date")])
    .agg({"value": ["sum"], "date2": ["first", "last"]})
    .reset_index()
)
x.columns = x.columns.map(" ".join).str.strip()
x = x.rename(
    columns={
        "value sum": "value",
        "date2 first": "start-time",
        "date2 last": "end-time",
    }
).sort_values(by="date")
print(x)

Prints:

   id                date  value          start-time            end-time
0   1 2012-01-01 00:09:40      3 2012-01-01 00:09:45 2012-01-01 00:09:47
2   2 2012-01-01 00:09:40      2 2012-01-01 00:09:47 2012-01-01 00:09:48
1   1 2012-01-01 00:09:50      3 2012-01-01 00:09:51 2012-01-01 00:09:53
3   2 2012-01-01 00:10:00      5 2012-01-01 00:10:00 2012-01-01 00:10:06
6   3 2012-01-01 00:30:00      5 2012-01-01 00:30:04 2012-01-01 00:30:09
4   2 2012-01-01 00:30:10      2 2012-01-01 00:30:18 2012-01-01 00:30:19
5   2 2012-01-01 00:30:20      2 2012-01-01 00:30:23 2012-01-01 00:30:24
7   3 2012-01-01 00:30:20      3 2012-01-01 00:30:25 2012-01-01 00:30:29
8   3 2012-01-01 00:30:30      3 2012-01-01 00:30:30 2012-01-01 00:30:33
user_100359850
Related