Update of the last element of a dataframe by another dataframe

user_13175278 0
Viewed 31

I have the following dataframes:

df = pd.DataFrame({'nameCompany': ['Piestrita Inc', 'Total Play', 'Yate Inc', 'Spider Comp', 'Tech solutions', 'LG Inno'], 
                   'code': ['1', '1', '2', '3', '3', '3']
                    'results': ['Rick', 'Patram', 'Pulis', 'Marie', 'Landon', 'Freddy']})

df2 = pd.DataFrame({'nameCompany': ['Alaska Inc', 'Kira', 'Joli Molly', 'Health Society'], 
                   'code': ['1', '2', '3', '3']})

df:

nameCompany code results
Piestrita Inc 1 Rick
Total Play 1 Patram
Yate Inc 2 Pulis
Spider Comp 3 Marie
Tech solutions 3 Landon
LG Inno 3 Freddy

df2:

nameCompany code
Alaska Inc 1
Kira 2
Joli Molly 3
Health Society 3

I need to make an update in the df in order to update the value of companyName if it appears in the df2 the code of the df, this update must be on the last element of the df if only one code appears in the df2 but if more appear it must be in the last positions, therefore, the output should be the following one:

df_new = pd.DataFrame({'nameCompany': ['Piestrita Inc', 'Alaska Inc', 'Kira', 'Spider Comp', 'Joli Molly', 'Health Society'], 
                   'code': ['1', '1', '2', '3', '3', '3']
                    'results': ['Rick', 'Patram', 'Pulis', 'Marie', 'Landon', 'Freddy']})

df_new:

nameCompany code results
Pietrista Inc 1 Rick
Alaska Inc 1 Patram
Kira 2 Pulis
Spider Comp 3 Marie
Joli Molly 3 London
Health Society 3 Freddy

I have tried with the update method but I have not obtained the expected results, any suggestions?

pandas dataframe python
1 Answers

Use GroupBy.cumcount with ascending=False for counter column from last values, then use DataFrame.merge by it and code and last use Series.combine_first:

df['g'] = df.groupby('code').cumcount(ascending=False)
df2['g'] = df2.groupby('code').cumcount(ascending=False)


df = df.merge(df2, on=['code','g'], how='left', suffixes=['','_']).drop('g', axis=1)
df['nameCompany'] = df.pop('nameCompany_').combine_first(df['nameCompany'])
print (df)
      nameCompany code results
0   Piestrita Inc    1    Rick
1      Alaska Inc    1  Patram
2            Kira    2   Pulis
3     Spider Comp    3   Marie
4      Joli Molly    3  Landon
5  Health Society    3  Freddy
user_29010020
Related