Filling missing data by random choosing from non missing values in pandas dataframe

Question

I have a pandas data frame where there are a several missing values. I noticed that the non missing values are close to each other. Thus, I would like to impute the missing values by randomly choosing the non missing values.

For instance:

import pandas as pd
import random
import numpy as np

foo = pd.DataFrame({'A': [2, 3, np.nan, 5, np.nan], 'B':[np.nan, 4, 2, np.nan, 5]})
foo
    A   B
0   2 NaN
1   3   4
2 NaN   2   
3   5 NaN
4 NaN   5

I would like for instance foo['A'][2]=2 and foo['A'][5]=3 The shape of my pandas DataFrame is (6940,154). I try this

foo['A'] = foo['A'].fillna(random.choice(foo['A'].values.tolist()))

But it not working. Could you help me achieve that? Best regards.

score 9 · Answer 1 · edited Jan 13 '21 at 01:41

9

You can use pandas.fillna method and the random.choice method to fill the missing values with a random selection of a particular column.

import random
import numpy as np

df["column"].fillna(lambda x: random.choice(df[df[column] != np.nan]["column"]), inplace =True)

Where column is the column you want to fill with non nan values randomly.

edited Jan 13 '21 at 01:41

sophocles

13,593
3
14
33

answered Apr 04 '16 at 22:02

bamdan

836
7
21

7

I try it. But instead of imputing with the values it put ` at 0x7fa4eb48b9b0>`.. – Donald Gedeon Apr 04 '16 at 22:05
sorry, can you provide some sample data? – bamdan Apr 04 '16 at 22:08
I have edited my question with a sample data. Thanks – Donald Gedeon Apr 04 '16 at 22:15
8

I have fund the answer. I did this : `foo = foo.apply(lambda x: x.fillna(random.choice(x.dropna())), axis=1)`. Your answer gave the clue. Thank you very much for your help. – Donald Gedeon Apr 04 '16 at 22:25
no worries. Glad I could help :) It was a bit confusing. – bamdan Apr 04 '16 at 22:32

score 7 · Answer 2 · answered Dec 10 '17 at 23:52

7

This works well for me on Pandas DataFrame

def randomiseMissingData(df2):
    "randomise missing data for DataFrame (within a column)"
    df = df2.copy()
    for col in df.columns:
        data = df[col]
        mask = data.isnull()
        samples = random.choices( data[~mask].values , k = mask.sum() )
        data[mask] = samples

return df

answered Dec 10 '17 at 23:52

Karolis

1,558
1
14
23

1

For a pandas data frame this is a smart way of doing it as the statistics of the sample data reflects by definition the statistics of the original data. In this way you can fill the gaps while maintaining the same stats. – Eelco van Vliet Aug 24 '18 at 14:57
1

Better assign with `df.loc[mask, col] = samples` to avoid warnings – Carlos Pinzón May 06 '20 at 00:59

score 5 · Answer 3 · answered Apr 23 '21 at 18:32

5

I did this for filling NaN values with a random non-NaN value:

import random

df['column'].fillna(random.choice(df['column'][df['column'].notna()]), inplace=True)

answered Apr 23 '21 at 18:32

mohannatd

51
1
2

score 3 · Answer 4 · answered May 23 '17 at 17:40

This is another approach to this question after making improvement on the first answer and according to how to check if an numpy int is nand found here in numpy documentation

foo['A'].apply(lambda x: np.random.choice([x for x in range(min(foo['A']),max(foo['A'])]) if (np.isnan(x)) else x)

score 1 · Answer 5 · answered Jan 02 '18 at 16:40

Here is another Pandas DataFrame approach

import numpy as np
def fill_with_random(df2, column):
    '''Fill `df2`'s column with name `column` with random data based on non-NaN data from `column`'''
    df = df2.copy()
    df[column] = df[column].apply(lambda x: np.random.choice(df[column].dropna().values) if np.isnan(x) else x)
    return df

Naomi Fridman · Answer 6 · 2021-07-25T10:54:58.203

0

for me only this worked, all the examples above failed. Some filled same number, some didn't fill nothing.

def fill_sample(df, col):
    tmp = df[df[col].notna()[col].sample(len(df[df[col].isna()])).values
    k = 0
    for i,row in df[df[col].isna()].iterrows():

       df.at[i, col] = tmp[k]
       k+=1
    return df

edited Jul 25 '21 at 10:54

answered Jul 22 '21 at 20:32

Naomi Fridman

2,095
2
25
36

1

Please don't embed code as a screenshot. Instead, paste it as text, and use Markdown to format it as code. That makes it easier to read, copy, and paste. It also helps ensure that it shows up in search results. – Jeremy Caney Jul 22 '21 at 20:40

score 0 · Answer 7 · answered Sep 16 '22 at 13:06

0

Not the most concise, but probably the most performant way to go:

nans = df[col].isna()
non_nans = df.loc[df[col].notna(), col]
samples = np.random.choice(non_nans, size=nans.sum())
df.loc[nans, col] = samples

answered Sep 16 '22 at 13:06

SumakuTension

542
1
9
26

Filling missing data by random choosing from non missing values in pandas dataframe

7 Answers7

Linked