Menu
I have a data frame in Pyspark. In this data frame I have a column which is of timestamp data type. Now I want to add extra 2 hours for each row of the timestamp column without creating any new columns.
For Example: This is sample data
df
id testing_time test_name
1 2017-03-12 03:19:58 Raising
2 2017-03-12 03:21:30 sleeping
3 2017-03-12 03:29:40 walking
4 2017-03-12 03:31:23 talking
5 2017-03-12 04:19:47 eating
6 2017-03-12 04:33:51 working
I want to have something like below.
df1
id testing_time test_name
1 2017-03-12 05:19:58 Raising
2 2017-03-12 05:21:30 sleeping
3 2017-03-12 05:29:40 walking
4 2017-03-12 05:31:23 talking
5 2017-03-12 06:19:47 eating
6 2017-03-12 06:33:51 working
How can I do that?
743
The current timestamp can be added as a new column to spark Dataframe using the current_timestamp() function of the sql module in pyspark. The method returns the timestamp in the yyyy-mm-dd hh:mm:ss.
Using PySpark SQL functions datediff() , months_between() you can calculate the difference between two dates in days, months, and year, let's see this by using a DataFrame example. You can also use these to calculate age.
Solution: Spark functions provides hour() , minute() and second() functions to extract hour, minute and second from Timestamp column respectively. hour – function hour() extracts hour unit from Timestamp column or string column containing a timestamp.
Timestamp difference in PySpark can be calculated by using 1) unix_timestamp() to get the Time in seconds and subtract with other time to get the seconds 2) Cast TimestampType column to LongType and subtract two long values to get the difference in seconds, divide it by 60 to get the minute difference and finally ...
One approach, that doesn't require explicit casting and uses Spark interval literals (with arguable readability advantages):
df = df.withColumn('testing_time', df.testing_time + F.expr('INTERVAL 2 HOURS'))
df.show()
+---+-------------------+---------+
| id| testing_time|test_name|
+---+-------------------+---------+
| 1|2017-03-12 05:19:58| Raising|
| 2|2017-03-12 05:21:30| sleeping|
| 3|2017-03-12 05:29:40| walking|
| 4|2017-03-12 05:31:23| talking|
| 5|2017-03-12 06:19:47| eating|
| 6|2017-03-12 06:33:51| working|
+---+-------------------+---------+
Or, in full:
import pyspark.sql.functions as F
from datetime import datetime
data = [
(1, datetime(2017, 3, 12, 3, 19, 58), 'Raising'),
(2, datetime(2017, 3, 12, 3, 21, 30), 'sleeping'),
(3, datetime(2017, 3, 12, 3, 29, 40), 'walking'),
(4, datetime(2017, 3, 12, 3, 31, 23), 'talking'),
(5, datetime(2017, 3, 12, 4, 19, 47), 'eating'),
(6, datetime(2017, 3, 12, 4, 33, 51), 'working'),
]
df = sqlContext.createDataFrame(data, ['id', 'testing_time', 'test_name'])
df = df.withColumn('testing_time', df.testing_time + F.expr('INTERVAL 2 HOURS'))
df.show()
+---+-------------------+---------+
| id| testing_time|test_name|
+---+-------------------+---------+
| 1|2017-03-12 05:19:58| Raising|
| 2|2017-03-12 05:21:30| sleeping|
| 3|2017-03-12 05:29:40| walking|
| 4|2017-03-12 05:31:23| talking|
| 5|2017-03-12 06:19:47| eating|
| 6|2017-03-12 06:33:51| working|
+---+-------------------+---------+
You can convert testing_time column to bigint in seconds using unix_timestamp function, add 2 hours (7200 s) and then cast the result back to timestamp:
import pyspark.sql.functions as F
df.withColumn("testing_time", (F.unix_timestamp("testing_time") + 7200).cast('timestamp')).show()
+---+-------------------+---------+
| id| testing_time|test_name|
+---+-------------------+---------+
| 1|2017-03-12 05:19:58| Raising|
| 2|2017-03-12 05:21:30| sleeping|
| 3|2017-03-12 05:29:40| walking|
| 4|2017-03-12 05:31:23| talking|
| 5|2017-03-12 06:19:47| eating|
| 6|2017-03-12 06:33:51| working|
+---+-------------------+---------+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
