Reputation: 126
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
Reputation: 3519
apply
functionwords = ['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:
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