retrieve food groups from food item list

Question

I have a dataframe of food items as follows: I have to create a food_group list that gives the food group it belongs to, for-example all type of yogurts should be in one group called yogurt.

I used a snippet to take the first segment of the comma separated name, but I do not get the result like putting all yogurts in one group

food_group_0 = [i.split(',') for i in data['name']]

food_group = [item[0] for item in food_group_0]


#To count how many of each entry there are in the list you can use the Counter class in the collections module:
from collections import Counter
c = Counter(food_group) 
print(c)

the dataframe

0                                          4-Grain Flakes
1                             4-Grain Flakes, Gluten Free
2                  4-Grain Flakes, Riihikosken Vehnämylly
3                                                  Almond
4                          Almond Drink, Sweetened, Alrpo
5                        Almond Drink, Unsweetened, Alrpo
6                                         Amaranth Flakes
7                                                 Anchovy
8                               Apple, Average, With Skin
9                           Apple, Domestic, Without Skin
10                             Apple, Domestic, With Skin
11                                           Apple, Dried
12                          Apple, Imported, Without Skin
13                             Apple, Imported, With Skin
14                                            Apple Chips
15                 Apple Crisp Delight, Apple, Oat Flakes
16                                              Apple Jam
17                    Apple Juice, Unsweetened, Vitamin C
18                 Apple Kissel, Apple Soup, Dried Apples
19                 Apple Kissel, Apple Soup, Fresh Apples
20      Apple Pie, Basic Sweet Dough, Gluten-Free, Con...
21             Apple Pie, Basic Sweet Dough, Low-Fat Milk
22      Apple Pie, Basic Sweet Dough, Naturally Gluten...
23               Apple Pie, Basic Sweet Dough, Whole Milk
24                            Apple Pie, Shortbread Crust
25      Apple Pie, Shortbread Crust, Gluten-Free, Cont...
26      Apple Pie, Shortbread Crust, Naturally Gluten-...
27             Apple Pie, Shortbread Crust With Sour Milk
28                          Apple Pie, Soft, Low-Fat Milk
29         Apple Pie With Quark Filling, Shortbread Crust
                              ...                        
4068    Yoghurt, Plain, A+, Fat 2.5%, 1 Ug Vitamin D, ...
4069    Yoghurt, Plain, A+, Fat 2.5%, Lactose-Free, 1 ...
4070    Yoghurt, Plain, A+, Fat 4%, 1 Ug Vitamin D, La...
4071    Yoghurt, Plain, A+, Fatfree, 1 Ug Vitamin D, L...
4072    Yoghurt, Plain, A+ Greek, 2 % Fat, Lactose-Fre...
4073             Yoghurt, Plain, Ab, 0.2% Fat, Probiotics
4074             Yoghurt, Plain, Ab, 2.5% Fat, Probiotics
4075                    Yoghurt, Plain, Activia, 3.4% Fat
4076    Yoghurt, Plain, Arla Protein, 1% Fat, Lactose-...
4077                    Yoghurt, Plain, Bulgarian, 9% Fat
4078                             Yoghurt, Plain, Fat-Free
4079    Yoghurt, Plain, Fat-Free, Lactose-Free, 1 Ug V...
4080    Yoghurt, Plain, Fat-Free, Low-Lactose, 0.5 Ug ...
4081          Yoghurt, Plain, Greek, 7% Fat, Lactose-Free
4082                      Yoghurt, Plain, Organic, 3% Fat
4083    Yoghurt, Plain, Pirkka Reducol, 2.5% Fat, Low-...
4084                      Yoghurt, Turkish/Greek, 10% Fat
4085        Yoghurt, Turkish/Greek, 10% Fat, Lactose-Free
4086                                        Yoghurt Sauce
4087                           Yoghurt With Jam, Fat-Free
4088       Yoghurt With Muesli, A+, Fat 3.5%, Low-Lactose
4089    Yoghurt With Quark, Flavoured, Arla, 1.4% Fat,...
4090    Yoghurt With Quark, Flavoured, Luonto+, 1.2% F...
4091    Yoghurt With Quark, Flavoured, Valio, 1.7% Fat...
4092                                   Zander, Pike-Perch
4093                        Zucchini, Boiled Without Salt
4094                              Zucchini, Summer Squash
4095                     Zucchini Filled With Minced Meat
4096                   Zucchini Filled With Soya And Rice
4097                      Zucchini Filled With Vegetables

Answer 1

You can actually do the string-spitting and indexing on the columns themselves - no need to extract the column and do list comprehensions.

Below I take whatever is before the first comma and put it in a column called food_group and then the first field after the same column and put it in a new column called sub_cat-egory:

df["food_group"] = df.name.str.split(",").str[0]
df["sub_cat"] = df.name.str.split(",").str[1]

Here is example output for some Yogurt data:

    id                                               name      food_group     sub_cat

44  4082                    Yoghurt, Plain, Organic, 3% Fat    Yoghurt        Plain
45  4083  Yoghurt, Plain, Pirkka Reducol, 2.5% Fat, Low-...    Yoghurt        Plain
46  4084                    Yoghurt, Turkish/Greek, 10% Fat    Yoghurt        Turkish/Greek
47  4085      Yoghurt, Turkish/Greek, 10% Fat, Lactose-Free    Yoghurt        Turkish/Greek
48  4086                                      Yoghurt Sauce    Yoghurt Sauce  NaN

Notice that any fields that are empty are filled with NaN. This will happen, when your name column only contains a single field (i.e. no commas).

EDIT

Here is the top of my dataframe, after the operation above:

In [13]: df.head(10)                                                                                                                                                   
Out[13]: 
   id                                    name       food_group                  sub_cat
0   0                          4-Grain Flakes   4-Grain Flakes                      NaN
1   1             4-Grain Flakes, Gluten Free   4-Grain Flakes              Gluten Free
2   2  4-Grain Flakes, Riihikosken Vehnämylly   4-Grain Flakes   Riihikosken Vehnämylly
3   3                                  Almond           Almond                      NaN
4   4          Almond Drink, Sweetened, Alrpo     Almond Drink                Sweetened
5   5        Almond Drink, Unsweetened, Alrpo     Almond Drink              Unsweetened
6   6                         Amaranth Flakes  Amaranth Flakes                      NaN
7   7                                 Anchovy          Anchovy                      NaN
8   8               Apple, Average, With Skin            Apple                  Average
9   9           Apple, Domestic, Without Skin            Apple                 Domestic

EDIT

In order to replace a row with another string, given a desired string is in that row, you can perform the following:

for keyword in keywords:
    df["new_col"] = df.name.apply(lambda x: keyword if keyword in x else x)

where keywords could be a list like this:

keywords = ["Yogurt", "chicken", "Drink"]

It still requires manually defining a list of keywords andlooping over them. You could also make this insensitive to the case of the word, but doing everything in e.g. lower-case:

lower_keywords = ["yogurt", "chicken", "drink"]

for keyword in lower_keywords:
    df["new_col"] = df.name.apply(lambda x: keyword if keyword in x.tolower() else x)

---

You could continue to make a multi-index from these two new columns, but is might not be necessary - it depends on what you want to do afterwards with the data.