CODEWITHSUNDEEP
pythonpandaspandas-groupby
Chinmay
Chinmay

Reputation: 145

Merge similar hours from Date Column into one

I have a dataframe which looks like this.

   Date                   MW
0 2017-01-01 09:00:00      1
1 2017-01-01 09:00:00      1
2 2017-01-01 09:00:00      1
3 2017-01-01 10:00:00      1
4 2017-01-01 10:00:00      1

I want to add up values for all the repeated hours and convert it into a single row. For example, for 09:00:00, the final value should be 3 MW.

I tried groupby but it says missing values for hours that are not in the dataset. Also, df.resample('H').sum() gives weird results.

Thanks for the help.

Upvotes: 0

Views: 27

Answers (1)

Chandu
Chandu

Reputation: 2129

Why not considering datetime as string while doing group by?

df = pd.DataFrame({'Date':['2017-01-01 09:00:00','2017-01-01 09:00:00','2017-01-01 10:00:00','2017-01-01 10:00:00'],
              'MW':[1,2,1,1]})
df['Date'] = pd.to_datetime(df1['Date'])
df.groupby('Date', as_index=False).sum()

output:

                  Date  MW
0  2017-01-01 09:00:00   3
1  2017-01-01 10:00:00   2

Upvotes: 1

Related Questions