Exracting to a list based on column conditions

Question

Trying to get competitor vendors for related parts. Requirement - If two vendors supplies one part, they will be competitors & it will be mapped against all related parts of vendors

#Input Data
list1 = ['A','A','A','D','E','F','G','H','I','J','K','L','M','N','O','Q']
list2 = ['A1','A2','A3','A4','A5','A6','A1','A1','A9','A10','A11','A12','A13','A14','A15','A16']

Input = pd.DataFrame({'Materials' : list1, 'Vendors':list2})
print(Input)

#Output required
list1 = ['A','A','A','D','E','F','G','H','I','J','K','L','M','N','O','Q']
list2 = ['A1','A2','A3','A4','A5','A6','A1','A1','A9','A10','A11','A12','A13','A14','A15','A16']
list3 = ['[A1,A2,A3]','[A1,A2,A3]','[A1,A2,A3]','A4','A5','A6','[A1,A2,A3]','[A1,A2,A3]','A9',
     'A10','A11','A12','A13','A14','A15','A16']
Output = pd.DataFrame({'Materials' : list1, 'Vendors':list2, 'Output':list3})
print(Output)

#Trying to get info of competitor vendors for all related materials
#Code which i am currently trying
df['gt_1'] = df.groupby(Vendor).Material.transform('nunique').explode()

Any suggestions pls

score 1 · Accepted Answer · answered Apr 24 '20 at 13:23

This should work:

df = Input.groupby(['Materials'],as_index=False).agg(lambda x: " ".join(x))\
          .rename(columns={'Vendors':'Competitors'})

df = Input.merge(df,how='left').groupby("Vendors")["Competitors"]\
          .agg(lambda x: " ".join(x)).apply(lambda x: set(x.split(" "))).reset_index()

Output = Input.merge(df,how='left')
Output

score 0 · Answer 2 · answered Apr 24 '20 at 16:21

This should work

grouped_data = Input.groupby('Materials')
Input['Output'] = 0
for key, group in grouped_data:
    vendors_list = group.Vendors.tolist()
    for i in range(len(Input)):
        if((Input.Vendors.loc[i] in vendors_list) and Input.Flag.loc[i] != 1):
            Input.Output.loc[i] = vendors_list
            Input.Flag.loc[i] = 1
Input.drop(columns = ['Flag'], inplace = True)

Exracting to a list based on column conditions

2 Answers2