My problem is as following:

I have a first dataframe (df1):

This is my second datafrane (df2)

I would like to replace the 'ERROR' values on the 'Client' column of df1 using the values of df2 'Client' column, but the thing is that I'm having issues to do it based on the 'Ticket' values: In this example, the first ERROR should be replaced by Alphabet based on Ticket 3453, the second ERROR should be replaced by AMD based on Ticket 2364.

Finally, the desired output should be something like this:

• Just to mention, the dataframes do not have the same shape

CodePudding user response：

You can assign with map

df1.loc[df1['Client'].eq('ERROR'),'Client'] = df1['Ticket'].map(df2.set_index('Ticket')['Client'])
df1
Out[192]: 
     Client  Ticket  Region Profile
0       BCA    1234      US  Shanon
1  Alphabet    3453    EMEA   Laura
2        RZ    7988  EUROPE   Mitch
3       AMD    2364      US   James
4       Trp    3429      MX     Rog

CodePudding user response：

data = df1.merge(df2, on='Ticket', how='left')
data.loc[data.Client_x.eq("ERROR"), "Client_x"] = data.Client_y
data.drop(columns=['Client_y']).rename(columns={'Client_x': 'Client'})

CodePudding user response：

Here's a way:

df1 = df1.set_index('Ticket')
df1.loc[df1.Client=='ERROR','Client'] = df2.set_index('Ticket').Client
df1.Client = df1.Client.fillna('ERROR')
df1 = df1.reset_index()[['Client', 'Ticket']   [col for col in df1.columns if col != 'Client']]

Input:

df1
  Client  Ticket  Region Profile
0    BCA    1234      US  Shanon
1  ERROR    3453    EMEA   Laura
2     RZ    7988  EUROPE   Mitch
3  ERROR    2364      US   James
4    Trp    3429      MX   Roger
5  ERROR    9999      US   James

df2
     Client  Ticket
0       HHA    3534
1  Alphabet    3453
2        HP    2355
3       AMD    2364

Output:

     Client  Ticket  Region Profile
0       BCA    1234      US  Shanon
1  Alphabet    3453    EMEA   Laura
2        RZ    7988  EUROPE   Mitch
3       AMD    2364      US   James
4       Trp    3429      MX   Roger
5     ERROR    9999      US   James