Simple toy dataframe:
df = pd.DataFrame({'mycol':['foo','bar','hello','there',np.nan,np.nan,np.nan,'foo'],
'mycol2':'this is here to make it a DF'.split()})
print(df)
mycol mycol2
0 foo this
1 bar is
2 hello here
3 there to
4 NaN make
5 NaN it
6 NaN a
7 foo DF
I'm trying to fill the NaNs in mycol with samples from itself, e.g. I want the NaNs to be replaced with samples of foo,bar,hello etc.
# fill NA values with n samples (n= number of NAs) from df['mycol']
df['mycol'].fillna(df['mycol'].sample(n=df.isna().sum(), random_state=1,replace=True).values)
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
# fill NA values with n samples, n=1. Dropna from df['mycol'] before sampling:
df['mycol'] = df['mycol'].fillna(df['mycol'].dropna().sample(n=1, random_state=1,replace=True)).values
# nothing happens
Expected Output: Nas filled with random samples from mycol:
mycol mycol2
0 foo this
1 bar is
2 hello here
3 there to
4 foo make
5 foo it
6 hello a
7 foo DF
edit for answer: @Jezrael's answer below sorted it, I had a problem with my indexes.
df['mycol'] = (df['mycol']
.dropna()
.sample(n=len(df),replace=True)
.reset_index(drop=True))
Interesting problem.
For me working set values with loc with converting values to numpy array for avoid data alignment:
a = df['mycol'].dropna().sample(n=df['mycol'].isna().sum(), random_state=1,replace=True)
print (a)
3 there
7 foo
0 foo
Name: mycol, dtype: object
#pandas 0.24+
df.loc[df['mycol'].isna(), 'mycol'] = a.to_numpy()
#pandas below
#df.loc[df['mycol'].isna(), 'mycol'] = a.values
print (df)
mycol mycol2
0 foo this
1 bar is
2 hello here
3 there to
4 there make
5 foo it
6 foo a
7 foo DF
Your solution should working if length of Series and index same like original DataFrame:
s = df['mycol'].dropna().sample(n=len(df), random_state=1,replace=True)
s.index = df.index
print (s)
0 there
1 foo
2 foo
3 bar
4 there
5 foo
6 foo
7 bar
Name: mycol, dtype: object
df['mycol'] = df['mycol'].fillna(s)
print (df)
# mycol mycol2
0 foo this
1 bar is
2 hello here
3 there to
4 there make
5 foo it
6 foo a
7 foo DF
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With