CODEWITHSUNDEEP
pythonpandasdataframegroupingpandas-groupby
Ari
Ari

Reputation: 6209

Grouping a column 'Time' by it's hour value

I have a column with a Time column in H:M:S format

ColA    Time       Amount
a       10:32:01      10
b       09:10:56      10   
c       13:43:11      10
d       10:05:00      10
e       09:35:30      10

I'm only concerned about the hour value, i'd like to end up with a table like below:

Time       Amount
10:00:00      20
09:00:00      20   
13:00:00      10

I'm doing this:

df.groupby('Time')['Amount'].sum()

But this obviously groups exact matching Time values. I just need to group by the hour.

Upvotes: 1

Views: 32

Answers (2)

Ari
Ari

Reputation: 6209

I liked the answer by @sentence however in the mean time I found I could just do the following:

df.Time = [hour.split(':')[0] for hour in df.Time]

It works for my case however I don't know what implications that has should I need to do anything else.

Upvotes: 0

sentence
sentence

Reputation: 8933

You may use the column Time as the index, and things become simple:

import pandas as pd

df = pd.DataFrame({"ColA":["a", "b", "c", "d", "e"],
                  "Time":["10:32:01", "09:10:56", "13:43:11", "10:05:00", "09:35:30"],
                  "Amount":[10,10,10,10,10]})

df['Time'] = pd.to_datetime(df['Time'])
df.set_index('Time', inplace=True)

df.groupby(df.index.hour).sum()

and you get:

    Amount
Time    
9   20
10  20
13  10

Upvotes: 2

Related Questions