Recommand · November 24, 2021 0

Count number of nans between two numbers in row

I have a Pandas DataFrame with consumption measurements over a several days, so that a measurement on a given day represents the consumption over all the previous days without measurements. For each consumer (a row) I have something like Nan, 10, Nan, Nan, Nan, Nan, Nan, Nan, 21, Nan, ..., meaning that the average consumption between the 10 and the 20 was 21/7=3 (six NaNs plus one).

The measurements come at irregular intervals, so I need to divide each measurement by the number of NaNs between it and the previous measurement. I want my output to be Nan, 10, Nan, Nan, Nan, Nan, Nan, Nan, 3, Nan, ... (I can live without getting the first measurement in each row right How can I do this? Here is an example of my data:

SP ID,2016-12-28,2016-12-29,2016-12-30,2016-12-31,2017-01-01,2017-01-03,2017-01-04,2017-01-05,2017-01-06,2017-01-09,2017-01-10,2017-01-11,2017-01-12,2017-01-13,2017-01-16,2017-01-17,2017-01-18,2017-01-19,2017-01-20,2017-01-21,2017-01-23,2017-01-24,2017-01-25,2017-01-26,2017-01-27,2017-01-29,2017-01-30,2017-01-31,2017-02-01,2017-02-02,2017-02-03,2017-02-06,2017-02-07,2017-02-08,2017-02-09,2017-02-10,2017-02-13,2017-02-14,2017-02-15,2017-02-16,2017-02-17,2017-02-18,2017-02-21,2017-02-22,2017-02-23,2017-02-24,2017-02-27,2017-02-28,2017-03-01,2017-03-02
100854,,,4.0,,,,,,,,,,,,,,,,,,,,,,,,,4.0,,,,,,,,,,,,,,,,,,,,3.0,,
120355,,,9.0,,,,,,,,,,,,,,,,,,,,,,,,9.0,,,,,,,,,,,,,,,,,,,,,,,9.0
200357,,,,,,,,,,,18.0,,,,,,,,,,,,,,,,,,,,,,,,22.0,,,,,,,,,,,,,,,