How to fill the null values with the average of the above value and the below value in python?

user_13854105 0
Viewed 115

My Data looks like this:

    ID      gcs    compact    active_clay       WTS     LOI
0   EA01    1530    40          9.02            22.7    4.58
1   EA02    NAN     NAN         NAN             NAN      NAN
2   EA03    1550    39          9.02            23.9    4.58
3   EA04    NAN     NAN          NAN            NAN      NAN
4   EA05    NAN     NAN          NAN            NAN      NAN
5   EA06    NAN     NAN          NAN            NAN      NAN
6   EA07    1520    40           9.02           24.9     4.58
7   EA08    NAN     NAN          NAN            NAN      NAN
8   EA09    1530    40           9.02           23.9     4.52
9   EA10    NAN    NAN           NAN            NAN      NAN

i wanted the null values to be filled with the average of the above and the below values for the entire data.

Example:

The null values in the 1st index should be filled with average(index 0 value, index 2 value)

The null values in the 3rd index should be filled with average(2nd index value,6th index value)

The null values in the 4th index should be filled with average(3rd index value,6th index value)

The null values in the 5th index should be filled with average(4th index value,6th index value) and so on..

This should be done for all the columns How can i do that.Please help.

python-requests pandas python-2.7 python-3.x python
1 Answers

I believe you need DataFrame.interpolate with back filling missing values:

df = df.interpolate().bfill()
print (df)
         Date     GCS  Compactability  Active Clay   WTS   LOI  Moisture
0  01-01-2019  1530.0            40.0         9.02  22.7  4.58      3.10
1  01-01-2019  1540.0            39.5         9.02  23.3  4.58      3.05
2  01-01-2019  1550.0            39.0         9.02  23.9  4.58      3.00
3  01-01-2019  1550.0            39.0         9.02  23.9  4.58      3.00
4  01-01-2019  1550.0            39.0         9.02  23.9  4.58      3.00
user_29010020
Related