dakro
dakro

Reputation: 126

Elegant way to conditionally combine two strings into a new column

I'm creating a new column of strings from other columns, where the columns match my criteria.

My goal is to scale this out to read over 12 fields / 30,000 rows of mis-categorized data.

Example Data:

df = pd.DataFrame({'clothes': ['furry boots', 'weird boots', 'furry gloves', 'weird gloves', 'furry coat', 'weird coat'],
      'barn': ['furry animal', 'big animal', 'furry fence', 'old fence', 'furry door', 'old door'],
      'crazy': ['heckin food', 'furry food', 'furry toes', 'old toes', 'furry hat', 'crazy cat']})
df

+---+--------------+--------------+-------------+
|   |   sparkle    |    misty     |    crazy    |
+---+--------------+--------------+-------------+
| 0 | furry boots  | furry animal | heckin food |
| 1 | weird boots  | big animal   | furry food  |
| 2 | furry gloves | furry fence  | furry toes  |
| 3 | weird gloves | old fence    | old toes    |
| 4 | furry coat   | furry door   | furry hat   |
| 5 | weird coat   | old door     | crazy cat   |
+---+--------------+--------------+-------------+

Desired Output:

+---+--------------+--------------+-------------+---------------------------------------+
|   |   sparkle    |    misty     |    crazy    |                 furry                 |
+---+--------------+--------------+-------------+---------------------------------------+
| 0 | furry boots  | furry animal | heckin food | furry boots, furry animal             |
| 1 | weird boots  | big animal   | furry food  | furry food                            |
| 2 | furry gloves | furry fence  | furry toes  | furry gloves, furry fence, furry toes |
| 3 | weird gloves | old fence    | old toes    |                                       |
| 4 | furry coat   | furry door   | furry hat   | furry coat, furry door, furry hat     |
| 5 | weird coat   | old door     | crazy cat   |                                       |
+---+--------------+--------------+-------------+---------------------------------------+

My Current Solution

df['furry'] = ''
df
df.loc[df['sparkle'].str.contains('furry'), 'furry'] = df['sparkle']
df.loc[df['misty'].str.contains('furry'), 'furry'] = df['furry'] + ', ' + df['misty']
df.loc[df['crazy'].str.contains('furry'), 'furry'] = df[['furry', 'crazy']].apply(lambda x: ', '.join(x), axis=1)
df


+---+--------------+--------------+-------------+---------------------------------------+
|   |   sparkle    |    misty     |    crazy    |                 furry                 |
+---+--------------+--------------+-------------+---------------------------------------+
| 0 | furry boots  | furry animal | heckin food | furry boots, furry animal             |
| 1 | weird boots  | big animal   | furry food  | , furry food                          |
| 2 | furry gloves | furry fence  | furry toes  | furry gloves, furry fence, furry toes |
| 3 | weird gloves | old fence    | old toes    |                                       |
| 4 | furry coat   | furry door   | furry hat   | furry coat, furry door, furry hat     |
| 5 | weird coat   | old door     | crazy cat   |                                       |
+---+--------------+--------------+-------------+---------------------------------------+

This "works" and I can clean up afterword, but it feels lousy. Hoping to learn here.

What I'm trying & struggling with:

Like I mentioned up top, I want to reduce this to read over 12 columns, many rows and for a library of words. I feel like I'm almost there ... I've looked at ''.join(), scanned through documentation for concat(), merge() ... I'm just getting stumped.

df = pd.DataFrame({'sparkle': ['furry boots', 'weird boots', 'furry gloves', 'weird gloves', 'furry coat', 'weird coat'],
      'misty': ['furry animal', 'big animal', 'furry fence', 'old fence', 'furry door', 'old door'],
      'crazy': ['heckin food', 'furry food', 'furry toes', 'old toes', 'furry hat', 'crazy cat']})
df['furry'] = ''

words = ['furry', 'old'] # added another word to demonstrate intent with real data
for key, value in df.items():
    df.loc[df[key].str.contains('|'.join(words)), 'furry'] = df['furry'] + ', ' + df[key]

df



+---+--------------+--------------+-------------+----------------------------------------------------------------------------------+
|   |   sparkle    |    misty     |    crazy    |                                      furry                                       |
+---+--------------+--------------+-------------+----------------------------------------------------------------------------------+
| 0 | furry boots  | furry animal | heckin food | , furry boots, furry animal, , furry boots, furry animal                         |
| 1 | weird boots  | big animal   | furry food  | , furry food, , furry food                                                       |
| 2 | furry gloves | furry fence  | furry toes  | , furry gloves, furry fence, furry toes, , furry gloves, furry fence, furry toes |
| 3 | weird gloves | old fence    | old toes    | , old fence, old toes, , old fence, old toes                                     |
| 4 | furry coat   | furry door   | furry hat   | , furry coat, furry door, furry hat, , furry coat, furry door, furry hat         |
| 5 | weird coat   | old door     | crazy cat   | , old door, , old door                                                           |
+---+--------------+--------------+-------------+----------------------------------------------------------------------------------+

Does anyone have any pointers / tips? Thanks for reading.

Upvotes: 1

Views: 57

Answers (1)

Poojan
Poojan

Reputation: 3519

  • you can use apply function
words = ['furry', 'old']
for word in words:
    df[word] = df.apply(lambda x: ', '.join([str(c) for c in x if word in str(c)]), axis=1)
df['all_combined'] = df[words].apply(lambda x:', '.join(x), axis=1)
df = df.drop(words, axis=1)

Update: You can iterate over multiple words and create new column for each.
Update2: Again you can combine this using apply.

Solution 2:

  • after getting your full question this looks most elegant solution to me.
words = ['furry', 'old']
df['all_combined'] = df.apply(lambda x: ', '.join([str(c) for c in x if any([w in str(c) for w in words])]), axis=1)

Upvotes: 3

Related Questions