CODEWITHSUNDEEP
pythonpandasdatetime
user
user

Reputation: 2884

merge multiple rows by average based on time range on a day

I have a dataframe which has 3 columns Date , Time , Value 

**Date**    **Time**   **Value**
7/1/1990    0:00:00       10
7/1/1990    1:00:00       10       
7/1/1990    1:15:00       12
7/1/1990    1:50:00       11
7/1/1990    2:00:00       10
7/1/1990    3:00:00       10
7/1/1990    3:30:00       12
7/1/1990    4:00:00       11
....
8/1/1990    0:00:00       11
8/1/1990    1:00:00       12       
8/1/1990    2:00:00       11
8/1/1990    2:50:00       11
8/1/1990    3:00:00       12
8/1/1990    4:00:00       10
8/1/1990    4:30:00       12
.....

I want to merge all rows by taking average for time frame like 0:00:00 to 0:59:59 or 4:00:00 to 4:59:59

**Date**    **Time**   **Value**
7/1/1990    0:00:00       10
7/1/1990    1:00:00       11       
7/1/1990    2:00:00       10
7/1/1990    3:00:00       11
7/1/1990    4:00:00       11
....
8/1/1990    0:00:00       11
8/1/1990    1:00:00       12       
8/1/1990    2:00:00       11
8/1/1990    3:00:00       12
8/1/1990    4:00:00       11
.....

Upvotes: 0

Views: 264

Answers (1)

Chris
Chris

Reputation: 29742

Use pandas.to_datetime and pandas.DataFrame.resample:

import pandas as pd

df.index = pd.to_datetime(df[['Date', 'Time']].apply(' '.join, 1), dayfirst=True)
new_df = df.resample('1H').mean().dropna().reset_index()
print(new_df)

Output:

                index  Value
0 1990-01-07 00:00:00   10.0
1 1990-01-07 01:00:00   11.0
2 1990-01-07 02:00:00   10.0
3 1990-01-07 03:00:00   11.0
4 1990-01-07 04:00:00   11.0
5 1990-01-08 00:00:00   11.0
6 1990-01-08 01:00:00   12.0
7 1990-01-08 02:00:00   11.0
8 1990-01-08 03:00:00   12.0
9 1990-01-08 04:00:00   11.0

Upvotes: 1

Related Questions