Merging two dataframes with pandas

Question

This is a subset of data frame F1:

id        code    s-code
l.1        1       11
l.2        2       12
l.3        3       13
f.1        4       NA
f.2        3        1
h.1        2        1
h.3        1        1

I need to compare the F1.id with F2.id and then add the differences in column "id" to the F2 data frame and fill in columns' values for the added "id" with 0.

this is the second data frame F2:

id        head    sweat  pain
l.1        1       0      1
l.3        1       0      0
f.2        3        1     1
h.3        1        1     0

The output should be like this:

F3:

id        head    sweat  pain
l.1        1       0      1
l.3        3       13     0  
f.2        3        1     1
h.1        2        1     1
h.3        1        1     0
l.2        0        0     0
h.1        0        0     0
f.1        0        0     0

I tried different solution, such as F1[(F1.index.isin(F2.index)) & (F1.isin(F2))] to return the differences, but non of them worked.

Check your expected output... there's a 13 in there. Is that a mistake? — cs95
– cs95, Commented Oct 4, 2017 at 22:43

BENY · Accepted Answer · 2017-10-04 22:51:49Z

4

By using reindex

df2.set_index('id').reindex(df1.id).fillna(0).reset_index()
Out[371]: 
    id  head  sweat  pain
0  l.1   1.0    0.0   1.0
1  l.2   0.0    0.0   0.0
2  l.3   1.0    0.0   0.0
3  f.1   0.0    0.0   0.0
4  f.2   3.0    1.0   1.0
5  h.1   0.0    0.0   0.0
6  h.3   1.0    1.0   0.0

answered Oct 4, 2017 at 22:51

BENY

324k22 gold badges176 silver badges250 bronze badges

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

7 Comments

cs95 Over a year ago

Very nice. You can shorten this: df2.set_index('id').reindex(df['id'], fill_value=0).reset_index() - bonus: you get int columns, so no need to convert.

piRSquared Over a year ago

It also preserves the dtypes

BENY Over a year ago

@cᴏʟᴅsᴘᴇᴇᴅ Yes you are right :) much nicer! BTW ...I do not think I get the expected out put he want ....

cs95 Over a year ago

It's the same as mine though? OP seemed to approve of it.

BENY Over a year ago

@cᴏʟᴅsᴘᴇᴇᴅ Base on his description , ours solution should be ok, but if you look at his expected out put ....

|

cs95 · Accepted Answer · 2017-10-04 22:49:11Z

3

Use an outer merge + fillna:

df[['id']].merge(df2, how='outer')\
            .fillna(0).astype(df2.dtypes)

    id  head  sweat  pain
0  l.1     1      0     1
1  l.2     0      0     0
2  l.3     1      0     0
3  f.1     0      0     0
4  f.2     3      1     1
5  h.1     0      0     0
6  h.3     1      1     0

edited Oct 4, 2017 at 22:49

answered Oct 4, 2017 at 22:42

cs95

406k106 gold badges744 silver badges797 bronze badges

3 Comments

Mary Over a year ago

@COLDSPEED, Thank you. Is there anyway not to mention the name of columns from df2? I have about 40 columns in df2.

piRSquared Over a year ago

astype(df2.dtypes)

cs95 Over a year ago

piRSquared, Wonderful. I didn't know you could do that. @Mary, you have your answer.

piRSquared · Accepted Answer · 2017-10-04 23:08:10Z

3

Outside the Box

i = np.setdiff1d(F1.id, F2.id)
F2.append(pd.DataFrame(0, range(len(i)), F2.columns).assign(id=i))

    id  head  sweat  pain
0  l.1     1      0     1
1  l.3     1      0     0
2  f.2     3      1     1
3  h.3     1      1     0
0  f.1     0      0     0
1  h.1     0      0     0
2  l.2     0      0     0

With a normal index

i = np.setdiff1d(F1.id, F2.id)
F2.append(
    pd.DataFrame(0, range(len(i)), F2.columns).assign(id=i),
    ignore_index=True
)

    id  head  sweat  pain
0  l.1     1      0     1
1  l.3     1      0     0
2  f.2     3      1     1
3  h.3     1      1     0
4  f.1     0      0     0
5  h.1     0      0     0
6  l.2     0      0     0

edited Oct 4, 2017 at 23:08

answered Oct 4, 2017 at 23:05

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Collectives™ on Stack Overflow

Merging two dataframes with pandas

3 Answers 3

7 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related