Merging pandas DataFrames with deleting some part of data

Question

I have these two dictionaries, that I would like to merge, but without deleting data of a, but deleting the non-matching values of b:

a = pd.DataFrame({'coname': ['Apple','Microsoft','JPMorgan','Facebook','Intel','McKinsey'],
    'eva': [20, 18, 73, 62, 56, 92],
    'ratio': [4, 7, 1, 6, 9, 8]
    })

b = pd.DataFrame({'coname': ['Apple','Microsoft','JPMorgan','Netflix','Total','Ford'],
     'city': ['Cupertino','Seattle','NYC','Palo Alto','Paris', 'Detroit'],
    'state': ['CA','WA','NY','CA','Ile de France', 'MI']
    })

I want to following output: EDITED

     coname   eva  ratio       city          state
0      Apple  20.0    4.0  Cupertino             CA
1  Microsoft  18.0    7.0    Seattle             WA
2   JPMorgan  73.0    1.0        NYC             NY
3   Facebook  62.0    6.0        NaN            NaN
4      Intel  56.0    9.0        NaN            NaN
5   McKinsey  92.0    8.0        NaN            NaN

I have tried

a = pd.merge(a,b, on = 'coname', how='outer')
for i in a['coname']:
    if i in b['coname']:
        a.drop(i)

with but I only get this:

      coname   eva  ratio       city          state
0      Apple  20.0    4.0  Cupertino             CA
1  Microsoft  18.0    7.0    Seattle             WA
2   JPMorgan  73.0    1.0        NYC             NY
3   Facebook  62.0    6.0        NaN            NaN
4      Intel  56.0    9.0        NaN            NaN
5   McKinsey  92.0    8.0        NaN            NaN
6    Netflix   NaN    NaN  Palo Alto             CA
7      Total   NaN    NaN      Paris  Ile de France
8       Ford   NaN    NaN    Detroit             MI

score 0 · Answer 1 · answered Mar 31 '21 at 21:16

how='left' will do the trick:

pd.merge(a,b, on = 'coname', how='left')

Result:

	coname	eva	ratio	city	state
0	Apple	20	4	Cupertino	CA
1	Microsoft	18	7	Seattle	WA
2	JPMorgan	73	1	NYC	NY
3	Facebook	62	6	nan	nan
4	Intel	56	9	nan	nan
5	McKinsey	92	8	nan	nan

Merging pandas DataFrames with deleting some part of data

1 Answers1