How to combine unique words when using pandas groupby-count on a dataframe?

Question

I have a data frame of dates, ID number, and ingredients (see example below). I have been successful at grouping the ingredients based on ID number, removing duplicates, and counting the unique number of ingredients per ID number as a function of time. However, I would like the option to combine ingredients into a single category instead of separate ones. For example, instead of outputting: Apples 2, Pears 3, I would get something like: Fruit 5.

This is my example data:

Date	ID Number	Ingredient
01-01-2022	1	Apple
01-01-2022	1	Apple
01-01-2022	1	Pear
01-01-2022	1	Beef
01-03-2022	2	Apple
01-04-2022	3	Apple
01-04-2022	3	Beef
01-04-2022	3	Beef
01-04-2022	4	Pear

My code currently is:

import numpy as np
import pandas as pd
import re
import datetime as dt
import glob
import os
from pathlib import Path
import openpyxl
import xlsxwriter

path2 = "/Users/ProjectPath"
df = pd.concat([pd.read_excel(f) for f in Path(path2).rglob('*.xlsx')])
df = df[["Date", "ID Number", "Ingredient"]]

df["Date"] = pd.to_datetime(df["Date"])
mask = (df["Date"] > "2022-01-01") & (df["Date"] <= "2022-12-31")
df22 = df.loc[mask]

df_Example1 = df22
df_Example1["Ingredient"] = df_Example1["Chemical"].str.lower()
df_Example1["Ingredient"].replace(" ", np.nan, inplace=True)
df_Example1 = df_Example1.drop_duplicates(subset=["ID Number", "Ingredient"], keep="first")

df_Example2 = df_Example1.groupby("Ingredient")["ID Number"].count().sort_values(ascending=False).to_frame("Count").reset_index()
df_Example3 = df_Example2[df_Example2["Count"] >0]
print(df_Example3)

This gives me the following output for the above dataset

	Ingredient	Count
0	Apple	3
1	Pear	2
2	Beef	2

The output I would like to be able to get is:

	Ingredient	Count
0	Fruit	4
1	Beef	2

The Apple and Pear are combined but still only counted once per ID number. And changing the name from Apple/Pear to Fruit.

I think I can do this either right before or right after the line using groupby but I have not had any luck so far.

Any help with how to code this would be greatly appreciated!

user_16343464 · Answer

Use a dictionary to replace your terms and aggregate after dropping the duplicates:

d = {'Apple': 'Fruit', 'Pear': 'Fruit'}
(df
 .assign(Ingredient=df['Ingredient'].map(d).fillna(df['Ingredient']))
 .drop_duplicates(['Ingredient', 'ID Number'])
 .groupby('Ingredient', as_index=False).agg(count=('Ingredient', 'count'))
)

or:

(df
 .assign(Ingredient=df['Ingredient'].map(d).fillna(df['Ingredient']))
 .drop_duplicates(['Ingredient', 'ID Number'])
 ['Ingredient'].value_counts().rename_axis('Ingredient').reset_index(name='count')
)

output:

  Ingredient  count
0       Beef      2
1      Fruit      4

How to combine unique words when using pandas groupby-count on a dataframe?

1 Answers