CODEWITHSUNDEEP
pythonpandasdate
Thiago
Thiago

Reputation: 35

Format dates output from excel using panda

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

Upvotes: 2

Views: 118

Answers (2)

Parvathirajan Natarajan
Parvathirajan Natarajan

Reputation: 1305

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

Upvotes: 2

sushanth
sushanth

Reputation: 8302

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

Upvotes: 0

Related Questions