CODEWITHSUNDEEP
pythonexcelpandasdataframe
Yukarii
Yukarii

Reputation: 27

Python Pandas - add column on a specific row, add specific row from one dataframe to another

Have being trying this desperately for 7 hours and still hasnt figured out a solution. So I have 2 Dataframes that I want to combine,using python pandas, with the below conditions:

  1. from the name in 'first table', add the remaining columns from the 'second table' with the same name
  2. if name cannot be found in 'second table', leave it blank
  3. if name is present in 'second table' but not in 'first table', add a new row in the dataframe with 'number' and 'description' (WITHOUT THE NAME) in the 'second table'

first table example:

    Name
 0  Apple
 1   Bear
 2    Car
 3    Dog

Second table example:

       Name  Number   Description
0     Apple       1    I am apple
1      Bear       2  you are bear
2       Dog       4   so are dogs
3  Elephant       5           moo

ideal result:

    Name  Number   Description
0  Apple     1.0    I am apple
1   Bear     2.0  you are bear
2    Car     NaN           NaN
3    Dog     4.0   so are dogs
4    NaN     5.0           moo

I have tried merging but it would not work (both inner and outer ways of pandas merging would give incorrect results) Once I include more conditions then I would get lots of errors.

I was able to get to this point (finding the name 'firsttable' row in 'secondtable')

for a in firsttable['Name']:
    for b in secondtable['Name']:
        if a == b:
            row = pd.DataFrame(secondtable.loc[(secondtable['Name']== b)])

but after that I was not able to add it into the dataframe. Thanks for the help!

Upvotes: 1

Views: 521

Answers (1)

jezrael
jezrael

Reputation: 862921

Use outer join in DataFrame.merge and then set NaN (default value) in Series.where for not matched values by df1['Name'] tested by Series.isin:

df = df1.merge(df2, on='Name', how='outer')
df['Name'] = df['Name'].where(df['Name'].isin(df1['Name']))
print (df)
    Name  Number   Description
0  Apple     1.0    I am apple
1   Bear     2.0  you are bear
2    Car     NaN           NaN
3    Dog     4.0   so are dogs
4    NaN     5.0           moo

Upvotes: 1

Related Questions