# Merseyside IT companies (SIC 62, L and CH postcodes), 2016 to 2026
# Teaching kit 2, UK Company Data. Snapshot: filings published up to 21 October 2026.
# Source: Contains Companies House data, Open Government Licence v3.0; processed by UK Company Data.
# Rebuild: https://data.globalmindsetmarketing.co.uk/datasets?sic=62&postcode=L,CH&year_from=2016&as_of=2026-10-21

url <- "https://data.globalmindsetmarketing.co.uk/kits/merseyside-it-companies-2016-2026.csv"
d <- read.csv(url, check.names = FALSE, fileEncoding = "UTF-8-BOM", colClasses = c("Company number" = "character"))
d$year <- as.integer(substr(d[["Period end"]], 1, 4))

# 1. How many companies file each year, and how many report employees?
aggregate(cbind(companies = d[["Company number"]]) ~ year, data = d, FUN = function(x) length(unique(x)))
tapply(!is.na(d[["Employees"]]), d$year, mean)

# 2. Median net assets by year (blank = not filed, so medians use filed values only)
aggregate(`Net assets` ~ year, data = d, FUN = median)

# 3. Survivors: companies with accounts in both 2018 and 2024, and their net assets growth
a <- d[d$year == 2018, c("Company number", "Net assets")]
b <- d[d$year == 2024, c("Company number", "Net assets")]
m <- merge(a, b, by = "Company number", suffixes = c(".2018", ".2024"))
m <- m[!is.na(m[["Net assets.2018"]]) & !is.na(m[["Net assets.2024"]]) & m[["Net assets.2018"]] > 0, ]
m$growth <- m[["Net assets.2024"]] / m[["Net assets.2018"]] - 1
summary(m$growth)

# Discussion: the survivors sample excludes companies that were dissolved before 2024.
# What bias does that create for a dissertation on "growth of Merseyside IT firms"?