How to fill the null values in python with the below condition?

[Kaaviya_1]

I have a dataframe with 5000 records. I want the null values to be filled with:
Average(All the Preceding values before null, First succeeding value after null)

data:

Date              gcs          Comp       Clay      WTS
2020-01-01        1550           41       9.41      22.6
2020-01-02        1540           48       9.50      25.8
2020-01-03         NAN          NAN       NAN        NAN
2020-01-04         1542          42       9.30       23.7
2020-01-05         1580          48       9.10       21.2
2020-01-06          NAN         NAN       NAN         NAN
2020-01-07          1520         40        10         20.2
2020-01-08          1523         30        25          19

Example: For the date 2020-01-03, i want the null value in the gcs column to be filled with the Average(1550,1540,1542) which gives 1544.

1550 and 1540 are the preceding values before null and 1542 is my first succeeding value after null.

Similarly,

For the date 2020-01-06 i want the null values for gcs column to be filled with Average(1550,1540,1544,1542,1580,1520) which gives 1546.

1550 till 1580 are the preceding values before null and 1520 is the first succeeding value after null.

Desired Output:

Date              gcs          Comp       Clay      WTS
2020-01-01        1550           41       9.41      22.6
2020-01-02        1540           48       9.50      25.8
2020-01-03         1544          43.66    9.403     24.03
2020-01-04         1542          42       9.30       23.7
2020-01-05         1580          48       9.10       21.2
2020-01-06          1546         43.77     9.45      22.92
2020-01-07          1520         40        10         20.2
2020-01-08          1523         30        25          19

Likewise i wanted to fill the null values in all the columns of my data frame.

[Reverend Jim]

In your example for record 3 you want (1550+1540+1542) // 3, but what would you do if record 4 was also NULL? Assuming no consecutive NULLS you can do

import os
import sys
import sqlite3

def Average(records, row, column):
    sum = records[row+1][column]
    for r in range(0,row):
        sum += records[r][column]
    return sum / (row+1)

con = sqlite3.connect('d:/testavg.db')
cur = con.cursor()
res = cur.execute("select * from test")

recs = []

print("\nInput values")

while (rec:= cur.fetchone()):
    date,gcs, comp, day, wts = rec
    print(f'{date=} {gcs=} {comp=} {day=} {wts=}')
    recs.append([date,gcs,comp,day,wts])

con.close()

for r in range(2,len(recs)-1):
    for c in range(1,len(recs[r])): 
        if recs[r][c] is None:
            recs[r][c] = Average(records=recs, row=r, column=c)

print("\nAdjusted values")

for rec in recs:
    date,gcs, comp, day, wts = rec
    #gcs = int(gcs)
    print(f'{date=} {gcs=} {comp=} {day=} {wts=}')

But the results differ slightly from yours because you truncated instead of rounding.