Modify Pyspark Dataframe with withColumn after groupby

Viewed 42

I have a data pyspark dataframe that I want to do a simple rows count and unique id count and then show me the percentage of the unique id against total unique id counts.

x = data.groupby('trxCampaign').agg(
  sum(lit(1)).alias('Nrows'),
  countDistinct(col('DocumentNumber')).alias('N docs')
)
x = x.withColumn('perc docs', col('N docs')/sum(col('N docs')))
display(x)

But I keep getting this error.

Error Message:

Analysis Exception: grouping expressions sequence is empty , and ' trxCampaign ' is not an aggregate function . Wrap ' ( ( CAST ( N docs AS DOUBLE ) / CAST ( sum ( N docs ) AS DOUBLE ) ) AS ` perc docs ' ) ' in windowing function ( s ) or wrap ' trxCampaign ' in first () (or first_value) if you don't care which value you get .; Aggregate [trxCampaign #15254 , Nrows#20127L , N docs #20128L, ( cast ( N docs # 20128L as double ) / cast ( sum ( N docs # 20128L ) as double ) ) AS perc docs # 20134 ]

Error Image

enter image description here

Is there a pyspark equivalent of pandas' reset_index() that I should know about?

I know how to calculate the percentage using this method

unique_docs = data.select('DocumentNumber').drop_duplicates().count()
x = data.groupby('trxCampaign').agg(
  sum(lit(1)).alias('Nrows'),
  countDistinct(col('DocumentNumber')).alias('N docs'), 
  (countDistinct(col('DocumentNumber')).alias('N docs')/unique_docs).alias('perc')
)
display(x)

But I don't understand why pyspark wont let me just modify the table with withColumn after aggregating it with groupby.

1 Answers

As mentionned samkart, the problem is that you are mixing agregation level without specifying group by method. I don't understand why you don't exploit the below code that you developed on your own ?

unique_docs = data.select('DocumentNumber').drop_duplicates().count()
x = data.groupby('trxCampaign').agg(
  sum(lit(1)).alias('Nrows'),
  countDistinct(col('DocumentNumber')).alias('N docs'), 
  (countDistinct(col('DocumentNumber')).alias('N docs')/unique_docs).alias('perc')
)
display(x)
Related