I have a table in SSMS that I'm querying to try and produce a Crosstab table in R, Shiny. Here is my sample data:
HouseNum SwitchNum Date Flipped
1 123 2/26/2019 U
1 123 2/26/2019 D
1 123 2/26/2019 U
1 123 2/26/2019 D
1 123 2/26/2019 U
1 456 2/26/2019 U
1 456 2/26/2019 D
1 456 3/1/2019 U
1 789 3/6/2019 U
1 789 3/6/2019 D
1 123 3/8/2019 U
1 123 3/8/2019 D
1 123 3/8/2019 U
1 123 3/8/2019 D
2 1122 3/8/2019 U
2 1122 3/8/2019 D
2 1122 3/8/2019 U
2 3344 3/9/2019 U
2 3344 3/9/2019 D
2 3344 3/10/2019 U
2 3344 3/10/2019 D
We have houses with light switches. We record when they are flipped Up (U) and Down (D). My goal is to produce a Crosstab in R that would group HouseNum and SwitchNum, and count how many times the switch went Up and Down on each Date. It would ultimately show this:
HouseNum SwitchNum 2/26/2019 3/1/2019 3/6/2019 3/8/2019 3/9/2019 3/10/2019
1 123 U(3),D(2) U(2),D(2)
1 456 U(1),D(1) U(1)
1 789 U(1),D(1)
2 1122 U(2),D(1)
2 3344 U(1),D(1) U(1),D(1)
Here are the library's I currently use:
library(dplyr)
library(DBI)
library(plotly)
library(shiny)
library(shinydashboard)
library(RJDBC)
library(readxl)
library(DT)
library(htmltools)
library(shinyBS)
And here are the one's I've tried for creating this Crosstab:
library(xtable)
library(reshape)
library(rpivotTable)
library(ggplot2)
library(tidyr)
library(reshape2)
I like how the tables look in DT and the sorting and filtering functionality that comes with it. I'd like to use similar functionality with the Crosstab if possible.
I resolved this by creating a concat field in my SQL query to output U or P with a COUNT(Flipped) then outputting it in R as follows:
dcast(df, HouseNum + SwitchNum ~ Date, value.var = "Flipped", fun.aggregate = toString)