Pyspark dataframe SQL

Question

I want to convert the following statement into DF select statement:

Select 
 YY,
 PP,
 YYYY,
 PPPP,
 Min(ID) as MinId, 
 Max(ID) as MaxID 
from LoadTable

I have tried the following but it doesnt seem to be working :

df.select(df.ID,df.YY, df.PP,df.YYYY,df.PPPPP).agg({"ID": "max", "ID": "min"}).toPandas().to_csv(outputFile, sep="|", header=True, index=False)

Did you check each component of the statement? Can you provide the errors you see. You see a bunch of things that you need to consider before posting on here. — Chinny84
– Chinny84, Commented Oct 21, 2016 at 18:42

Denny Lee · Accepted Answer · 2016-10-22 04:25:44Z

2

As you are performing an aggregate function, what you may be missing here is the GROUP BY statement. If so, your SQL statement would be:

SELECT YY, PP, YYYY, PPPP, Min(ID) as MinId, Max(ID) as MaxID 
  FROM LoadTable 
 GROUP BY YY, PP, YYYY, PPPP

The corresponding PySpark DataFrame statement would be then

from pyspark.sql import functions as F
df.groupBy(df.YY, df.PP, df.YYYY, df.PPPP).agg(F.min(df.ID), F.max(df.ID))

HTH!

answered Oct 22, 2016 at 4:25

Denny Lee

3,2641 gold badge22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1