Looking for source of "Error in `pivot_wider()`: ! Can't select columns that don't exist."

[Anish_Sastry]

# Load the necessary libraries
install.packages("readr")
install.packages("dplyr")
install.packages("tidyr")
install.packages("lubridate")
install.packages("knitr")
library(readr)
library(dplyr)
library(tidyr)
library(lubridate)
library(knitr)

# Define the file path and read in the data
file_path <- "XAU Raw data 6m.csv"
data <- read_csv("XAU Raw data 6m.csv",
    col_types = cols(Dates = col_datetime(format = "%m/%d/%Y %H:%M")))

# Define the function to calculate profit or loss made on a trade
calculate_profit_loss <- function(data, buy_sell_time, interval) {

  # Subset the data to only include rows on or after the specified buy/sell time
  data_subset <- data %>%
    filter(Dates >= buy_sell_time)

  # Calculate the profit or loss made at each interval
  data_subset <- data_subset %>%
    mutate(Profit_Loss = Close - lag(Close, n = interval),
           Profit_Loss = ifelse(is.na(Profit_Loss), 0, Profit_Loss))

  # Summarize the interval profit or loss
  interval_profit_loss <- data_subset %>%
    mutate(Interval = floor((Dates - buy_sell_time) / (60 * interval))) %>%
    group_by(Interval) %>%
    summarize(Interval_Profit_Loss = sum(Profit_Loss)) %>%
    mutate(Interval = as.character(Interval))

  # Summarize the daily profit or loss
  daily_profit_loss <- data_subset %>%
    mutate(Date = as.Date(Dates)) %>%
    group_by(Date) %>%
    summarize(Daily_Profit_Loss = sum(Profit_Loss))

  # Combine the interval and daily profit and loss summaries
  result <- bind_rows(interval_profit_loss, data.frame(Interval = "Total", Interval_Profit_Loss = sum(interval_profit_loss$Interval_Profit_Loss))) %>%
    pivot_wider(names_from = "Interval", values_from = "Interval_Profit_Loss")

  # Add the total daily profit or loss to the result
  result <- result %>%
    mutate(Interval = row_number()) %>%
    bind_rows(daily_profit_loss) %>%
    pivot_wider(names_from = c("Interval", "Date"), values_from = c("Interval_Profit_Loss", "Daily_Profit_Loss"))

  return(result)
}

# Example usage
buy_sell_time <- as.POSIXct("2024-01-01 6:00:00")
interval <- 5 # every 6 minutes

result <- calculate_profit_loss(data, buy_sell_time, interval)
kable(result) 

Error in `pivot_wider()`:
! Can't select columns that don't exist.
x Column `Interval_Profit_Loss` doesn't exist.
Backtrace:
 1. global calculate_profit_loss(data, buy_sell_time, interval)
 4. tidyr:::pivot_wider.data.frame(...)

# Define the function to calculate profit or loss made on a trade
calculate_profit_loss <- function(data, buy_sell_time, interval) {

  # Subset the data to only include rows on or after the specified buy/sell time
  data_subset <- data %>%
    filter(Dates >= buy_sell_time)

  # Calculate the profit or loss made at each interval
  data_subset <- data_subset %>%
    mutate(Profit_Loss = Close - lag(Close, n = interval),
           Profit_Loss = ifelse(is.na(Profit_Loss), 0, Profit_Loss))

  # Summarize the interval profit or loss
  interval_profit_loss <- data_subset %>%
    mutate(Interval = floor((Dates - buy_sell_time) / (60 * interval))) %>%
    group_by(Interval) %>%
    summarize(Interval_Profit_Loss = sum(Profit_Loss))

  # Summarize the daily profit or loss
  daily_profit_loss <- data_subset %>%
    mutate(Date = as.Date(Dates)) %>%
    group_by(Date) %>%
    summarize(Daily_Profit_Loss = sum(Profit_Loss))

  # Combine the interval and daily profit and loss summaries
  result <- bind_rows(interval_profit_loss, data.frame(Interval = "Total", Interval_Profit_Loss = sum(interval_profit_loss$Interval_Profit_Loss))) %>%
    pivot_wider(names_from = "Interval", values_from = "Interval_Profit_Loss")

  # Add the total daily profit or loss to the result
  result <- result %>%
    mutate(Interval = row_number()) %>%
    bind_rows(daily_profit_loss) %>%
    pivot_wider(names_from = c("Interval", "Date"), values_from = c("Interval_Profit_Loss", "Daily_Profit_Loss"))

  return(result)
}

# Example usage
buy_sell_time <- as.POSIXct("2024-01-01 6:00:00")
interval <- 5 # every 6 minutes

result <- calculate_profit_loss(data, buy_sell_time, interval)
kable(result)

Error in `bind_rows()`: ! Can't combine `..1$Interval` <duration<days>> and `..2$Interval` <character>. Backtrace:

1. global calculate_profit_loss(data, buy_sell_time, interval)
2. dplyr::bind_rows(interval_profit_loss, data.frame(Interval = "Total", Interval_Profit_Loss = sum(interval_profit_loss$Interval_Profit_Loss)))

Quitting from lines 9-69 [unnamed-chunk-1] (PLanalysis.Rmd) Execution halted

I have attached two forms of my code above, throwing up errors with the same variable I created within my function, any ideas on a fix*

[mduvekot]

rewrite it a bit to print the intermediate result to the console by adding an T pipe and you'll notice that there is no Interval_Profit_Loss column in the data frame that you're passing to pivot_wider.

# Add the total daily profit or loss to the result
  result <- result %>%
    mutate(Interval = row_number()) %>%
    bind_rows(daily_profit_loss) %T>%  print() %>%
    pivot_wider(
      names_from = c("Interval", "Date"), 
      values_from = c(
        "Interval_Profit_Loss", 
        "Daily_Profit_Loss")
      )