Merge the intersection of two CSV files with Scala

Question

From input 1:

fruit, apple, cider  
animal, beef, burger

and input 2:

animal, beef, 5kg
fruit, apple, 2liter
fish, tuna, 1kg

I need to produce:

fruit, apple, cider, 2liter
animal, beef, burger, 5kg

The closest example I could get is:

object FileMerger {
def main(args : Array[String]) {
  import scala.io._
  val f1 = (Source fromFile "file1.csv" getLines) map (_.split(", *")(1))
  val f2 = Source fromFile "file2.csv" getLines
  val out = new java.io.FileWriter("output.csv")
  f1 zip f2 foreach { x => out.write(x._1 + ", " + x._2 + "\n") }
  out.close
  }
}

The problem is that the example assumes that the two CSV files contain the same number of elements and in the same order. My merged result must only contain elements that are in the first and the second file. I am new to Scala, and any help will be greatly appreciated.

Answer 1

It's tough to infer a requirement from one example. May be something like this is would serve your needs:

• Create a map from key to line for the second file f2 (so from "animal, beef" -> "5kg")
• For each lines in the first file f1, get the key to look up in the map
• Look up value, if found write output

That translates to

val f1 = Source fromFile "file1.csv" getLines
val f2 = Source fromFile "file2.csv" getLines
val map = f2.map(_.split(", *")).map(arr => arr.init.mkString(", ") -> arr.last}.toMap
for {
  line <- f1
  key = line.split(", *").init.mkString(", ")
  value <- map.get(key)
} {
  out.write(line + ", " + value + "\n")
}

Answer 2

You need an intersection of the two files: the lines from file1 and file2 which share some criteria. Consider this through a set theory perspective: you have two sets with some elements in common, and you need a new set with those elements. Well, there's more to it than that, because the lines aren't really equal...

So, let's say you read file1, and that's of type List[Input1]. We could code it like this, without getting into any details of what Input1 is:

case class Input1(line: String)
val f1: List[Input1] = (Source fromFile "file1.csv" getLines () map Input1).toList

We can do the same thing for file2 and List[Input2]:

case class Input2(line: String)
val f2: List[Input2] = (Source fromFile "file2.csv" getLines () map Input2).toList

You might be wondering why I created two different classes if they have the exact same definition. Well, if you were reading structured data, you would have two different types, so let's see how to handle that more complex case.

Ok, so how do we match them, since Input1 and Input2 are different types? Well, the lines are matched by keys, which, according to your code, are the first column in each. So let's create a class Key, and conversions Input1 => Key and Input2 => Key:

case class Key(key: String)
def Input1IsKey(input: Input1): Key = Key(input.line split "," head) // using regex would be better
def Input2IsKey(input: Input2): Key = Key(input.line split "," head)

Ok, now that we can produce a common Key from Input1 and Input2, let's get the intersection of them:

val intersection = (f1 map Input1IsKey).toSet intersect (f2 map Input2IsKey).toSet

So we can build the intersection of lines we want, but we don't have the lines! The problem is that, for each key, we need to know from which line it came. Consider that we have a set of keys, and for each key we want to keep track of a value -- that's exactly what a Map is! So we can build this:

val m1 = (f1 map (input => Input1IsKey(input) -> input)).toMap
val m2 = (f2 map (input => Input2IsKey(input) -> input)).toMap

So the output can be produced like this:

val output = intersection map (key => m1(key).line + ", " + m2(key).line)

All you have to do now is output that.

Let's consider some improvements on this code. First, note that the output produced above repeats the key -- that's exactly what your code does, but not what you want in the example. Let's change, then, Input1 and Input2 to split the key from the rest of the args:

case class Input1(key: String, rest: String)
case class Input2(key: String, rest: String)

It's now a bit harder to initialize f1 and f2. Instead of using split, which will break all the line unnecessarily (and at great cost to performance), we'll divide the line right the at the first comma: everything before is key, everything after is rest. The method span does that:

def breakLine(line: String): (String, String) = line span (',' !=)

Play a bit with the span method on REPL to get a better understanding of it. As for (',' !=), that's just an abbreviated form of saying (x => ',' != x).

Next, we need a way to create Input1 and Input2 from a tuple (the result of breakLine):

def TupleIsInput1(tuple: (String, String)) = Input1(tuple._1, tuple._2)
def TupleIsInput2(tuple: (String, String)) = Input2(tuple._1, tuple._2)

We can now read the files:

val f1: List[Input1] = (Source fromFile "file1.csv" getLines () map breakLine map TupleIsInput1).toList
val f2: List[Input2] = (Source fromFile "file2.csv" getLines () map breakLine map TupleIsInput2).toList

Another thing we can simplify is intersection. When we create a Map, its keys are sets, so we can create the maps first, and then use their keys to compute the intersection:

case class Key(key: String)
def Input1IsKey(input: Input1): Key = Key(input.key)
def Input2IsKey(input: Input2): Key = Key(input.key)

// We now only keep the "rest" as the map value
val m1 = (f1 map (input => Input1IsKey(input) -> input.rest)).toMap
val m2 = (f2 map (input => Input2IsKey(input) -> input.rest)).toMap

val intersection = m1.keySet intersect m2.keySet

And the output is computed like this:

val output = intersection map (key => key + m1(key) + m2(key))

Note that I don't append comma anymore -- the rest of both f1 and f2 start with a comma already.