how to Convert “unknown format” strings to time in Python-CodePudding

i'm trying to change the column of "Start time" format to Time format only but when i used the below code i found the below error message "Unknown string format: Annual"

enter image description here

FP_sheet=pd.read_excel(r"C:\Users\Mahmoud.Bader\Desktop\FP Attendance V1.6 Apr 22.xlsx","Attendance").fillna("")
FP_sheet['Start Shift']=pd.to_datetime(FP_sheet['Start Shift']).dt.strftime('%H:%M %p')
FP_sheet.head()

CodePudding user response：

You have to filter rows and get only rows with date - and later use only these rows

mask = (FP_sheet['Date'] != "Off day")

FP_sheet['Date'][mask] = pd.to_datetime(FP_sheet['Date'][mask]).dt.date

For more filters you can use & as "and", | as "or", ~ as not

mask = (df['Date'] != 'Off day') & (df['Date'] != 'Annual')

mask = ~( (df['Date'] == 'Off day') | (df['Date'] == 'Annual') )

Minimal working example

import pandas as pd

data = {
    'Date': [
        '2022.05.26 18:00',
        'Off day',
        '2022.05.16 18:00',
        'Annual', 
        '2022.05.06 18:00',
    ], 
}

df = pd.DataFrame(data)

print('\n--- before ---\n')
print(df)

mask = (df['Date'] != 'Off day') & (df['Date'] != 'Annual')
#mask = ~( (df['Date'] == 'Off day') | (df['Date'] == 'Annual') )

df['Date'][mask] = pd.to_datetime(df['Date'][mask]).dt.date

print('\n--- after ---\n')
print(df)

Result:

-- before ---

               Date
0  2022.05.26 18:00
1           Off day
2  2022.05.16 18:00
3            Annual
4  2022.05.06 18:00

--- after ---

         Date
0  2022-05-26
1     Off day
2  2022-05-16
3      Annual
4  2022-05-06

EDIT:

You can also use .apply() to run function which will check if it is text - and return this text, or if it is datetime - and return date

import pandas as pd
import datetime

# --- functions ---

def convert(item):
    if item in ['Off day', 'Annual']:
        return item
    else:
        return datetime.datetime.strptime(item, '%Y.%m.%d %H:%M').date()

# --- main ---

data = {
    'Date': [
        '2022.05.26 18:00',
        'Off day',
        '2022.05.16 18:00',
        'Annual',
        '2022.05.06 18:00',
    ], 
}
    
df = pd.DataFrame(data)

print('\n--- before ---\n')
print(df)

df['Date'] = df['Date'].apply(convert)
    
print('\n--- after ---\n')
print(df)

EDIT:

if you want to check list

mask = ~df['Date'].isin(['Off day', 'Annual'])