Pandas: replace values in dataframe with another dataframes values based on condition

Question

I have the following dataframes:

df1:

+-----+------+------+------+------+------+
| No. | col1 | col2 | col3 | Type | ...  |
+-----+------+------+------+------+------+
| 123 |    2 |    5 |    2 | MN   | ...  |
| 453 |    4 |    3 |    1 | MN   | ...  |
| 146 |    7 |    9 |    4 | AA   | ...  |
| 175 |    2 |    4 |    3 | MN   | ...  |
| 643 |    0 |    0 |    0 | NAN  | ...  |
+-----+------+------+------+------+------+

df2:

+-----+------+------+------+------+
| No. | col1 | col2 | col3 | Type |
+-----+------+------+------+------+
| 123 |   24 |   57 |   22 | MN   |
| 453 |   41 |   39 |   15 | MN   |
| 175 |   21 |   43 |   37 | MN   |
+-----+------+------+------+------+

I want to replace col1, col2 and col3 in df1 with corresponding values in df2 if Type equals MN

Desired output:

df1:

+-----+------+------+------+------+-----+
| No. | col1 | col2 | col3 | Type | ... |
+-----+------+------+------+------+-----+
| 123 |   24 |   57 |   22 | MN   | ... |
| 453 |   41 |   39 |   15 | MN   | ... |
| 146 |    7 |    9 |    4 | AA   | ... |
| 175 |   21 |   43 |   37 | MN   | ... |
| 643 |    0 |    0 |    0 | NAN  | ... |
+-----+------+------+------+------+-----+

EDIT

I tried:

df1[df1.Type == 'MN'] = df2.values

but I get this error:

ValueError: Must have equal len keys and value when setting with an ndarray

Guess the reason is, that df2 does not have equal number of columns. So how do I make sure, that only the specific columns (col1 - col3) are replaced in df1?

jezrael · Accepted Answer · 2018-05-14 07:55:27Z

2

I think need combine_first for match by No. column:

#filter only `MN` rows if necessary
df22 = df2[df2['Type'] == 'MN'].set_index('No.')
df1 = df22.combine_first(df1.set_index('No.')).reset_index().reindex(columns=df1.columns)
print (df1)

   No.  col1  col2  col3 Type  col
0  123  24.0  57.0  22.0   MN  ...
1  146   7.0   9.0   4.0   AA  ...
2  175  21.0  43.0  37.0   MN  ...
3  453  41.0  39.0  15.0   MN  ...
4  643   0.0   0.0   0.0  NAN  ...

edited May 14, 2018 at 7:55

answered May 14, 2018 at 7:14

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

MaMo Over a year ago

Great, the first one works. With the .reindex(df1['No.']) I get the error: ValueError: cannot reindex from a duplicate axis

jezrael Over a year ago

@MaMo - need same order as df1 ?

MaMo Over a year ago

looks like the order of df1 is used anyway. :D

MaMo Over a year ago

you mean the order of the rows? Their order does not matter, only the order of the columns matter

jezrael Over a year ago

@MaMo - Need only first solution. :)

|

pe-perry · Accepted Answer · 2018-05-14 07:58:40Z

Your code doesn't work because the number of columns of df1 and df2 are different.

from io import StringIO
import pandas as pd

x1 = """No.,col1,col2,col3,Type,Oth
123,2,5,2,MN,...
453,4,3,1,MN,...
146,7,9,4,AA,...
175,2,4,3,MN,...
643,0,0,0,NAN,...
"""
x2 = """No.,col1,col2,col3,Type
123,24,57,22,MN
453,41,39,15,MN
175,21,43,37,MN
"""

df1 = pd.read_csv(StringIO(x1), sep=",")
df2 = pd.read_csv(StringIO(x2), sep=",")

df1.loc[df1.Type == 'MN', ["No.","col1","col2","col3","Type"]] = df2.values
# Output:
# >>> print(df1)
#    No.  col1  col2  col3 Type  Oth
# 0  123    24    57    22   MN  ...
# 1  453    41    39    15   MN  ...
# 2  146     7     9     4   AA  ...
# 3  175    21    43    37   MN  ...
# 4  643     0     0     0  NAN  ...

But there is a problem if the column order of df1 and df2 are different.

df1 = pd.read_csv(StringIO(x1), sep=",")
df3 = df2.copy()[["No.","Type","col1","col2","col3"]]
df1.loc[df1.Type == 'MN', ["No.","col1","col2","col3","Type"]] = df3.values
# Output: 
# >>> print(df1)
#    No. col1  col2  col3 Type  Oth
# 0  123   MN    24    57   22  ...
# 1  453   MN    41    39   15  ...
# 2  146    7     9     4   AA  ...
# 3  175   MN    21    43   37  ...
# 4  643    0     0     0  NAN  ...

To avoid this, you can try

df1.loc[df1.Type == 'MN', ["No.","col1","col2","col3","Type"]] = (
    df3[["No.","col1","col2","col3","Type"]].values)
# Output:
# >>> print(df1)
#    No.  col1  col2  col3 Type  Oth
# 0  123    24    57    22   MN  ...
# 1  453    41    39    15   MN  ...
# 2  146     7     9     4   AA  ...
# 3  175    21    43    37   MN  ...
# 4  643     0     0     0  NAN  ...

However, there is still a problem if the number of 'MN' records are different in df1 and df2

df1 = pd.read_csv(StringIO(x1), sep=",")
df4 = df2.copy().iloc[:2]
df1.loc[df1.Type == 'MN', ["No.","col1","col2","col3","Type"]] = (
    df4[["No.","col1","col2","col3","Type"]].values)
# Error: 
# ValueError: shape mismatch: value array of shape (2,) could not be broadcast to 
# indexing result of shape (3,)

So what you need may be like this

df = pd.merge(df1, df2, how='left', on=['No.', 'Type'])
df['col1'] = df.apply(lambda x: x.col1_y if x.Type == 'MN' else x.col1_x, axis=1)
df['col2'] = df.apply(lambda x: x.col2_y if x.Type == 'MN' else x.col2_x, axis=1)
df['col3'] = df.apply(lambda x: x.col3_y if x.Type == 'MN' else x.col3_x, axis=1)
df = df[["No.","col1","col2","col3","Type"]]
# Output:
#>>> print(df)
#   No.  col1  col2  col3 Type
#0  123  24.0  57.0  22.0   MN
#1  453  41.0  39.0  15.0   MN
#2  146   7.0   9.0   4.0   AA
#3  175  21.0  43.0  37.0   MN
#4  643   0.0   0.0   0.0  NAN

Collectives™ on Stack Overflow

Pandas: replace values in dataframe with another dataframes values based on condition

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related