Apache Spark

PySpark – avg()

In Python, PySpark is a Spark module used to provide a similar kind of Processing like spark using DataFrame. avg() in PySpark is used to return the average value from a particular column in the DataFrame. We can get the average in three ways.

  1. Method 1: Using select() method
  2. Method 2: Using agg() method
  3. Method 3: Using groupBy() method

Before that, we have to create PySpark DataFrame for demonstration.

Example:
We will create a dataframe with 5 rows and 6 columns and display it using the show() method.

#import the pyspark module
import pyspark
#import SparkSession for creating a session
from pyspark.sql import SparkSession

#create an app named linuxhint
spark_app = SparkSession.builder.appName('linuxhint').getOrCreate()

# create student data with 5 rows and 6 attributes
students =[
{'rollno':'001','name':'sravan','age':23,
  'height':5.79,'weight':67,'address':'guntur'},
{'rollno':'002','name':'ojaswi','age':16,
  'height':3.79,'weight':34,'address':'hyd'},
{'rollno':'003','name':'gnanesh chowdary','age':7,
  'height':2.79,'weight':17,'address':'patna'},
{'rollno':'004','name':'rohith','age':9,
  'height':3.69,'weight':28,'address':'hyd'},
{'rollno':'005','name':'sridevi','age':37,
  'height':5.59,'weight':54,'address':'hyd'}]

# create the dataframe
df = spark_app.createDataFrame( students)

#display dataframe
df.show()

Output:

Capture.PNG

Method 1: Using select() method

We can get the average value from the column in the dataframe using the select() method. By using the avg() method, we can get the average value from the column. To use this method, we have to import it from pyspark.sql.functions module, and finally, we can use the collect() method to get the average from the column

Syntax:

df.select(avg(‘column_name’))

Where,

  1. df is the input PySpark DataFrame
  2. column_name is the column to get the average value

If we want to return the average value from multiple columns, we have to use the avg() method inside the select() method by specifying the column name separated by a comma.

Syntax:

df.select(avg(‘column_name’), avg (‘column_name’),………., avg (‘column_name’))

Where,

  1. df is the input PySpark DataFrame
  2. column_name is the column to get the average value

Example 1: Single Column

This example will get the average value from the height column in the PySpark dataframe.

#import the pyspark module
import pyspark
#import SparkSession for creating a session
from pyspark.sql import SparkSession
#import the average - avg function
from pyspark.sql.functions import avg

#create an app named linuxhint
spark_app = SparkSession.builder.appName('linuxhint').getOrCreate()

# create student data with 5 rows and 6 attributes
students =[
{'rollno':'001','name':'sravan','age':23,
  'height':5.79,'weight':67,'address':'guntur'},
{'rollno':'002','name':'ojaswi','age':16,
  'height':3.79,'weight':34,'address':'hyd'},
{'rollno':'003','name':'gnanesh chowdary','age':7,
  'height':2.79,'weight':17,'address':'patna'},
{'rollno':'004','name':'rohith','age':9,
  'height':3.69,'weight':28,'address':'hyd'},
{'rollno':'005','name':'sridevi','age':37,
  'height':5.59,'weight':54,'address':'hyd'}]

# create the dataframe
df = spark_app.createDataFrame( students)

#return the average from the height column
df.select(avg('height')).collect()

Output:

[Row(avg(height)=4.33)]

In the above example, the average value from the height column is returned.

Example 2:Multiple Columns

This example will get the average value from the height, age, and weight columns in the PySpark dataframe.

#import the pyspark module
import pyspark
#import SparkSession for creating a session
from pyspark.sql import SparkSession
#import the average function - avg
from pyspark.sql.functions import avg

#create an app named linuxhint
spark_app = SparkSession.builder.appName('linuxhint').getOrCreate()

