Bottom Page

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
Quote
#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.
Quote
#3
It will be very helpful for you to visit: https://pymotw.com/3/datetime/
Quote
#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
Quote
#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")
Quote
#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")
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  comparing 2 lists and highlighting key elements kapilan15 2 89 Jan-15-2019, 03:00 PM
Last Post: kapilan15
  Would like to input a date variable and determine whether it is within the time range harold 3 109 Jan-05-2019, 09:04 AM
Last Post: Gribouillis
  Help Convert difference between inputted date and now aspeniii 1 116 Nov-30-2018, 07:31 PM
Last Post: micseydel
  Date format conversion "/Date(158889600000)/" lbitten 2 129 Nov-29-2018, 02:14 PM
Last Post: Larz60+
  Date Variables and URL Parameters pythonjm 3 214 Nov-07-2018, 08:43 PM
Last Post: snippsat
  check log filename with present date CyberDaemon 5 358 Aug-22-2018, 08:42 PM
Last Post: CyberDaemon
  string to date time- suggestion required anna 4 322 Aug-17-2018, 08:36 AM
Last Post: buran
  sorting a list of tuples based on date bluefrog 2 335 Aug-10-2018, 02:31 AM
Last Post: ichabod801
  Looping through dictionary and comparing values with elements of a separate list. Mr_Keystrokes 5 478 Jun-22-2018, 03:08 PM
Last Post: wavic
  Values in date URL DrSavage 5 377 Jun-12-2018, 03:52 PM
Last Post: DrSavage

Forum Jump:


Users browsing this thread: 1 Guest(s)