Convert dataframe column values to column names and display count

Question

rules       close_time
100670   December-2019
100670   November-2019
100664   December-2019
100670   November-2019
100670   December-2019
100664   December-2019
100664   November-2019

Here's how I want it. The total count should be displayed per rule for each month.

rules    December-2019   November-2019
100670               2               2
100664               2               1

In my opinion you should look at solution below: https://stackoverflow.com/questions/29836477/pandas-create-new-column-with-count-from-groupby . — s3nh, Jan 02 '20 at 09:50

James · Accepted Answer · 2020-01-02T10:02:26.397

0

I think you just want a pivot table with the count for the values.

import pandas as pd

df = pd.DataFrame({
 'rules': [100670, 100670, 100664, 100670, 100670, 100664, 100664],
 'close_time': ['December-2019', 'November-2019', 'December-2019', 'November-2019',
                'December-2019', 'December-2019', 'November-2019']
})
df.pivot_table(index='rules', columns=['close_time'], aggfunc=len)
# returns:
close_time  December-2019  November-2019
rules
100664                  2              1
100670                  2              2

If you want rules to be a column and not an index, you can use:

df.pivot_table(
    index='rules', 
    columns=['close_time'], 
    aggfunc=len
).reset_index().rename_axis(columns={'close_time': None})
# returns:
    rules  December-2019  November-2019
0  100664              2              1
1  100670              2              2

edited Jan 02 '20 at 10:02

answered Jan 02 '20 at 09:52

James

32,991
4
47
70

Getting this error - TypeError: unhashable type: 'list' – GK21 Jan 02 '20 at 10:00
Does copying and pasting my example (including the `df` assignment) not work for you? – James Jan 02 '20 at 10:05
still getting the error - TypeError: unhashable type: 'list' – GK21 Jan 02 '20 at 10:07
What version of pandas as you running? `pd.__version__` – James Jan 02 '20 at 10:11
pandas version 0.25.3 – GK21 Jan 02 '20 at 10:17
I have the same version. Maybe restart python? – James Jan 02 '20 at 10:20
Boy that is weird. I can copy and paste this into a new python instance and it works fine. – James Jan 02 '20 at 10:26

score 0 · Answer 2 · answered Jan 04 '20 at 09:59

0

Here's another way to do it:

df = df.groupby(['rules','close_time']).size().unstack()
df.index.name = None

close_time  December-2019  November-2019
100664                  2              1
100670                  2              2

answered Jan 04 '20 at 09:59

YOLO

20,181
5
20
40

Convert dataframe column values to column names and display count

2 Answers2