SparkSQL on pyspark: how to generate time series?

by

EDIT
This creates a dataframe with one row containing an array of consecutive dates:

from pyspark.sql.functions import sequence, to_date, explode, col

spark.sql("SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval 1 month) as date")

+------------------------------------------+
|                  date                    |
+------------------------------------------+
| ["2018-01-01","2018-02-01","2018-03-01"] |
+------------------------------------------+

You can use the explode function to “pivot” this array into rows:

spark.sql("SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval 1 month) as date").withColumn("date", explode(col("date"))

+------------+
|    date    |
+------------+
| 2018-01-01 |
| 2018-02-01 |
| 2018-03-01 |
+------------+

(End of edit)

Spark v2.4 support sequence function:

sequence(start, stop, step) – Generates an array of elements from start to stop (inclusive), incrementing by step. The type of the returned elements is the same as the type of argument expressions.

Supported types are: byte, short, integer, long, date, timestamp.

Examples:

SELECT sequence(1, 5);

[1,2,3,4,5]

SELECT sequence(5, 1);

[5,4,3,2,1]

SELECT sequence(to_date(‘2018-01-01’), to_date(‘2018-03-01’), interval 1 month);

[2018-01-01,2018-02-01,2018-03-01]

https://docs.databricks.com/spark/latest/spark-sql/language-manual/functions.html#sequence

More Related Contents:

  1. How to run independent transformations in parallel using PySpark?
  2. Pyspark filter dataframe by columns of another dataframe
  3. PySpark: how to resample frequencies
  4. %d format: a number is required not list
  5. How to melt Spark DataFrame?
  6. Count number of non-NaN entries in each column of Spark dataframe with Pyspark
  7. Set scientific notation with fixed exponent and significant digits for multiple subplots
  8. How to check if spark dataframe is empty?
  9. cv2.imread always returns NoneType
  10. Spark DataFrame: Computing row-wise mean (or any aggregate operation)
  11. unknown error: call function result missing ‘value’ for Selenium Send Keys even after chromedriver upgrade
  12. PySpark converting a column of type ‘map’ to multiple columns in a dataframe
  13. Pip does not work after upgrade to ubuntu-16.10
  14. Best way to get the max value in a Spark dataframe column
  15. How to loop through each row of dataFrame in pyspark
  16. Reshaping/Pivoting data in Spark RDD and/or Spark DataFrames
  17. ImportError when importing certain modules from SciPY
  18. Cannot find col function in pyspark
  19. AWS python lambda function:No module named requests
  20. How to pass a constant value to Python UDF?
  21. How to zip two array columns in Spark SQL
  22. How does python’s time.time() method work?
  23. How can I access python variable in Spark SQL?
  24. PySpark: How to fillna values in dataframe for specific columns?
  25. How to add suffix and prefix to all columns in python/pyspark dataframe
  26. How do I get Python libraries in pyspark?
  27. Horizontal scrolling won’t activate for ttk Treeview widget
  28. How to fit a 2D ellipse to given points
  29. Pyspark changing type of column from date to string
  30. PySpark: compute row maximum of the subset of columns and add to an exisiting dataframe

Leave a Comment