filter a csv file by partial string matching for a column

Question

I have a big csv file(contains 21 column). The file contains last few days data of user. I want to filter this file based on column values. The column is "Logout Time". Column value is like "Mon Jun 15 11:12:12 EST 2015". I want to make a seperate csv file for those records whose "Logout Time" is "Mon Jun 15" irrespective of the time. I tried to do this using pandas. To get those records I tried this:

df[df['Logout Time'].str.contains("Mon Jun 15")]

this gives me error as:

ValueError : cannot index with vector containing NA / NaN values

I tried this one also:

df[df['Logout Time'].str.contains("Mon Jun 15") == True]

this doesnt filter the results. it gave me whole records of original csv.

Can you post the output from df.info() what is log out time? is a str or a datetime dtype? Your first attempt should have worked unless you had no matches, what does df['Logout Time'].str.contains("Mon Jun 15") show? — EdChum
– EdChum, Commented Jun 24, 2015 at 13:52
I see dtype for that column as object, neither str nor datetime. df['Logout Time'].str.contains("Mon Jun 15") shows Value error ValueError : cannot index with vector containing NA / NaN values — Nitesh Selkari
– Nitesh Selkari, Commented Jun 24, 2015 at 13:58
So does df['Logout Time'].dropna().str.contains("Mon Jun 15") return anything? — EdChum
– EdChum, Commented Jun 24, 2015 at 14:18
yes..It gives me the boolean output for the match (True/False) — Nitesh Selkari
– Nitesh Selkari, Commented Jun 24, 2015 at 14:23
So does df[df['Logout Time'].dropna().str.contains("Mon Jun 15")] work? — EdChum
– EdChum, Commented Jun 24, 2015 at 14:24

EdChum · Accepted Answer · 2015-06-24 15:32:31Z

2

Your problem is that your data contains NaN values so you need to drop them using dropna first:

df[df['Logout Time'].dropna().str.contains("Mon Jun 15")]

so the above should work.

answered Jun 24, 2015 at 15:32

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

Collectives™ on Stack Overflow

filter a csv file by partial string matching for a column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related