Change column format from Seconds to Minutes

Related searches

I have a DF with several columns with the data in seconds e.g: 600, which I want to convert to minutes with the following format 00:10:00.

I have found the following code and ran some tests it does work as expected, however, I have several columns which I want to change the format and I'm looking for the most efficient way to run the code without having to create a function for each column.

Thanks in advance.

def format_duration(row):
      seconds = row.Default_Misc  ## changed this
      minutes, seconds = divmod(seconds, 60)
      hours, minutes = divmod(minutes, 60)
      return '{:02d}:{:02d}:{:02d}'.format(hours, minutes, seconds)


aux['Default_Misc'] = aux.apply(format_duration, axis=1)
aux.to_csv(folder_path / 'Report.csv', index=False, encoding='UTF-8')

Again, currently, I'm able to apply the function to the column ['Default_Misc'], however, there are more columns that required the format change.

df = pd.DataFrame({'Time': [600,1200,1800,2000]})

pd.to_timedelta(df['Time'], 's')

0   00:10:00
1   00:20:00
2   00:30:00
3   00:33:20

You can make it a timedelta, and tell it that your column is seconds.

Change column format from Seconds to Minutes, To convert seconds entered in cells to minutes and seconds, divide the seconds by the total number of seconds in a day (60 multiplied by 60 multiplied by 24) and then apply the time custom number format of mm:ss (minutes and seconds). Applying a custom number format for minutes and seconds To apply custom number formatting for minutes and seconds, enter or select a time format using the Format Cells dialog box: Select the cells, columns or rows to which you want to apply a custom number format. Press Ctrl + 1 or right-click and select Format Cells.

Try this:

# sample data
np.random.seed(1)
df = pd.DataFrame({'sec': np.random.randint(200,500, 5)})

# format:
df['sec'].astype('datetime64[s]').dt.strftime("%H:%M:%S")

output:

0    00:03:57
1    00:07:15
2    00:04:32
3    00:07:35
4    00:06:43
Name: sec, dtype: object

if you want to update your data:

df['sec'] = df['sec'].astype('datetime64[s]').dt.strftime("%H:%M:%S")

How to Convert Seconds to Minutes and Seconds in Excel, Go to the first cell in the new column that pairs with a cell in the seconds column. In my example, that was H4. In the formula bar, type =G4/86400 then press Enter. The seconds convert into minutes and seconds! Convert hh:mm:ss time format to seconds / minutes / hours with Kutools for Excel. On the contrary of above problem, if you want to convert hh:mm:ss time format to seconds, minutes or hours, I can recommend a useful tool-Kutools for Excel, with its Convert Time feature, you can quickly convert the time format to the number of hours, minutes or seconds.

A longer, more cumbersome version:

def timeFormat(seconds, format):
    dt = str(datetime.timedelta(seconds=seconds))
    return (datetime.datetime.strptime(dt, '%H:%M:%S')).strftime(format)

fr = pd.DataFrame({'Datetimes':[600, 1200, 3600]})
fr = fr.Datetimes.apply(timeFormat, format='%H:%M:%S')

Excel: Convert seconds to minutes, Divide the number of seconds by 86400 (the number of seconds in a day);. Format the result as a time. (To format an Excel cell to display a time, select the cell� I am trying to convert a column that contains a time in seconds into a duration that includes hundreds of a second . eg. Convert 242.5 secs into 00:04:02.05

Convert Seconds to Time in Excel, The cells in columns C and F are formatted with the general format. So therefore, you could take the seconds (in column F) and divide them by 60 to get the� However, inserting a new column isn’t enough — I have to format the cells in that column so that they display the result in minutes and seconds. To format the column, select the column header, right-click on it, select Format Cells, select Custom on the Number tab, then select mm:ss from the list of types. Click OK.

How do I convert seconds to minutes in Microsoft Excel?, (BTW, 0.10 might still be 0.1022, if you change the cell format to display 4 decimal places.) Oh, I wonder if your average formula is still =24*AVERAGE(I2:I31), using 24 because the time used to be hours and minutes. Then an average of 10 "hours" 13 "minutes" (intended to be minutes and seconds) would display as 10.22 "hours" (intended to minutes).

Guys, is there a way to add a new column to show HH-MM-SS ( as per below example). In excel for example i would simply divide the seconds by 86400 and change the format in the column to hh-ss-mm.

Comments
  • This is what I was looking for, I was able to create a function to iterate thru all the columns and change the format... thanks a lot
  • Actually, my solution only works for less than 24 hours. You should go for @BenPap's answer