Count number of columns above a date

user_15842395 0
Viewed 56

I have a pandas dataframe with several columns and I would like to know the number of columns above the date 2016-12-31 . Here is an example:

ID Bill Date 1 Date 2 Date 3 Date 4 Bill 2
4 6 2000-10-04 2000-11-05 1999-12-05 2001-05-04 8
6 8 2016-05-03 2017-08-09 2018-07-14 2015-09-12 17
12 14 2016-11-16 2017-05-04 2017-07-04 2018-07-04 35

And I would like to get this column

Count
0
2
3
pandas python
3 Answers

Just create the mask and call sum on axis=1

date = pd.to_datetime('2016-12-31')
(df[['Date 1','Date 2','Date 3','Date 4']]>date).sum(1)

OUTPUT:

0    0
1    2
2    3
dtype: int64

If needed, call .to_frame('count') to create datarame with column as count

(df[['Date 1','Date 2','Date 3','Date 4']]>date).sum(1).to_frame('Count')
   Count
0      0
1      2
2      3
user_91363480

Use df.filter to filter the Date* columns + .sum(axis=1)

(df.filter(like='Date') > '2016-12-31').sum(axis=1).to_frame(name='Count')

Result:

   Count
0      0
1      2
2      3
user_150706970

You can do:

df['Count'] = (df.loc[:, [x for x in df.columns if 'Date' in x]] > '2016-12-31').sum(axis=1)

Output:

     ID   Bill      Date 1      Date 2      Date 3      Date 4   Bill 2  Count
0     4      6  2000-10-04  2000-11-05  1999-12-05  2001-05-04        8      0
1     6      8  2016-05-03  2017-08-09  2018-07-14  2015-09-12       17      2
2    12     14  2016-11-16  2017-05-04  2017-07-04  2018-07-04       35      3

We select columns with 'Date' in the name. It's better when we have lots of columns like these and don't want to put them one by one. Then we compare it with lookup date and sum 'True' values.

user_133401430
Related