0

I have data in seconds that I need to convert to H:MM:SS. When this data comes in it also has a date field in a separate column. I need to convert the seconds data into H:MM:SS but keep the date field as a date. I need the output to look like the desired output in Excel.

Example desired output: excel output

I've tried using Excel writer and setting the default format of date_format or datetime_format however this converts all datetime columns in the excel file. Previous responses from jmcnamara indicates that this is because cell format takes precedence over column or row format.

Here is some sample code that i've gotten to work but it's not very pythonic. It involves saving the dataframe to excel but then re-opening that exact file.

# imports
import pandas as pd
import random
from openpyxl import load_workbook
from openpyxl.styles import NamedStyle


# generate data
numbers = (random.sample(range(500, 2000), 10))
df = pd.DataFrame(numbers)
df.rename(columns={df.columns[0]:'Time'}, inplace=True)


# convert to time
df['Timestamp'] = pd.to_timedelta(df['Time'], unit='s') + pd.Timestamp(0)
#df['Openpyxl Time'] = pd.to_timedelta(df['Time'], unit='s') + pd.Timestamp(0)


# write to file
writer = pd.ExcelWriter('test.xlsx', engine = 'xlsxwriter')
df.to_excel(writer, sheet_name= 'Sheet 1', index=False)
writer.save()

# load just created file
wb = load_workbook('test.xlsx')
ws = wb.active

# set format style
date_style = NamedStyle(name='datetime', number_format='h:mm:ss')

# simple way to format but also formats column header
for cell in ws['C']:
    cell.style = date_style

#more complex way to format, but does not format column header
# for row in ws.iter_rows('C{}:C{}'.format(ws.min_row+1, ws.max_row)):
#     for cell in row:
#         cell.style = date_style

wb.save('test.xlsx')
wb.close()

How do i re-write this to not have to re-open the excel file to change the different columns to different datetime formats?

The desired output also can't be read as a string in excel. I need to be able to derive averages and sum from the timestamps.

Thanks!

Community
  • 1
  • 1
anon_investor
  • 3
  • 1
  • 2
  • This should answer part of your question: [How do I convert seconds to hours, minutes and seconds?](https://stackoverflow.com/questions/775049/how-do-i-convert-seconds-to-hours-minutes-and-seconds) – benvc Jan 07 '19 at 19:45
  • Thanks. Should have been more specific in my desired output. It cannot be read as a string in excel. I'll update my original post. – anon_investor Jan 07 '19 at 21:20
  • Just use the utils in openpyxl to write a dataframe to a worksheet. Much more control than using `to_excel()` – Charlie Clark Jan 08 '19 at 10:13
  • Thanks Charlie Clark. I researched openpyxl's util's and have come up with a better answer (in my opinion). I'm going to answer my question! thanks! – anon_investor Jan 09 '19 at 13:48

1 Answers1

0

After the recommendation from Charlie Clark in the comments above, i used OpenpyXL's utils package to convert the pandas dataframe to openpyxl's workbook. Once converted to a workbook i can still utilize the same code for the rest of the script. 

 # imports
import pandas as pd
import random
from openpyxl.styles import NamedStyle
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import Workbook


# generate data
numbers = (random.sample(range(500, 2000), 10))
df = pd.DataFrame(numbers)
df.rename(columns={df.columns[0]: 'Time'}, inplace=True)


# convert to time
df['Timestamp'] = pd.to_timedelta(df['Time'], unit='s') + pd.Timestamp(0)

# create empty openpyxl workbook
wb = Workbook()
ws = wb.active

# convert pandas dataframe to openpyxl workbook
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

# set format style in openpyxl
date_style = NamedStyle(name='datetime', number_format='h:mm:ss')

# simple way to format but also formats column header
for cell in ws['B']:
    cell.style = date_style

# more complex way to format, but does not format column header
# for row in ws.iter_rows('C{}:C{}'.format(ws.min_row+1, ws.max_row)):
#     for cell in row:
#         cell.style = date_style

# save workbook
wb.save('test.xlsx')
wb.close()
anon_investor
  • 3
  • 1
  • 2