How To Get Index Number Of Rows With Condition In Pandas

I have one dataframe in which I need to do comparison with Date column with conditions and need to get index numbers. Condition:- If date == 01-06-2018 then get me index number of

Solution 1:

You can create a custom function:

def getdate(date,df=df):
    df=df.copy()
    df['Date']=pd.to_datetime(df['Date'],dayfirst=True)
    date=pd.to_datetime(date,dayfirst=True)
    if df['Date'].eq(date).any():
        return df[df['Date'].eq(date)].index
    else:
        date=(date-pd.DateOffset(months=6))
        return df[df['Date'].eq(date)].index

Finally:

out=getdate('01-06-2018')

output of out:

Int64Index([4, 12, 20, 28], dtype='int64')

Explaination:

we are creating a function which check the given date (currently '01-06-2018' is given) is equal to the dates in df['Date'] Series and it returns boolean Series and we are passing that boolean series into DataFrame df and filtering the results and getting index by using .index attribute this is happening in if block and In the else block if there is no row present in the dataframe for the condition that we checked in if block then we are substracting or you can also say rounding the date to 6 month previous(by using pd.DateOffset()) so now the date(that you entered (currently '01-06-2018' is given) becomes '01-12-2017'(since we are using to_datetime() method inside the function so it becomes '2017-12-01') so then we are checking if this date is present in the 'df3' column in your dataframe if it is present then it will give you the indexes else it will give you empty Series

Explaination of every line inside the function:

def getdate(date,df=df):
    df=df.copy()
    #creating the copy of the dataframe so that the changes made here doesn't reflect in your original dataframe
    df['Date']=pd.to_datetime(df['Date'],dayfirst=True)
    #converting the Date column to datetime dtype
    date=pd.to_datetime(date,dayfirst=True)
    #converting the date that you passed when calling the function '01-06-2018' to datetimeif df['Date'].eq(date).any():
    #checking if '01-06-2018' is present in 'Date' columnreturn df[df['Date'].eq(date)].index
        #Filtering out result and getting indexelse:
    #If '01-06-2018' is not present in 'Date' column then
        date=(date-pd.DateOffset(months=6))
        #Rounding/substracting the 6 months so now date become '2017-12-01'return df[df['Date'].eq(date)].index
        #Filtering out result and getting index

Update:

After adding conditions your function becomes:

def getdate(date,df=df):
    df=df.copy()
    df['Date']=pd.to_datetime(df['Date'],dayfirst=True)
    date=pd.to_datetime(date,dayfirst=True)
    if df['Date'].eq(date).any():
        return df[df['Date'].eq(date)].index
    else:
        to_check=pd.to_datetime(pd.Series(['01-03-18','01-06-2018','01-09-2018']),dayfirst=True)
        if to_check.isin([date]).any():
            date=pd.to_datetime('01-12-2017',dayfirst=True)
        else:
            date=(date-pd.DateOffset(months=6))
        return df[df['Date'].eq(date)].index