Project 2: Data Tidying

Fictional Business Data

Introducation

I created a fictional business data .csv file containing very messy data. The raw data was wide with inconsistent units and an outlier. It was not formatted for analysis, so before exploring the relationships, I first tidyed the data. I then calculated profit and found the city with the greatest earnings.

Import Data

Import data from github hosted .csv and glimpse what we are working with.

url <- "https://raw.githubusercontent.com/jocslater-code/DATA607/refs/heads/main/Project2/5A_BusinessCostsRevenue.csv"
df_raw <- read.csv(url)
glimpse(df_raw)

Rows: 5
Columns: 14
$ Location       <chr> "Boston", "Chicago", "LA", "New York", "Dallas"
$ Name           <chr> "Bobby", "Denise", "Ethan", "April", "Emma"
$ Jan.Cost       <chr> "29,000", "41,000", "30500", "19000", "38000"
$ Jan.Revenue    <int> 50000, 45000, 39000, 31500, 42000
$ Feb.Cost..k.   <dbl> 50.0, 50.6, 31.0, 22.0, 16.0
$ Feb.Revenue    <int> 52000, 34000, 38000, 31000, 58000
$ March.Cost..k. <int> 40, 14, 60, 28, 31
$ March.Revenue  <int> 48000, 41000, 65000, 33000, 35000
$ April_Cost     <int> 50000, 56000, 60000, 30000, 35000
$ April_revenue  <int> 53000, 34000, 58000, 31000, 33000
$ May.Cost       <int> 25000, 14000, 36000, 37000, 24000
$ may.revenue    <int> 6150000, 21000, 37000, 39500, 34000
$ June.Cost      <int> 45000, 38000, 62000, 29000, 20000
$ June.Revenue   <int> 44000, 46000, 55000, 30000, 28000

Tidy Data

The data needs to be formatted for analysis. I first cleaned up the column names using the Janitor library. In order to pivot and combine columns, I needed to convert everything to characters and remove the commas. There are also some months that are reported in thousands, so the units of the cost and revenue needed to be standardized. Then I converted from wide to long and fixed the column names.

df_tidy <- df_raw %>%

  clean_names() %>%
  
  # Convert everything to character to prevent mismatches later
  mutate(across(everything(), as.character)) %>%
  
  # Pivot all month columns 
  pivot_longer(
    cols = -c(location, name),
    names_to = "measure",
    values_to = "value"
  ) %>%
  
  # Remove commas and convert to to numeric
  mutate(
    value = as.numeric(str_remove_all(value, ","))
  ) %>%
  
  # Extract month name and cost/revenue
  mutate(
    month = str_extract(measure, "^[a-z]+"),
    metric = if_else(str_detect(measure, "cost"), "cost", "revenue"),
    
    # Scale cost values if given in thousands
    value = if_else(metric == "cost" & value <= 100, value * 1000, value)) %>%


  # Pivot into separate `cost` and `revenue` columns
  pivot_wider(
    id_cols = c(location, name, month),
    names_from = metric,
    values_from = value
  ) %>%
  
  # Clean up month formatting and reorder
  mutate(
    month = str_to_title(month),
    month = factor(month, levels = c("Jan", "Feb", "March", "April", "May", "June"))
  ) %>%
  arrange(location, month)

head(df_tidy)

# A tibble: 6 × 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 March 40000   48000
4 Boston   Bobby April 50000   53000
5 Boston   Bobby May   25000 6150000
6 Boston   Bobby June  45000   44000

Analysis

First we subtract the costs from revenue to calculate profit and summarize that in a table. This allows us to see that there is an outlier in the data, likely a mistake. The Boston revenue for May was $6,150,000, suggesting a typo. When that is corrected to $61,500, the data looks much more reasonable.

df_tidy <- df_tidy %>%
  mutate(
    profit = revenue - cost,
    month = factor(month, levels = c("Jan", "Feb", "March", "April", "May", "June"))
  )

