How to compare and match data from different columns of same dataframe

Question

i am new to programming and trying to learn python. pardon me if this sounds silly. i am trying to compare two columns in a dataframe and match the values based on the first column(used as reference). when the values in first column are not available in second or third columns, then i need to enter an NaN. could anyone helpme out how to do this? please look at the input and expected output below

Input dataframe:

index	A	B	C
0	290	390	160
1	390	450	290
2	160	290	NaN
3	450	NaN	450

Expected Output

index	A	B	C
0	290	290	290
1	390	390	NaN
2	160	NaN	160
3	450	450	450

badhusha muhammed · Accepted Answer · 2020-12-15 20:30:14Z

1

You can do something like this

df = pd.DataFrame([[290, 390, 160],[390, 450, 290], [160, 290, np.NaN], [450, np.NaN, 450]], columns=['A', 'B', 'C'])

lis = list(df['A'])
print(lis)

Output

[290, 390, 160, 450]

Then

b = [i if i in list(df['B']) else np.nan for i in lis]
c = [i if i in list(df['C']) else np.nan for i in lis]
print(b)
print(c)

Output

[290, 390, nan, 450] #b
[290, nan, 160, 450] #c

Replace the column B,C with list b and c

df = df.assign(B=b)
df = df.assign(C=c)

index	A	B	C
0	290	290	290
1	390	390	NaN
2	160	NaN	160
3	450	450	450

edited Dec 15, 2020 at 20:30

answered Dec 15, 2020 at 20:13

badhusha muhammed

4914 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

How to compare and match data from different columns of same dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related