# create student data with 5 rows and 6 attributes
students =[
{'rollno':'001','name':'sravan','age':23,
  'height':5.79,'weight':67,'address':'guntur'},
{'rollno':'002','name':'ojaswi','age':16,
  'height':3.79,'weight':34,'address':'hyd'},
{'rollno':'003','name':'gnanesh chowdary','age':7,
  'height':2.79,'weight':17,'address':'patna'},
{'rollno':'004','name':'rohith','age':9,
  'height':3.69,'weight':28,'address':'hyd'},
{'rollno':'005','name':'sridevi','age':37,
  'height':5.59,'weight':54,'address':'hyd'}]

# create the dataframe
df = spark_app.createDataFrame( students)

#return the average from the height ,age and weight columns
df.select(avg('height'),avg('age'),avg('weight')).collect()

Output:

[Row(avg(height)=4.33, avg(age)=18.4, avg(weight)=40.0)]

In the above example, the average value from the height, age and weight columns is returned.

Method 2: Using agg() method

We can get the average value from the column in the dataframe using the agg() method. This method is known as aggregation, which groups the values within a column. It will take dictionary as a parameter in that key will be column name and value is the aggregate function, i.e., avg . By using the avg() method, we can get the average value from the column, and finally, we can use the collect() method to get the average from the column.

Syntax:

df.agg({‘column_name’:avg})

Where,

  1. df is the input PySpark DataFrame
  2. column_name is the column to get the average value
  3. avg is an aggregation function used to return the average

If we want to return the average value from multiple columns, we have to specify the column name with the avg function separated by a comma.

Syntax:

df.agg({‘column_name’:avg,‘column_name’:avg,…………………,‘column_name’:avg})

Where,

  1. df is the input PySpark DataFrame
  2. column_name is the column to get the average value
  3. avg is an aggregation function used to return the average

Example 1: Single Column

This example will get the average value from the height column in the PySpark dataframe.

#import the pyspark module
import pyspark
#import SparkSession for creating a session
from pyspark.sql import SparkSession

#create an app named linuxhint
spark_app = SparkSession.builder.appName('linuxhint').getOrCreate()

# create student data with 5 rows and 6 attributes
students =[
{'rollno':'001','name':'sravan','age':23,
  'height':5.79,'weight':67,'address':'guntur'},
{'rollno':'002','name':'ojaswi','age':16,
  'height':3.79,'weight':34,'address':'hyd'},
{'rollno':'003','name':'gnanesh chowdary','age':7,
  'height':2.79,'weight':17,'address':'patna'},
{'rollno':'004','name':'rohith','age':9,
  'height':3.69,'weight':28,'address':'hyd'},
{'rollno':'005','name':'sridevi','age':37,
  'height':5.59,'weight':54,'address':'hyd'}]

# create the dataframe
df = spark_app.createDataFrame( students)

#return the average from the height column
df.agg({'height': 'avg'}).collect()

Output:

[Row(avg(height)=4.33)]

In the above example, the average value from the height column is returned.

Example 2: Multiple Columns

This example will get the average value from the height, age, and weight columns in the PySpark dataframe.

#import the pyspark module
import pyspark
#import SparkSession for creating a session
from pyspark.sql import SparkSession

#create an app named linuxhint
spark_app = SparkSession.builder.appName('linuxhint').getOrCreate()

# create student data with 5 rows and 6 attributes
students =[
{'rollno':'001','name':'sravan','age':23,
  'height':5.79,'weight':67,'address':'guntur'},
{'rollno':'002','name':'ojaswi','age':16,
  'height':3.79,'weight':34,'address':'hyd'},
{'rollno':'003','name':'gnanesh chowdary','age':7,
  'height':2.79,'weight':17,'address':'patna'},
{'rollno':'004','name':'rohith','age':9,
  'height':3.69,'weight':28,'address':'hyd'},
{'rollno':'005','name':'sridevi','age':37,
  'height':5.59,'weight':54,'address':'hyd'}]

