Code
library(tidyverse)Project 2 — Data Tidying: Business Costs and Revenue
Introduction
This is one of the three datasets for Project 2. In this file, I will tidy Jocelyn’s Business Costs and Revenue dataset from 5A discussion posts. I will decide the tidying steps myself, based on what makes the data messy, and then use the analysis ideas Jocelyn suggested in her post.
Source and Requested Analysis
This dataset is a CSV file that Jocelyn created, with monthly costs and revenues for five people in different US cities from January to June. She suggested calculating profits, plotting monthly trends by region, and looking at the national totals over the six months.
Data Structure
The data is wide because each month has its own cost and revenue columns, like “Jan Cost” and “Jan Revenue”. In the tidy version, each row will show one location in one month, with location, name, month, cost, and revenue. I will keep cost and revenue as separate columns so I can easily calculate profit in each row.
Planned Approach
First, I will upload Jocelyn’s CSV file to GitHub as it is, in its original wide format, and read it into R from there. After that, before cleaning the column names, I will convert the cost and revenue values to numbers and multiply the columns marked with (k) by 1,000, since this information is contained in the column names. Next, I will clean the column names so they all follow the same format. Because each column name holds two pieces of information, the month and whether it is a cost or a revenue, one pivot_longer() is not enough. So I will use pivot_longer(), split the name into month and measure, and then use pivot_wider() to turn cost and revenue back into their own columns.
Once the data is tidy, I will calculate profit for each row, plot the monthly trends by location, and add up the costs, revenues, and profits for all locations. I will use summary tables and ggplot2 charts, and end with a conclusion.
Anticipated Challenges
The column names are inconsistent in three ways: different capital letters (“May Cost” and “may revenue”), underscores instead of spaces (“April_Cost”), and short and full month names (“Jan” and “March”). There is also an extra space after “Name”. The February and March costs are written in thousands (k), so I will multiply them by 1,000, and some January costs are stored as text with commas, like “29,000”, so I will change them to numbers. Boston’s May revenue is 6,150,000, which looks like a typo, so I will need to fix it or leave it out and explain my choice.
Tidying the Data
Loading the packages
The reason why I chose tidyverse is it includes both tidyr and dplyr also ggplot2. I found it useful.
Reading CSV
I read Jocelyn’s raw CSV file from my GitHub repository and look at its column names, number of rows, and first few rows.
Code
# Import the raw wide-format data directly from GitHub
costs_raw <- read.csv(
"https://raw.githubusercontent.com/esradogan3/Project_2/refs/heads/main/5A_BusinessCostsRevenue.csv",
check.names = FALSE
)
# Structure before tidying
names(costs_raw) # column names [1] "Location" "Name" "Jan Cost" "Jan Revenue"
[5] "Feb Cost (k)" "Feb Revenue" "March Cost (k)" "March Revenue"
[9] "April_Cost" "April_revenue" "May Cost" "may revenue"
[13] "June Cost" "June Revenue" Code
nrow(costs_raw) # number of rows[1] 5Code
head(costs_raw) # representative sample Location Name Jan Cost Jan Revenue Feb Cost (k) Feb Revenue March Cost (k)
1 Boston Bobby 29,000 50000 50.0 52000 40
2 Chicago Denise 41,000 45000 50.6 34000 14
3 LA Ethan 30500 39000 31.0 38000 60
4 New York April 19000 31500 22.0 31000 28
5 Dallas Emma 38000 42000 16.0 58000 31
March Revenue April_Cost April_revenue May Cost may revenue June Cost
1 48000 50000 53000 25000 6150000 45000
2 41000 56000 34000 14000 21000 38000
3 65000 60000 58000 36000 37000 62000
4 33000 30000 31000 37000 39500 29000
5 35000 35000 33000 24000 34000 20000
June Revenue
1 44000
2 46000
3 55000
4 30000
5 28000Fixing the values before cleaning
I turn the cost and revenue values into numbers, multiply the “(k)” columns by 1,000, and clean all the column names so they follow the same format.
Code
# Step 1: Fix values before renaming, while the "(k)" labels are still there
costs_fixed <- costs_raw |>
# Convert all cost and revenue columns to numbers
# (parse_number removes commas, so "29,000" becomes 29000)
mutate(across(matches("cost|revenue"), ~ parse_number(as.character(.x)))) |>
# Columns labeled "(k)" are in thousands, so multiply them by 1,000
mutate(across(contains("(k)"), ~ .x * 1000))
# Step 2: Clean column names so they all follow the same format
costs_clean <- costs_fixed |>
rename_with(~ .x |>
str_remove("\\(k\\)") |> # remove the "(k)" label (already used)
str_trim() |> # remove extra spaces, e.g. "Name "
str_to_lower() |> # same case everywhere
str_replace_all("[ _]+", "_") |> # spaces and underscores become "_"
str_replace("^([a-z]{3})[a-z]*_(cost|revenue)$", "\\1_\\2") # "march" -> "mar"
)
names(costs_clean) [1] "location" "name" "jan_cost" "jan_revenue" "feb_cost"
[6] "feb_revenue" "mar_cost" "mar_revenue" "apr_cost" "apr_revenue"
[11] "may_cost" "may_revenue" "jun_cost" "jun_revenue"Tidy Format
I reshape the data from wide to tidy format, so each row shows one location in one month, with separate columns for cost and revenue.
Code
costs_tidy <- costs_clean |>
# Step 1: Make it long. Each column name like "jan_cost" is split
# into two new columns: month ("jan") and measure ("cost")
pivot_longer(
cols = matches("_(cost|revenue)$"),
names_to = c("month", "measure"),
names_sep = "_",
values_to = "amount"
) |>
# Step 2: Put cost and revenue back into their own columns
pivot_wider(
names_from = measure,
values_from = amount
) |>
# Step 3: Keep months in calendar order, not alphabetical order
mutate(month = factor(month, levels = c("jan", "feb", "mar", "apr", "may", "jun")))
costs_tidy# A tibble: 30 × 5
location name month cost revenue
<chr> <chr> <fct> <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 rowsEach column name holds two pieces of information: the month and the measure. So I used pivot_longer() to stack all month columns and split each name into a month column and a measure column. Then I used pivot_wider() to turn cost and revenue back into separate columns, because I need both in the same row to calculate profit. Finally, I set the months as a factor in calendar order, so tables and plots show January to June instead of alphabetical order.
Checking missing and inconsistent values
I check the tidy data for missing values, inconsistent text, and unusual numbers before analyzing it.
Code
# 1. Count missing values in each column
costs_tidy |>
summarise(across(everything(), ~ sum(is.na(.x))))# A tibble: 1 × 5
location name month cost revenue
<int> <int> <int> <int> <int>
1 0 0 0 0 0Code
# 2. Check the text columns for spelling or spacing problems
costs_tidy |>
distinct(location, name)# A tibble: 5 × 2
location name
<chr> <chr>
1 Boston Bobby
2 Chicago Denise
3 LA Ethan
4 New York April
5 Dallas Emma Code
# 3. Look at the range of costs and revenues to spot unusual values
costs_tidy |>
select(cost, revenue) |>
summary() cost revenue
Min. :14000 Min. : 21000
1st Qu.:25750 1st Qu.: 33250
Median :33000 Median : 39250
Mean :35370 Mean : 244533
3rd Qu.:44000 3rd Qu.: 49500
Max. :62000 Max. :6150000 Code
# 4. Show the rows with the highest revenue
costs_tidy |>
arrange(desc(revenue)) |>
head()# A tibble: 6 × 5
location name month cost revenue
<chr> <chr> <fct> <dbl> <dbl>
1 Boston Bobby may 25000 6150000
2 LA Ethan mar 60000 65000
3 LA Ethan apr 60000 58000
4 Dallas Emma feb 16000 58000
5 LA Ethan jun 62000 55000
6 Boston Bobby apr 50000 53000The data has no missing values, but two values are inconsistent. Los Angeles is written as “LA”, while all other cities use their full names. Boston’s May revenue is 6,150,000, while every other revenue is between 21,000 and 65,000. The summary also shows the problem: the mean revenue is 244,533, while the median is only 39,250. This single value pulls the mean far above all the other revenues.
Handling inconsistent values
I fix the inconsistent values I found: I use full city names and set Boston’s May revenue to missing.
I changed “LA” to “Los Angeles” so all locations follow the same naming style. Boston’s other monthly revenues are between 44,000 and 53,000, so 6,150,000 is almost certainly a typo. Since I cannot know the correct value, I set it to missing (NA) instead of guessing. This means Boston’s May profit will also be missing, and the national totals for May will include only four locations.
Code
costs_tidy <- costs_tidy |>
mutate(
# Use full city names for every location
location = recode(location, "LA" = "Los Angeles"),
# Boston's May revenue (6,150,000) is a clear typo, so set it to missing
revenue = if_else(location == "Boston" & month == "may", NA_real_, revenue)
)
# Show the row I changed
costs_tidy |>
filter(location == "Boston", month == "may")# A tibble: 1 × 5
location name month cost revenue
<chr> <chr> <fct> <dbl> <dbl>
1 Boston Bobby may 25000 NAAnalysis
Calculating profit
I calculate the profit for each location and month by subtracting cost from revenue. Since Boston’s May revenue is missing, its May profit is also missing.
Code
costs_tidy <- costs_tidy |>
mutate(profit = revenue - cost)
costs_tidy# A tibble: 30 × 6
location name month cost revenue profit
<chr> <chr> <fct> <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 NA NA
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 rowsMonthly profit trends by location
I plot the monthly profit for each location to compare their trends from January to June. Boston’s line has a gap in May because that value is missing.
Code
ggplot(costs_tidy, aes(x = month, y = profit, color = location, group = location)) +
geom_line() +
geom_point() +
geom_hline(yintercept = 0, linetype = "dashed", color = "gray") +
scale_y_continuous(labels = scales::comma) +
labs(
title = "Monthly Profit by Location (January to June)",
x = "Month",
y = "Profit (USD)",
color = "Location"
)
Total profit by location
I add up each location’s profit over the six months to see which locations did best overall. The months column shows how many months are included, because Boston’s May profit is missing.
Code
location_totals <- costs_tidy |>
filter(!is.na(profit)) |>
group_by(location) |>
summarise(
months = n(),
total_cost_usd = sum(cost),
total_revenue_usd = sum(revenue),
total_profit_usd = sum(profit)
) |>
arrange(desc(total_profit_usd))
location_totals# A tibble: 5 × 5
location months total_cost_usd total_revenue_usd total_profit_usd
<chr> <int> <dbl> <dbl> <dbl>
1 Dallas 6 164000 230000 66000
2 Boston 5 214000 247000 33000
3 New York 6 165000 196000 31000
4 Los Angeles 6 279500 292000 12500
5 Chicago 6 213600 221000 7400National totals by month
I add up the costs, revenues, and profits of all locations for each month. I leave out Boston’s May row because its revenue is missing, so the May totals include only four locations. The locations column shows how many locations are included in each month.
Code
national_totals <- costs_tidy |>
filter(!is.na(revenue)) |>
group_by(month) |>
summarise(
locations = n(),
total_cost_usd = sum(cost),
total_revenue_usd = sum(revenue),
total_profit_usd = sum(profit)
)
national_totals# A tibble: 6 × 5
month locations total_cost_usd total_revenue_usd total_profit_usd
<fct> <int> <dbl> <dbl> <dbl>
1 jan 5 157500 207500 50000
2 feb 5 169600 213000 43400
3 mar 5 173000 222000 49000
4 apr 5 231000 209000 -22000
5 may 4 111000 131500 20500
6 jun 5 194000 203000 9000Code
ggplot(national_totals, aes(x = month, y = total_profit_usd)) +
geom_col(fill = "steelblue") +
geom_hline(yintercept = 0, color = "gray40") +
scale_y_continuous(labels = scales::comma) +
labs(
title = "National Total Profit by Month",
x = "Month",
y = "Total Profit (USD)"
)
Conclusion
Across the six months, the five locations made a combined profit of 149,900 USD (not counting Boston in May). Dallas had the highest total profit, 66,000 USD, mostly because of a very strong February, when it earned 42,000 USD in profit from 58,000 USD in revenue and only 16,000 USD in costs. Boston (33,000 USD over five months) and New York (31,000 USD) came next. New York was the most stable location, with a positive profit in every month. Chicago had the lowest total profit, 7,400 USD, and the most ups and downs: it lost 16,600 USD in February and 22,000 USD in April, but earned 27,000 USD in March.
At the national level, profits were strongest in the first three months: 50,000 USD in January, 43,400 USD in February, and 49,000 USD in March. April was the only month with a national loss (−22,000 USD), because costs rose to 231,000 USD while revenue fell to 209,000 USD. Profits recovered in May (20,500 USD, with only four locations) and June (9,000 USD), but stayed well below the early months.
One limitation is Boston’s May revenue. I treated it as missing because 6,150,000 is clearly a typo, so the May totals are not fully comparable with the other months.
Use of LLM
I used Claude (Anthropic) as an assistant for coding and writing support in this project. I reviewed, ran, and checked all code and results myself.
References
Anthropic. (2026). Claude (Opus 5.5 version) [Large language model]. https://claude.ai