Mastering Date Manipulation in PySpark
Time-based data processing is a critical aspect of data engineering, and PySpark provides a rich set of functions to handle date and time efficiently.
1. Extracting Year from a Date Column
Problem: Extract the year from the column event_date.
Solution
from pyspark.sql import SparkSession
from pyspark.sql.functions import year
spark = SparkSession.builder.appName("ExtractYear").getOrCreate()
data = [("2023-04-15",), ("2022-11-30",), ("2021-08-25",)]
df = spark.createDataFrame(data, ["event_date"])
df = df.withColumn("year", year(df.event_date))
df.show()
2. Date Difference Calculation
Problem: Calculate the difference in days between start_date and end_date.
Solution
from pyspark.sql.functions import datediff, to_date
data = [("2023-01-01", "2023-02-01"), ("2023-03-15", "2023-03-20")]
df = spark.createDataFrame(data, ["start_date", "end_date"])
df = df.withColumn("start_date", to_date(df.start_date, "yyyy-MM-dd"))
df = df.withColumn("end_date", to_date(df.end_date, "yyyy-MM-dd"))
df = df.withColumn("date_diff", datediff(df.end_date, df.start_date))
df.show()
3. Filter Records Based on Date
Problem: Filter records where event_date is after 2023-06-01.
Solution
from pyspark.sql.functions import col
data = [("2023-05-15",), ("2023-07-20",), ("2023-06-05",)]
df = spark.createDataFrame(data, ["event_date"])
df_filtered = df.filter(col("event_date") > "2023-06-01")
df_filtered.show()
4. Add Days to a Date
Problem: Add 30 days to order_date.
Solution
from pyspark.sql.functions import date_add
data = [("2023-01-10",), ("2023-06-15",), ("2023-07-30",)]
df = spark.createDataFrame(data, ["order_date"])
df = df.withColumn("new_date", date_add(df.order_date, 30))
df.show()
5. Find the Maximum Date
Problem: Determine the latest date from payment_date.
Solution
from pyspark.sql.functions import max
data = [("2023-02-15",), ("2023-06-25",), ("2023-01-10",)]
df = spark.createDataFrame(data, ["payment_date"])
df_max = df.agg(max("payment_date").alias("max_date"))
df_max.show()
6. Truncate Date to First Day of Month
Problem: Truncate sale_date to the first day of its respective month.
Solution
from pyspark.sql.functions import trunc
data = [("2023-04-12",), ("2023-07-23",), ("2023-08-05",)]
df = spark.createDataFrame(data, ["sale_date"])
df = df.withColumn("first_day_of_month", trunc(df.sale_date, "MM"))
df.show()
7. Group by Year
Problem: Group records by year extracted from transaction_date.
Solution
df = df.withColumn("year", year(df.transaction_date))
df.groupBy("year").count().show()
8. Filter Records Within a Date Range
Problem: Filter records where visit_date is between 2023-01-01 and 2023-05-01.
Solution
df_filtered = df.filter((col("visit_date") >= "2023-01-01") & (col("visit_date") <= "2023-05-01"))
df_filtered.show()
9. Extract Day of the Week
Problem: Extract the day of the week from attendance_date.
Solution
from pyspark.sql.functions import date_format
df = df.withColumn("day_of_week", date_format(df.attendance_date, "EEEE"))
df.show()
10. Check Leap Year
Problem: Identify if birth_date falls in a leap year.
Solution
from pyspark.sql.functions import year
df = df.withColumn("is_leap_year", (year(df.birth_date) % 4 == 0) & ((year(df.birth_date) % 100 != 0) | (year(df.birth_date) % 400 == 0)))
df.show()
11. Convert String to Date Format
Problem: Convert arrival_time from dd-MM-yyyy to yyyy-MM-dd.
Solution
from pyspark.sql.functions import to_date
df = df.withColumn("formatted_date", to_date(df.arrival_time, "dd-MM-yyyy"))
df.show()
12. Calculate Week Number
Problem: For each shipment_date, calculate the week number.
Solution
from pyspark.sql.functions import weekofyear
df = df.withColumn("week_number", weekofyear(df.shipment_date))
df.show()
13. Find Records from the Last 7 Days
Problem: Identify all records where log_date is within the last 7 days.
Solution
from pyspark.sql.functions import current_date
df_filtered = df.filter(col("log_date") >= date_add(current_date(), -7))
df_filtered.show()
14. Format Date as String
Problem: Format booking_date as dd/MM/yyyy.
Solution
df = df.withColumn("formatted_date", date_format(df.booking_date, "dd/MM/yyyy"))
df.show()
15. Find the First and Last Record by Date
Problem: Find the first and last record based on created_at.
Solution
from pyspark.sql.functions import min, max
df_min_max = df.agg(min("created_at").alias("first_record"), max("created_at").alias("last_record"))
df_min_max.show()
16. Difference Between Dates in Months
Problem: Calculate the difference between two dates in months.
Solution
from pyspark.sql.functions import months_between
df = df.withColumn("month_diff", months_between(df.end_month, df.start_month))
df.show()
17. Convert UTC to Local Time
Problem: Convert utc_timestamp from UTC to IST.
Solution
from pyspark.sql.functions import from_utc_timestamp
df = df.withColumn("ist_time", from_utc_timestamp(df.utc_timestamp, "Asia/Kolkata"))
df.show()
18. Find Holidays
Problem: Check if holiday_date is a public holiday.
Solution
pythonCopyEditholiday_list = ["2023-01-01", "2023-12-25"]
df = df.withColumn("is_holiday", col("holiday_date").isin(holiday_list))
df.show()
19. Round Time to Nearest Hour
Problem: Round meeting_time to the nearest hour.
Solution
from pyspark.sql.functions import date_trunc
df = df.withColumn("rounded_time", date_trunc("hour", df.meeting_time))
df.show()
20. Extract Quarter
Problem: Extract the quarter of the year from invoice_date.
Solution
from pyspark.sql.functions import quarter
df = df.withColumn("quarter", quarter(df.invoice_date))
df.show()
