I have 2 csv files lets say A.csv and B.csv. A.csv has columns a,b,c,d and B.csv has columns x,y,z,t. I want to search that if an entry in column a exist in column x then print z and d if that rows.
Like,
for each i in A
if A.[a][i] exist in B.x
print A.[d][i] + B.[z][i]
-- I have already the code below. I just need to learn how to settle the code for this
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
A = pd.read_csv('path1')
B = pd.read_csv('path2')
Imagine your csv data file looks like below:
print(df1)
A B C D
0 1 4 7 4
1 2 5 8 5
2 3 6 9 8
print(df2
X Y Z T
0 1 11 6 8
1 5 12 8 0
2 2 13 0 4
A simple merge would solve your problem, considering Left table is df1 and Right is df2
df = df1.merge(df2,left_on='A',right_on='X')[['Z','D']]
print(df)
Z D
0 6 4
1 0 5
This will return the matched row elements from mentioned columns. (here Z,D)
I think the simplest solution is to use a left join:
>>> print(A)
a b c d
0 1 2 3 4
1 2 3 4 5
2 4 4 5 6
>>> print(B)
x y z t
0 1 20 30 40
1 3 4 5 6
>>> result = A.merge(left_on='a', right=B, right_on='x', how='left')[['z', 'd']].dropna()
>>> print(result)
z d
0 30.0 4
AandBand an expected output?