pandasloops

Pandas get first negative number from the bottom


I have following df. In which I want to see the first negative number after the last outcome number. & in the "first negative from last " col "this should occur or rather anything to highlight that row. The "this should appear on the first negative after the last outcome number (outcome number can be postive or negative & "this should appear only against negative rice number & not the positive rice number).

import pandas as pd
import numpy as np
df1=pd.DataFrame({
        "rice": [49235.6,-49234.5,-49230.05,-49190.2,49193.1,-49140.95,-49125.3],
        "outcome": [-68.8499999999985,np.nan,np.nan,np.nan,-41.4000000000014,np.nan,np.nan,],
        "first negative from last ": [np.nan,np.nan,np.nan,np.nan,np.nan,"this",np.nan],
       })

I was trying to use

df.shit()

but not sure how to put conditions.

For better understanding following 2 df had shown exactly in which row "this" would appear.

df2=pd.DataFrame({
        "rice": [49235.6,-49234.5,-49230.05,-49190.2,49193.1,49140.95,-49125.3],
        "outcome": [-68.8499999999985,np.nan,np.nan,np.nan,56,np.nan,np.nan,],
        "first negative from last ": [np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,"this"],
       })




df3=pd.DataFrame({
        "rice": [49235.6,49234.5,-49230.05,-49190.2,-49193.1,-49140.95,-49125.3],
        "outcome": [6.8499999999985,np.nan,np.nan,np.nan,np.nan,np.nan,np.nan,],
        "first negative from last ": [np.nan,np.nan,"this",np.nan,np.nan,np.nan,np.nan],
       })

Solution

  • Code

    import numpy as np
    cond = (df1['outcome'].bfill().isna() & df1['rice'].lt(0))
    df1['first negative from last'] = (
        np.where(cond & (df1.index == cond.idxmax()), 'this', None)
    )
    

    df1:

           rice  outcome first negative from last
    0  49235.60   -68.85                     None
    1 -49234.50      NaN                     None
    2 -49230.05      NaN                     None
    3 -49190.20      NaN                     None
    4  49193.10   -41.40                     None
    5 -49140.95      NaN                     this
    6 -49125.30      NaN                     None