Select dataframe rows matching date regular expression in Python

Question

In the following dataframe, how can I select rows that have date matching '2018-06%'?

Date                     Cost   Source
2018-06-01 00:00:77      0.03   RFP
2018-06-01 00:05:77      0.01   RFP
2018-06-01 04:09:77      0.90   EFO
2018-05-02 06:99:08      0.07   THP
2018-07-01 09:05:79      0.20   RFP

Expected output

Date                     Cost   Source
2018-06-01 00:00:77      0.03   RFP
2018-06-01 00:05:77      0.01   RFP
2018-06-01 04:09:77      0.90   EFO

user3483203 · Accepted Answer · 2018-06-07 19:06:55Z

2

It looks like you have bad timestamps, which will make it difficult to convert to datetime and search by month.

My first recommendation would be to fix that data upstream, although if that is not an option, or if you are set on using regular expressions to match, you can simply use ^2018-06:

df[df.Date.str.match(r'^2018-06')]

                  Date  Cost Source
0  2018-06-01 00:00:77  0.03    RFP
1  2018-06-01 00:05:77  0.01    RFP
2  2018-06-01 04:09:77  0.90    EFO

The regex is quite straightforward:

^           # Asserts beginning of string
2018-06     # matches your date

answered Jun 7, 2018 at 19:06

user3483203

51.3k10 gold badges72 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

iprof0214 Over a year ago

Thanks Chrisz, that worked! Timestamp is not an issue, I wanted to select only current month data from the dataframe.

user3483203 Over a year ago

I am unable to convert your timestamps to datetime because of the hh:mm:ss are not valid. If you have them as datetimes, there is a much easier way to solve this.

Collectives™ on Stack Overflow

Select dataframe rows matching date regular expression in Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related