CODEWITHSUNDEEP
pythonpandas
MarilynDavis
MarilynDavis

Reputation: 35

Can I do str manipulation like this in pandas?

I have used pandas to get my data looking like the dict in the code below.

I want to find all the salsa types, and put them in a dict with number of items with that salsa type being the dictionary value.

Here it is in Python. Is there a way to do a thing like this in Pandas? Or is this task where I should itertuples and use plain-ole-Python?

#!/usr/bin/env python3
import pandas as pd

items_df = pd.DataFrame({'choice_description': {0: '[Tomatillo Red Chili Salsa, [Fajita Vegetables, Black Beans, Pinto Beans, Cheese, Sour Cream, Guacamole, Lettuce]]', 1: '[Tomatillo-Red Chili Salsa (Hot), [Black Beans, Rice, Cheese, Sour Cream]]', 2: '[Fresh Tomato Salsa (Mild), [Rice, Cheese, Sour Cream, Guacamole, Lettuce]]', 3: '[Tomatillo Red Chili Salsa, [Fajita Vegetables, Black Beans, Pinto Beans, Cheese, Sour Cream, Guacamole, Lettuce]]'}, 'item_name': {0: 'Chips and Fresh Tomato Salsa', 1: 'Chips and Tomatillo-Green Chili Salsa', 2: 'Chicken Bowl', 3: 'Steak Burrito'}})

salsa_types_d = {}

for row in items_df.itertuples():
    for food in row[1:]:
        fixed_foods_l = food.replace("and",',').replace('[','').replace(']','').split(',')
        fixed_foods_l = [f.strip() for f in fixed_foods_l if f.find("alsa") > -1]
        for fixed_food in fixed_foods_l:
            salsa_types_d[fixed_food] = salsa_types_d.get(fixed_food, 0) + 1

print('\n'.join("%-33s:%d" % (k,salsa_types_d[k]) for k in sorted(salsa_types_d,key=salsa_types_d.get,reverse=True)))

"""
Output:

Tomatillo Red Chili Salsa        :2
Fresh Tomato Salsa               :1
Fresh Tomato Salsa (Mild)        :1
Tomatillo-Green Chili Salsa      :1
Tomatillo-Red Chili Salsa (Hot)  :1

---
Thank you for any insight.

Marilyn
"""

Upvotes: 1

Views: 108

Answers (1)

Bharath M Shetty
Bharath M Shetty

Reputation: 30605

This can be done without using for loops one of the way is creating a separated df by stacking the columns and then replacing the values after that dropping the values which do not contain alsa. Then finally using value_counts to get the frequency. 

new_df = items_df.stack().reset_index(drop=True)
         .replace(['and', '\[', '\]'],[',', '',''], regex=True).str.split(',')
         .apply(lambda x: pd.Series([i.lstrip() for i in x if 'alsa' in i]))[0].value_counts()

Output: 

Tomatillo Red Chili Salsa          2
Tomatillo-Green Chili Salsa        1
Tomatillo-Red Chili Salsa (Hot)    1
Fresh Tomato Salsa (Mild)          1
Fresh Tomato Salsa                 1
Name: 0, dtype: int64

Upvotes: 1

Related Questions