CODEWITHSUNDEEP
pythonpandas
stevendesu
stevendesu

Reputation: 16821

Pandas Concatenate DataFrames, Keep One Column

I have two DataFrames:

>>> df1
              above below
asn   country
12345 US      5     4
      MX      6     3
54321 MX      4     5
>>> df2
              above below
asn   country
12345 MX      1     0
54321 MX      0     1
      US      1     0

Note that while there are some common indexes (12345/MX and 54321/MX) there are some indexes that only appear in df1 (12345/US) and some that only appear in df2 (54321/US)

I wish to add the values together so that if a row does not exist in df1, it is added, and if a row does not exist in df2, the counts are unaffected.

Desired output:

>>> (df1 + df2, somehow)
12345 US      5     4
      MX      7     3
54321 MX      4     6
      US      1     0

What's happening to me:

>>> df1 + df2
12345 US      NaN   NaN
      MX      7.0   3.0
54321 MX      4.0   6.0
      US      NaN   NaN

Upvotes: 1

Views: 136

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

Use add with fill_value parameter:

df1.add(df2, fill_value=0)

Output:

               above  below
asn   country              
12345 MX         7.0    3.0
      US         5.0    4.0
54321 MX         4.0    6.0
      US         1.0    0.0

And, you can astype to get back to integers:

df1.add(df2, fill_value=0).astype(int)

Output:

               above  below
asn   country              
12345 MX           7      3
      US           5      4
54321 MX           4      6
      US           1      0

Upvotes: 3

Related Questions