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 fractional parts of floats Skaperen 4 117 Mar-19-2019, 03:19 AM
Last Post: casevh
  Printing Easter date occurrences samsonite 8 202 Mar-06-2019, 11:49 AM
Last Post: samsonite
  display the contents of a sqlite3 database according to the dates atlass218 4 119 Mar-03-2019, 06:43 AM
Last Post: atlass218
  Comparing values in large txt files StevenVF 2 115 Feb-28-2019, 09:07 AM
Last Post: StevenVF
  finding yesterday and tomorrrow without using date.time module apexman 10 303 Feb-25-2019, 05:33 AM
Last Post: samsonite
  Looking for an up to date example to query mysql UtiliseIT 5 143 Feb-19-2019, 05:35 AM
Last Post: UtiliseIT
  Comparing the count of rows from the tables that are present in two different databas krt5 6 145 Feb-15-2019, 03:20 PM
Last Post: krt5
  matplotlib annotate datapoints with corresponding dates nuncio 0 89 Feb-08-2019, 11:26 AM
Last Post: nuncio
  How to chang start date for my request xyz987 1 134 Jan-29-2019, 05:40 AM
Last Post: xyz987
  Would like to input a date variable and determine whether it is within the time range harold 3 180 Jan-05-2019, 09:04 AM
Last Post: Gribouillis

Forum Jump:


Users browsing this thread: 1 Guest(s)