PANDAS: Need help counting the instances of a list of items present in a column, and the summing the value in another column that matches

user_19731133 0
Viewed 53

I have a Dataframe like:

import pandas as pd
from datetime import datetime
data = {'PRACTICE': [1,2,3,1,1], 'Postcode': ['BT1234', 'BT4321', 'AB1234', 'BT1234', 'BT1234'], 
        'month': [datetime(2013, 4, 1), datetime(2013, 4, 1), datetime(2013, 4, 1), datetime(2013, 3, 1), datetime(2013, 3, 1)],
        'VTN_NM': ['Gabapentin', 'Gabapentin', 'Diazepam', 'Diazepam', 'Gabapentin elixir'], 
        'Total Items': [6, 5, 11, 4, 3]}
df = pd.DataFrame(data)

and a list of search terms for the VTM_NM column:

search_terms = [
    'Gabapentin', 'Pregabalin', 'Tramadol', 'Oxycodone',
    'Morphine', 'Diazepam', 'Temazepam', 'Codeine',
    'Buprenorphine', 'Methadone', 'Methylphenidate'
]

I want to count/sum the Total Items column for all the search terms when list items are present in the VTM_NM column, based on the month, PRACTICE and postcode columns. So when any of the items in the list are present in the VTM_NM column it adds the Total Quantity for that given month practice and postcode value. The counted values could then be stored in a new column e.g. gabapentin_count etc. If gabapentin was prescribed 5 times in one month by a practice of a given postcode then the total count for each of the 5 prescriptions would be added.

So for this input, the output should look like:

PRACTICE Postcode Month Gaba_count Diaz_count
1 BT1234 2013.3 3 4
1 BT1234 2013.4 6 0
2 BT4321 2013.4 5 0
3 AB1234 2013.4 0 11
etc

I think I need to use groupby() to solve this problem, but I can't figure it out, and none of the code I found online works either. How can I get this result?

pandas data-analysis python
1 Answers

Filter your data using your list like so:

df2 = df[df['VTN_NM'].isin(search_terms)]

Then pivot your data:

df2 = df2.pivot_table(index=['PRACTICE', 'Postcode', 'month'], 
                      columns=['VTN_NM'], 
                      fill_value=0, 
                      aggfunc='sum')
print(df2)

Output:

                             Total Items
VTN_NM                          Diazepam Gabapentin
PRACTICE Postcode month
1        BT1234   2013-03-01           4          0
                  2013-04-01           0          6
2        BT4321   2013-04-01           0          5
3        AB1234   2013-04-01          11          0

Or maybe you mean to search for each term and fix the column:

df['VTN_NM'] = df['VTN_NM'].str.extract('(' + '|'.join(search_terms) + ')')

Pivoting, same as above:

out = df.pivot_table(index=['PRACTICE', 'Postcode', 'month'], 
                     columns=['VTN_NM'], 
                     fill_value=0, 
                     aggfunc='sum')
print(out)

Output:

                             Total Items
VTN_NM                          Diazepam Gabapentin
PRACTICE Postcode month
1        BT1234   2013-03-01           4          3
                  2013-04-01           0          6
2        BT4321   2013-04-01           0          5
3        AB1234   2013-04-01          11          0

Or, a combination of the two... where you fix and then filter. It's not fully clear from your post.

Using groupby, you weren't off thinking about that route:

df['terms'] = df['VTN_NM'].str.extract('(' + '|'.join(search_terms) + ')')
print(df.groupby(['PRACTICE', 'Postcode', 'month', 'terms'])['Total Items'].sum().unstack('terms').fillna(0))

# Output:

terms                         Diazepam  Gabapentin
PRACTICE Postcode month
1        BT1234   2013-03-01       4.0         3.0
                  2013-04-01       0.0         6.0
2        BT4321   2013-04-01       0.0         5.0
3        AB1234   2013-04-01      11.0         0.0
user_118659560
Related