Find exact cell-values in column1 and join values of column2 into new column

user_11163209 0
Viewed 50
import pandas as pd
import numpy as np

df = pd.read_csv ('input.csv')
df.head()
df['visibility'] = np.where(df['sku']== df['parent-sku'], 'visible', 'search') #add column if same sku in parent-sku
  
combo4 = df.groupby(['parent-sku'])['sku'].apply(', '.join)
data = print(combo4)
df['grouped items'] = np.where(df['sku']== df['parent-sku'], data, '') #format(combo2)


df.to_csv('output.csv', index=False)

So, my goal is to go over all CSV file, Check in column "PARENT-SKU" for repeating value in row, (4 items are same for exaple); take values from SKU column (each is unique) next to it; Merge with comma and append to new GROUPED column - only to first repeating of PARENT-SKU or only the one that has "VISIBLE"

Can't wrap my head around it. Whats wrong with it? Curent output on grouped items is NaN.

Example of data:

Parent SKU SKU VISIBLE
A A1 VISIBLE
A A2 SEARCH
A A3 SEARCH
B B1 VISIBLE
B B2 SEARCH

I get wery veird results, when tried with function, would love to see output:

Parent SKU SKU VISIBLE GROUPED ITEMS
A A1 VISIBLE A1,A2,A3
A A2 SEARCH
A A3 SEARCH
B B1 VISIBLE B1,B2
B B2 SEARCH

Thanks a lot! And a beer for you!

pandas csv numpy python
2 Answers

You can .groupby and join the items in the group to a string. From there use mask to identify duplicated() rows, which you can change to ''.

Per your comment, you can also exclude certain words using replace, but you have to use some regex to also replace the comma depending if at front or back, but you don't want to replace font and back comma. Otherwise, some items won't be separated by commas. This regex (,)? looks backwards for an optional comma:

srs = df.groupby('Parent SKU')['SKU'].transform(','.join)
df['GROUPED ITEMS'] = (srs.replace(['simple(,)?', '(,)?simple'], '', regex=True)
                          .mask(srs.duplicated(), ''))
df

OR If you want in list format, then you can use str.split(','):

srs = df.groupby('Parent SKU')['SKU'].transform(','.join)
df['GROUPED ITEMS'] = (srs.replace(['simple(,)?', '(,)?simple'], '', regex=True)
                          .mask(srs.duplicated(), ''))
df

Out[1]: 
  Parent SKU     SKU  VISIBLE GROUPED ITEMS
0          A      A1  VISIBLE         A1,A3
1          A  simple   SEARCH              
2          A      A3   SEARCH              
3          B      B1  VISIBLE         B1,B2
4          B      B2   SEARCH
user_63667700

Heres one way of doing that.

my_dict = {}
my_list = []

for val in df['Parent SKU'].values:
    my_dict[val] = []

for val in df['SKU'].values:
    if val[0] in my_dict:
       my_dict[val[0]].append(val)
    
for val in df['SKU'].values:
    if val[1] == '1':
        my_list.append(my_dict[val[0]])
    else:
        my_list.append('')

df['GROUPED ITEMS'] = my_list

Output:

     parentSKU  SKU   visible        GROUPED ITEMS
0         A     A1      visible        [A1, A2, A3]
1         A     A2      search  
2         A     A3      search  
3         B     B1      visible        [B1, B2]
4         B     B2      search
user_147222970
Related