PySpark Fast method for unpivoting a dataframe

Question

Is there a fast and efficient way to unpivot a dataframe? I have used the follwoing methods and although both work on a sample data when on full set it runs for hours and never completes.

Method 1:

def to_long(df, by): # Filter dtypes and split into column names and type description cols, dtypes = zip(*((c, t) for (c, t) in df.dtypes if c not in by)) # Spark SQL supports only homogeneous columns assert len(set(dtypes)) == 1, "All columns have to be of the same type" # Create and explode an array of (column_name, column_value) structs kvs = explode(array([ struct(lit(c).alias("question_id"), col(c).alias("response_value")) for c in cols ])).alias("kvs") return df.select(by + [kvs]).select(by + ["kvs.question_id", "kvs.response_value"])

Method 2:

def rowExpander(row): rowDict = row.asDict() valA = rowDict.pop('user_id') for k in rowDict: yield Row(**{'user_id': valA , 'question_id' : k, 'response_value' : row[k]}) user_response_df = spark.createDataFrame(response_df.rdd.flatMap(rowExpander))

deronwu · Accepted Answer · 2018-09-21 01:43:04Z

Maybe you can try select each column as a new dataframe and union all of them
Like this

# Get all columns except 'user_id' cols = [col for col in df.columns if col != 'user_id'] # Select user_id and another column as a new dataframe. # Use column_name as the value of the new column `question_id` # Use column_value as the value of the new column `response_value` # Then union all of these new dataframes df = reduce(lambda df1, df2: df1.union(df2), [df.select('user_id', F.lit(c).alias('question_id'), F.col(c).alias('response_value')) for c in cols])

Ran Feldesh · Accepted Answer · 2019-07-24 16:39:10Z

0

df.selectExpr('col1', 'stack(2, "col2", col2, "col3", col3) as (cols, values)')

answered Jul 24, 2019 at 16:39

Ran Feldesh

1,19912 silver badges18 bronze badges

Collectives™ on Stack Overflow

PySpark Fast method for unpivoting a dataframe

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related