Overwrite NaNs in a column based on identifier

Question

I have a dataframe that contains some NaN-values in a t-column. The values in the t-column belong to a certain id and should be the same per id:

df = pd.DataFrame({"t"  :   [4, 4, 1, 1, float('nan'), 2, 2, 2, float('nan'), 10],
                   "id":    [1, 1, 2, 2, 3, 3, 3 , 3, 4, 4]})

Therefore, I would like to overwrite the NaN in t with the non-NaN in t for the respective id and ultimately end up with

df = pd.DataFrame({"t"  :   [4, 4, 1, 1, 2, 2, 2, 2, 10, 10],
                   "id":    [1, 1, 2, 2, 3, 3, 3 , 3, 4, 4]})

similar to this [link](https://stackoverflow.com/questions/27905295/how-to-replace-nans-by-preceding-values-in-pandas-dataframe) ? — gyx-hh, Mar 21 '18 at 10:59
@HamzaHaider No, not quite. If you read my OP, then my example is based on an `id` that we use to identify the correct `t` to use - it is not necessarily based preceding values — N08, Mar 21 '18 at 11:01

Anton vBR · Accepted Answer · 2018-03-21T12:52:04.643

5

New strategy... Create a map by dropping na and reassign using loc and mask.

import pandas as pd

df = pd.DataFrame({"t"  :   [4, 4, 1, 1, float('nan'), 2, 2, 2, float('nan'), 10],
                   "id":    [1, 1, 2, 2, 3, 3, 3 , 3, 4, 4]})

# create mask
m = pd.isna(df['t'])

# create map
#d = df[~m].set_index('id')['t'].drop_duplicates()
d = df[~m].set_index('id')['t'].to_dict()

# assign map to the slice of the dataframe containing nan
df.loc[m,'t'] = df.loc[m,'id'].map(d)

print(df)

df returns:

   id     t
0   1   4.0
1   1   4.0
2   2   1.0
3   2   1.0
4   3   2.0
5   3   2.0
6   3   2.0
7   3   2.0
8   4  10.0
9   4  10.0

edited Mar 21 '18 at 12:52

answered Mar 21 '18 at 11:01

Anton vBR

18,287
5
40
46

1

I think you have better solution :) – jezrael Mar 21 '18 at 11:36
For some odd reason `drop_duplicates` removes all the rows I have? I use `isnull` as `isna` isn't in my Panda-library – N08 Mar 21 '18 at 12:47
1

@N08 ok, what version are you running on? Did you consider upgrading your pandas? You can also try to_dict() (**updated my answer**) – Anton vBR Mar 21 '18 at 12:51
1

It works with `to_dict`, thanks. I am running `0.20.1` – N08 Mar 21 '18 at 12:56

jezrael · Answer 2 · 2018-03-21T11:17:38.860

2

Use sort_values with groupby and transform for same column with first:

df['t'] = df.sort_values(['id','t']).groupby('id')['t'].transform('first')

Alternative solution is map by Series created by dropna with drop_duplicates:

df['t'] = df['id'].map(df.dropna(subset=['t']).drop_duplicates('id').set_index('id')['t'])

print (df)
   id     t
0   1   4.0
1   1   4.0
2   2   1.0
3   2   1.0
4   3   2.0
5   3   2.0
6   3   2.0
7   3   2.0
8   4  10.0
9   4  10.0

edited Mar 21 '18 at 11:17

answered Mar 21 '18 at 11:04

jezrael

822,522
95
1,334
1,252

Yes, this was better. I think however my solution can be faster for large sets as it only reassigns a slice using loc, don't you think? – Anton vBR Mar 21 '18 at 11:15
1

@AntonvBR - It is possible, the best test it. – jezrael Mar 21 '18 at 11:16

Overwrite NaNs in a column based on identifier

2 Answers2