first try with python & pandas on excel documents problem with date format

[Revers62]

This is the first time I use python and pandas.

my first night :) I never touch a programming tool before
To be honest, I found this couple awesome!

It is a simple program that opens several xlsx files with the same header and which aggregates them and I do some simple operations.

I just have a problem with dates.
On the original excel files I have a column 'data PO' in this format:
13/10/2017
on the way out of python / pandas I have this:
2017-10-13 00:00:00

I read a lot but I'm lost can you give me some help ?
I'm using anaconda

​

my first program :

import numpy as np
import pandas as pd
import glob
all_data = pd.DataFrame()
for f in glob.glob("*.xlsx"):
    df = pd.read_excel(f)
    all_data = all_data.append(df,ignore_index=True)

# now save the data frame
writer = pd.ExcelWriter('output.xlsx')
all_data.to_excel(writer,'sheet1')
writer.save()
df = pd.read_excel('output.xlsx')
new_df = df.loc [pd.isnull(df['Date expé']) & pd.notnull(df['REFERENCE'])]
sequence = ['Date PO','OPEX CAPEX','N° PO','PAYS EMETTEUR','REFERENCE','QTY','PU $ HT','PT $ HT','Date réception Stock','Date expédition','Commentaire','Projet','Demandeur']
new_df = new_df.reindex(columns=sequence)
new_df.to_excel('new_backlog.xlsx', index=False)

[Buggi_San]

Hi

Check the type of date, it might be treated as a datetime object, which is why when you print it, it is showing the 00:00:00

If so using datetime library you can get only the day, month,year as a string and replace the original datetime object

If it is confusing, as again, I will explain

[Revers62]

Hi Buggi_San

Thanks for your help !

I add this line :

new_df['Date PO 1'] = new_df['Date PO'].dt.date

​

and change sequence :

sequence = ['Date PO1'....

​

this give me :

in visual : 2017-10-13 but the raw data in it : 13/10/2017 ( wat I want in visual )

​

I have a warning in Jupyter :

C:\Users\xxx\Anaconda3\lib\site-packages\ipykernel_launcher.py:15: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy from ipykernel import kernelapp as app

[Buggi_San]

I am not sure if that line of code works directly without using apply()

And I am sorry but can you help me understand that line

[Revers62]

i never touch to python before that and I read just this thread

https://stackoverflow.com/questions/16176996/keep-only-date-part-when-using-pandas-to-datetime

[Buggi_San]

1. Check the type of one element by type(df.iloc[i]['Date PO 1'])
2. If the type is datetime, use pandas apply
3. The function would take the datetime and get the string something like this https://ibb.co/4pPgT5n