CODEWITHSUNDEEP
pythonpandas
Kocur4d
Kocur4d

Reputation: 6931

Transforming DataFrame to get the count of records for a given hours

I have a DataFrame which looks like this:

enter image description here

Where Time request submitted is a timestamp. date and hour are the values extracted from Time request submitted as extra columns.

I would like to transform it to something like this:

enter image description here

Where row represents the date and the columns are hours: 9AM, 10AM and so on. I would like the values to be the counts for the given hour for a given day instead of 0s. Hope it make sense.

I was playing a bit with groupby, resample and pivot_table but I can't seem to be able to achieve what I am looking for.

Upvotes: 1

Views: 47

Answers (1)

jezrael
jezrael

Reputation: 863256

I believe you need crosstab:

df1 = pd.crosstab(df['date'], df['hour'])

Alternatives:

df1 = df.pivot_table(index='date', columns='hour', aggfunc='size', fill_value=0)

df1 = df.groupby(['date', 'hour']).size().unstack(fill_value=0)

Or:

df1 = pd.crosstab(df['Time request submitted'].rename('date'), 
                  df['Time request submitted'].rename('hour'))

Upvotes: 3

Related Questions