CODEWITHSUNDEEP
pythonpandaspandas-groupby
add-semi-colons
add-semi-colons

Reputation: 18830

pandas fill NaN in a data frame column using another data frame column

There are two data frames where both have common user_id. Lets call them df_A and df_B. df_B has one record per user. df_A contains multiple records per user but for each user there exist one record with NaN.

df_A

    user_id     start_time       time_calc
0     421    2015-10-25 11:42:35      8.97
1     421    2015-10-25 11:51:33      4.85
2     421    2015-10-25 11:56:24      6.77
3     421    2015-10-25 13:03:10       NaN
4     493    2015-10-26 05:08:13      0.85
5     493    2015-10-26 05:09:04       NaN
6     636    2015-10-17 15:30:53      2.55
7     636    2015-10-17 15:33:26      4.17
8     636    2015-10-17 15:37:36      2.35
9     636    2015-10-17 15:39:57      0.32
10    636    2015-10-17 15:40:16      0.43
11    636    2015-10-17 15:40:42      0.65
12    636    2015-10-17 15:41:21       NaN

df_B

   user_id        last_time
0    100     2015-11-04 01:52:16
1    421     2015-10-25 14:03:23
2    493     2015-10-26 05:09:51
3    636     2015-11-03 20:15:54
4    030     2015-11-03 17:44:20
5    174     2015-10-10 00:20:37
6    437     2015-10-24 12:34:38
7    481     2015-10-28 03:18:24
8    952     2015-10-28 21:07:15
9    197     2015-10-18 14:52:34

Goal is to fill NaN in df_A by doing df_B.last_time - df_A.start_time but only for the Nan while making using the correct user_id to match the records.

logically: user_id 421 NaN record should get filled using the following data: 2015-10-25 14:03:23 - 2015-10-25 13:03:10

df_A.groupby('user_id').time_calc.fillna(df_B.last_time - df_A.time_calc.isnull['start_time'])

Unfortunately, this didn't work.

Upvotes: 2

Views: 51

Answers (1)

cs95
cs95

Reputation: 403128

First, build a fill-value mapper using merge as the first step:

i = df_A[df_A.time_calc.isnull()].merge(df_B)
j = i['last_time'].sub(i['start_time']).dt.total_seconds() / 60
j.index = i.user_id


j
user_id
421       60.216667
493        0.783333
636    24754.550000
dtype: float64

Now, do a fillna:

df_A['time_calc'] = df_A['time_calc'].fillna(df_A.user_id.map(j))  
df_A

    user_id          start_time     time_calc
0       421 2015-10-25 11:42:35      8.970000
1       421 2015-10-25 11:51:33      4.850000
2       421 2015-10-25 11:56:24      6.770000
3       421 2015-10-25 13:03:10     60.216667
4       493 2015-10-26 05:08:13      0.850000
5       493 2015-10-26 05:09:04      0.783333
6       636 2015-10-17 15:30:53      2.550000
7       636 2015-10-17 15:33:26      4.170000
8       636 2015-10-17 15:37:36      2.350000
9       636 2015-10-17 15:39:57      0.320000
10      636 2015-10-17 15:40:16      0.430000
11      636 2015-10-17 15:40:42      0.650000
12      636 2015-10-17 15:41:21  24754.550000

Upvotes: 2

Related Questions