Search for keywords within csv/word files which are listed in an excel sheet

Question

We have 1000's of csv, word files in different folders. Is there any open source data mining software which will look into the csv files for

1) First has to check for the Project names. If present..

2) Look for the County

3) Then, State -> Country

4)If all the conditions are satisfied, assign Order to the file.

I've brief experience working with Orange data mining software. Tried that, but couldn't even able to check project names within the csv's. Just installed Rapidminer and i can't even comprehend what i can do with it.

If anyone can shed light on what sort of software/process i have to look, will be greatly helpful.

Note: Most of the csv's & word documents are having more than 20 pages

Answer 1

Try something like the following in python:

import pandas as pd

df = pd.read_csv(path_to_file, header=0) # header=0 means the first row contains column names

# locate the records where none of the following columns are null ('~' means not)
mask = ~df[['Project Name', 'County', 'State', 'Country']].isnull()
df[ mask ]

I'm not entirely sure what you want to do with the 'Order' columns, but if it's to get just the "Order" column from the above statement, then just slightly modify it to the following:

df['Order'][mask]

If you want to set the value of "Order" for those columns that aren't null, then just do this:

# we need to adjust our mask because the current version won't work with the .loc function that we'll use below

mask = df[(~df['PROJECTNAME'].isnull())&(~df['County'].isnull())&(~df['State'].isnull())&(~df['Country'].isnull())]

df.loc[ mask, "Order"] = whatever you want

If you want to save all the orders where the specified columns aren't null to a separate csv, do this:

df[ mask ].to_csv(name_your_file)