Filtering using R to a specific date range

Question

I have a set of categorical variables listed by date. The desired outcome is a plot of counts of the categorical variables selected by a particular date range. I can produce a plot of the entire set but no variations that I have found (or people have suggested I use) produces that outcome. Date is formatted as date and libloc is a character. The end result desired is plot of the number of instructions we do in different locations by semester. I understand this is an unimportant/uninteresting question to most of you -- but I am a 62 year old classics librarian stuck at home because of the pandemic having to learn to program so I can keep my job - so can people please be kind. I realize I am not phrasing my question the way you might want but I am doing the best I can trying to do this.

library(ggplot)
library(lubridate)
library(readr)

df <- read_excel("C:/Users/12083/Desktop/instructions/datasetd.xlsx")
df %>%
  select(date,Location) %>%
  filter(date >= as.Date("2017-01-05") & date <= as.Date("2018-01-10"))%>%
  group_by(Location) %>%
  summarise(count=n())
g <- ggplot(df, aes(Location))
g + geom_bar()

Answer 1

Hope this helps:

#### Filtering using R to a specific date range ####
# From: https://stackoverflow.com/questions/62926802/filtering-using-r-to-a-specific-date-range

# First, I downloaded a sample dataset with dates and categorical data from here: 
# https://vincentarelbundock.github.io/Rdatasets/datasets.html
# Specifically, I got weather.csv


setwd("F:/Home Office/R")

data = read.csv("weather.csv") # Read the data into R
head(data)                     # Quality control, looks good
data = data[,2:3]              # For this example, I cut it to only take the relevant columns
data$date = as.Date(data$date) # This formats the date as dates for R
library(tidyverse)             # This will import some functions that you need, spcifically %>% and ggplot

# Step 0: look that the data makes sense to you
summary(data$date)
summary(data$city)

# Step 1: filter the right data
filtered = data %>% 
  filter(date > as.Date("2016-07-01") & date < as.Date("2017-07-01")) # This will only take rows between those dates

# Step 2: Plot the filtered data
# Using a bar plot: 
plot = ggplot(filtered, aes(x=city, fill = city)) + geom_bar() # You don't really need the fill, but I like it
plot

# Quality control: look at the numbers before and after the filtering:
summary(data$city)
summary(filtered$city)

Outputs:

> summary(short.data$city)
 Auckland   Beijing   Chicago    Mumbai San Diego 
      731       731       731       731       731 
> summary(filtered$city)
 Auckland   Beijing   Chicago    Mumbai San Diego 
      364       364       364       364       364 

You might be able to make it more elegant... but I think it works well

EDIT TO MAKE IT INTO A LINE PLOT

This edit is following your request in the comments:

# Line plot
# The major difference between geom_bar() and geom_line() is that 
# geom_line() requires both an X and Y values.
# So first I created a new data frame which has these values:
summarised.data = filtered %>%
  group_by(city) %>%
  tally()

# Now you can create the plot with ggplot:
# Notes: 
# 1. group = 1 is necessary
# 2. I added geom_point() so that each X value gets a point. I think it's easier to read. You can remove this if you like
plot.line = ggplot(summarised.data, aes(x=city, y=n, group = 1)) + geom_line() + geom_point()
plot.line

Outputs:

> summarised.data
# A tibble: 5 x 2
  city          n
  <fct>     <int>
1 Auckland    364
2 Beijing     364
3 Chicago     364
4 Mumbai      364
5 San Diego   364

Answer 2

Salve!

You might find that my santoku package helps. It can chop dates into intervals:

library(santoku)
library(dplyr)

df_summary <- df %>%
  select(date,Location) %>%
  filter(date >= as.Date("2017-01-05") & date <= as.Date("2018-01-10")) %>%
  mutate(semester = chop(date, as.Date(c("2017-01-05", "2017-01-09")))) %>%
  group_by(Location, semester) %>%
  summarise(count=n())

Obviously you will want to pick your semester dates appropriately.

Then you can print with something like:

ggplot(df_summary, aes(semester, count)) + geom_col() + facet_wrap(vars(location))

