Tukukkk

I have dataframe with data as:

The value of "relation" is determined from the codeid. Leather has "codeid"=11 which is already appeared against bag, so in relation we put the value bag.

Same happens for shoes.

ToDo: Fill the value of "relation", by putting check on codeid in terms of dataframes. Any help would be appreciated.

Edit: Same codeid e.g. 11 can appear > twice. But the "relation" can have only value as bag because bag is the first one to have codeid=11. i have updated the picture as well.

If want only first dupe value to last duplicated use transform with first and then set NaN values by loc with duplicated:

df = pd.DataFrame({'id':[1,2,3,4,5],

                   'name':list('brslp'),

                   'codeid':[11,12,13,11,13]})



df['relation'] = df.groupby('codeid')['name'].transform('first')

print (df)

   id name  codeid relation

0   1    b      11        b

1   2    r      12        r

2   3    s      13        s

3   4    l      11        b

4   5    p      13        s

#get first duplicated values of codeid

print (df['codeid'].duplicated(keep='last'))

0     True

1    False

2     True

3    False

4    False

Name: codeid, dtype: bool



#get all duplicated values of codeid with inverting boolenam mask by ~ for unique rows   

print (~df['codeid'].duplicated(keep=False))

0    False

1     True

2    False

3    False

4    False

Name: codeid, dtype: bool



#chain boolen mask together 

print (df['codeid'].duplicated(keep='last') | ~df['codeid'].duplicated(keep=False))

0     True

1     True

2     True

3    False

4    False

Name: codeid, dtype: bool

#replace True values by mask by NaN 

df.loc[df['codeid'].duplicated(keep='last') | 

       ~df['codeid'].duplicated(keep=False), 'relation'] = np.nan

print (df)

   id name  codeid relation

0   1    b      11      NaN

1   2    r      12      NaN

2   3    s      13      NaN

3   4    l      11        b

4   5    p      13        s

I think you want to do something like this:

import pandas as pd

df = pd.DataFrame([['bag', 11, 'null'], 

                  ['shoes', 12, 'null'], 

                  ['shopper', 13, 'null'], 

                  ['leather', 11, 'bag'], 

                  ['plastic', 13, 'shoes']], columns = ['name', 'codeid', 'relation'])



def codeid_analysis(rows):

    if rows['codeid'] == 11:

        rows['relation'] = 'bag'

    elif rows['codeid'] == 12:

        rows['relation'] = 'shirt' #for example. You should put what you want here

    elif rows['codeid'] == 13:

        rows['relation'] = 'pants' #for example. You should put what you want here

    return rows



result = df.apply(codeid_analysis, axis = 1)

print(result)

It is not the optimal solution since it is costly to your memory, but here is my try. df1 is created in order to hold the null values of the relation column, since it seems that nulls are the first occurrence. After some cleaning, the two dataframes are merged to provide into one.

import pandas as pd

df = pd.DataFrame([['bag', 11, 'null'], 

                  ['shoes', 12, 'null'], 

                  ['shopper', 13, 'null'], 

                  ['leather', 11, 'bag'], 

                  ['plastic', 13, 'shopper'],

                  ['something',13,""]], columns = ['name', 'codeid', 'relation'])



df1=df.loc[df['relation'] == 'null'].copy()#create a df with only null values in relation

df1.drop_duplicates(subset=['name'], inplace=True)#drops the duplicates and retains the first entry

df1=df1.drop("relation",axis=1)#drop the unneeded column



final_df=pd.merge(df, df1, left_on='codeid', right_on='codeid')#merge the two dfs on the columns names