2

I have a DataFrame, D1:

Date Symbol ICO_to
    5/28/2017 18:00 MYST 5/30/2017
    5/29/2017 18:00 MYST 5/30/2017
    5/30/2017 18:00 MYST 5/30/2017
    6/1/2017 18:00 MYST 5/30/2017
    6/2/2017 18:00 MYST 5/30/2017
    6/3/2017 18:00 MYST 5/30/2017
    6/4/2017 18:00 MYST 5/30/2017
    6/5/2017 18:00 MYST 5/30/2017
    6/6/2017 18:00 MYST 5/30/2017

Per This link I'm trying two methods to identify the 'Date' value (closest match) that is closest to the 'ICO_to' date value (all rows have the same value). First I try to truncate, which should remove rows up to that Date value:

D1.Date = pd.to_datetime(D1.Date) 

D1.rename(columns={'ICO to': 'ICO_to'}, inplace=True)
D1.ICO_to = pd.to_datetime(D1.ICO_to)

ICO_to = D1['ICO_to'][0] #All values in this column are the same, I just want to reference that value
ICO_to = pd.to_datetime(ICO_to) # to make sure the value is a datetime

First_date_row = D1['Date'].truncate(before=ICO_to).iloc[-1] #Remove all rows not after/= to the ICO_to date value

However I get this error:

TypeError: Cannot compare type 'Timestamp' with type 'long'

Well, I know those are datetime values so not sure what the deal is. the ICO_to variable is a timestamp. I try this instead:

First_date_row = D1['Date'].loc[D1.index.get_loc(datetime.datetime(D1['ICO_to'][0]),method='nearest')] #Identify the row where 'Date' nearest matches 'ICO_to' value at row 0 

Using this instead of truncation, I get this error:

TypeError: an integer is required 

How can I either identify the Date value that most nearly matches the ICO_to value, or remove all rows before the closest match through truncation? Either method will work.

Nick ODell
  • 15,465
  • 3
  • 32
  • 66
Cole Starbuck
  • 603
  • 3
  • 11
  • 21

1 Answers1

3

If you convert the rows to datetime objects, then you can just do simple math on the columns to find the absolute minimum distance.

import pandas as pd

D1.Date = pd.to_datetime(D1.Date)
D1.ICO_to = pd.to_datetime(D1.ICO_to)
D1[min(abs(D1.Date - D1.ICO_to)) == abs(D1.Date - D1.ICO_to)]

    Date    Symbol  ICO_to
1   2017-05-29 18:00:00 MYST    2017-05-30 00:00:00

As you can see, you'll need to be a bit careful with what you mean by close. Since you have hour information on the Date, but only a day on the ICO_to time, do you mean midnight or do you mean noon or any time at all during the day? The last option will complicate this method a bit.

If you want to get all parts of the dataframe up to that value, then you can do this. First sort the DataFrame to ensure it's ordered, then slice it for all indices less than or equal to that where the min occurs.

D1.sort_values(by='Date', inplace=True)
D1.reset_index(drop=True)
D1[D1.index <= D1[min(abs(D1.Date - D1.ICO_to)) == abs(D1.Date - D1.ICO_to)].index[0]]

    Date    Symbol  ICO_to
0   2017-05-28 18:00:00 MYST    2017-05-30 00:00:00
1   2017-05-29 18:00:00 MYST    2017-05-30 00:00:00
ALollz
  • 57,915
  • 7
  • 66
  • 89
  • You can also replace df.ICO_to with just a single date variable if you need that in the comparison. There's really no need to replicate the value in the entire `DataFrame` – ALollz Mar 23 '18 at 02:03
  • Your answers are getting better. +1 – cs95 Mar 23 '18 at 02:27
  • I understand, I was once a beginner (well, still am, but hey). I used to spam `apply` and answer really basic questions without knowing better. Hang in there, you'll get your badge soon if you're dedicated enough. – cs95 Mar 23 '18 at 02:34
  • If I see you answer a question that I answered as well, and if your answer is good, I won't hesitate to upvote. I'd appreciate it if you did the same, not just with me but with my fellow colleagues as well, most of us are really friendly and willing to return an act of goodwill :) (just gotta make a comment so they know it's you). – cs95 Mar 23 '18 at 02:36
  • And COLDSPEED saves the day! edit: So looks like truncate and the loc method won't work here, but it's good that if you have two datetimes you can just compare like this. Thanks @ALollz – Cole Starbuck Mar 23 '18 at 02:36
  • Okay great to know! Yeah a lot of time I just practice solving the problems. Typically you and others beat me to it, but in the week I've learned quite a few new functions and techniques. I'll definitely upvote the good answers! – ALollz Mar 23 '18 at 02:39