Pandas Dataframe Count Categorical data by row, group by datetime index

user_13724437 0
Viewed 413

I have a timeseries Dataframe of Categorical values.

I'd like to count unique categorical values into new columns (named by value), like so:

df           a     b     c                df1         x     y
17/6/2021    x     x     y                17/6/2021   2     1
18/6/2021    x     x     x   >>>>>>>>>    18/6/2021   3     0
19/6/2021    y     x     x                19/6/2021   2     1
20/6/2021    y     y     y                20/6/2021   0     3

Would appreciate any guidance on what function is suitable for this task. TIA

pandas dataframe
2 Answers

Here's one way:

df = df.set_index('<your date column name>').apply(pd.Series.value_counts, 1).fillna(0)

Example:

df = pd.DataFrame({'date': {0: '17/6/2021', 1: '18/6/2021', 2: '19/6/2021', 3: '20/6/2021'},
              'a': {0: 'x', 1: 'x', 2: 'y', 3: 'y'},
              'b': {0: 'x', 1: 'x', 2: 'x', 3: 'y'},
              'c': {0: 'y', 1: 'x', 2: 'x', 3: 'y'}})

df1 = df.set_index('date').apply(pd.Series.value_counts, 1).fillna(0)
df1
OUTPUT:
             x    y
date               
17/6/2021  2.0  1.0
18/6/2021  3.0  0.0
19/6/2021  2.0  1.0
20/6/2021  0.0  3.0
user_154380330

Another option with melt + crosstab:

s = df.melt('date')
df1 = pd.crosstab(s['date'], s['value']).rename_axis(columns=None).reset_index()

        date  x  y
0  17/6/2021  2  1
1  18/6/2021  3  0
2  19/6/2021  2  1
3  20/6/2021  0  3

Benefit of this approach is the counts are of the correct type (int) since NaNs are not introduced converting the dtype to float.

Explaination:

melt converts data to long form:

s = df.melt('date')

         date variable value
0   17/6/2021        a     x
1   18/6/2021        a     x
2   19/6/2021        a     y
3   20/6/2021        a     y
4   17/6/2021        b     x
5   18/6/2021        b     x
6   19/6/2021        b     x
7   20/6/2021        b     y
8   17/6/2021        c     y
9   18/6/2021        c     x
10  19/6/2021        c     x
11  20/6/2021        c     y

Then crosstab between date and value:

df1 = pd.crosstab(s['date'], s['value'])

value      x  y
date           
17/6/2021  2  1
18/6/2021  3  0
19/6/2021  2  1
20/6/2021  0  3

Lastly cleanup axis names and index:

df1 = pd.crosstab(s['date'], s['value']).rename_axis(columns=None).reset_index()

        date  x  y
0  17/6/2021  2  1
1  18/6/2021  3  0
2  19/6/2021  2  1
3  20/6/2021  0  3
user_154978880
Related