Python Forum

Full Version: Pandas and Date: problem with operator.How to resolve
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi.

I have a file csv (es10.csv) like this:
Output:
Conc. Date I II 8926 18/11/2014 1 22 8927 20/11/2014 2 33 8928 30/11/2014 3 15 8929 18/12/2014 4 80 8930 28/12/2014 8 60 8931 31/12/2014 9 12
Note: Date = day**month**Year

Now, i would like to get the following output:
Output:
Conc. Date I II 8929 18/12/2014 4 1 8930 28/12/2014 8 0 8931 31/12/2014 9 1
Note: the value of the date must be greater than 30/11/2014

My code is:

df=pd.read_csv("esv10.csv",encoding='windows-1252',sep="\t",decimal=',',keep_default_na=False, na_values=[''], converters={"Conc.":str},)
pd.options.display.float_format = '{:,.0f}'.format
myfilter=df["Date"]>"30/11/2014"
df= df.where(myfilter ,axis=1).dropna()
print(df)
If i use the operator ==, output = 8928 30/11/2014 3 15
Is it possible to use operator: > or < or =!?

Thanks
I changed the sep="\t" to sep=" "
and added df['Date'] = pd.to_datetime(df['Date']) to turn the date column into a datetime type
import pandas as pd

df=pd.read_csv("some_data.csv",encoding='windows-1252',sep=" ",decimal=',',keep_default_na=False, na_values=[''], converters={"Conc.":str},)
pd.options.display.float_format = '{:,.0f}'.format
df['Date'] = pd.to_datetime(df['Date'])
myfilter=df["Date"]>"30/11/2014"
df= df.where(myfilter ,axis=1).dropna()
print(df)
Output:
Conc. Date I II 3 8929 2014-12-18 4 80 4 8930 2014-12-28 8 60 5 8931 2014-12-31 9 12
Thanks Yoriz, but the problem is format column date and transform it like this:
8929 18/12/2014 4 80
(day-month-year)
........
Use dt.strftime to covert to wanted format.
>>> import pandas as pd

>>> df = pd.read_clipboard()
>>> df['Date'] = pd.to_datetime(df['Date'])
>>> df
   Conc.       Date  I  II
3   8929 2014-12-18  4  80
4   8930 2014-12-28  8  60
5   8931 2014-12-31  9  12

>>> df['Date'] = df['Date'].dt.strftime('%d/%m/%Y')
>>> df
   Conc.        Date  I  II
3   8929  18/12/2014  4  80
4   8930  28/12/2014  8  60
5   8931  31/12/2014  9  12 
Thanks Snippsat. The conversion work, but applying "df.where" to filter df["Date"]>"30/11/2014"", the result does not change. Is valid the operator > or i do to use only operator ==?
(May-13-2019, 04:05 PM)frame Wrote: [ -> ]The conversion work, but applying "df.where" to filter df["Date"]>"30/11/2014"", the result does not change. Is valid the operator > or i do to use only operator ==?
The code for @Yoriz should work,is that what you have problem with or are doing something else?
Doing myfilter = df["Date"] == "30/11/2014" it will only drop.
Output:
Conc. Date I II 2 8928.0 30/11/2014 3.0 15.0
Test of code:
>>> import pandas as pd

>>> df = pd.read_clipboard()
>>> df['Date'] = pd.to_datetime(df['Date'])
>>> df
   Conc.       Date  I  II
0   8926 2014-11-18  1  22
1   8927 2014-11-20  2  33
2   8928 2014-11-30  3  15
3   8929 2014-12-18  4  80
4   8930 2014-12-28  8  60
5   8931 2014-12-31  9  12

>>> myfilter = df["Date"] > "30/11/2014"
>>> df = df.where(myfilter ,axis=1).dropna()
>>> df['Date'] = df['Date'].dt.strftime('%d/%m/%Y')
>>> df
    Conc.        Date    I    II
3  8929.0  18/12/2014  4.0  80.0
4  8930.0  28/12/2014  8.0  60.0
5  8931.0  31/12/2014  9.0  12.0
Now it works very well, Thanks for your patience Snippsat and Yoriz