CODEWITHSUNDEEP
pythonpandasdataframemerge
Mary
Mary

Reputation: 1142

Merging two dataframes with pandas

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.

Upvotes: 1

Views: 78

Answers (3)

piRSquared
piRSquared

Reputation: 294516

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

Upvotes: 3

BENY
BENY

Reputation: 323376

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

Upvotes: 4

cs95
cs95

Reputation: 403128

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

Upvotes: 3

Related Questions