Need help using Excel data in R

Question

I'm just starting to use R and imported an Excel file named tblMainXmasFund.xlsx. I can read it using

readxl::read_excel("C:/Excel/For R/tblMainXmasFund.xlsx"). 

However, when I try to do calculations like sum(Amount) I get:

Error: object 'Amount' not found. If I use sum("Amount") I get: invalid 'type' (character) of argument.

R recognizes the Amount column as being numeric (it's actually double) so I'm guessing I need to change my syntax but don't know to what.

There are only two columns of data (CheckDate and Amount) I will be using so would like to know the code for doing a calculation like sum(Amount) where CheckDate >=10/1/2022 and <=12/31/2022. All suggestions are appreciated.

Answer 1

Hard to know exactly what is going on, but here is my best guess. I have created this 3x2 sheet in Excel to mimic yours:

I then saved it into R by loading the readxl package to load the sheet, saved it as a data frame, then ran the sum on the Amount variable:

# Load library for excel functions:
library(readxl)

# Save excel sheet into data frame:
df <- read_excel("slack.xlsx")

# Run sum of Amount:
sum(df$Amount)

Which gives you the sum below:

> sum(df$Amount)
[1] 352.84

Edit

Based off your comment below, you can summarize your CheckDate values with the following code:

library(tidyverse)

reconstruct %>% 
  mutate(CheckDate = as.character(CheckDate)) %>% 
  group_by(CheckDate) %>% 
  summarise(Total = sum(Amount))

Which gives you these summarized values by date:

# A tibble: 6 x 2
  CheckDate  Total
  <chr>      <dbl>
1 2020-10-15  2800
2 2020-10-16  1500
3 2020-10-17  1000
4 2020-10-19  3600
5 2020-10-20  2450
6 2020-10-21  1700

To graph these, you can use the following basic code, which just adds the ggplot and geombar commands:

reconstruct %>% 
  mutate(CheckDate = as.character(CheckDate)) %>% 
  group_by(CheckDate) %>% 
  summarise(Total = sum(Amount)) %>%
  ggplot(aes(x=CheckDate,
             weight=Total))+
  geom_bar()

If you want it to be a little fancier, you can also use this:

reconstruct %>% 
  mutate(CheckDate = as.character(CheckDate)) %>% 
  group_by(CheckDate) %>% 
  summarise(Total = sum(Amount)) %>%
  ggplot(aes(x=CheckDate,
             weight=Total,
             fill = CheckDate))+
  geom_bar()+
  theme_bw()+
  labs(title = "Check by Date Bar Graph",
       x = "Date of Check",
       y = "Total Amount",
       caption = "*Data obtained from StackOverflow.")+
  theme(legend.position = "none",
        plot.title = element_text(face = "bold"),
        plot.caption = element_text(face = "italic"))+
  scale_fill_hue(l=40, c=35)

Which gives you something a little more colorful: