CODEWITHSUNDEEP
pythonpandaspandas-groupbysequence
Deoj
Deoj

Reputation: 71

Time sequence in pandas dataframe

Below is the sample of dataframe (df):-

alpha value
0 a 5
1 a 8
2 a 4
3 b 2
4 b 1

I know how to make the sequence (numbers) as per the group:

df["serial"] = df.groupby("alpha").cumcount()+1
alpha value serial
0 a 5 1
1 a 8 2
2 a 4 3
3 b 2 1
4 b 1 2

But instead of number I need date-time in sequence having 30 mins interval:

Expected result:

alpha value serial
0 a 5 2021-01-01 23:30:00
1 a 8 2021-01-02 00:00:00
2 a 4 2021-01-02 00:30:00
3 b 2 2021-01-01 23:30:00
4 b 1 2021-01-02 00:00:00

Upvotes: 1

Views: 325

Answers (2)

U13-Forward
U13-Forward

Reputation: 71570

Try with to_datetime and groupby with cumcount, and then multiplying by pd.Timedelta for 30 minutes:

>>> df['serial'] = pd.to_datetime('2021-01-01 23:30:00') + df.groupby('alpha').cumcount() * pd.Timedelta(minutes=30)
>>> df
  alpha  value              serial
0     a      5 2021-01-01 23:30:00
1     a      8 2021-01-02 00:00:00
2     a      4 2021-01-02 00:30:00
3     b      2 2021-01-01 23:30:00
4     b      1 2021-01-02 00:00:00
>>>

Upvotes: 1

Henry Yik
Henry Yik

Reputation: 22493

You can simply multiply your result with a pd.Timedelta:

print ((df.groupby("alpha").cumcount()+1)*pd.Timedelta(minutes=30)+pd.Timestamp("2021-01-01 23:00:00"))

0   2021-01-01 23:30:00
1   2021-01-02 00:00:00
2   2021-01-02 00:30:00
3   2021-01-01 23:30:00
4   2021-01-02 00:00:00
dtype: datetime64[ns]

Upvotes: 1

Related Questions