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