Intuitive tool to filter multiple CSV files or multiple columns in one CSV file with complex IF requests

Question

I have a large email list I broadcast to, where each subscriber has multiple tags. My email broadcast system lets me download each tag as a separate CSV file containing all tagged email addresses* and not containing those without the tag.

I need to combine these tags in complex ways, so that I can essentially create a new set of tags to upload back to the site, as a new CSV file to be imported.

The software would either work off the original CSV files themselves (which would be less work for me) or off a resulting file I'd create by combining all of them into a CSV file with emails as the first column and tags as all other columns, with some Boolean 1/0 or yes/no etc.

This wouldn't be a once-off, so having a system in place to do these processes often is necessary.

Parsing the data to create a new file

I'd like to take the data (whether it be in from separate one-column CSV files, or simply from the resulting new single CSV file with multiple columns) and compare the yes/no combinations of the tags for each email and based on combinations (see below), decide whether or not that email is included or ignored.

This could be a simple case of adding a new 0 or 1 column to the same "grand" CSV file (which I could then add the extra step of filtering only '1's to the final CSV file) or adding/not adding that email itself to the final CSV file.

Complex operations needed

Here is a sample of the kind of processes I would need to run:

• all of (tag1, tag7, tag8) and NOT (tag2, tag20)
• at least 2 out of any from (tag11, tag12, tag13, tag14, tag15, tag16, tag17, tag18)
• (if tag17 is present, AND any out of (tag22, tag23, tag24)) OR (if tag18 is present, AND any out of (tag25, tag26, tag27))
• only tag24, and NOT any other tag
• only tag24, and NOT any out of (tag 11, tag12, tag13, tag14)

From some research online, I can see that the solution to my problem most likely involves writing up the code in python or similar, but I'm hoping there is a user-friendly software application that can do it so I can achieve what I'd like without coding.

If coding is required, it would ideally be kept to a minimum, or I'd have a script ready to go that I'd only need to alter file-sources & the if requests. I'm comfortable with writing if structures. Otherwise, I have basic coding experience and can learn what I need to do in that software environment.

I own Excel 2017 (maybe there is a plug-in I'm not aware of for my complex filters) and can pay for software to do this if needed, depending on its functionality, although features beyond what I've described here aren't necessary.

Thanks for any recommendations!!

*The downloaded CSV file also has their name in another column, but I'd remove that for the purposes of this process.

Answer 1

I would load it into PostgreSQL, and then use SQL to query it. Those are pretty complex operations.

Answer 2

I had asked a similar question related to the same issue, and eventually came to an answer there that I can apply directly here.

A simple program for doing this in a graphical or otherwise very simple interface may or may not exist, but essentially it would seem that the easiest way to do it by far is with the pandas library within Python. Here is documentation for setting that up.

One of the main reasons is that this programming language is very straightforward to learn (more so than SQL, suggested by the other answerer - and I disagree that the operations are that complex), and I'd argue that you could get on top of it with an afternoon of learning basic Python programming (especially if you have learned some form of programming in the past), and understanding basic Boolean logic.

Taking the first example I cited and how it would be implemented in Python, with the pandas library imported (and removing some other code like setting up the databases etc.), it could be rendered something like the following, presuming a table with columns filled with these tags & values in the cells as True or False:

• all of (tag1, tag7, tag8) and NOT (tag2, tag20)

result = alldata[(alldata[tag1] & alldata[tag7] & alldata[tag8]) & ~(alldata[tag2] | alldata[tag20])]

To explain this briefly, if your data is imported to a dataframe of values called "alldata", then calling "alldata[tag1]" will return a list of True and False variables in a 1-dimensional array, corresponding to the points in the dataframe where tag1 is true or false. Then

alldata[tag1] & alldata[tag7] & alldata[tag8]

will combine each of these three tags, such that you'll get an array the same length, but now it's only got True values when all three of them were True and is false otherwise.

Similarly,

alldata[tag2] | alldata[tag20]

will combine an array of True/False for tag2 and tag20, and return values of True when either one is true and False only if both are False.

The ~ operation will invert this and turn all True to False and all False to True.

Then the & between the first and second parts will combine them such that the resulting parts of the array only remain True if the answer were True in both the first and second.

This leaves you with a resulting array of True and False values, where "True" corresponds to a point where all of tag1, tag7 & tag8 are true and tag2 or tag20 are False.

But if you feed this new array back in as a call between square brackets (up to now we have only fed tagX into the square brackets), then

alldata[ ## the resulting combination we have ## ]

will actually remove the "False" values from out of the alldata dataframe in the result, and you have a new dataframe that only has the remaining values, which is what we wanted!

You'd then export this as a new CSV file. You can apply similar operations or variations within python to get all of the other ones I had suggested in my original question.

I've been having lots of fun enjoying learning pandas and Python (I have some basic programming experience from college), but I realise some of this may feel intimidating. I hope it doesn't and this answer helps someone in a similar situation to the one I was in!