1

I have a dataframe df1.

Time        Category
23:05:07    a
23:11:12    b
23:12:15    a
23:16:12    a

Another dataframe df2 has been created returning the count of occurences of each of the unique values in column category and into intervals of 5 minutes. Using this code df2= df1.resample('5T').category.value_counts()

df2

Time   Category   Category 
23:05  a          1
23:10  a          1
23:10  b          1
23:15  a          1

Is there a way to use the unique values as column headers? Look like:

Time   a   b 
23:05  1   0
23:10  1   1
23:15  1   0
Deluxe88888
  • 113
  • 6

2 Answers2

1

value_counts returns multiindex series. So, you just need to work directly on its result by chaining unstack to get your desired output.

Since you were able to resample, I assume Time is your index and it is already in datetime or timedelta dtype

df_final = df1.resample('5T').Category.value_counts().unstack(fill_value=0)

Out[79]:
Category  a  b
Time
23:05:07  1  0
23:10:07  1  1
23:15:07  1  0
Andy L.
  • 24,909
  • 4
  • 17
  • 29
0

The code below should work. I renamed your second 'Category' column to 'CatUnique'.

df.groupby(['Time','Category'])['CatUnique'].sum().unstack().fillna(0).reset_index()
rhug123
  • 7,893
  • 1
  • 9
  • 24