Pandas combine two columns into one and exclude NaN values

Question

I have a 5k x 2 column dataframe called "both". I want to create a new 5k x 1 DataFrame or column (doesn't matter) by replacing any NaN value in one column with the value of the adjacent column.

ex:

    Gains  Loss
0    NaN   NaN
1    NaN -0.17
2    NaN -0.13
3    NaN -0.75
4    NaN -0.17
5    NaN -0.99
6   1.06   NaN
7    NaN -1.29
8    NaN -0.42
9   0.14  NaN

so for example, I need to swap the NaNs in the first column in rows 1 through 5 with the values in the same rows, in second column to get a new df of the following form:

    Change  
0     NaN  
1    -0.17 
2    -0.13  
3    -0.75 
4    -0.17  
5    -0.99  
6    1.06  

how do I tell python to do this??

Answer 1

You may fill the NaN values with zeroes and then simply add your columns:

both["Change"] = both["Gains"].fillna(0) + both["Loss"].fillna(0)

Then — if you need it — you may return the resulting zeroes back to NaNs:

both["Change"].replace(0, np.nan, inplace=True)

The result:

    Gains      Loss  Change
0     NaN       NaN     NaN
1     NaN     -0.17   -0.17
2     NaN     -0.13   -0.13
3     NaN     -0.75   -0.75
4     NaN     -0.17   -0.17
5     NaN     -0.99   -0.99
6    1.06       NaN    1.06
7     NaN     -1.29   -1.29
8     NaN     -0.42   -0.42
9    0.14       NaN    0.14

Finally, if you want to get rid of your original columns, you may drop them:

both.drop(columns=["Gains", "Loss"], inplace=True)

Answer 2

There are many ways to achieve this. One is using the loc property:

import pandas as pd
import numpy as np

df = pd.DataFrame({'Price1': [np.nan,np.nan,np.nan,np.nan,
                              np.nan,np.nan,1.06,np.nan,np.nan],
                   'Price2': [np.nan,-0.17,-0.13,-0.75,-0.17,
                              -0.99,np.nan,-1.29,-0.42]})

df.loc[df['Price1'].isnull(), 'Price1'] = df['Price2']
df = df.loc[:6,'Price1']

print(df)

Output:

    Price1
0     NaN
1   -0.17
2   -0.13
3   -0.75
4   -0.17
5   -0.99
6    1.06

You can see more complex recipes in the Cookbook