Format dates output from excel using panda

Question

I'm trying to print out data values extracted from excel file using pandas but the dates are coming with hours values as well

the code:

import pandas as pd

df = pd.read_excel(r'C:\gits\PCIv1.xlsm',sheet_name=9,names=None)

df = df.dropna()
print(df)

the output

  Unnamed: 0             Unnamed: 1           Unnamed: 2
0         ID                   Loja                 Data
6         19  B019 BOMPREÇO PIEDADE  2020-05-17 00:00:00

I want to eliminate the '00:00:00' and keep only the day/month in format DD/MM

Any help?

Tks

On the column with the date make sure the datatype is datetime and use something like df[“date”].str.apply(lambda x: x.strftime(“%d/%m”) — JQadrad
– JQadrad, Commented May 17, 2020 at 13:41
@JQadrad, this will cause value error since value in 0th index is not a valid date pattern. — sushanth
– sushanth, Commented May 17, 2020 at 14:00

Parvathirajan Natarajan · Accepted Answer · 2020-05-17 14:15:45Z

2

Use converters while reading the excel which will make your life easier, then you have to apply dt.strftime

import pandas as pd
df2 = pd.read_excel('sample.xlsx', converters = {'Date' : pd.to_datetime})
df2['Date'] = df2['Date'].dt.strftime('%d/%m')
df2

Try the below code (Format the field after reading the excel)

df1 = pd.DataFrame({'Name':['A','B','A','B','C']
                    , 'ID':[3,4,3,5,6]
                    , 'Date':['2019-12-10 00:00:00','2019-12-10 00:00:00','2019-12-10 00:00:00'
                              ,'2019-12-10 00:00:00','2019-12-10 00:00:00']})
df1['Date'] = pd.to_datetime(df1['Date'], format="%Y-%m-%d %H:%M:%S", errors="coerce").dt.strftime('%d/%m')
df1

Then write your DataFrame into the Excel

edited May 17, 2020 at 14:15

answered May 17, 2020 at 14:00

Parvathirajan Natarajan

1,32417 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thiago Over a year ago

great! df['Data'] = df['Data'].dt.strftime('%d/%m') gave me a perfect solution! Tks!

sushanth · Accepted Answer · 2020-05-17 13:56:48Z

0

Try this,

import pandas as pd

df = pd.read_excel(r'C:\gits\PCIv1.xlsm',sheet_name=9)
pd.to_datetime(df['Data'], format="%Y-%m-%d %H:%M:%S", errors="coerce").dt.strftime("%d-%m")

to_datetime

answered May 17, 2020 at 13:56

sushanth

8,2923 gold badges20 silver badges31 bronze badges

Collectives™ on Stack Overflow

Format dates output from excel using panda

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related