Count occurrences within range

user_14576516 0
Viewed 255

I have a dataset which like:

ID Amt   TYPE
1  1000  A
2  200   NA
3  1100  S

I need to count the occurrences of each type within a specific range for each type:

Range     A_Count NA_Count S_Count
0-1000    1       1        0
1001-2000 0       0        1

I am tyring to get this so that I can draw a plot using this dataframe, with range as the x-axis and the counts as y-axis. How do I achieve this?

pandas matplotlib numpy python
2 Answers

Tabulating

First cut() the amounts into ranges and then crosstab() vs type:

df['Range'] = pd.cut(df.Amt, bins=[0, 1000, 2000])
tab = pd.crosstab(df.Range, df.TYPE).add_suffix('_Count')

# TYPE          A_Count  NA_Count  S_Count
# Range                                   
# (0, 1000]           1         1        0
# (1000, 2000]        0         0        1

  • Note that if NA here means NaN, then replace() them as strings when tabulating:

    tab = pd.crosstab(df.Range, df.TYPE.replace(np.nan, 'NA')).add_suffix('_Count')

  • By default, the tabulation will drop empty bins. If you want to keep all bins, use dropna=False:

    tab = pd.crosstab(df.Range, df.TYPE, dropna=False).add_suffix('_Count')

Plotting

Plot the cross table either with DataFrame.plot.bar():

tab.plot.bar()

Or seaborn.barplot():

sns.barplot(
    data=tab.reset_index().melt('Range', value_name='Count'),
    x='Range',
    y='Count',
    hue='TYPE',
)

crosstab figure

user_131383640

You can use pandas.cut and pandas.DataFrame.unstack

df['group'] =  pd.cut(df.Amt, [0,1000,1100])
(df.groupby('group')
   ['TYPE'].value_counts(dropna=False)
   .unstack(-1)
   .fillna(0)
   .add_suffix('_count')
)

output:

TYPE          nan_count  A_count  S_count
group                                    
(0, 1000]           1.0      1.0      0.0
(1000, 1100]        0.0      0.0      1.0
user_163434640
Related