Python Forum
Pandas and Date: problem with operator.How to resolve
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Pandas and Date: problem with operator.How to resolve
#1
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
Reply
#2
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
Reply
#3
Thanks Yoriz, but the problem is format column date and transform it like this:
8929 18/12/2014 4 80
(day-month-year)
........
Reply
#4
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 
Reply
#5
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 ==?
Reply
#6
(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
Reply
#7
Now it works very well, Thanks for your patience Snippsat and Yoriz
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Pandas read csv file in 'date/time' chunks MorganSamage 4 1,649 Feb-13-2023, 11:24 AM
Last Post: MorganSamage
  Does a pandas have a date without a time? AlekseyPython 6 4,875 Feb-10-2021, 09:24 AM
Last Post: Naheed
  problem with opening csv file in pandas saratha 2 2,476 Jul-21-2020, 10:55 AM
Last Post: saratha
  replace nan values by mean group by date.year, date.month wissam1974 5 8,331 Feb-19-2020, 06:25 PM
Last Post: AnkitGupta
  Obtaining Correct Date In Pandas DataFrame eddywinch82 14 5,851 Feb-17-2020, 11:45 AM
Last Post: eddywinch82
  Problem with date type (object to datetime) karlito 6 3,532 Oct-16-2019, 08:07 AM
Last Post: karlito
  Trying to Pass date to pandas search from input prompt curranjohn46 1 2,073 Oct-10-2019, 10:01 AM
Last Post: curranjohn46
  Need help passing date to pandas query curranjohn46 1 5,423 Oct-10-2019, 09:59 AM
Last Post: curranjohn46
  How to resolve scipy differences? Oliver 2 3,852 Oct-08-2019, 08:40 PM
Last Post: Oliver
  Pandas converting date to epoch randor 2 3,869 Jul-16-2019, 02:41 AM
Last Post: scidam

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020