23

I would like to calculate avg and count in a single group by statement in Pyspark. How can I do that?

df = spark.createDataFrame([(1, 'John', 1.79, 28,'M', 'Doctor'),
                        (2, 'Steve', 1.78, 45,'M', None),
                        (3, 'Emma', 1.75, None, None, None),
                        (4, 'Ashley',1.6, 33,'F', 'Analyst'),
                        (5, 'Olivia', 1.8, 54,'F', 'Teacher'),
                        (6, 'Hannah', 1.82, None, 'F', None),
                        (7, 'William', 1.7, 42,'M', 'Engineer'),
                        (None,None,None,None,None,None),
                        (8,'Ethan',1.55,38,'M','Doctor'),
                        (9,'Hannah',1.65,None,'F','Doctor')]
                       , ['Id', 'Name', 'Height', 'Age', 'Gender', 'Profession'])

#This only shows avg but also I need count right next to it. How can I do that?

df.groupBy("Profession").agg({"Age":"avg"}).show()
df.show()

Thank you.

melik
  • 1,268
  • 3
  • 21
  • 42

1 Answers1

59

For the same column:

from pyspark.sql import functions as F
df.groupBy("Profession").agg(F.mean('Age'), F.count('Age')).show()

If you're able to use different columns:

df.groupBy("Profession").agg({'Age':'avg', 'Gender':'count'}).show()
Pierre Gourseaud
  • 2,347
  • 13
  • 24