Python Forum
Comparing Dates to find the next date
Thread Rating:
  • 2 Vote(s) - 2.5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Comparing Dates to find the next date
#1
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.56
Data 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.32
I 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
Reply
#2
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.
Reply
#3
It will be very helpful for you to visit: https://pymotw.com/3/datetime/
Reply
#4
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
Reply
#5
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")
Reply
#6
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 missing
I am not sure if this line of code is working. Because the problem seems to be with this line

df3 = pandas.to_datetime(df2, format="%d / %m / %Y")
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Compare current date on calendar with date format file name Fioravanti 1 112 Mar-26-2024, 08:23 AM
Last Post: Pedroski55
  Python date format changes to date & time 1418 4 513 Jan-20-2024, 04:45 AM
Last Post: 1418
  Find overlapping date in database Hilal 2 1,670 Dec-18-2021, 08:15 PM
Last Post: Hilal
  Date format and past date check function Turtle 5 4,066 Oct-22-2021, 09:45 PM
Last Post: deanhystad
  How to add previous date infront of every unique customer id's invoice date ur_enegmatic 1 2,190 Feb-06-2021, 10:48 PM
Last Post: eddywinch82
  How to add date and years(integer) to get a date NG0824 4 2,802 Sep-03-2020, 02:25 PM
Last Post: NG0824
  Substracting today's date from a date in column of dates to get an integer value firebird 1 2,099 Jul-04-2019, 06:54 PM
Last Post: Axel_Erfurt
  How to change existing date to current date in a filename? shankar455 1 2,271 Apr-17-2019, 01:53 PM
Last Post: snippsat
  Date format conversion "/Date(158889600000)/" lbitten 2 2,788 Nov-29-2018, 02:14 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

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