how can I fill NaN values by the mean of the adjacent column in Pandas DataFrame with a loop

user_13839692 0
Viewed 863

I have a large data set, and I have some missing value, I want to fill the NAN values by the mean of the column before and after , and in certain cases i have NaN values consecutive in these case I want to replace all this nan values by the first value of non nan can found for examples : I should use a loop

   0   1     2   3     4     5   6   7  8  9  10  11    12    13  14    15    16
19.0  NaN  NaN NaN  29.0  30.0 NaN 16.0  15.0 16.0  17.0 NaN  28.0  30.0 NaN  28.0  18.0

The goal is for the data to look like this:

 0   1     2   3     4     5   6   7  8  9  10  11    12    13  14    15    16
19.0  29.0  29.0 29.0  29.0  30.0 23.0 16.0  15.0 16.0  17.0 22.5 28.0  30.0 29  28.0  18.0
pandas mean rows dataframe
2 Answers

Let's try:

# where df is not null
s = df.notna()

# check for `NaN` with valid left and right:    
mask = s.shift(1, axis=1) & s.shift(-1, axis=1)

# fill as required
df[:] = np.where(mask, df.interpolate(axis=1), df.bfill(axis=1).ffill(axis=1))

Output:

      0     1     2     3     4     5     6     7     8     9    10    11  \
0  19.0  29.0  29.0  29.0  29.0  30.0  23.0  16.0  15.0  16.0  17.0  22.5   

     12    13    14    15    16  
0  28.0  30.0  29.0  28.0  18.0
user_42384080

Let

import numpy as np 
import pandas as pd 

a = "0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 19.0 NaN NaN NaN 29.0 30.0 NaN 16.0 15.0 16.0 17.0 NaN 28.0 30.0 NaN 28.0 18.0"

l = np.array([int(float(e)) if e != 'NaN' else np.nan for e in a.split(' ')])

Then what you are looking for could be accomplished with

subset_ranges = [0, 3]
replacements = {}

for i in range(len(l)-1):
    subset = l[subset_ranges[0]: subset_ranges[1]]
    if pd.isnull(subset[1]) and not pd.isnull(subset[0]) and not pd.isnull(subset[2]):
        replacements[subset_ranges[0]+1] = np.nanmean(subset)
    subset_ranges[0] += 1
    subset_ranges[1] += 1
l = np.array([e if i not in replacements.keys() else replacements[i] for i, e in enumerate(l)])

df = pd.DataFrame(l.reshape(-1, 1))
df.fillna(method='bfill', inplace=True)
user_133901390
Related