CODEWITHSUNDEEP
pythonpandasdataframe
isaac.af95
isaac.af95

Reputation: 113

Update of the last element of a dataframe by another dataframe

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?

Upvotes: 1

Views: 36

Answers (1)

jezrael
jezrael

Reputation: 863751

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

Upvotes: 1

Related Questions