Intersection of Pandas two dataframe with almost have same column except one

Question

tab1 having column name as: 'index', 'Province/State', 'Country/Region', 'Lat', 'Long', 'Date', 'Recovered'

tab2 having column name as: 'index', 'Province/State', 'Country/Region', 'Lat', 'Long', 'Date', 'Confirmed'

due to missing values in both the table so, I want to join these unordered (46550 × 6 and 44275 × 6) tables on common lat and long values. I tried merging join but failed to accomplish the results.

can you show your attempt? I think you need to do a union of both the tables. please read [mcve] and [ask] and add some sample data with an expected output. — Umar.H, Jul 15 '20 at 11:35
Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — Nicolas Gervais, Jul 15 '20 at 11:42

score 0 · Answer 1 · answered Jul 15 '20 at 11:32

Since you said you need "intersection", so I guess you need a inner join.

df = tab1.merge(tab2, how='inner', on=['Lat', 'Long'])

After this, you may have other columns duplicated because your dataframes have almost same columns as you claimed.

For example, you will have Province/State_x from tab1 and Province/State_y from tab2. You will need to drop these duplicated columns:

# E.g.
df.drop(columns=['Province/State_y'])

You probably also want to rename them back:

# E.g.
df.rename(columns={'Province/State_x': 'Province/State'})

@ShubhamRawatShubhi, if my answer worked, would you mind accept my answer so we can close this, please? Thanks — Christopher, Jul 19 '20 at 11:13

Intersection of Pandas two dataframe with almost have same column except one

1 Answers1