Merging from df1 to df2 on same column - expanding dataset out

Question

I am making a small mistake and I'm not sure how to merge two df correctly. I want to merge on IBES_cusip to get gvkey into df1.

I try the following, but it just expands the dataset out and does not match correctly:

df1 = df1.merge(df2, how = 'left', on =['IBES_cusip'])

df1

       IBES_cusip       pends pdicity  ... ltg_eps  futepsgrowth 
0        00036110  1983-05-31     ANN  ...     NaN           NaN  
1        00036110  1983-05-31     ANN  ...     NaN           NaN  
2        00036110  1983-05-31     ANN  ...     NaN           NaN  
3        98970110  1983-05-31     ANN  ...     NaN           NaN  
4        98970110  1983-05-31     ANN  ...     NaN           NaN  
          ...         ...     ...  ...     ...           ...      
373472   98970111  2018-12-31     ANN  ...   10.00      0.381119  
373473   98970111  2018-12-31

df2

         gvkey IBES_cusip
0       024538   86037010
1       004678   33791510
2       066367   26357810
3       137024   06985P20
4       137024   06985P20
       ...        ...
833796  028955   33975610
833797  061676   17737610
833798  011096   92035510
833799  005774   44448210
833800  008286   69489010

Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — wwnde, Oct 08 '20 at 23:00
Which one? I had a look through Pandas Merging 101 before and wasn't sure which one would work — oceanbeach96, Oct 08 '20 at 23:03
I went through that before and the merging I used was based off that. It is not working and that's why I reached out with this question — oceanbeach96, Oct 08 '20 at 23:07
IBES_cusip is in both. I just want the gvkey in df2 to be in df1 with the same IBES_cusip — oceanbeach96, Oct 08 '20 at 23:09
For one column, you can do `map`: `df1['gvkey'] = df1['IBES_cusip'].map( df2.set_index('IBES_cusip')['gvkey'] )`. — Quang Hoang, Oct 08 '20 at 23:21
Also, once you say *they all did not work*, you should definitely say **in your question** why that was the case. Did they throw some error? Did they produce unexpected output? etc. — Quang Hoang, Oct 08 '20 at 23:23
The map you provided raised this error: InvalidIndexError: Reindexing only valid with uniquely valued Index objects — oceanbeach96, Oct 09 '20 at 00:08

score 2 · Accepted Answer · answered Oct 09 '20 at 00:32

Your main problem is that your df2 contains duplicate values in IBES_cusip column. from the sample you gave I can see that

3       137024   06985P20
4       137024   06985P20

are the same values, this would cause the to get unwanted results (duplicate rows in the output).
try this

df1 = df1.merge(df2.drop_duplicates(subset=['IBES_cusip']), how='left', on='IBES_cusip')

Which should technically just add a gvkey column to your df1.

This assumes that you are pretty sure that you don't have rows with the same IBES_cusip that are matched with different gvkey otherwise you need to figure that out first.

Ahhh! Perfect. My mistake. Thanks for the help! :) – oceanbeach96 Oct 09 '20 at 01:01 — oceanbeach96, Oct 09 '20 at 01:01

Merging from df1 to df2 on same column - expanding dataset out

1 Answers1