Creating columns in one dataframe only from non matching columns from another dataframe and set all values to 0

Question

I have two dataframes that look like this:

df1

A    B    C
5    1    5
4    2    8
2    5    3

df2

A    B    C    D
4    3    4    1
3    5    1    2
1    2    5    4

df1 and df2 share the same columns names except "D" which is only found in df2. What I would like to do is add D to df1 but fill all rows with "0"'s

In other words, if a column exists in df2 but it doesn't in df1, add that column to df1 but make all values in that column 0 (below)

df1

A    B    C    D
5    1    5    0
4    2    8    0
2    5    3    0

I realize it would be very easy to add one column called "D" to df1 but this is just a dummy example when in reality I am dealing with much larger and many more dataframes. So, I am looking for a way to do this with a code I could implement in a loop or iteratively

I think the word add is getting confusing here. Seems like you simply want to create a column 'D' with the static value of 0 in df1, leaving everything else unchanged? — ALollz
– ALollz, Commented Dec 20, 2021 at 19:04

ALollz · Accepted Answer · 2021-12-20 19:05:57Z

2

You can find the missing columns with Index.difference.

Then there are a ton of ways to assign multiple columns with a static value to a DataFrame, so here's one where you unpack a dictionary where the keys are the column names and the values of that dict is the static value you want to assign.

df1 = df1.assign(**{x: 0 for x in df2.columns.difference(df1.columns)})

   A  B  C  D
0  5  1  5  0
1  4  2  8  0
2  2  5  3  0

answered Dec 20, 2021 at 19:05

ALollz

59.7k7 gold badges73 silver badges97 bronze badges

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

7 Comments

Melderon Over a year ago

This works! Thank you. However, is there any way to preserve the order of the columns? They seem to be added quite randomly

user17242583 Over a year ago

@Melderon check my answer. I believe it does that for you, too.

Melderon Over a year ago

@richardec for some reason, it turned everything into a float. I just need "0" as the above answer provides. Thank you, though

user17242583 Over a year ago

@Melderon that's easy to fix. Just add .astype(int), like in my answer.

ALollz Over a year ago

@Melderon which columns are getting mis-ordered? The ones in DF1 or the ones in DF2 that are missing?

|

Quang Hoang · Accepted Answer · 2021-12-20 19:02:42Z

1

You can use DataFrame.add with fill_value:

print(df1.add(df2, fill_value=0))

Output:

   A  B  C    D
0  9  4  9  1.0
1  7  7  9  2.0
2  3  7  8  4.0

Note: This method will fill the existing nan in each dataframe with 0 as well.

edited Dec 20, 2021 at 19:02

answered Dec 20, 2021 at 18:59

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Comments

score 1 · Accepted Answer · 2021-12-20 19:34:56Z

1

Try this:

df3 = df1.add(df2).fillna(0).astype(int)

Output:

>>> df3
   A  B  C  D
0  9  4  9  0
1  7  7  9  0
2  3  7  8  0

edited Dec 20, 2021 at 19:34

answered Dec 20, 2021 at 19:10

user17242583

Comments

Mykola Zotko · Accepted Answer · 2021-12-20 20:17:45Z

0

You can reindex one dataframe using columns from another one:

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

answered Dec 20, 2021 at 20:17

Mykola Zotko

18.2k6 gold badges88 silver badges90 bronze badges

Collectives™ on Stack Overflow

Creating columns in one dataframe only from non matching columns from another dataframe and set all values to 0

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related