how to distribute raw value into multiple blank raw in the same column in data frame using python?

Question

my data as:

id   date      consumption
1    1/2/2020   500
1    1/3/2020   nan
1    1/4/2020   nan
1    1/5/2020   nan
1    1/6/2020   900

consumption column represents a monthly reading of consumption and is cumulative. i need to distribute consumption 900 on nan cell in (consumption) column and depend on scale factors i had the data i need to be as example like:

id   date    value
1    1/2/2020   500
1    1/3/2020   650
1    1/4/2020   700
1    1/5/2020   800
1    1/6/2020   900

Answer 1

let's start first by the code for recreating the example data to test on it :

import pandas as pd
data={"id":[1,2,3,4,5],"date":["1/2/2020","1/3/2020","1/4/2020","1/5/2020","1/6/2020"],"consumption":[500,None,None,None,900]}

df=pd.DataFrame.from_dict(data)
df

which give us our initial datframe

Next we're going create a list filler, that calculates the values that should be in between ; for our example :

  dfnotnull=df[df.iloc[:,2].notnull()]
  amount=dfnotnull.iloc[-1,2]-dfnotnull.iloc[0,2]
  count=dfnotnull.iloc[i+1,0]-dfnotnull.iloc[i,0]-1

  filler=[dfnotnull.iloc[0,2]]
  for j in range(count):
    filler.append(filler[j]+amount/(count+1))
  filler.append(dfnotnull.iloc[-1,2])
  filler

  df['consumption'] = filler
  df

which give us this result :

To get more generic result , we would be looping through the notnull values dataframe , between each two non null values we fill the values as we saw in the example:

dfnotnull=df[df.iloc[:,2].notnull()]
for i in range(len(dfnotnull)-1):
  amount=dfnotnull.iloc[i+1,2]-dfnotnull.iloc[i,2]
  count=len(df.loc[df.consumption.isnull(), 'consumption'])
  filler=[dfnotnull.iloc[i,2]]
  for j in range(count):
    filler.append(filler[j]+amount/(count+1))
  filler.append(dfnotnull.iloc[i+1,2])
  filler
  df.loc[df.loc[df.iloc[:,2].notnull()].index[i]:df.loc[df.iloc[:,2].notnull()].index[i+1],"consumption"]=filler