Dataframe request with groupBy

Question

I'm a beginner in Spark and I want to calculate the average of number per name.

I have a JSON file with this information

df = spark.read.json("myjson.json")
df.select(avg(df["number"]), df["name"]).groupBy("name").show()

But I'm doing it wrong..

How can I solve my problem?

Thanks a lot

Answer 1

You are probably thinking in terms of regular SQL but spark sql is a bit different.

You'll need to group by field before performing your aggregation. Thus the following, you can write your query as followed :

df.groupBy('name').agg({'number': 'mean'}).show

You can also write it using a SQL dialect by registering the DataFrame as a temp table and then query on it use the SQLContext or HiveContext :

df.registerTempTable("people")
df2 = sqlContext.sql("select name, avg(number) as average from people group by name")

I hope that this answers your question.