Pyspark: how to convert hours with decimal to hh:mm

user_13355467 0
Viewed 591

I have the following sample dataframe that has object ids and total hours. the decimal values are minutes converted into a fraction of an hour.

# +----+----+--------+
# |col1|total_hours  |
# +----+-------------+
# |obj1| 48387.837   |
# |obj2| 45570.0201  |
# |obj3| 39339.669   |
# |obj4| 37673.235   |
# |obj5| 3576        |
# |obj6| 15287.9999  |
# +----+-------------+

I want to show the total hours in hours: minutes format.

desired output:

# +----+----+--------+
# |col1|total_hours  |
# +----+-------------+
# |obj1| 48387:50    |
# |obj2| 45570:01    |
# |obj3| 39339:40    |
# |obj4| 37673:14    |
# |obj5| 3576:00     |
# |obj6| 15288:00    |
# +----+-------------+

in SQL I am able to do this with the following function :

  hr = trunc(col1);
  minutes = round(hr -trunc(hr)* 0.6, 2);

  hours_minutes= trim(replace(to_char(hr + minutes ,'999999999990.90'),'.',':'));

How can this be done in Pyspark?

pyspark-dataframes apache-spark-sql
2 Answers

This is going to require string manipulation given that simple formatting can't work. This is picking up the mod of the number, multiplying it by 60, formatting both and then concatenating:

df.withColumn('total_hours_str', 
   f.concat(f.regexp_replace(f.format_number(f.floor(df.total_hours), 0), ',', ''), 
            f.lit(':'),  
            f.lpad(f.format_number(df.total_hours%1*60, 0), 2, '0'))).show()

Output:

+----+-----------+---------------+
|col1|total_hours|total_hours_str|
+----+-----------+---------------+
|obj1|  48387.837|       48387:50|
|obj2| 45570.0201|       45570:01|
|obj3|  39339.669|       39339:40|
|obj4|  37673.235|       37673:14|
|obj5|     3576.0|        3576:00|
+----+-----------+---------------+

EDIT:
As you're having fractional values that end up being rounded to a whole hour, I suggest you round before processing the column:

df.withColumn('rounded_total_hours', f.round(df['total_hours'],2))\
  .withColumn('total_hours_str', 
      f.concat(f.regexp_replace(f.format_number(f.floor(f.col('rounded_total_hours')), 0), ',', ''), 
               f.lit(':'),  
               f.lpad(f.format_number(f.col('rounded_total_hours')%1*60, 0), 2, '0'))).show()

Which produces:

+----+-----------+-------------------+---------------+
|col1|total_hours|rounded_total_hours|total_hours_str|
+----+-----------+-------------------+---------------+
|obj1|  48387.837|           48387.84|       48387:50|
|obj2| 45570.0201|           45570.02|       45570:01|
|obj3|  39339.669|           39339.67|       39339:40|
|obj4|  37673.235|           37673.24|       37673:14|
|obj5|     3576.0|             3576.0|        3576:00|
|obj6| 15287.9999|            15288.0|       15288:00|
+----+-----------+-------------------+---------------+
user_57615580

If your desired datatype is a string then this can be done with string concat.

Steps:

  1. Extract the hours by creating a column that casts total_hours to IntegerType()
  2. Extract the fraction of hours by subtracting that value from the total_hours
  3. multiply that decimal by 60 to get the number of minutes
  4. casts to string and concat with a : seperator.

Code:

from pyspark.sql.types import IntegerType
from pyspark.sql.functions import concat_ws

df = df.withColumn('total_hour_int', df['total_hours'].cast(IntegerType())
df = df.withColumn('hours_remainder', df['total_hours']-df['total_hour_int'])
df = df.withColumn('minutes', df['hours_remainder']*60)
df = df.withColumn('minutes_full', df['minutes'].cast(IntegerType())
df = df.withColumn('total_hours_string', concat_ws(':', df['total_hour_int'], df['minutes_full'])
user_33336870
Related