Answer 3

This is a new answer because the approach is different

#### Filtering using R to a specific date range ####
# From: https://stackoverflow.com/questions/62926802/filtering-using-r-to-a-specific-date-range

# First, the data I took by copy and pasting from here: 
# https://stackoverflow.com/questions/63006201/mapping-sample-data-to-actual-csv-data
# and saved it as bookdata.csv with Excel


setwd("C:/Users/di58lag/Documents/scratchboard/Scratchboard")
data = read.csv("bookdata.csv") # Read the data into R

head(data)                                            # Quality control, looks good
data$dates = as.Date(data$dates, format = "%d/%m/%Y") # This formats the date as dates for R
library(tidyverse)                                    # This will import some functions that you need, spcifically %>% and ggplot

# Step 0: look that the data makes sense to you
summary(data$dates)
summary(data$city)

# Step 1: filter the right data
start.date = as.Date("2020-01-02")
end.date   = as.Date("2020-01-04")

filtered = data %>% 
  filter(dates >= start.date & 
         dates <= end.date) # This will only take rows between those dates

# Step 2: Plotting
# Now you can create the plot with ggplot:
# Notes: 
# I added geom_point() so that each X value gets a point. 
# I think it's easier to read. You can remove this if you like
# Also added color, because I like it, feel free to delete

Plot = ggplot(filtered, aes(x=dates, y=classes, group = city)) + geom_line(aes(linetype=city, color = city)) + geom_point(aes(color=city))
Plot

# For a clean version of the plot:
clean.plot = ggplot(filtered, aes(x=dates, y=classes, group = city)) + geom_line(aes(linetype=city))
clean.plot

Outputs: Plot:

Clean.plot:

EDIT: ADDED A TABLE FUNCTION!

After reading your comments I think I figured out what you're trying to do. You asked for:

"counts of location of instructors on the vertical and dates on the horizontal."

The problem is that the original data doesn't actually give you the number of counts - ie "how many times a specific location apears in a specific date". Therefore, I had to add another line using the table function to calculate this:

data.table = as.data.frame(table(filtered))

this calculates how many times each combination of date+location apears and give a value called "Freq".

Now you can plot this Freq as the count as follows:

# Step 1.5: Counting the values
data.table = as.data.frame(table(filtered)) # This calculates the frequency of each date+location combination
data.table = data.table %>% filter(Freq>0)  # This is used to cut out any Freq=0 values (you don't want to plot cases where no event occured)
data.table$dates = as.Date(data.table$dates) # You need to rerun the "as.Date" func because it formats the dates back to "Factors"

#Quality control:
dim(filtered)   # Gives you the size of the dataframe before the counting
dim(data.table) # Gives the size after the counting
summary(data.table) # Will give you a summary of how many values are for each city, what is the date range and what is the Frequency range

# Now you can create the plot with ggplot:
# Notes: 
# I added geom_point() so that each X value gets a point. 
# I think it's easier to read. You can remove this if you like
# Also added color, because I like it, feel free to delete

Plot = ggplot(data.table, aes(x=dates, y=Freq, group = city)) + geom_line(aes(linetype=city, color = city)) + geom_point(aes(color=city))
Plot

# For a clean version of the plot:
clean.plot = ggplot(filtered, aes(x=dates, y=classes, group = city)) + geom_line(aes(linetype=city))
clean.plot

I have a feeling it's not exactly what you wanted becuase the numbers are quite low (ranging between 1-12 counts) but this is what I understand.

OUTPUTS:

> summary(data.table) 
          city        dates                 Freq      
 Pocatello  :56   Min.   :2015-01-12   Min.   :1.000  
 Idaho Falls:10   1st Qu.:2015-02-10   1st Qu.:1.000  
 Meridian   : 8   Median :2015-03-04   Median :1.000  
            : 0   Mean   :2015-03-11   Mean   :1.838  
 8          : 0   3rd Qu.:2015-04-06   3rd Qu.:2.000  
 Boise      : 0   Max.   :2015-06-26   Max.   :5.000  
 (Other)    : 0