integer convert into time-CodePudding

DocDate  DocTime  DocEntry  DocNum                CardName U_OrdrType  \
0  2019-01-01     1511         1  100001  xyz Company    Advance   
1  2019-01-01     1512         2  100002  xyz Company    Advance   
2  2019-01-01     1514         3  100003  xyz Company    Advance   
3  2019-01-01     1516         4  100004         xyz    Advance

Retrive dataq from SQL database in which time is in integer format (1511) I want to covert time like 15:11 Please also note that some locations midnight 12 indicate only (0) I want 0 as 00:00

CodePudding user response：

If you want it as strings then you can use apply to run own function which convert it

import pandas as pd

data = {
    'DocTime': [1511, 1512, 1514, 1516, 0]
}

def convert(val):
    hours = val // 100
    minutes = val % 100
    return f"{hours:02}:{minutes:02}"

df = pd.DataFrame(data)

df['DocTimeString'] = df['DocTime'].apply(convert)

print(df)

Result:

   DocTime DocTimeString
0     1511         15:11
1     1512         15:12
2     1514         15:14
3     1516         15:16
4        0         00:00

You could also use datetime.time() - so you could later simply calculate difference between times - but it may display it with seconds. But still you can use this value with strftime to generate strings.

import pandas as pd
import datetime

data = {
    'DocTime': [1511, 1512, 1514, 1516, 0]
}

def convert(val):
    hours = val // 100
    minutes = val % 100
    return datetime.time(hours, minutes)
                         

df = pd.DataFrame(data)

df['DocTimeDatetime'] = df['DocTime'].apply(convert)
df['DocTimeString']   = df['DocTimeDatetime'].apply(lambda dt:dt.strftime('%H:%M'))
#df['DocTimeString']   = df['DocTimeDatetime'].dt.strftime('%H:%M') # doesn't work because it is not `Datetime` object

print(df)

Result:

   DocTime DocTimeDatetime DocTimeString
0     1511        15:11:00         15:11
1     1512        15:12:00         15:12
2     1514        15:14:00         15:14
3     1516        15:16:00         15:16
4        0        00:00:00         00:00

Eventually you could convert all values to string with 4-digits (so 0 has to be 0000) and then you could parse it as %H%M without :

import pandas as pd
import datetime

data = {
    'DocTime': [1511, 1512, 1514, 1516, 0]
}


df = pd.DataFrame(data)

# convert to strings with 4-digits

df['DT'] = df['DocTime'].apply(lambda x: f"{x:04}")

# parse text as `%H%M` - and get only `time`

df['DT'] = pd.to_datetime(df['DT'], format='%H%M').dt.time


print(df)

Result

   DocTime        DT
0     1511  15:11:00
1     1512  15:12:00
2     1514  15:14:00
3     1516  15:16:00
4        0  00:00:00

CodePudding user response：

df['DocEntry'] = pd.to_datetime(df['DocEntry']).dt.strftime("%H:%M")