Data Cleaning

Set up

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.5
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   3.5.2     ✔ tibble    3.3.0
✔ lubridate 1.9.4     ✔ tidyr     1.3.1
✔ purrr     1.1.0     
── 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 errors

library(dplyr)

Data import

df <- read_csv("data/ai_investment.csv") |>
  glimpse()

Warning: One or more parsing issues, call `problems()` on your data frame for details,
e.g.:
  dat <- vroom(...)
  problems(dat)

Rows: 140 Columns: 6
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (5): Country, Country_label, INDUSTRY, STAGE, Sum_of_deals
num (1): Year

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

Rows: 140
Columns: 6
$ Country       <chr> "ALL", "ALL", "ALL", "ALL", "ALL", "ALL", "ALL", "ALL", …
$ Country_label <chr> "-- All countries --", "-- All countries --", "-- All co…
$ INDUSTRY      <chr> "Other", "Other", "Other", "Other", "Other", "Other", "O…
$ STAGE         <chr> "VC", "VC", "VC", "VC", "VC", "VC", "VC", "VC", "VC", "V…
$ Sum_of_deals  <chr> "946.7111605", "1396.019576", "2238.991074", "3855.41406…
$ Year          <dbl> 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 20…

Identify issues for cleaning

Main problem:

Inconsistent column number

In the dataset, some rows in the industry column contain one value, while others are split into two columns.

This happens because certain INDUSTRY values contain commas but are not enclosed in quotation marks, causing R to interpret them as two separate fields.

To fix this, the comma-containing entries in the INDUSTRY column must be manually enclosed in quotes.

Other problems:

Rename columns

Change data type

Import the raw dataset

Read raw text lines

lines <- readLines("data/ai_investment.csv", warn = FALSE)

Split line by comma

split_lines <- strsplit(lines, ",")

Inspect the dataset

Count number of fields per line

field_counts <- sapply(split_lines, length)

For inspection

table(field_counts)  

field_counts
 6  7 
85 56 

85 rows have 6 column, while 56 rows have 7 column which have to be repaired.

Perform cleaning step by step

colnames(df) <- df[1, ]

df <- df[-1, ]

• Fix corrupted rows (INDUSTRY splitted into 2 fields)

fixed_lines <- lapply(split_lines, function(x) {
  if (length(x) == 7) {
    # Combine INDUSTRY parts
    industry <- paste0(x[3], ",", x[4])
    return(c(x[1], x[2], industry, x[5], x[6], x[7]))
  } else {
    return(x)
  }
})

• Convert list → data.frame

df <- as.data.frame(do.call(rbind, fixed_lines), stringsAsFactors = FALSE)

• Use first row as header

colnames(df) <- df[1, ]
df <- df[-1, ]

• Rename columns & Convert column types

df_clean <- df |>
  rename('investment' = 'Sum_of_deals',
         'industry' = 'INDUSTRY') |>
  mutate(Year = as.integer(Year),
         investment = as.numeric(investment))

df_clean <- df |>
  rename('investment' = 'Sum_of_deals',
         'industry' = 'INDUSTRY') |>
  mutate(Year = as.integer(Year),
         investment = as.numeric(investment))

• Inspect

str(df_clean)

'data.frame':   140 obs. of  6 variables:
 $ Country      : chr  "ALL" "ALL" "ALL" "ALL" ...
 $ Country_label: chr  "-- All countries --" "-- All countries --" "-- All countries --" "-- All countries --" ...
 $ industry     : chr  "Other" "Other" "Other" "Other" ...
 $ STAGE        : chr  "VC" "VC" "VC" "VC" ...
 $ investment   : num  947 1396 2239 3855 5354 ...
 $ Year         : int  2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 ...

Save the cleaned version as .RData

save(df_clean, file = "out/ai_investment_cleaned.RData")