-4

I have a historical list of prospects I've contacted, with timestamp of each contact, and my corresponding profit from each call. I'd like to use this data to target the most profitable prospects based on what HOUR of day I previously contacted them. Basically, I want to generate a list of most profitable prospects based on time of day, so I can call the most profitable at all times.

I am looking to create a prompt in R or python that looks like this:

Prompt - "What time is it?"

*Then I input the time and I am shown a list of "ProspectID" sorted from highest "profitability" to lowest, maybe displaying only the top 50.

I have 3 columns and 100,000 records - "timeofday" - UTC time "prospectID" - ID of prospect I am looking to target "profitability" - amount of expected profit.

Sample Data

Joe Johnson
  • 73
  • 1
  • 1
  • 10
  • 1
    Hi, what is your question? Where is your code? Please see how to create a [minimal reproductible example](https://stackoverflow.com/a/5963610/2414988) to make it easier for us to help you. – Biblot Oct 18 '19 at 14:26
  • I am asking how to create a script in R to return the most profitable prospects based on hour of day? – Joe Johnson Oct 18 '19 at 14:39
  • For example, if I am prompted for time of day, and I input 13:20, I want to be shown the list of prospects in order of profitability during that hour. – Joe Johnson Oct 18 '19 at 14:41

1 Answers1

1

Since we don't have much information about your data (see the link in my comment to provide us with a minimal dataset, screenshot aren't easily imported in R or Python), I assumed you had (or could import) a data frame similar to this, with records all from the same day:

df <- data.frame(
  timeofday     = hms::as_hms(c("18:47:22","16:39:58", "07:30:05")),
  prospectID    = c(1, 2, 3),
  profitability = runif(3)
)

using the dplyr package, you could ask the user for a specific hour, filter your data frame accordingly and order by profitability:

library(dplyr)
library(lubridate)

hour <- as.numeric(readline(prompt="Hour: "))
df %>% 
  filter(hour(timeofday) == hour) %>%
  arrange(-profitability)
Biblot
  • 695
  • 3
  • 18
  • Thanks, I think I'm close, but for some reason I'm getting an error when running the last bit of code: "Error in hour(timeofday) : could not find function "hour" – Joe Johnson Oct 18 '19 at 15:32
  • > sapply(mydata, class) $`timeofday` [1] "POSIXct" "POSIXt" $prospectID [1] "numeric" $profitability [1] "numeric" – Joe Johnson Oct 18 '19 at 15:33
  • 1
    @JoeJohnson See my edit, I forgot to add the `lubridate` package for the `hour` function. – Biblot Oct 18 '19 at 15:38
  • ok I think I solved the above issue - but am now getting this error: Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘filter’ for signature ‘"data.frame", "logical"’ – Joe Johnson Oct 18 '19 at 15:38
  • 1
    @JoeJohnson You will get this error when attempting to apply an S4 generic function to an object of a class for which no defined S4 method exists. Use `dplyr::filter` instead of `filter`. You might have a package that already imported a function `filter`, masking the one from `dplyr`. – Biblot Oct 18 '19 at 15:42
  • getting closer, the code works now! But I'm not getting any results... df %>% + dplyr::filter(hour(df$timeofday) == hour) %>% dplyr::arrange(-profitability) [1] timeofday prospectID profitability <0 rows> (or 0-length row.names) – Joe Johnson Oct 18 '19 at 15:44
  • this is what an entry looks like in my R data - "1899-12-31 13:21:00" - the excel data shows this cell to be "13:21" in the cell and "1:21:00 PM" in the function box – Joe Johnson Oct 18 '19 at 15:48
  • `readline` returns a `character` vector, I convert it to `numeric` with `as.numeric`. If `timeofday` contains only dates as `hour:minutes`, convert the column to `hms` format, using `hms::as_hms()`. I updated my solution accordingly. Also, double check my code: you wrote `df %>% + dplyr::filter(hour(df$timeofday)` in your comment whereas my solution shows `df %>% filter(hour(timeofday) == hour)` – Biblot Oct 18 '19 at 15:57
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/201108/discussion-between-samuel-diebolt-and-joe-johnson). – Biblot Oct 18 '19 at 16:00