topic Re: issue with group by in Data Engineering

issue with group by

Braxx — Wed, 05 Jan 2022 10:58:54 GMT

I am trying to group by a data frame by "PRODUCT", "MARKET" and aggregate the rest ones specified in col_list. There are much more column in the list but for simplification lets take the example below.

Unfortunatelly I am getting the error:

"TypeError: unhashable type: 'Column'

on the line with expr

col_list = ["value", "units"]
 
exprs = {sum(x).alias(x) for x in col_list}
df2 = df1.groupBy("PRODUCT", "MARKET").agg(exprs)

TIA

Re: issue with group by

Anonymous — Wed, 05 Jan 2022 12:38:00 GMT

I think you'll need to comma separate each sum within the aggregate. I've never seen a list comprehension in the aggregate before.

.agg(sum(y).alias(y),
         sum(x).alias(x),
       .....)

Re: issue with group by

Reza — Thu, 06 Jan 2022 20:14:05 GMT

You should replace "{" with "["

exprs = [sum(x).alias(x) for x in col_list]

Re: issue with group by

Pholo — Mon, 10 Jan 2022 08:23:54 GMT

Hi @Shivers Robert

Try to use something like that

import pyspark.sql.functions as F
 
def year_sum(year, column_year, column_sum):
  return F.when(
    F.col(column_year) == year, F.col(column_sum)
  ).otherwise(F.lit(None))
  
display(df.select(*[F.sum(year_sum(i, 'year', 'your_column_variable')).alias(str(i)) for i in [2018, 2019]]))
#### OR you can use the pivot method
display(df.groupby(F.lit('fake')).pivot('year').agg(F.sum('your_column_variable')).drop('fake'))

let meknow if it works.

Re: issue with group by

Braxx — Mon, 10 Jan 2022 12:11:43 GMT

ya, Thanks. that's one thing. Another one was a missing "*".

Complete answer:

col_list = ["value", "units"]
 
exprs = [sum(x).alias(x) for x in col_list]
df2 = df1.groupBy("PRODUCT", "MARKET").agg(*exprs)