Replace values of DataFrame by values of another Dataframe

Question

I want to replace the values of one DataFrame with the values of another DataFrame (not necessarily of the same size).

df1:
       Ticker  A  B   C  D  E
Date   Symbol
Nov 12   Y    Nan 1  Nan 1  1
Dec 1    Y    Nan Nan 1  1  1
Dec 5    Y     1  Nan 1  1 Nan 
Dec 8    Y    Nan Nan 1  1  1


df2:
Ticker    A  B   C  D  E
Date   
Nov 12    12  42  10 15 16
Nov 13    14  45  11 14 18
Nov 14    12  42  19 22 20  
...               ...
Dec 1     12  46  11 12 17
Dec 5     19  49  13 13 15
Dec 8     11  41  10 15 10

I want to replace the valid values in df1 (i.e.replace the 1's) with the corresponding values from df2. I want to return the following DataFrame:

df3:

       Ticker  A  B   C    D    E
Date   Symbol
Nov 12   Y    Nan 42  Nan  15   16
Dec 1    Y    Nan Nan 11   12   17
Dec 5    Y     19 Nan 13   13   Nan 
Dec 8    Y    Nan Nan 10   15   10

I have tried to multiply them but df1 is a multiindex. Thank you in advance.

score 0 · Answer 1 · answered Jul 20 '21 at 20:41

You can use df.update with a filter_func that checks whether the values to be updated equal 1. This requires the dataframes to have matching index columns (Date). A working example:

import numpy as np
import pandas as pd

df1 = pd.DataFrame({'Date': {1: 'Nov 12', 2: 'Dec 1', 3: 'Dec 5', 4: 'Dec 8'}, 'Symbol': {1: 'Y', 2: 'Y', 3: 'Y', 4: 'Y'}, 'A': {1: np.nan, 2: np.nan, 3: 1.0, 4: np.nan}, 'B': {1: 1.0, 2: np.nan, 3: np.nan, 4: np.nan}, 'C': {1: np.nan, 2: 1.0, 3: 1.0, 4: 1.0}, 'D': {1: 1.0, 2: 1.0, 3: 1.0, 4: 1.0}, 'E': {1: 1.0, 2: 1.0, 3: np.nan, 4: 1.0}})
df2 = pd.DataFrame({'Date': {1: 'Nov 12', 2: 'Nov 13', 3: 'Nov 14', 4: 'Dec 1', 5: 'Dec 5', 6: 'Dec 8'}, 'A': {1: 12, 2: 14, 3: 12, 4: 12, 5: 19, 6: 11}, 'B': {1: 42, 2: 45, 3: 42, 4: 46, 5: 49, 6: 41}, 'C': {1: 10, 2: 11, 3: 19, 4: 11, 5: 13, 6: 10}, 'D': {1: 15, 2: 14, 3: 22, 4: 12, 5: 13, 6: 15}, 'E': {1: 16, 2: 18, 3: 20, 4: 17, 5: 15, 6: 10}})

df1 = df1.set_index('Date')
df2 = df2.set_index('Date')

df1.update(df2, filter_func=lambda x: x == 1)

Ouput df1:

Date	Symbol	A	B	C	D	E
Nov 12	Y	nan	42	nan	15	16
Dec 1	Y	nan	nan	11	12	17
Dec 5	Y	19	nan	13	13	nan
Dec 8	Y	nan	nan	10	15	10

PS. Try flattening the column headers if the multiindex throws an error.

Replace values of DataFrame by values of another Dataframe

1 Answers1