PySpark GroupBy Guide: Super Simple Way to Group Data

Do you struggle effectively managing big datasets? Are you bored with rigid, slow approaches to organizing data?

This post will discuss PySpark's GroupBy capabilities and how they could transform your data processing chores.

What is Data Grouping?

The next step in data analytics is data grouping. We could sort the data bits into many different groups, or we could control the data based on certain rules.

With this method, you can combine data, which makes it easier to find trends, patterns, and outliers in a dataset.

For instance:

• Month-based grouping of sales statistics (Knowledge of seasonal tendencies.)
• Grouping information according to area. (Showing variations in revealing performance in several spheres.)

Many disciplines, including:?

• Finance
• Marketing
• Sociology and related fields

What is PySpark GroupBy functionality?

PySpark GroupBy is a useful tool often used to group data and do different things on each group as needed.?

People who work with data can use this method to combine one or more columns and use one or more aggregation operations on a DataFrame, such as sum, average, count, min, max, and so on.

In this question, Meta is asked to calculate the total (i.e., cumulative sum) energy consumption of the Meta/Facebook data centers in all three continents by the date.

Link to this question: https://platform.stratascratch.com/coding/10084-cum-sum-energy-consumption

Steps to Solve:

1. Merge data frames.
2. Organize by Date
3. Calculate the cumulative sum.
4. Calculate the consumption in terms of a percentage of the whole cumulative.
5. Handling Unfiltered Data

Here is the code.

import pandas as pd
import numpy as np
from pyspark.sql import functions as F
from pyspark.sql import Window

merged_df = fb_eu_energy.union(fb_na_energy).union(fb_asia_energy)

df = merged_df.groupby('date').agg(F.sum('consumption').alias('consumption')).orderBy('date')
df = df.withColumn('cumulative_total_consumption', F.sum('consumption').over(Window.orderBy('date')))
df = df.withColumn('percentage_of_total_consumption', F.round((F.col('cumulative_total_consumption') / F.sum('consumption').over(Window.partitionBy())).cast('double') * 100))

df = df.drop('consumption')
df = df.withColumn('date', F.date_format(F.col('date'), 'yyyy-MM-dd'))

result = df.toPandas()
result        

Here is the output

To learn more about PySpark, check out this: What is PySpark?

How does PySpark GroupBy work, and its advantages over traditional grouping methods?

Standard methods may take a long time to work with large amounts of data and may run out of memory. Distributed computing is used by PySpark. This is a much faster and better way to handle huge amounts of data.

In this question, we are asked to find all wines from the winemag_p2 dataset produced in the country with the highest sum of points in the winemag_p1 dataset.

Link to this question: https://platform.stratascratch.com/coding/10040-find-all-wines-from-the-winemag_p2-dataset-which-are-produced-in-countries-that-have-the-highest-sum-of-points-in-the-winemag_p1-dataset

Steps to Solve:

1. Remove every non-null value.
2. Collect and Group
3. Highest national points for a certain country
4. Combine Datasets

Here is the code.

import pyspark.sql.functions as F

country = winemag_p1.filter(F.col('country').isNotNull())

high_point = country.groupBy('country').agg(F.sum('points').alias('sum')).orderBy(F.desc('sum')).limit(1).select('country')

result = winemag_p2.join(high_point, 'country', 'inner')

result.toPandas()        

Here is the output’s first two rows;

The final DataFrame will show wines from the nation with the most points, therefore highlighting PySpark's GroupBy ability to manage challenging aggregation chores.

With PySpark's GroupBy tools, we can manage far more vast datasets than conventional approaches and more effectively complete difficult data aggregation chores.

How do you perform data grouping using PySpark GroupBy?

Simply put, Pyspark Groupyby lets you summarize or control data in a massive dataset environment where specific criteria are important.

This question asks us to identify the date from the Meta/Facebook data centers with the greatest total energy consumption and then output the date with the overall energy consumption across all data centers.

Link to this question: https://platform.stratascratch.com/coding/10064-highest-energy-consumption

Steps to Solve:

1. Union DataFrames
2. Group by Date
3. Find Maximum Consumption Date
4. Filter and Select Results

Here is the code.

import pyspark.sql.functions as F

df = fb_eu_energy.union(fb_asia_energy).union(fb_na_energy)
consumption = df.groupBy('date').agg(F.sum('consumption').alias('total_consumption'))
result = consumption.filter(consumption['total_consumption'] == consumption.select(F.max('total_consumption')).collect()[0][0]).select('date', 'total_consumption')
result.toPandas()        

Here is the output.

The resulting DataFrame will display the date with the highest total energy consumption and the corresponding total consumption value. This example demonstrates how to use PySpark's GroupBy functionality to efficiently perform data grouping and aggregation.

Real-World Examples

Data grouping and aggregation are common tasks for data analysis in real-world scenarios. PySpark's GroupBy functionality provides an efficient way to handle these operations on large datasets. Here, we will look at a specific example from the Meta/Facebook data centers.

In this question, we are asked to calculate the total energy consumption of the Meta data centers in all three continents by the date.

