How to perform union on two DataFrames with different amounts of columns in spark?

by

In Scala you just have to append all missing columns as nulls.

import org.apache.spark.sql.functions._

// let df1 and df2 the Dataframes to merge
val df1 = sc.parallelize(List(
  (50, 2),
  (34, 4)
)).toDF("age", "children")

val df2 = sc.parallelize(List(
  (26, true, 60000.00),
  (32, false, 35000.00)
)).toDF("age", "education", "income")

val cols1 = df1.columns.toSet
val cols2 = df2.columns.toSet
val total = cols1 ++ cols2 // union

def expr(myCols: Set[String], allCols: Set[String]) = {
  allCols.toList.map(x => x match {
    case x if myCols.contains(x) => col(x)
    case _ => lit(null).as(x)
  })
}

df1.select(expr(cols1, total):_*).unionAll(df2.select(expr(cols2, total):_*)).show()

+---+--------+---------+-------+
|age|children|education| income|
+---+--------+---------+-------+
| 50|       2|     null|   null|
| 34|       4|     null|   null|
| 26|    null|     true|60000.0|
| 32|    null|    false|35000.0|
+---+--------+---------+-------+

Update

Both temporal DataFrames will have the same order of columns, because we are mapping through total in both cases.

df1.select(expr(cols1, total):_*).show()
df2.select(expr(cols2, total):_*).show()

+---+--------+---------+------+
|age|children|education|income|
+---+--------+---------+------+
| 50|       2|     null|  null|
| 34|       4|     null|  null|
+---+--------+---------+------+

+---+--------+---------+-------+
|age|children|education| income|
+---+--------+---------+-------+
| 26|    null|     true|60000.0|
| 32|    null|    false|35000.0|
+---+--------+---------+-------+

More Related Contents:

  1. Spark Equivalent of IF Then ELSE
  2. How to add a constant column in a Spark DataFrame?
  3. Spark Dataframe distinguish columns with duplicated name
  4. How do I add a new column to a Spark DataFrame (using PySpark)?
  5. How to change dataframe column names in pyspark?
  6. ‘PipelinedRDD’ object has no attribute ‘toDF’ in PySpark
  7. Explode in PySpark
  8. Retrieve top n in each group of a DataFrame in pyspark
  9. Pyspark: explode json in column to multiple columns
  10. How can we JOIN two Spark SQL dataframes using a SQL-esque “LIKE” criterion?
  11. Dividing complex rows of dataframe to simple rows in Pyspark
  12. Pyspark ‘NoneType’ object has no attribute ‘_jvm’ error
  13. Why is Apache-Spark – Python so slow locally as compared to pandas?
  14. Cast column containing multiple string date formats to DateTime in Spark
  15. How to explode multiple columns of a dataframe in pyspark
  16. Spark add new column to dataframe with value from previous row
  17. PySpark: multiple conditions in when clause
  18. PySpark create new column with mapping from a dict
  19. Pyspark: Replacing value in a column by searching a dictionary
  20. Create Spark DataFrame. Can not infer schema for type
  21. Pivot String column on Pyspark Dataframe
  22. Using UDF ignores condition in when
  23. Create single row dataframe from list of list PySpark
  24. Total size of serialized results of 16 tasks (1048.5 MB) is bigger than spark.driver.maxResultSize (1024.0 MB)
  25. When to cache a DataFrame?
  26. Multiple condition filter on dataframe
  27. How to return a “Tuple type” in a UDF in PySpark?
  28. How to connect HBase and Spark using Python?
  29. How to pivot on multiple columns in Spark SQL?
  30. Apache Spark — Assign the result of UDF to multiple dataframe columns

Leave a Comment