Spark - add new column to data frame with columns matching given pattern scala

Question

I want to append to dataframe with a new map column of existing columns which start with a given common prefix.

For example, I have input of

{"Prefix_A": "v_A", "Prefix_B": "v_B", "Field": "v"}, {"Prefix_A": "v_A", "Prefix_B": "v_B", "Prefix_C": "v_C", "Field": "v"}

I want to combine all fields with prefix "Prefix_" and get an output of

{"NewColumn": {"Prefix_A": "v_A", "Prefix_B": "v_B"}, "Field": "v"}, {"NewColumn": {"Prefix_A": "v_A", "Prefix_B": "v_B", "Prefix_C": "v_C"}, "Field": "v"}

I want to do this on the fly, i.e. I don't know the columns as the data is schemaless json dump.

Further, I want to construct a new map column of existing columns which matches given regular expression.

Can you provide an example of your expected input and output? It is hard to tell what exactly you are trying to do. It would also be helpful if you had a specific question to answer. — Kit Menke
– Kit Menke, Commented Mar 8, 2019 at 20:22

rluta · Accepted Answer · 2019-03-09 16:33:22Z

1

Assuming, you have an input file data.json containing your json entries, you can get the expected output with the following code:

import org.apache.spark.sql.functions.{col,struct}
import spark.implicits._

val df = spark.read.json("data.json")
val (prefixedColumns, otherColumns) = df.columns.partition(_.startsWith("Prefix"))
val transformedDf = df.select(
    struct(prefixedColumns.map(col):_*).as("NewColumn")
    +: otherColumns.map(col):_*)
transformedDf.write.json("output.json")

Basically, you recreate a new list of columns using the necessary functions and then apply them in select using the scala :_* notation to transform the sequence to varargs

edited Mar 9, 2019 at 16:33

answered Mar 8, 2019 at 21:20

rluta

6,9772 gold badges22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Leo C Over a year ago

Nice solution. Not sure why you refer to those built-in functions as UDFs though.

rluta Over a year ago

I tend to call all non-sql standard functions "udfs". I fixed the text to just reference "functions"

Collectives™ on Stack Overflow

Spark - add new column to data frame with columns matching given pattern scala

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related