Objective

You are working as a Materials Engineer and you are required to process two sets of data related to various experiments conducted in your lab. You need to write a function that performs a join operation between your experiments log and your materials catalog.

Task

Join df_experiments and df_materials to bring more context to the experiment results. Specifically, you want to join on the material_id field that is common between the two DataFrames.

The join operation should be such that all records from both the df_experiments and df_materials DataFrames are included, regardless if they have a match in the other DataFrame (a full outer join).

Save your resulting DataFrame as result_df. Ensure the output matches the exact schema order requested, and order the final output by material_id in ascending order, then by experiment_id in ascending order.

File Path

• Experiments Dataset: /home/interview/experiments.csv
• Materials Dataset: /home/interview/materials.csv
• Starter script: /home/interview/materials_join.py

Schema

experiments.csv

materials.csv

Expected Output Schema

Example

Given this sample input:

df_experiments

| experiment_id | material_id | experiment_date | experiment_results |
|---------------+-------------+-----------------+--------------------|
| 1 | 101 | 2023-07-01 | 7.6 |
| 2 | 102 | 2023-07-02 | 8.3 |
| 3 | 103 | 2023-07-03 | 6.9 |
| 4 | 101 | 2023-07-04 | 7.2 |

df_materials

| material_id | material_name | material_type |
|-------------|---------------+---------------|
| 101 | Material A | Type X |
| 102 | Material B | Type Y |
| 104 | Material C | Type Z |

The expected output would be:

| experiment_id | material_id | material_name | material_type | experiment_date | experiment_results |
|---------------+-------------+---------------+---------------+-----------------+--------------------|
| 1 | 101 | Material A | Type X | 2023-07-01 | 7.6 |
| 4 | 101 | Material A | Type X | 2023-07-04 | 7.2 |
| 2 | 102 | Material B | Type Y | 2023-07-02 | 8.3 |
| 3 | 103 | null | null | 2023-07-03 | 6.9 |
| null | 104 | Material C | Type Z | null | null |

(Note: Material 103 has no name metadata, and Material 104 has no experiment results. Both are retained in the final output).

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("PrepareshSpark").getOrCreate()
spark.sparkContext.setLogLevel("ERROR")

df_experiments = spark.read.csv("/home/interview/experiments.csv", header=True, inferSchema=True)
df_materials = spark.read.csv("/home/interview/materials.csv", header=True, inferSchema=True)

# Step 1: Perform a full outer join on the shared material_id column
joined_df = df_experiments.join(df_materials, on="material_id", how="outer")

# Step 2: Format the final schema in the exact requested order and sort
result_df = joined_df.select(
    "experiment_id", 
    "material_id", 
    "material_name", 
    "material_type", 
    "experiment_date", 
    "experiment_results"
).orderBy("material_id", "experiment_id")

# --- Do not edit below this line ---
result_df.coalesce(1).write.csv("/home/interview/output", header=True, mode="overwrite")
spark.stop()

Explanation

Step 1: Full Outer Join

joined_df = df_experiments.join(df_materials, on="material_id", how="outer")

To combine the DataFrames without losing any information, we must perform a full outer join (specified in PySpark by using how="outer").

• An inner join would drop Material 103 (no metadata) and Material 104 (no experiments).
• A left or right join would only save one of them.
• By using an outer join, PySpark keeps every single row from both tables, filling in the missing gaps with null values.

Step 2: Output Formatting and Sorting

result_df = joined_df.select(
    "experiment_id", "material_id", "material_name", 
    "material_type", "experiment_date", "experiment_results"
).orderBy("material_id", "experiment_id")

Because .join() can arrange columns unpredictably based on its internal execution plan, we chain a .select() block at the end to explicitly arrange the columns so they match the exact sequence requested by the Expected Output Schema. Finally, we append .orderBy("material_id", "experiment_id") to deterministically sort the results, handling ties gracefully.