CODEWITHSUNDEEP
apache-sparkdataframepysparkapache-spark-sql
Rudy
Rudy

Reputation: 19

How to filter date data in spark dataframes?

SO I have the following dataset with date format Month Day, Year..

df = spark.read.format('csv').options(header = 'true').load("D:\\datasets\\googleplaystore.csv")


df.select('App', 'Last Updated').show()

I get the output 

+--------------------+------------------+
|                 App|      Last Updated|
+--------------------+------------------+
|Photo Editor & Ca...|   January 7, 2018|
| Coloring book moana|  January 15, 2018|
|U Launcher Lite –...|    August 1, 2018|
|Sketch - Draw & P...|      June 8, 2018|
|Pixel Draw - Numb...|     June 20, 2018|
|Paper flowers ins...|    March 26, 2017|
|Smoke Effect Phot...|    April 26, 2018|
|    Infinite Painter|     June 14, 2018|
|Garden Coloring Book|September 20, 2017|
|Kids Paint Free -...|      July 3, 2018|
|Text on Photo - F...|  October 27, 2017|
|Name Art Photo Ed...|     July 31, 2018|
|Tattoo Name On My...|     April 2, 2018|
|Mandala Coloring ...|     June 26, 2018|
|3D Color Pixel by...|    August 3, 2018|
|Learn To Draw Kaw...|      June 6, 2018|

When I try to conver this date to a particular format say "yyyyMMdd"

df.select('App', date_format(('Last Updated'), "yyyyMMdd").alias("date")).show()

I get 

+--------------------+----+
|                 App|date|
+--------------------+----+
|Photo Editor & Ca...|null|
| Coloring book moana|null|
|U Launcher Lite –...|null|
|Sketch - Draw & P...|null|
|Pixel Draw - Numb...|null|
|Paper flowers ins...|null|
|Smoke Effect Phot...|null|
|    Infinite Painter|null|
|Garden Coloring Book|null|
|Kids Paint Free -...|null|
|Text on Photo - F...|null|
|Name Art Photo Ed...|null|
|Tattoo Name On My...|null|
|Mandala Coloring ...|null|
|3D Color Pixel by...|null|
|Learn To Draw Kaw...|null|
|Photo Designer - ...|null|
|350 Diy Room Deco...|null|

Not sure where I'm going wrong. Please do help.

I'm also wondering how I can filter using dates. I know I'm supposed to use lit(), lt, gt.. but I'm not sure of the correct syntax for this dataset.

Any help would be appericiated.

thanks

Upvotes: 0

Views: 8775

Answers (2)

Rakesh Kumar
Rakesh Kumar

Reputation: 4420

Here is complete solution for both points: -

First problem is date parsing -

date_format accepts date column and format it into any combination. But here Last Updated is a string column. To convert string in date it requires to_date. Check below I parsed string to date.

data = sqlContext.createDataFrame([
    ["Photo Editor & Ca...", "   January 7, 2018"],
    [" Coloring book moana", "  January 15, 2018"],
    ["U Launcher Lite –...", "    August 1, 2018"],
    ["ketch - Draw & P...", "      June 8, 2018"],
    ["Pixel Draw - Numb...", "     June 20, 2018"],
    ["Paper flowers ins...", "    March 26, 2017"],
    ["moke Effect Phot...", "    April 26, 2018"],
    ["    Infinite Painter", "     June 14, 2018"],
    ["Garden Coloring Book", "September 20, 2017"],
    ["Kids Paint Free -...", "      July 3, 2018"],
    ["Text on Photo - F...", "  October 27, 2017"],
    ["Name Art Photo Ed...", "     July 31, 2018"],
    ["Tattoo Name On My...", "     April 2, 2018"],
    ["Mandala Coloring ...", "     June 26, 2018"],
    ["3D Color Pixel by...", "    August 3, 2018"],
    ["Learn To Draw Kaw...", "      June 6, 2018"]
], ["app", "Last Updated"])
from pyspark.sql import functions as F 
parsed_date_data = data.withColumn(
    "date",
    F.to_date(
        F.trim(F.col("Last Updated")),
        "MMMM dd, yyyy"
    )
)
parsed_date_data.show()
+--------------------+------------------+----------+
|                 app|      Last Updated|      date|
+--------------------+------------------+----------+
|Photo Editor & Ca...|   January 7, 2018|2018-01-07|
| Coloring book moana|  January 15, 2018|2018-01-15|
|U Launcher Lite â...|    August 1, 2018|2018-08-01|
| ketch - Draw & P...|      June 8, 2018|2018-06-08|
|Pixel Draw - Numb...|     June 20, 2018|2018-06-20|
|Paper flowers ins...|    March 26, 2017|2017-03-26|
| moke Effect Phot...|    April 26, 2018|2018-04-26|
|    Infinite Painter|     June 14, 2018|2018-06-14|
|Garden Coloring Book|September 20, 2017|2017-09-20|
|Kids Paint Free -...|      July 3, 2018|2018-07-03|
|Text on Photo - F...|  October 27, 2017|2017-10-27|
|Name Art Photo Ed...|     July 31, 2018|2018-07-31|
|Tattoo Name On My...|     April 2, 2018|2018-04-02|
|Mandala Coloring ...|     June 26, 2018|2018-06-26|
|3D Color Pixel by...|    August 3, 2018|2018-08-03|
|Learn To Draw Kaw...|      June 6, 2018|2018-06-06|
+--------------------+------------------+----------+

