How to combine two dataframes on a column, with missing rows in one of them?

user_17380645 0
Viewed 267

I am trying to combine dataframe_A:

file 1
file 2
file 3 
file 4 
file 5

with dataframe_B:

file 2 | some data  | more data
file 4 | other data | additional data
file 5 | data       | data data

along the file_name column, to end up with something like this:

file 1 |     ~      |      ~
file 2 | some data  | more data
file 3 |     ~      |      ~
file 4 | other data | additional data
file 5 | data       | data data

I want to end up with a dataframe the length of dataframe_A, with all the data from dataframe_B, and with blank/whatever in the intervening spaces.

the joins and merges I have tried so far just end up with something that looks like dataframe_B, which is not what I want. What do I need to do?

pandas dataframe python
1 Answers

Use merge with how='left' parameter:

>>> dfA.merge(dfB, on='A', how='left').fillna('~')

        A           B                C
0  file 1           ~                ~
1  file 2   some data        more data
2  file 3           ~                ~
3  file 4  other data  additional data
4  file 5        data        data data

I recommend reading our extended introduction: Pandas Merging 101

Setup:

dfA = pd.DataFrame({'A': ['file 1', 'file 2', 'file 3', 'file 4', 'file 5']})

dfB = pd.DataFrame({'A': ['file 2', 'file 4', 'file 5'],
                    'B': ['some data', 'other data', 'data'],
                    'C': ['more data', 'additional data', 'data data']})
user_152399510
Related