issue with group by

Braxx
Contributor II

‎01-05-2022 02:58 AM

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

Labels:
0 Kudos

Anonymous
Not applicable

‎01-05-2022 04:38 AM

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),
       .....)

Reza
New Contributor III

‎01-06-2022 12:14 PM

You should replace "{" with "["

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

Braxx
Contributor II
In response to Reza

‎01-10-2022 04:11 AM

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)

View solution in original post

0 Kudos

Pholo
Contributor

‎01-10-2022 12:23 AM

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.

0 Kudos