How to avoid duplicate columns after join?

by

The simple answer (from the Databricks FAQ on this matter) is to perform the join where the joined columns are expressed as an array of strings (or one string) instead of a predicate.

Below is an example adapted from the Databricks FAQ but with two join columns in order to answer the original poster’s question.

Here is the left dataframe:

val llist = Seq(("bob", "b", "2015-01-13", 4), ("alice", "a", "2015-04-23",10))

val left = llist.toDF("firstname","lastname","date","duration")

left.show()

/*
+---------+--------+----------+--------+
|firstname|lastname|      date|duration|
+---------+--------+----------+--------+
|      bob|       b|2015-01-13|       4|
|    alice|       a|2015-04-23|      10|
+---------+--------+----------+--------+
*/

Here is the right dataframe:

val right = Seq(("alice", "a", 100),("bob", "b", 23)).toDF("firstname","lastname","upload")

right.show()

/*
+---------+--------+------+
|firstname|lastname|upload|
+---------+--------+------+
|    alice|       a|   100|
|      bob|       b|    23|
+---------+--------+------+
*/

Here is an incorrect solution, where the join columns are defined as the predicate left("firstname")===right("firstname") && left("lastname")===right("lastname").

The incorrect result is that the firstname and lastname columns are duplicated in the joined data frame:

left.join(right, left("firstname")===right("firstname") &&
                 left("lastname")===right("lastname")).show

/*
+---------+--------+----------+--------+---------+--------+------+
|firstname|lastname|      date|duration|firstname|lastname|upload|
+---------+--------+----------+--------+---------+--------+------+
|      bob|       b|2015-01-13|       4|      bob|       b|    23|
|    alice|       a|2015-04-23|      10|    alice|       a|   100|
+---------+--------+----------+--------+---------+--------+------+
*/

The correct solution is to define the join columns as an array of strings Seq("firstname", "lastname"). The output data frame does not have duplicated columns:

left.join(right, Seq("firstname", "lastname")).show

/*
+---------+--------+----------+--------+------+
|firstname|lastname|      date|duration|upload|
+---------+--------+----------+--------+------+
|      bob|       b|2015-01-13|       4|    23|
|    alice|       a|2015-04-23|      10|   100|
+---------+--------+----------+--------+------+
*/

More Related Contents:

  1. How to query JSON data column using Spark DataFrames?
  2. Encoder error while trying to map dataframe row to updated row
  3. How do I detect if a Spark DataFrame has a column
  4. Spark Scala: How to convert Dataframe[vector] to DataFrame[f1:Double, …, fn: Double)]
  5. How to define schema for custom type in Spark SQL?
  6. Flattening Rows in Spark
  7. How can I change column types in Spark SQL’s DataFrame?
  8. Spark unionAll multiple dataframes
  9. How can I pass extra parameters to UDFs in Spark SQL?
  10. How to split a dataframe into dataframes with same column values?
  11. How to zip two (or more) DataFrame in Spark
  12. How to define a custom aggregation function to sum a column of Vectors?
  13. Spark DataFrame: does groupBy after orderBy maintain that order?
  14. Better way to convert a string field into timestamp in Spark
  15. Filling gaps in timeseries Spark
  16. Spark UDAF with ArrayType as bufferSchema performance issues
  17. Apache Spark how to append new column from list/array to Spark dataframe
  18. How to use COGROUP for large datasets
  19. What are possible reasons for receiving TimeoutException: Futures timed out after [n seconds] when working with Spark [duplicate]
  20. Why does join fail with “java.util.concurrent.TimeoutException: Futures timed out after [300 seconds]”?
  21. how to filter out a null value from spark dataframe
  22. Create new Dataframe with empty/null field values
  23. What are the various join types in Spark?
  24. Provide schema while reading csv file as a dataframe in Scala Spark
  25. Replace missing values with mean – Spark Dataframe
  26. Apache Spark, add an “CASE WHEN … ELSE …” calculated column to an existing DataFrame
  27. How to calculate the size of dataframe in bytes in Spark?
  28. Spark-Monotonically increasing id not working as expected in dataframe?
  29. Calculate Cosine Similarity Spark Dataframe
  30. Difference between two rows in Spark dataframe

Leave a Comment