Counting dictionary items in a column-CodePudding

I have a dictionary

billable =  {'nonop'   : [99221, 99234, 99238, 99252, 99253, 99254, 99255, 99282, 99283, 99284, 99285, 99239, 99222, 99223, 99218, 99219, 99220, 99217, 99235],
             'rounding'   : [99231, 99232 ,99233, 99225, 99226],
             'clinic' : [99243, 99211 ,99215, 99213, 99214, 99212 ,99205, 99203, 99204, 99202, 9924, 99244, 99245, 99024],
             'critcare' : [99292, 99291]}

and a df with a column CPT, df['CPT'] with a list of many entries, dtype: object, approximately 4000 rows

I am trying to output the total count of each dictionary item

non-op = 1400 (sum of all the nonop dictionary items above) rounding = 500 (sum of all the rounding dictionary items above)

etc.

I tried this with a limited list.

nonop = ['99212', '99234']

df2 = pd.Series([df['CPT'].str.count(c).sum() for c in nonop], index=nonop)
print(df2)

But it just returned

99212 0.0 99234 0.0 dtype: float64

From the excel file itself I know there are 25 instances of 99212 in the column

Thank you in advance!

CodePudding user response：

IIUC, this is one way about it

# list of the values
noop=[99221, 99234]

# using loc, filter CPT id preset in the list, and take value count
df.loc[df['CPT'].isin(noop)]['CPT'] .value_counts()

99234    1
Name: CPT, dtype: int64

CodePudding user response：

Here is one way to do it.

df = pd.DataFrame(cpt_data)
df['label'] = df['CPT']

for cat in list(billable.keys()):
    df['label'] = np.where(df['CPT'].isin(billable[cat]), cat, df['label'])

Output:

    CPT          label
0   99231     rounding
1   99232     rounding
2   99243       clinic
3   99221        nonop
4   99211       clinic

You can find the total values in each category using value_countsmethod

df['label'].value_counts()

Output:

           label
clinic         2
rounding       2
nonop          1