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

Question

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:

from the name in 'first table', add the remaining columns from the 'second table' with the same name
if name cannot be found in 'second table', leave it blank
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!

jezrael · Accepted Answer · 2021-02-25 09:22:03Z

1

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

edited Feb 25, 2021 at 9:22

answered Feb 25, 2021 at 8:41

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related