Software to merge CSV files with true/false columns if entry is present

Question

I have multiple separate CSV files that are just a single column list of email addresses, where each file represents a 'tag' associated with that email. I'd like to merge them into one CSV file, such that all email addresses in any files are listed in the first column, and all other columns are the file names and a true/false or 1/0 if the email is in that file.

Which software would have this feature, and how would I implement it? I own the latest version of Excel if that can do it.

Answer 1

Perl is great for this.. You can also whip up an easy bash one liner too. This should work though.

#!/usr/bin/env perl
use strict;
use warnings;

use Text::CSV_XS;

my $csv = Text::CSV_XS->new;
my %db, @tags;

while (my $email = <<>>) {
  chomp $email;
  push @tags, $ARGV;
  $db{$email}{$ARGV} = 1;
}

# Header
$csv->say(*STDOUT, ["Email", map $_, @tags]);

foreach my $email (keys %db) {
  $csv->say(*STDOUT, [$email, map $db{$email}{$_}//0, @tags]);
}

If you don't have Text::CSV_XS, you can install it with

cpan Text::CSV_XS;

Save this to a file, runme, chmod u+x runme, and then ./runme *

Answer 2

In the end, it seems like there are two ways to handle this based on the level of experience I had, that I can share here in case anyone else is in a similar situation.

1. Very simply do it in Excel using a macro. This SO answer explains well how to do a basic merge on common values. If you want to solve the problem quickly, this is likely your best bet.
2. As Eric Shain said in the first comment, the pandas library within Python is designed especially for things like this, and allows for much more interesting possibilities. Since I needed more flexibility and the ability to do more down the road, I went with this solution, since simple programming within Python is very easy, even if you are rusty as I was. See the third comment for excellent links to get started!