# create the dataframe
df = spark_app.createDataFrame( students)

#return the average from the height,age and weight columns
df.agg({'height': 'avg','age': 'avg','weight': 'avg'}).collect()

Output:

[Row(avg(weight)=40.0, avg(age)=18.4, avg(height)=4.33)]

In the above example, the average value from the height, age and weight columns is returned.

Method 3: Using groupBy() method

We can get the average value from the column in the dataframe using the groupBy() method. This method will return the average value by grouping similar values in a column. We have to use avg() function after performing groupBy() function

Syntax:

df.groupBy(group_column).avg(‘column_name’)

Where,

  1. df is the input PySpark DataFrame
  2. group_column is the column where values are grouped based on this column
  3. column_name is the column to get the average value
  4. avg is an aggregation function used to return the average

Example 1:

In this example, we will group the address column with the height column to return the average value based on this address column.

#import the pyspark module
import pyspark
#import SparkSession for creating a session
from pyspark.sql import SparkSession

#create an app named linuxhint
spark_app = SparkSession.builder.appName('linuxhint').getOrCreate()

# create student data with 5 rows and 6 attributes
students =[
{'rollno':'001','name':'sravan','age':23,
  'height':5.79,'weight':67,'address':'guntur'},
{'rollno':'002','name':'ojaswi','age':16,
  'height':3.79,'weight':34,'address':'hyd'},
{'rollno':'003','name':'gnanesh chowdary','age':7,
  'height':2.79,'weight':17,'address':'patna'},
{'rollno':'004','name':'rohith','age':9,
  'height':3.69,'weight':28,'address':'hyd'},
{'rollno':'005','name':'sridevi','age':37,
  'height':5.59,'weight':54,'address':'hyd'}]

# create the dataframe
df = spark_app.createDataFrame( students)

#return the average from the height column grouping with address column
df.groupBy('address').avg('height').collect()

Output:

There are three unique values in the address field – hyd, guntur, and patna. So the average will be formed by grouping the values across the address values.

[Row(address='hyd', avg(height)=4.3566666666666665),
Row(address='guntur', avg(height)=5.79),
Row(address='patna', avg(height)=2.79)]

Example 2:

In this example, we will group the address column with the weight column to return the average value based on this address column.

#import the pyspark module
import pyspark
#import SparkSession for creating a session
from pyspark.sql import SparkSession

#create an app named linuxhint
spark_app = SparkSession.builder.appName('linuxhint').getOrCreate()

# create student data with 5 rows and 6 attributes
students =[
{'rollno':'001','name':'sravan','age':23,
  'height':5.79,'weight':67,'address':'guntur'},
{'rollno':'002','name':'ojaswi','age':16,
  'height':3.79,'weight':34,'address':'hyd'},
{'rollno':'003','name':'gnanesh chowdary','age':7,
  'height':2.79,'weight':17,'address':'patna'},
{'rollno':'004','name':'rohith','age':9,
  'height':3.69,'weight':28,'address':'hyd'},
{'rollno':'005','name':'sridevi','age':37,
  'height':5.59,'weight':54,'address':'hyd'}]

# create the dataframe
df = spark_app.createDataFrame( students)

#return the average from the weight column grouping with address column
df.groupBy('address').avg('weight').collect()

Output:

There are three unique values in the address field – hyd, guntur, and patna. So the average will be formed by grouping the values across the address values.

[Row(address='hyd', avg(weight)=38.666666666666664),
Row(address='guntur', avg(weight)=67.0),
Row(address='patna', avg(weight)=17.0)]

Conclusion:

We discussed how to get the average value from the PySpark DataFrame using the select() and agg() methods. To get the average value by grouping with other columns, we used the groupBy along with the avg() function.

About the author

Gottumukkala Sravan Kumar

B tech-hon's in Information Technology; Known programming languages - Python, R , PHP MySQL; Published 500+ articles on computer science domain