Add two columns of different dataframes taking into account missing values

Question

How can I add columns of two dataframes (A + B), so that the result (C) takes into account missing values ('---')?

DataFrame A

a = pd.DataFrame({'A': [1, 2, 3, '---', 5]})

    A
0   1
1   2
2   3
3   ---
4   5

DataFrame B

b = pd.DataFrame({'B': [3, 4, 5, 6, '---']})

    B
0   3
1   4
2   5
3   6
4   ---

Desired Result of A+B

Is it important for the value of your missing value to be indicated by "---"? — Momchill
– Momchill, Commented Apr 11, 2019 at 20:01

Vaishali · Accepted Answer · 2019-04-11 20:08:46Z

3

Replace the '---' with np.nan, add the columns and fillna with '---'

(a['A'].replace('---', np.nan)+b['B'].replace('---', np.nan)).fillna('---')

You can assign the result to a new dataframe or an existing one:

df = pd.DataFrame()
df.assign(C = (a['A'].replace('---', np.nan)+b['B'].replace('---', np.nan)).fillna('---'))

OR

a.assign(C = (a['A'].replace('---', np.nan)+b['B'].replace('---', np.nan)).fillna('---'))

answered Apr 11, 2019 at 20:02

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

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

2 Comments

Result is no dataframe anymore

This is a series, the result can be assigned to a new data frame or an existing one