pandas - merge two data frames overwrite and specify which columns to keep

user_2261950 0
Viewed 8496

Im trying to merge to panda dataframes, although what I want may not actually be a merge.

I have a two columns in two frames that match, one column shares unique values that can be used to join. the other column has one empty field and one populated one.

I want to overwrite the emtpy fields whilst matching on the unique fields but only keep the column thats overwritten, I do not want the rest of the columns from the second DataFrame.

hopefully the below will explain a little further

>>> animals = [{"animal" : "dog", "name" : "freddy", "food" : ""},{"animal" : "cat", "name" : "dexter", "food" : ""},{"animal" : "dog", "name" : "lou lou", "food" : ""}]
>>> foods = [{"name" : "freddy", "food" : "dog mix", "brand" : "doggys dog"},{"name" : "dexter", "food" : "fussy cat mix", "brand" : "fish fishy"},{"name" : "lou lou", "food" : "bones", "brand" : "i was a cow"}]
>>> a_pd = pd.DataFrame(animals)
>>> a_pd
  animal food     name
0    dog        freddy
1    cat        dexter
2    dog       lou lou
>>> f_pd = pd.DataFrame(foods)
>>> f_pd
         brand           food     name
0   doggys dog        dog mix   freddy
1   fish fishy  fussy cat mix   dexter
2  i was a cow          bones  lou lou
>>>
>>>
>>> animal_data = a_pd.merge(f_pd, on='name', how='left')
>>> animal_data
  animal food_x     name        brand         food_y
0    dog          freddy   doggys dog        dog mix
1    cat          dexter   fish fishy  fussy cat mix
2    dog         lou lou  i was a cow          bones
>>>

I should just have food and I dont want the brand (also to note this is sample data and the live data has a lot more columns

desired results

>>> animal_data
  animal        name            food
0    dog      freddy         dog mix
1    cat      dexter   fussy cat mix
2    dog     lou lou           bones
pandas python
4 Answers

Use:

animal_data = a_pd.merge(f_pd, on='name', how='left', suffixes=('_x','')).drop('food_x', axis=1)

Output:

  animal     name        brand           food
0    dog   freddy   doggys dog        dog mix
1    cat   dexter   fish fishy  fussy cat mix
2    dog  lou lou  i was a cow          bones

Or

a_pd[['animal','name']].merge(f_pd, how='left')

Output:

  animal     name        brand           food
0    dog   freddy   doggys dog        dog mix
1    cat   dexter   fish fishy  fussy cat mix
2    dog  lou lou  i was a cow          bones
user_63615310

You can using update 

a_pd.set_index('name',inplace=True)
a_pd.update(f_pd.set_index('name'))
a_pd
Out[68]: 
        animal           food
name                         
freddy     dog        dog mix
dexter     cat  fussy cat mix
lou lou    dog          bones
a_pd.reset_index()
Out[69]: 
      name animal           food
0   freddy    dog        dog mix
1   dexter    cat  fussy cat mix
2  lou lou    dog          bones

Or we using map 

a_pd.food=a_pd.name.map(f_pd.set_index('name').food)
a_pd
Out[74]: 
  animal           food     name
0    dog        dog mix   freddy
1    cat  fussy cat mix   dexter
2    dog          bones  lou lou
user_79645270

I'd either try drop or just selecting columns you want to keep:

animal_data.drop(['food_x', 'brand'], axis=1, inplace=True)

or 

animal_data = animal_data[['animal', 'name', 'food']]
user_94139900

It might be best to merge views of the dataframes that do not contain the columns you don't want in the merged dataframe. For example:

a_cols = ['animal', 'name']
f_cols = ['food', 'name']
a_pd[a_cols].merge(f_pd[f_cols], on='name', how='left')

This may be faster and may save you some memory if working with extremely large dataframes, as only the relevant columns are carried forward in the merge.

user_50757200
Related