jeudi 16 juin 2016

Pandas: Getting the distance to the row used to fill the missing/na values

I will do my best to explain this.

I have a pandas dataframe and I am trying to get the distance to the row which was used to fill a row of nan values in the same dataframe.

To elaborate, say I have the following dataframe (df) that contains 2 rows of nan values,

     A    B    C
0  0.0  0.0  0.0
1  1.0  2.0  3.0
2  NaN  NaN  NaN
3  NaN  NaN  NaN

Using the forward fill df.fillna(method='ffill'), I get the following dataframe,

     A    B    C
0  0.0  0.0  0.0
1  1.0  2.0  3.0
2  1.0  2.0  3.0
3  1.0  2.0  3.0

I would like to have another column (let's call it Proximity) in the dataframe that contains the distance to the row from which it filled its nan values, in this example, it should be,

     A    B    C  Proximity
0  0.0  0.0  0.0  0.0
1  1.0  2.0  3.0  0.0
2  1.0  2.0  3.0  1.0
3  1.0  2.0  3.0  2.0

Row 2's Proximity is 1, since it used the row above it to fill its own nan values.

Row 3's Proximity is 2, since it used the row above it by two levels to fill its own nan values.

Row 1 is already filled, therefore, the row it used to fill its own nan values is zero because it doesn't have any nan values.

Is there a way I can compute that Proximity column efficiently ?

By efficiently, I mean without using for loops.

Thanks in advance!

Aucun commentaire:

Enregistrer un commentaire