0

I have the following column:

0       3012022.0
1       3012022.0
2       3012022.0
3       3012022.0
4       3012022.0
          ...    
351    24032022.0
352    24032022.0

df.Data = df.Data.astype('str') 

I converted the float to string and I'm trying to transform them in datetype:

df['data'] = pd.to_datetime(df['data'], format='%d%m%Y'+'.0').dt.strftime('%d-%m-%Y')

output: ValueError: day is out of range for month

the code is:

os.chdir('/home/carol/upload')
for file in glob.glob("*.xlsx"):
    xls = pd.ExcelFile('/home/carol/upload/%s'%(file))
    
    if len(xls.sheet_names) > 1:
        list_sheets= []
        for i in xls.sheet_names:
            df = pd.read_excel(xls, i)
            list_sheets.append(df)
        df = pd.concat(list_sheets)
    else:
        df = pd.read_excel(xls)

    df = df[['Data','Frota','Placa','ValorFrete', 'ValorFaturado','CodFilial','NomeFilial']]
    df = df.dropna()
    df = df.apply(lambda x: x.astype(str).str.lower())
    df.columns = df.columns.str.lower()
    df.columns = df.columns.str.normalize('NFKD').str.encode('ascii', errors='ignore').str.decode('utf-8')
    df= df.apply(lambda x: x.str.normalize('NFKD').str.encode('ascii', errors='ignore').str.decode('utf-8'))

    df['data'] = pd.to_datetime(df['data'].astype(str).str.split('\.').str[0], format='%d%m%Y')

1 Answer 1

0

Convert to string, remove the decimal point and coerce to datettime. Code as follows

 df['data'] = pd.to_datetime(df['data'].astype(str).str.split('\.').str[0], format='%d%m%Y')



    data
0   2022-01-30
1   2022-01-30
2   2022-01-30
3   2022-01-30
4   2022-01-30
351 2022-03-24
352 2022-03-24
4
  • it continues to give me " day is out of range for month" 13 hours ago
  • Unless you provided a sample dataset that is not representative of your situation. You can see with your sample I extracted it out. Are you using my code as is or you are making additions?
    – wwnde
    13 hours ago
  • I updated with my code, maybe I'm doing something wrong! I used your code 13 hours ago
  • 1
    run with a short dataset, the one you provided and check. That will help you identify where issues are, Unless I am provided with additional data I am unable to improve my code to cater for your situation
    – wwnde
    13 hours ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.