How to add a column to pandas DataFrame based on the result of a condition

Question

I want to add a column to a DataFrame based on whether or not the person was blocked before or after an incentive.

For example, in row 0 the person was Blocked after the IncentiveStart date so a 1 will go there so at the end I can count each time. Likewise, if a person is Cleared after the incentive then a 1 will go in the corresponding cell.

I can't figure out to do it with Pandas since the only way I can think of is with an if statement. If it's any help, columns = ReceiptDate, IncentiveStart are DateTime objects.

Thanks in advance.

Here's my DataFrame and below it what I want it to look like:

    df = pd.DataFrame([['AMW','Blocked','5/22/2011','10/1/2015','TRUE'], 
                            ['AMW','Cleared','6/1/2011','','FALSE'],
                            ['CRC','Cleared','6/1/2011','','FALSE'],
                            ['BRO','Blocked','6/2/2016','10/1/2015','TRUE'],
                            ['WOR','Cleared','5/12/2011','','FALSE'],
                            ['BUR','Cleared','6/9/2015','10/1/2014','FALSE'],
                            ['COM','Cleared','6/1/2011','','FALSE'],], 
                           columns=['person','Clear_Decline','ReceiptDate',
                                    'IncentiveStart','hasIncentive'])

      person Clear_Decline ReceiptDate IncentiveStart hasIncentive
    0    AMW       Blocked   5/22/2011      10/1/2015         TRUE
    1    AMW       Cleared    6/1/2011                       FALSE
    2    CRC       Cleared    6/1/2011                       FALSE
    3    BRO       Blocked    6/2/2016      10/1/2015         TRUE
    4    WOR       Cleared   5/12/2011                       FALSE
    5    BUR       Cleared    6/9/2015      10/1/2014        FALSE
    6    COM       Cleared    6/1/2011                       FALSE

    df = pd.DataFrame([['AMW','Blocked','5/22/2011','10/1/2015','TRUE',0,0,1,0], 
                            ['AMW','Cleared','6/1/2011','','FALSE',1,0,0,0],
                            ['CRC','Cleared','6/1/2011','','FALSE',1,0,0,0],
                            ['BRO','Blocked','6/2/2016','10/1/2015','TRUE',0,0,0,1],
                            ['WOR','Cleared','5/12/2011','','FALSE',1,0,0,0],
                            ['BUR','Cleared','6/9/2015','10/1/2014','FALSE',0,1,0,0],
                            ['COM','Cleared','6/1/2011','','FALSE',1,0,0,0],], 
                           columns=['person','Clear_Decline','ReceiptDate',
                                    'IncentiveStart','hasIncentive',
                                    'Clearedbefore','ClearedAfter','Blockedbefore','BlockedAfter'])

person Clear_Decline ReceiptDate IncentiveStart hasIncentive  Clearedbefore  ClearedAfter  Blockedbefore  BlockedAfter
    0    AMW       Blocked   5/22/2011      10/1/2015         TRUE              0             0              1             0
    1    AMW       Cleared    6/1/2011                       FALSE              1             0              0             0
    2    CRC       Cleared    6/1/2011                       FALSE              1             0              0             0
    3    BRO       Blocked    6/2/2016      10/1/2015         TRUE              0             0              0             1
    4    WOR       Cleared   5/12/2011                       FALSE              1             0              0             0
    5    BUR       Cleared    6/9/2015      10/1/2014        FALSE              0             1              0             0
    6    COM       Cleared    6/1/2011                       FALSE              1             0              0             0

Answer 1

Thanks to @Edward for pointing me in the right direction.

I don't think the dates need to be converted since they are already in the right type so Pandas is able to check which one is older.

With that in mind I was able to tweak @Edward's answer and come up with my own:

df['BlockedAfter'] = 0
df.ix[(df['Clear_Decline'] == 'Blocked') & (df['ReceiptDate'] >= df['IncentiveStart']) & 
                    (df['IncentiveStart']).notnull(), 'BlockedAfter'] = 1

  person Clear_Decline ReceiptDate IncentiveStart hasIncentive  BlockedAfter
0    AMW       Blocked  2011-05-22     2015-10-01         TRUE             0
1    AMW       Cleared  2011-06-01            NaT        FALSE             0
2    CRC       Cleared  2011-06-01            NaT        FALSE             0
3    BRO       Blocked  2016-06-02     2015-10-01         TRUE             1
4    WOR       Cleared  2011-05-12            NaT        FALSE             0
5    BUR       Cleared  2015-06-09     2014-10-01        FALSE             0
6    COM       Cleared  2011-06-01            NaT        FALSE             0

Answer 2

I show a solution for df['BlockedAfter'], and i hope you can repeat it for all variables

first - convert to datetime

df['ReceiptDate'] = pd.to_datetime(df['ReceiptDate'])
df['IncentiveStart'] = pd.to_datetime(df['IncentiveStart'])

then we get the time difference

df['time'] = df['IncentiveStart'].values.astype('datetime64[D]').astype(int) - df['ReceiptDate'].values.astype('datetime64[D]').astype(int)

And a new column with two condition

df['BlockedAfter'] = 0
df.ix[(df['time'] > 0) & (df['Clear_Decline'] == 'Blocked'), 'BlockedAfter'] = 1
df['BlockedAfter']
0    1
1    0
2    0
3    0
4    0
5    0
6    0
Name: BlockedAfter, dtype: int64

Answer 3

Another way is to define if statements as a function, then apply them along an axis. For example:

# Taking `df` defined in OP's question
df.ReceiptDate =  pd.to_datetime(df.ReceiptDate)
df.IncentiveStart = pd.to_datetime(df.IncentiveStart)

df.ReceiptDate =  pd.to_datetime(df.ReceiptDate)
df.IncentiveStart = pd.to_datetime(df.IncentiveStart)

def condition(row):
    if row['Clear_Decline'] == 'Cleared':
        if row['hasIncentive'] == 'FALSE':
            if row['ReceiptDate']>row['IncentiveStart']:
                return 0
            else:
                return 1
        else:
            return 1
    else:
        return 0

df['Clearedbefore'] = df.apply(condition, axis=1)

This way your code is longer but logic might be more clear.