# Calculate summary by location
df_summary <- df_tidy %>%
  group_by(location) %>%
  summarise(
    Total_Cost = sum(cost, na.rm = TRUE),
    Total_Revenue = sum(revenue, na.rm = TRUE),
    Total_Profit = sum(profit, na.rm = TRUE),
    Avg_Profit_Margin = mean(profit / revenue, na.rm = TRUE)
  )

df_summary

# A tibble: 5 × 5
  location Total_Cost Total_Revenue Total_Profit Avg_Profit_Margin
  <chr>         <dbl>         <dbl>        <dbl>             <dbl>
1 Boston       239000       6397000      6158000            0.276 
2 Chicago      213600        221000         7400            0.0199
3 Dallas       164000        230000        66000            0.242 
4 LA           279500        292000        12500            0.0574
5 New York     165000        196000        31000            0.161 

# Fix the typo
df_tidy <- df_tidy %>%
  mutate(revenue = if_else(location == "Boston" & month == "May", 61500, revenue))

# Re-calculate profit
df_tidy <- df_tidy %>%
  mutate(
    profit = revenue - cost,
    month = factor(month, levels = c("Jan", "Feb", "March", "April", "May", "June"))
  )

# Re-calculate summary by location
df_summary <- df_tidy %>%
  group_by(location) %>%
  summarise(
    Total_Cost = sum(cost, na.rm = TRUE),
    Total_Revenue = sum(revenue, na.rm = TRUE),
    Total_Profit = sum(profit, na.rm = TRUE),
    Avg_Profit_Margin = mean(profit / revenue, na.rm = TRUE)
  )

# Build Great Table
df_summary %>%
  gt() %>%
  tab_header(
    title = md("**Financial Performance by Location**"),
    subtitle = md("*Summary of Total Cost, Revenue, and Profit*")
  ) %>%
  cols_label(
    location = "Location",
    Total_Cost = "Total Cost",
    Total_Revenue = "Total Revenue",
    Total_Profit = "Total Profit",
    Avg_Profit_Margin = "Avg Profit"
  ) %>%
  fmt_currency(
    columns = c(Total_Cost, Total_Revenue, Total_Profit),
    currency = "USD",
    decimals = 0
  ) %>%
  fmt_percent(
    columns = Avg_Profit_Margin,
    decimals = 1
  ) %>%
  grand_summary_rows(
    columns = c(Total_Cost, Total_Revenue, Total_Profit),
    fns = list(Total = ~sum(.)),
    fmt = ~ fmt_currency(., currency = "USD", decimals = 0)
  ) 

Financial Performance by Location
Summary of Total Cost, Revenue, and Profit
	Location	Total Cost	Total Revenue	Total Profit	Avg Profit
	Boston	$239,000	$308,500	$69,500	20.9%
	Chicago	$213,600	$221,000	$7,400	2.0%
	Dallas	$164,000	$230,000	$66,000	24.2%
	LA	$279,500	$292,000	$12,500	5.7%
	New York	$165,000	$196,000	$31,000	16.1%
Total	—	$1,061,100	$1,247,500	$186,400	—

ggplot(df_tidy, aes(x = month, y = profit, color = location, group = location)) +
  geom_line(linewidth = 1.2) +
  geom_point(size = 2.5) +
  scale_y_continuous(labels = scales::dollar) +
  theme_minimal() +
  labs(
    title = "Monthly Profit by Location",
    x = "Month",
    y = "Profit",
    color = "Location"
  )

Discussion and Next Steps

After the Boston outlier was identified and corrected, Boston still remained the second best performer. However, Dallas just out performed Boston with a 24% profit margin compared to Boston’s 21%. Both the cities owe their status to one outstanding month, rather than steady high profits. Chicago shows volatile month to month performance, while the rest of the cities earned more modestly, but also more consistently.

Next steps with this data could be to aggregating all costs/revenues to see the national performance over the 6 months.