First I download the libraries I am likley to use.
library(tidyverse)## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.2.1 ✔ readr 2.2.0
## ✔ forcats 1.0.1 ✔ stringr 1.6.0
## ✔ ggplot2 4.0.3 ✔ tibble 3.3.1
## ✔ lubridate 1.9.5 ✔ tidyr 1.3.2
## ✔ purrr 1.2.2
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errorslibrary(dplyr)
library(ggplot2)
library(gt)The original data about cost and revenue by employees in different locations are uploaded in GitHub. They are retrieved from there.
url <- "https://raw.githubusercontent.com/Enrique01234/607-Fall-2026/refs/heads/main/5A_BusinessCostsRevenue.csv"The working data frame is created. It is called “BizCR”
BizCR<- read_csv(file= url, show_col_types = FALSE, progress = FALSE)The column names have different patterns. Thus, I label the columns in a consistent way. First I used the prefixes “rev” and “cost”. However, the extra letter in the latter caused a problem when I tried to separate the month from the revenue and the cost. Consequently, I went back and I used these column names.
colnames(BizCR)<- c("Location", "Name", "cosJAN", "revJAN","cosFEB", "revFEB", "cosMAR", "revMAR", "cosAPR", "revAPR", "cosMAY", "revMAY", "cosJUN", "revJUN")BizCR## # A tibble: 5 × 14
## Location Name cosJAN revJAN cosFEB revFEB cosMAR revMAR cosAPR revAPR cosMAY
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Boston Bobby 29000 50000 50 52000 40 48000 50000 53000 25000
## 2 Chicago Denise 41000 45000 50.6 34000 14 41000 56000 34000 14000
## 3 LA Ethan 30500 39000 31 38000 60 65000 60000 58000 36000
## 4 New York April 19000 31500 22 31000 28 33000 30000 31000 37000
## 5 Dallas Emma 38000 42000 16 58000 31 35000 35000 33000 24000
## # ℹ 3 more variables: revMAY <dbl>, cosJUN <dbl>, revJUN <dbl>The columns for cost in February and March are in different units. Although I discovered this later and I had to retrace my steps. I am fixing it here (a) because it seems better to fix it here and (b) for ease of exposition.
BizCR<-BizCR|>
mutate(cosFEB1000 = cosFEB*1000)BizCR<-BizCR|>
mutate(cosMAR1000 = cosMAR*1000)BizCR## # A tibble: 5 × 16
## Location Name cosJAN revJAN cosFEB revFEB cosMAR revMAR cosAPR revAPR cosMAY
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Boston Bobby 29000 50000 50 52000 40 48000 50000 53000 25000
## 2 Chicago Denise 41000 45000 50.6 34000 14 41000 56000 34000 14000
## 3 LA Ethan 30500 39000 31 38000 60 65000 60000 58000 36000
## 4 New York April 19000 31500 22 31000 28 33000 30000 31000 37000
## 5 Dallas Emma 38000 42000 16 58000 31 35000 35000 33000 24000
## # ℹ 5 more variables: revMAY <dbl>, cosJUN <dbl>, revJUN <dbl>,
## # cosFEB1000 <dbl>, cosMAR1000 <dbl>Now I have two extra columns (5x16 instead of 5x14 as above). As those columns are not needed, I delete them
BizCR<-BizCR[-c(5,7)]BizCR## # A tibble: 5 × 14
## Location Name cosJAN revJAN revFEB revMAR cosAPR revAPR cosMAY revMAY cosJUN
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Boston Bobby 29000 50000 52000 48000 50000 53000 25000 6.15e6 45000
## 2 Chicago Denise 41000 45000 34000 41000 56000 34000 14000 2.10e4 38000
## 3 LA Ethan 30500 39000 38000 65000 60000 58000 36000 3.7 e4 62000
## 4 New York April 19000 31500 31000 33000 30000 31000 37000 3.95e4 29000
## 5 Dallas Emma 38000 42000 58000 35000 35000 33000 24000 3.4 e4 20000
## # ℹ 3 more variables: revJUN <dbl>, cosFEB1000 <dbl>, cosMAR1000 <dbl>Although it is not seen here, the new columns are at the end. However, their names are not consistent. I rename them and create a new data frame (BizCR2).
BizCR2<-BizCR|>
rename(cosFEB = cosFEB1000, cosMAR = cosMAR1000)BizCR2## # A tibble: 5 × 14
## Location Name cosJAN revJAN revFEB revMAR cosAPR revAPR cosMAY revMAY cosJUN
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Boston Bobby 29000 50000 52000 48000 50000 53000 25000 6.15e6 45000
## 2 Chicago Denise 41000 45000 34000 41000 56000 34000 14000 2.10e4 38000
## 3 LA Ethan 30500 39000 38000 65000 60000 58000 36000 3.7 e4 62000
## 4 New York April 19000 31500 31000 33000 30000 31000 37000 3.95e4 29000
## 5 Dallas Emma 38000 42000 58000 35000 35000 33000 24000 3.4 e4 20000
## # ℹ 3 more variables: revJUN <dbl>, cosFEB <dbl>, cosMAR <dbl>Now I have a consistent data frame. I can tidy it.
BizPivoted<- BizCR2|>
pivot_longer (!c(Location, Name), names_to = "M", values_to = "Money")BizPivoted## # A tibble: 60 × 4
## Location Name M Money
## <chr> <chr> <chr> <dbl>
## 1 Boston Bobby cosJAN 29000
## 2 Boston Bobby revJAN 50000
## 3 Boston Bobby revFEB 52000
## 4 Boston Bobby revMAR 48000
## 5 Boston Bobby cosAPR 50000
## 6 Boston Bobby revAPR 53000
## 7 Boston Bobby cosMAY 25000
## 8 Boston Bobby revMAY 6150000
## 9 Boston Bobby cosJUN 45000
## 10 Boston Bobby revJUN 44000
## # ℹ 50 more rowsHowever, it is not fully tidy. While the last column includes all the monetary values, some are costs and some are revenues.
In a new data frame (BizPivoted2) I create two columns separating the information in column “M” between the month and the type of monetary value (i.e. whether they are revenue or cost). It is at this stage that having only three letters (either “cos” or “rev”) before the month is very important.
BizPivoted2<- BizPivoted|>
separate(M, c("CR", "Month"), 3)BizPivoted2## # A tibble: 60 × 5
## Location Name CR Month Money
## <chr> <chr> <chr> <chr> <dbl>
## 1 Boston Bobby cos JAN 29000
## 2 Boston Bobby rev JAN 50000
## 3 Boston Bobby rev FEB 52000
## 4 Boston Bobby rev MAR 48000
## 5 Boston Bobby cos APR 50000
## 6 Boston Bobby rev APR 53000
## 7 Boston Bobby cos MAY 25000
## 8 Boston Bobby rev MAY 6150000
## 9 Boston Bobby cos JUN 45000
## 10 Boston Bobby rev JUN 44000
## # ℹ 50 more rowsIn order to have each variable in a column and for each column to be a variable, I wide-pivot and create a new data frame (BizPivoted3).
BizPivoted3<- BizPivoted2|>
pivot_wider(names_from = CR, values_from = Money)BizPivoted3## # A tibble: 30 × 5
## Location Name Month cos rev
## <chr> <chr> <chr> <dbl> <dbl>
## 1 Boston Bobby JAN 29000 50000
## 2 Boston Bobby FEB 50000 52000
## 3 Boston Bobby MAR 40000 48000
## 4 Boston Bobby APR 50000 53000
## 5 Boston Bobby MAY 25000 6150000
## 6 Boston Bobby JUN 45000 44000
## 7 Chicago Denise JAN 41000 45000
## 8 Chicago Denise FEB 50600 34000
## 9 Chicago Denise MAR 14000 41000
## 10 Chicago Denise APR 56000 34000
## # ℹ 20 more rowsThis is a tidy data frame.
Now I can find the month with the highest revenue per employee.
BizPivoted3|>
group_by(Name)|>
slice_max(rev)## # A tibble: 5 × 5
## # Groups: Name [5]
## Location Name Month cos rev
## <chr> <chr> <chr> <dbl> <dbl>
## 1 New York April MAY 37000 39500
## 2 Boston Bobby MAY 25000 6150000
## 3 Chicago Denise JUN 38000 46000
## 4 Dallas Emma FEB 16000 58000
## 5 LA Ethan MAR 60000 65000There seems to be something odd about Bobby’s results. The results for Bobby are orders of magnitude superior to any other performance. I suspect an error in data entry.
I can also calculate profits (Revenue minus cost) in a new data frame. The new variable could have been simply added to the current data frame too.
BizPivoted4<- BizPivoted3|>
mutate(profit = rev-cos)BizPivoted4## # A tibble: 30 × 6
## Location Name Month cos rev profit
## <chr> <chr> <chr> <dbl> <dbl> <dbl>
## 1 Boston Bobby JAN 29000 50000 21000
## 2 Boston Bobby FEB 50000 52000 2000
## 3 Boston Bobby MAR 40000 48000 8000
## 4 Boston Bobby APR 50000 53000 3000
## 5 Boston Bobby MAY 25000 6150000 6125000
## 6 Boston Bobby JUN 45000 44000 -1000
## 7 Chicago Denise JAN 41000 45000 4000
## 8 Chicago Denise FEB 50600 34000 -16600
## 9 Chicago Denise MAR 14000 41000 27000
## 10 Chicago Denise APR 56000 34000 -22000
## # ℹ 20 more rowsBizPivoted4|>
slice_min(profit)## # A tibble: 1 × 6
## Location Name Month cos rev profit
## <chr> <chr> <chr> <dbl> <dbl> <dbl>
## 1 Chicago Denise APR 56000 34000 -22000As I was not expecting losses, I want to explore who and when experienced losses.
arrange(BizPivoted4, profit)## # A tibble: 30 × 6
## Location Name Month cos rev profit
## <chr> <chr> <chr> <dbl> <dbl> <dbl>
## 1 Chicago Denise APR 56000 34000 -22000
## 2 Chicago Denise FEB 50600 34000 -16600
## 3 LA Ethan JUN 62000 55000 -7000
## 4 LA Ethan APR 60000 58000 -2000
## 5 Dallas Emma APR 35000 33000 -2000
## 6 Boston Bobby JUN 45000 44000 -1000
## 7 LA Ethan MAY 36000 37000 1000
## 8 New York April APR 30000 31000 1000
## 9 New York April JUN 29000 30000 1000
## 10 Boston Bobby FEB 50000 52000 2000
## # ℹ 20 more rowsIn half a year, Denise had two months of losses. They are the largest ones: three and two times larger than the next one, which corresponds to Ethan. Ethan also had two out of six months experiencing losses.
Of the six results with losses, three happened in April. They affected Denise and Ethan as well as Emma. However, Emma’s were much lower.