Compare two dataframes, add missing columns and populate with zeros

Question

I have two pandas dataframes df1 and df2. I need to find a union of columns of both and append the missing columns in both of them with zeros suppose we have two dataframes

import pandas as pd
df1=pd.DataFrame({'c1':[0,0,1],'c2':[1,0,0],'c3':[0,6,0]})
df2=pd.DataFrame({'c2':[0,0,5],'c4':[1,5,0],'c5':[2,4,2]})

then the result should be

df1=pd.DataFrame({'c1':[0,0,1],'c2':[1,0,0],'c3':[0,6,0],'c4':[0,0,0],'c5':[0,0,0]})
df2=pd.DataFrame({'c1':[0,0,0],'c2':[0,0,5],'c3':[0,0,0],'c4':[1,5,0],'c5':[2,4,2]})

The question shows no effort to solve the issue. Always provide a minimal reproducible example with code, data, errors, current & expected output, as formatted text & you're expected to try to solve the problem first and show your effort. — Trenton McKinney
– Trenton McKinney, Commented Oct 2, 2022 at 18:46
This answer in the duplicate is the same as the answer below. — Trenton McKinney
– Trenton McKinney, Commented Oct 2, 2022 at 19:07
@TrentonMcKinney In another answer we use 3 dataframes and here only 2. These questions are similar but not duplicates. — Mykola Zotko
– Mykola Zotko, Commented Oct 2, 2022 at 19:37
@MykolaZotko that's irrelevant. In the same way there doesn't need to be separate questions to concat 4 dataframes compared to 2 dataframes, there doesn't need to be a question for every number of dataframes here. Therefore, you erroneously reopened the question. This is a duplicate of this question — Trenton McKinney
– Trenton McKinney, Commented Oct 2, 2022 at 19:47

mozway · Accepted Answer · 2022-10-02 18:41:02Z

3

You can get the columns union and reindex:

cols = df1.columns.union(df2.columns)

df1 = df1.reindex(cols, axis=1, fill_value=0)
df2 = df2.reindex(cols, axis=1, fill_value=0)

output:

# df1
   c1  c2  c3  c4  c5
0   0   1   0   0   0
1   0   0   6   0   0
2   1   0   0   0   0

# df2
   c1  c2  c3  c4  c5
0   0   0   0   1   2
1   0   0   0   5   4
2   0   5   0   0   2

answered Oct 2, 2022 at 18:41

mozway

267k13 gold badges56 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

Compare two dataframes, add missing columns and populate with zeros

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related