python pandas summarizing nominal variables (counting)

Question

I have following data frame:

KEY PROD PARAMETER Y/N
1    AAA    PARAM1   Y
1    AAA    PARAM2   N
1    AAA    PARAM3   N
2    AAA    PARAM1   N
2    AAA    PARAM2   Y
2    AAA    PARAM3   Y
3    CCC    PARAM1   Y
3    CCC    PARAM2   Y
3    CCC    PARAM3   Y

I am interested in summarizing Y/N column values by PROD and PARAMETER columns and get the following output:

PROD  PARAM Y N
 AAA PARAM1 1 1
 AAA PARAM2 1 1
 AAA PARAM3 1 1
 CCC PARAM1 1 0
 CCC PARAM2 1 0
 CCC PARAM3 1 0

While Y and N values are counts of Y/N column values from the original data frame.

Hey Felix, can you give us a sample of the df in a code line so we can play with it? — Steven G, Sep 27 '16 at 18:17
@StevenG, [read_clipboard()](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_clipboard.html) is your friend ;) — MaxU - stand with Ukraine, Sep 27 '16 at 18:29

Nickil Maveli · Accepted Answer · 2016-09-28T07:31:44.333

4

You could use pivot_table by creating an additional column with the value 1 as it doesn't matter either ways (You are only counting them)

df['Y/Ncount'] = 1

df = df.pivot_table(index=['PROD', 'PARAMETER'], columns=['Y/N'], values=['Y/Ncount'], 
                    aggfunc=sum, fill_value=0)

df.columns = [col for col in df.columns.get_level_values(1)]
df.reset_index()

The simplest operation to use under this scenario would be crosstab which would produce the frequency counts of values present inside the Y/N column:

pd.crosstab([df['PROD'], df['PARAMETER']], df['Y/N'])

edited Sep 28 '16 at 07:31

answered Sep 27 '16 at 18:20

Nickil Maveli

29,155
8
82
85

Ah, I'd forgotten about `crosstab`! Nice solution! – Alicia Garcia-Raboso Sep 27 '16 at 19:09
Even me. Just remembered about it. Was thinking along the lines of `groupby`/ `pivot` before. – Nickil Maveli Sep 27 '16 at 19:11

score 3 · Answer 2 · answered Sep 27 '16 at 18:21

You want to get the counts of the values in the Y/N column, grouped by PROD and PARAMETER.

import io
import pandas as pd

data = io.StringIO('''\
KEY PROD PARAMETER Y/N
1    AAA    PARAM1   Y
1    AAA    PARAM2   N
1    AAA    PARAM3   N
2    AAA    PARAM1   N
2    AAA    PARAM2   Y
2    AAA    PARAM3   Y
3    CCC    PARAM1   Y
3    CCC    PARAM2   Y
3    CCC    PARAM3   Y
''')
df = pd.read_csv(data, delim_whitespace=True)

res = (df.groupby(['PROD', 'PARAMETER'])['Y/N'] # Group by `PROD` and `PARAMETER`
                                                # and select the `Y/N` column
         .value_counts()                        # Get the count of values
         .unstack('Y/N')                        # Long-to-wide format change
         .fillna(0)                             # Fill `NaN`s with zero
         .astype(int))                          # Cast to integer
print(res)

Output:

Y/N             N  Y
PROD PARAMETER      
AAA  PARAM1     1  1
     PARAM2     1  1
     PARAM3     1  1
CCC  PARAM1     0  1
     PARAM2     0  1
     PARAM3     0  1

python pandas summarizing nominal variables (counting)

2 Answers2