Link to this question: https://platform.stratascratch.com/coding/10084-cum-sum-energy-consumption/

Steps to Solve:

1. Union DataFrames
2. Group by Date.
3. Calculate Cumulative Sum
4. Calculate Percentage
5. Clean Data

Here is the code.

import pandas as pd
import numpy as np
from pyspark.sql import functions as F
from pyspark.sql import Window

merged_df = fb_eu_energy.union(fb_na_energy).union(fb_asia_energy)

df = merged_df.groupBy('date').agg(F.sum('consumption').alias('consumption')).orderBy('date')

df = df.withColumn('cumulative_total_consumption', F.sum('consumption').over(Window.orderBy('date')))

df = df.withColumn('percentage_of_total_consumption', F.round((F.col('cumulative_total_consumption') / F.sum('consumption').over(Window.partitionBy())).cast('double') * 100))

df = df.drop('consumption')
df = df.withColumn('date', F.date_format(F.col('date'), 'yyyy-MM-dd'))

result = df.toPandas()
result        

Here is the output.

The final DataFrame will have the totals of all the energy data points and the percentage of total consumption for each date. This shows how powerful PySpark's GroupBy feature is for quickly and easily combining large amounts of data.

Common grouping operations such as aggregation, filtering, and sorting

Aggregation, filtering, and sorting are all common grouping operations that are used in data analysis to summarize and get insights from big datasets. The GroupBy feature in PySpark makes these tasks easier, which makes working with big data easier.

Aggregation

This question asks us to find out how many checks happen in the 94102 postal code area each January, May, or November

Link to this question: https://platform.stratascratch.com/coding/9734-number-of-inspections-by-zip

Steps to Solve:

1. Filter by month and postal code
2. Sort by month and year
3. Count the inspections
4. Pivot and Aggregate

Here is the code.

import pyspark.sql.functions as F

result = sf_restaurant_health_violations \
    .where((F.col('business_postal_code') == 94102) & (F.month('inspection_date').isin(1, 5, 11))) \
    .groupBy(F.year('inspection_date').alias('year'), F.month('inspection_date').alias('mm')) \
    .agg(F.count('*').alias('cnt')) \
    .groupBy('year') \
    .pivot('mm') \
    .agg(F.sum('cnt')) \
    .fillna(0) \
    .toPandas()

result        

Here is the output

With a different column for each month, the resulting DataFrame will show how many inspections were done each year in January, May, and November. This shows how to use PySpark's GroupBy feature to do complex aggregation and pivot actions quickly.

Filtering

In this question, we need to find out the user ID, language, and location of all Nexus 5 control group users in Italy who don't speak Italian.

Link to this question: https://platform.stratascratch.com/coding/9609-find-nexus5-control-group-users-in-italy-who-dont-speak-italian

Steps to Solve:

1. Filter by Group, Location, and Language
2. Join DataFrames
3. Sort Results

Let’s see the code.

playbook_experiments = playbook_experiments.filter((playbook_experiments.device == 'nexus 5') & (playbook_experiments.experiment_group == 'control_group') & (playbook_experiments.location == 'Italy'))
playbook_users = playbook_users.filter((playbook_users.language != 'Italian') & (playbook_users.language != 'italian'))
merged_df = playbook_experiments.join(playbook_users, on='user_id', how='inner')
result = merged_df.sort('occurred_at').select('user_id', 'language', 'location').toPandas()
result        

Here is the output

Sorting?

In this question, we are asked to arrange a column of random IDs based on their second character in ascending alphabetical order

Link to this question: https://platform.stratascratch.com/coding/2166-sorting-by-second-character

Steps to Solve:

1. Extract Second Character
2. Sort by Second Character

Here is the code.

import pyspark.sql.functions as F

random_id = random_id.withColumn("second", F.substring(random_id["id"], 2, 1))
result = random_id.orderBy("second").drop("second")
result.toPandas()        

Here is the output.

The resulting DataFrame will display the IDs sorted by the second character of each ID, demonstrating PySpark's ability to handle sorting operations efficiently.

Best Practices for Efficient Data Grouping

It's important to group data efficiently if you want to get the most out of your computer and make sure that data analysis jobs go smoothly, especially when you're working with big datasets. Here are some of the best ways to use PySpark to efficiently group data:

1. Use the Right Types of Data:
2. Sort data early:
3. Improve how memory is used:
4. Use of leverage Built-in Functions:
5. Avoid Shuffling Data Unnecessarily:

Of course, doing practice is the best way. Check PySpark Interview Questions to do this practice you need.

Conclusion

In this article, we explored the efficient data grouping capabilities of PySpark's GroupBy functionality. We covered data grouping basics, demonstrated how to use PySpark for grouping and aggregating data, and highlighted the advantages of PySpark over traditional methods.

To master these techniques, practice and apply them using our platform. The platform offers a vast collection of coding questions and datasets, providing hands-on experience to tackle complex data problems confidently and efficiently.

Start practicing today to refine your data analysis skills!