+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Comparing Dates to find the next date (/thread-15319.html)
Comparing Dates to find the next date - Hass - Jan-13-2019
Hello I have an excel file with 2 sheets.
Data in sheet 1
Date Open High Low Close 16/11/2012 22.25 23.93 22.18 23.56 15/11/2012 22.34 22.50 21.65 22.17 14/11/2012 20.10 22.50 19.93 22.36 13/11/2012 19.61 20.11 19.56 19.86 12/11/2012 19.15 20.17 18.87 20.07 9/11/2012 19.96 20.00 19.13 19.21 8/11/2012 20.52 20.73 19.98 19.99 7/11/2012 20.85 20.95 20.37 20.47 6/11/2012 21.24 21.37 20.99 21.17 5/11/2012 21.10 21.48 20.92 21.25 2/11/2012 21.26 21.69 21.07 21.18 1/11/2012 21.08 21.44 21.01 21.21 31/10/2012 20.82 21.50 20.73 21.11 26/10/2012 22.40 22.88 21.88 21.94 25/10/2012 23.29 23.31 22.47 22.56Data in sheet 2
Date Open High Low Close 5/12/2012 27.75 27.90 27.26 27.71 4/12/2012 27.06 27.76 26.68 27.46 3/12/2012 28.00 28.88 26.98 27.04 30/11/2012 27.26 28.00 26.76 28.00 29/11/2012 26.50 27.52 26.16 27.32 28/11/2012 25.94 26.49 25.75 26.36 27/11/2012 26.04 26.50 25.46 26.15 26/11/2012 24.94 26.09 24.81 25.94 23/11/2012 24.58 24.68 23.88 24.00 21/11/2012 23.22 24.53 23.05 24.32 20/11/2012 22.73 23.90 22.70 23.10 19/11/2012 23.96 24.12 22.82 22.92 16/11/2012 22.25 23.93 22.18 23.56 15/11/2012 22.34 22.50 21.65 22.17 14/11/2012 20.10 22.50 19.93 22.36 13/11/2012 19.61 20.11 19.56 19.86 12/11/2012 19.15 20.17 18.87 20.07 9/11/2012 19.96 20.00 19.13 19.21 8/11/2012 20.52 20.73 19.98 19.99 7/11/2012 20.85 20.95 20.37 20.47 6/11/2012 21.24 21.37 20.99 21.17 5/11/2012 21.10 21.48 20.92 21.25 2/11/2012 21.26 21.69 21.07 21.18 1/11/2012 21.08 21.44 21.01 21.21 31/10/2012 20.82 21.50 20.73 21.11 26/10/2012 22.40 22.88 21.88 21.94 25/10/2012 23.29 23.31 22.47 22.56 24/10/2012 24.13 24.25 22.85 23.23 23/10/2012 19.25 19.80 19.10 19.50 22/10/2012 19.20 19.43 19.05 19.32I am trying to write code where it will look at the date 16/11/2012 which is in sheet 1 and this will always be cell A2 and look for the next date available which will be 19/11/2012 in sheet 2.
Unfortunately I have no idea how to write this code. Can someone please point me in the right direction.
Thanks
RE: Comparing Dates to find the next date - stullis - Jan-13-2019
You'll need the datetime module. Each date in the sheets will have to be instantiated as a datetime.datetime or datetime.date object and then compared. That module provides everything you need to manipulate the date as well to determine the next available date.
RE: Comparing Dates to find the next date - Larz60+ - Jan-13-2019
It will be very helpful for you to visit: https://pymotw.com/3/datetime/
RE: Comparing Dates to find the next date - Hass - Jan-19-2019
Hi Guys,
This is the code so far. My understanding is that df2 which is referencing the "date" column should be converted to datetime type.
import pandas
import datetime
df1=pandas.read_excel("FB.xlsx",sheet_name=0)
df2 = df1.set_index("Date")
df3 = pandas.to_datetime((df2, format="%d / %m / %Y"))Can someone please point out where I am going wrong
RE: Comparing Dates to find the next date - stullis - Jan-19-2019
The problem is likely the extra parentheses. Since pandas incorporates datetime operations, you actually don't need the datetime module; apologies for the bad advice. Try this:
import pandas
df1=pandas.read_excel("FB.xlsx",sheet_name=0)
df2 = df1.set_index("Date")
df3 = pandas.to_datetime(df2, format="%d / %m / %Y")
RE: Comparing Dates to find the next date - Hass - Jan-20-2019
Hi,
Unfortunately that did not work.
it returned the following error message
Error:Traceback (most recent call last):
File "C:\Users\Hass\eclipse-workspace\FTS\src\Excel.py", line 12, in <module>
df3 = pandas.to_datetime(df2, format="%d / %m / %Y")
File "C:\Program Files\Python\lib\site-packages\pandas\core\tools\datetimes.py", line 454, in to_datetime
result = _assemble_from_unit_mappings(arg, errors=errors)
File "C:\Program Files\Python\lib\site-packages\pandas\core\tools\datetimes.py", line 542, in _assemble_from_unit_mappings
"is missing".format(required=','.join(req)))
ValueError: to assemble mappings requires at least that [year, month, day] be specified: [day,month,year] is missingdf3 = pandas.to_datetime(df2, format="%d / %m / %Y")