Second How we can apply the filter to data frame -

parsed_date_data.where("date = '2018-01-07'").show()
+--------------------+------------------+----------+
|                 app|      Last Updated|      date|
+--------------------+------------------+----------+
|Photo Editor & Ca...|   January 7, 2018|2018-01-07|
+--------------------+------------------+----------+

parsed_date_data.filter("date = '2018-01-07'").show()
+--------------------+------------------+----------+
|                 app|      Last Updated|      date|
+--------------------+------------------+----------+
|Photo Editor & Ca...|   January 7, 2018|2018-01-07|
+--------------------+------------------+----------+

parsed_date_data.where(F.col("date") == '2018-01-07').show()
+--------------------+------------------+----------+
|                 app|      Last Updated|      date|
+--------------------+------------------+----------+
|Photo Editor & Ca...|   January 7, 2018|2018-01-07|
+--------------------+------------------+----------+

parsed_date_data.filter(F.col("date") == '2018-01-07').show()
+--------------------+------------------+----------+
|                 app|      Last Updated|      date|
+--------------------+------------------+----------+
|Photo Editor & Ca...|   January 7, 2018|2018-01-07|
+--------------------+------------------+----------+

parsed_date_data.filter(parsed_date_data.date == '2018-01-07').show()
+--------------------+------------------+----------+
|                 app|      Last Updated|      date|
+--------------------+------------------+----------+
|Photo Editor & Ca...|   January 7, 2018|2018-01-07|
+--------------------+------------------+----------+


parsed_date_data.where(parsed_date_data.date == '2018-01-07').show()
+--------------------+------------------+----------+
|                 app|      Last Updated|      date|
+--------------------+------------------+----------+
|Photo Editor & Ca...|   January 7, 2018|2018-01-07|
+--------------------+------------------+----------+

parsed_date_data.where(parsed_date_data.date.isin('2018-01-07')).show()
+--------------------+------------------+----------+
|                 app|      Last Updated|      date|
+--------------------+------------------+----------+
|Photo Editor & Ca...|   January 7, 2018|2018-01-07|
+--------------------+------------------+----------+

parsed_date_data.filter(parsed_date_data.date.isin('2018-01-07')).show()
+--------------------+------------------+----------+
|                 app|      Last Updated|      date|
+--------------------+------------------+----------+
|Photo Editor & Ca...|   January 7, 2018|2018-01-07|
+--------------------+------------------+----------+

Even though you can apply sub filters -

parsed_date_data.filter(F.month(parsed_date_data.date) == '08').show()
+--------------------+------------------+----------+
|                 app|      Last Updated|      date|
+--------------------+------------------+----------+
|U Launcher Lite â...|    August 1, 2018|2018-08-01|
|3D Color Pixel by...|    August 3, 2018|2018-08-03|
+--------------------+------------------+----------+

Here is complete API to understand pyspark functions.

Upvotes: 1

Vijay Krishna
Vijay Krishna

Reputation: 1067

The Issue you are getting null is because date_format expects current_date().

So you first need to convert your January 7, 2018 to date type using function to_date. 

scala> val df1 = df.withColumn("date format",to_date($"Last Updated","MMMMMM dd, yyyy"))
df1: org.apache.spark.sql.DataFrame = [App: string, Last Updated: string ... 1 more field]

scala> df1.show()
+-----------------+---------------+-----------+
|              App|   Last Updated|date format|
+-----------------+---------------+-----------+
|Photo Editor & Ca|January 7, 2018| 2018-01-07|
+-----------------+---------------+-----------+

Then Apply date_format. 

scala> val df2 = df1.withColumn("date",date_format($"date format","yyyyMMdd"))
df2: org.apache.spark.sql.DataFrame = [App: string, Last Updated: string ... 2 more fields]

scala> df2.show()
+-----------------+---------------+-----------+--------+
|              App|   Last Updated|date format|    date|
+-----------------+---------------+-----------+--------+
|Photo Editor & Ca|January 7, 2018| 2018-01-07|20180107|
+-----------------+---------------+-----------+--------+

Ref:

https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/2458071/Date+Functions+and+Properties+Spark+SQL

Upvotes: 0

Related Questions