Скачать или смотреть How to Join Two DataFrames on Multiple Conditions in PySpark

Learn how to effectively join two DataFrames in PySpark with multiple conditions and identify "no shows" for appointments.
---
This video is based on the question https://stackoverflow.com/q/66933858/ asked by the user 'ritzen101' ( https://stackoverflow.com/u/15545542/ ) and on the answer https://stackoverflow.com/a/66934417/ provided by the user 'mck' ( https://stackoverflow.com/u/14165730/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Join two dataframes on multiple conditions pyspark

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Join Two DataFrames on Multiple Conditions in PySpark

Joining DataFrames in PySpark can seem daunting, especially when you have to account for various conditions. In this guide, we’ll explore how you can join two DataFrames to identify the "no shows" in test appointments based on specific criteria. If you have two tables, one for appointment bookings and another for actual test attendance, and you want to see who missed their tests, you're in the right place!

Problem Statement

You have two DataFrames:

testAppointment - This table contains records of individuals who have booked appointments for tests.

actualTests - This table includes records of tests that individuals actually attended.

Your goal is to join these two DataFrames such that the resulting table includes a new column, NoShows, which indicates whether each individual had a scheduled appointment but did not show up for their test.

Example DataFrames

Let's take a look at the data:

testAppointment Table:

personIdtestDatex2021-02-12y2021-03-18x2020-11-01z2020-09-10y2021-01-08z2020-12-24actualTests Table:

personIdActualtestDatex2021-02-12y2021-03-18x2020-11-01z2020-09-10In this example, person y (2021-01-08) and person z (2020-12-24) are the ones who did not show up for their respective tests since there are no entries for them in the actualTests table.

Solution Approach

To solve this problem, we will perform a left join on the two DataFrames, using the personId and respective dates as join conditions. After the join operation, we will create a new column that indicates whether the corresponding ActualtestDate is null—this will help us identify no-shows.

Step-by-Step Implementation

Import Necessary Libraries
Ensure you have PySpark set up and import the required classes:

[[See Video to Reveal this Text or Code Snippet]]

Create a Spark Session
Initialize your Spark session:

[[See Video to Reveal this Text or Code Snippet]]

Load the DataFrames
Load the two DataFrames (for the sake of example, let's assume they're already defined as testappointment and test):

[[See Video to Reveal this Text or Code Snippet]]

Perform the Left Join
Join the two DataFrames on two conditions: date and personId:

[[See Video to Reveal this Text or Code Snippet]]

View the Result
Finally, output the resulting DataFrame to see which individuals had no-shows:

[[See Video to Reveal this Text or Code Snippet]]

Result

The output will be:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

In this guide, we explored how to join two DataFrames in PySpark on multiple conditions to find out which appointments had no shows. This approach is particularly useful in practical scenarios like health management where tracking missed appointments can provide significant insights.

Consider utilizing this pattern to handle your own DataFrame joining tasks efficiently in PySpark!