Combing two dataframes in Pandas Python

Question

I would like to combine two dataframes

I would like to combine both the dataframes such a way that the accts are the same.

For eg, acct 10 should values in CME and NISSAN while the rest are zeros.

It's easier if you format with print(df.to_string()), that way DataFrames can be easily read via `pd.read_clipboard()` — Andy Hayden, Feb 19 '19 at 05:27
```df1.set_index('acct').combine_first(df2.set_index('acct')).reset_index()``` This should work. — Aritesh, Feb 19 '19 at 05:29

anky · Answer 1 · 2019-02-19T05:48:26.250

2

I think you can use df.combine_first():

It will update null elements with value in the same location in other.

df2.combine_first(df1)

Also, you can try:

pd.concat([df1.set_index('acct'),df2.set_index('acct')],axis=1).reset_index()

edited Feb 19 '19 at 05:48

answered Feb 19 '19 at 05:32

anky

74,114
11
41
70

score 0 · Answer 2 · answered Feb 19 '19 at 05:41

It looks like what you're trying to do is merge these two DataFrames.

You can use df.merge to merge the two. Since you want to match on the acct column, set the on keyword arg to "acct" and set how to "inner" to keep only those rows that appear in both DataFrames.

For example:

merged = df1.merge(df2, how="inner", on="acct")

Output:

+------+--------------------+------------------+-------------------+-----------+--------------------+-------------------+--------------------+
| acct |       GOODM        |       KIS        |      NISSAN       |    CME    |        HKEX        |        OSE        |        SGX         |
+------+--------------------+------------------+-------------------+-----------+--------------------+-------------------+--------------------+
|   10 |                    |                  | 1397464.227495019 | 1728005.0 |                0.0 |                   |                    |
|   30 | 30569.300965712766 | 4299649.75104102 |                   |    6237.0 |                    |                   |                    |
+------+--------------------+------------------+-------------------+-----------+--------------------+-------------------+--------------------+

If you want to fill empty values with zeroes, you can use df.fillna(0).

Combing two dataframes in Pandas Python

2 Answers2

Linked