How do I merge two CSV's with nearly identical sets using rules for which data is kept? (Using Ruby & FasterCSV)

Question

I have two csv files, each with 13 columns.

The first column of each row contains a unique string. Some are duplicated in each file, some only exist in one file.

If the row exists in only one file I want to keep it in the new file.

If it exists in both I want to keep the one that has a certain value (or lacks a certain value) in a certain column of that same row.

For example:

file 1:

D600-DS-1991, name1, address1, date1
D601-DS-1991, name2, address2, date2
D601-DS-1992, name3, address3, date3

file 2:

D600-DS-1991, name1, address1, time1
D601-DS-1992, dave1, address2, date2

I would keep the first row of the first file because the fourth column contains date instead of time. I would keep the second row of the first file since its first column, first row value is unique. I would keep the second row of the second file as the third row of the new file because it contains text other than "name#" in the second column.

Should I first map all of the unique values to one another so that each file contains the same number of entries - even if some are blank or just have filler data?

I only know a little ruby and python... but I much prefer to solve this with a single Ruby file if at all possible since I will be able to understand the code better. If you can't do it in Ruby then please feel free to answer differently!

Answer 1

I'm not super happy with my solution but it works:

require 'csv'

def readcsv(filename)
  csv = {}
  CSV.foreach(filename) do |line|
    csv[line[0]] = { name: line[1], address: line[2], date: line[3] }
  end
  csv
end

csv1 = readcsv('orders1.csv')
csv2 = readcsv('orders2.csv')

results = {}
csv1.each do |id, val|
  unless csv2[id]
    results[id] = val # checks to see if it only exists in 1 file
    next
  end

  #see if name exists
  if (val[:name] =~ /name/) && (csv2[id]) && (csv2[id][:name] =~ /name/).nil?
    csv1.delete(id)
  end

  #missing some if statement regarding date vs. time
end

results = results.merge(csv2) # merge together whatever is remaining

CSV.open('newfile.csv', 'w') do |csv|
  results.each do |key, val|
    row = []
    csv << (row.push(key, val.values)).flatten
  end
end

Output of newfile.csv :

D601-DS-1991, name2, address2, date2
D600-DS-1991, name1, address1, time1
D601-DS-1992, dave1, address2, date2

Answer 2

I won't give you the complete code but here's a general approach to such a problem:

require 'csv'

# list of csv files to read
files = ['a.csv', 'b.csv']

# used to resolve conflicts when we have a existing entry with same id
# here, we prefer the new entry if its fourth column starts with `'date'`
# this also means that the last file in the list above wins if both entries are valid.
def resolve_conflict(existing_entry, new_entry)
  if new_entry[3].start_with? 'date'
    new_entry
  else
    existing_entry
  end
end

# keep a hash of entries, with the unique id as key.
# we use this id to detect duplicate entries later on.
entries = {}

CSV.foreach(file) do |new_entry|
  # get id (first column) from row
  id = new_entry[0]

  # see if we have a conflicting entry
  existing_entry = entries[id]
  if existing_entry.nil?
    # no conflict, just save the row
    entries[id] = new_entry
  else
    # resolve conflict and save that
    entries[id] = resolve_conflict(existing_entry, new_entry)
  end
end

# now all conflicts are resolved
# note that stale rows from the first file could now be in the result
# you might want to filter them out as well
# we can now build a new csv file with the result
CSV.open("result.csv", "w") do |csv|
  entries.values.each do |row|
    csv << row
  end
end