Merge two dataframe based on condition

Question

I'm trying to merge two dataframes conditionally.

In df1, it has duration. In df2, it has usageTime. On df3, I want to set totalTime as df1's duration value if df2 has no usageTime value.

Here is df1:

>> df1
            duration
device
1110100         53.8
1110101         64.7
1110102         52.6
1110103         14.4

And df2:

>> df2
           usageTime
deviceId
1110100         87.6
1110101         94.3
1110102         None
1110103         None

The next dataframe I want to create is:

>> df3
          totalUsage
device
1110100         87.6
1110101         94.3
1110102         52.6
1110103         14.4

Things I tried:

pandas.DataFrame.combine_first()

df3 = df2.combine_first(df1.rename(columns={'duration': 'totalUsage'}))

Returns:

        totalUsage  usageTime
device
1110100       53.8       87.6
1110101       64.7       94.3
1110102       52.6       None
1110103       14.3       None

pandas.DataFrame.fillna()

df3 = df2.fillna(df1)
df3.columns = ['totalUsage']

Returns:

          totalUsage
device
1110100         87.6
1110101         94.3
1110102          NaN
1110103          NaN

I am open to all ideas.

`df3 = df2.combine_first(df1.rename(columns={'duration': 'totalUsage'}))` ? — Henry Ecker, Sep 15 '21 at 13:54
[Merge Pandas Dataframe: how to add columns and replace values](https://stackoverflow.com/q/62355898/15497888) — Henry Ecker, Sep 15 '21 at 13:59
@HenryEcker The result not as I want. `totalUsage` returned with [53.8, 64.7, 52.6, 14.3] — Hayrettin Basaraner, Sep 15 '21 at 14:35
@mosc9575 Your solution not worked properly too. It returned with `totalUsage` column as [87.6, 94.3, NaN, NaN] — Hayrettin Basaraner, Sep 15 '21 at 14:38
Can you check [this](https://stackoverflow.com/q/34989341) ? — jezrael, Sep 16 '21 at 05:56

score 2 · Accepted Answer · answered Sep 16 '21 at 05:57

2

Specify the column names when using fillna and then convert the result to_frame:

df3 = df2.usageTime.fillna(df1.duration).to_frame(name='totalUsage')

#          totalUsage
# deviceId           
# 1110100        87.6
# 1110101        94.3
# 1110102        52.6
# 1110103        14.4

answered Sep 16 '21 at 05:57

tdy

36,675
19
86
83

1

Exactly https://stackoverflow.com/questions/34989341/how-to-remove-nan-value-while-combining-two-column-in-panda-data-frame – jezrael Sep 16 '21 at 05:58
have to be very careful the deviceId list is exactly the same in all dataframes. Same order and no holes – Shubham Periwal Sep 16 '21 at 05:58
@ShubhamPeriwal are you sure they have to be the same order? it doesn't seem like that's necessary when i just tested – tdy Sep 16 '21 at 06:02
@tdy Yeah, it does not required. New dataframes order is same as base dataframe. – Hayrettin Basaraner Sep 16 '21 at 06:10

Merge two dataframe based on condition

1 Answers1