Project 2 Dataset 1

Author

Nick Weiner

Introduction

For Project 2, our objective is to make 3 different ELT pipelines for 3 different wide-format .csv files.

For my first one, I will be looking at a graph of car break-in data over time to understand and visualize the “Kia Boys” car jacking exploit, one that I personally have been a victim of. See video for details on the method. https://www.youtube.com/watch?v=j78GIBMdW3g

The Code

First I will load in the libraries then I have to peel apart this .csv, which has a strange format for the columns. Big columns for location, and sub columns for counts of total car break-ins versus Kia/Hyunai break-ins.

library(knitr)

Warning: package 'knitr' was built under R version 4.5.3

library(tidyverse)

Warning: package 'tidyverse' was built under R version 4.5.3

Warning: package 'ggplot2' was built under R version 4.5.3

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.2.0     ✔ 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.1     
── 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(lubridate)

CarBreakins <- read.csv("Motherboard VICE News Kia Hyundai Theft Data - Data.csv")

kable(head(CarBreakins))

X	Denver	X.1	X.2	El.Paso	X.3	X.4	Portland	X.5	X.6	Atlanta	X.7	X.8	Chicago	X.9	X.10	Virginia.Beach	X.11	X.12	Louisville	X.13	X.14	San.Diego	X.15	X.16	Sacramento	X.17	X.18	Dallas	X.19	X.20	Fort.Worth	X.21	X.22	Bakersfield	X.23	X.24	Omaha..NE	X.25	X.26	Lubbock..TX	X.27	X.28	Fremont..CA	X.29	X.30	San.Antonio	X.31	X.32	Irving..Texas	X.33	X.34	Madison..WI	X.35	X.36	Frisco..TX	X.37	X.38	Lincoln..NE	X.39	X.40	Wichita..KS	X.41	X.42	Plano..TX	X.43	X.44	Akron..OH	X.45	X.46	Buffalo..NY	X.47	X.48	Chula.Vista..CA	X.49	X.50	Reno..NV	X.51	X.52	Oxnard..CA	X.53	X.54	San.Francisco	X.55	X.56	McKinney..TX	X.57	X.58	Arlington..TX	X.59	X.60	Garland..TX	X.61	X.62	Riverside.County..CA	X.63	X.64	Stockton..CA	X.65	X.66	Corpus.Cristi..TX	X.67	X.68	Cincinatti	X.69	X.70	Montgomery.County..MD	X.71	X.72	Henderson..NV	X.73	X.74	San.Bernardino..CA	X.75	X.76	Newport.News..VA	X.77	X.78	Amarillo..TX	X.79	X.80	New.Haven..CT	X.81	X.82	Washington..D.C.	X.83	X.84	Boise..ID	X.85	X.86	Austin..TX	X.87	X.88	Chandler..AZ	X.89	X.90	Modesto..CA	X.91	X.92	San.Jose..CA	X.93	X.94	Aurora..IL	X.95	X.96	Vancouver..WA	X.97	X.98	Raleigh..NC	X.99	X.100	St..Petersburg..FL	X.101	X.102	Milwaukee..WI	X.103	X.104	Los.Angeles..CA	X.105	X.106	Glendale..AZ	X.107	X.108	Orlando..FL	X.109	X.110	Eugene..OR	X.111	X.112	Rochester..NY	X.113	X.114	Durham..NC	X.115	X.116	Norfolk..VA	X.117	X.118	Pittsburgh..PA	X.119	X.120	Miami..FL	X.121	X.122	Spokane..WA	X.123	X.124	Salt.Lake.City..UT	X.125	X.126	Minneapolis..MN	X.127	X.128	Anaheim..CA	X.129	X.130	Providence..RI	X.131	X.132	Syracuse..NY	X.133	X.134	Peoria..AZ	X.135	X.136	Houston..TX	X.137	X.138	Prince.George.s.County..MD	X.139	X.140
	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent	Kia/Hyundais	All	Percent
Dec/19	48	615	8%	13	103	13%	13	592	2%				46	767	6%				14			25	430	6%	2			21			12	352	3%	9	360	3%	11	277	4%	6	130	5%		39					2	58	3%	4	83	5%		12			28		3	182	2%	3	64	5%	9	67	13%	7	107	7%	6	77	8%		82		1	48	2%	11	312	4%	0	17	0%	8	101	8%	3	112	3%	11	594	2%	3	119	3%	1	48	2%				2	52	4%				5	217	2%	2	21	10%	1	92	1%	6	106	6%	4			3	20	15%	10	289	3%	2	25	8%	5	118	4%	6	525	1%	1	12	8%				5	89	6%		66		22	339	6%	53	1409	4%	9	134	7%	14	109	13%	1	94	1%	3	64	5%	5	79	6%	11			9	34	26%	0			1	126	1%	6	117	5%	22	326	7%	0	18	0%	1	14	7%	2	36	6%	2	27	7%	24	603		11	210	5%
Jan/20	21	519	4%	9	95	9%	12	559	2%				36	669	5%				18	301	6%	14	423	3%	9	192	5%	26	1060	2%	13	340	4%	13	393	3%	19	291	7%	2	101	2%		63					3	78	4%	4	75	5%		15			62		4	167	2%	1	42	2%	1	62	2%	7	136	5%	3	82	4%		99		2	45	4%	10	270	4%	0	16	0%	3	87	3%	0	111	0%	10	583	2%	5	167	3%	2	45	4%	21	131	16%	1	49	2%				14	166	8%	3	31	10%	5	90	6%	5	96	5%	5	177	3%	0	16	0%	11	280	4%	3	38	8%	1	106	1%	6	420	1%	0	22	0%				5	90	6%		53		23	312	7%	42	1409	3%	10	120	8%	8	104	8%	2	63	3%	8	81	10%	9	87	10%	3			4	37	11%	6			4	136	3%	4	121	3%	30	490	6%	0	18	0%	3	15	20%	5	51	10%	1	17	6%	18	586		10	215	5%
Feb/20	28	402	7%	5	64	8%	10	498	2%				40	653	6%				10	277	4%	16	411	4%	4	157	3%	23	874	3%	4	310	1%	12	323	4%	13	266	5%	3	100	3%		43					7	71	10%	1	45	2%		8			51		5	179	3%	1	25	4%	2	42	5%	9	100	9%	2	50	4%		68		0	28	0%	9	285	3%	0	15	0%	6	75	8%	0	78	0%	10	610	2%	3	143	2%	0	28	0%	10	114	9%	1	26	4%				8	165	5%	3	30	10%	1	75	1%	5	86	6%	10	184	5%	0	20	0%	12	273	4%	4	37	11%	2	84	2%	9	477	2%	1	9	11%				2	82	2%		44		24	290	8%	39	1280	3%	3	128	2%	5	91	5%	2	62	3%	4	53	8%	3	57	5%	1			3	32	9%	5			0	121	0%	1	116	1%	18	423	4%	0	14	0%	2	18	11%	6	40	15%	2	19	11%	15	556		6	169	4%
Mar/20	35	508	7%	6	75	8%	4	465	1%				38	628	6%				14	296	5%	29	412	7%	4	197	2%	22	842	3%	15	292	5%	9	302	3%	17	261	7%	7	109	6%		59					4	75	5%	2	53	4%		11			46		11	187	6%	1	34	3%	2	52	4%	2	80	3%	2	83	2%	1	67	1%	2	54	4%	14	279	5%	3	18	17%	1	73	1%	1	78	1%	9	559	2%	6	127	5%	2	54	4%	16	139	12%	5	71	7%				11	176	6%	5	23	22%	1	90	1%	5	89	6%	8	182	4%	1	20	5%	8	346	2%	3	33	9%	3	146	2%	6	457	1%	0	18	0%				3	86	3%		46		27	267	10%	50	1477	3%	13	115	11%	14	126	11%	2	63	3%	4	64	6%	9	69	13%	5			2	28	7%	4			0	92	0%	3	125	2%	19	369	5%	0	15	0%	2	16	13%	1	28	4%	2	17	12%	26	524		7	167	4%
Apr/20	39	616	6%	1	57	2%	7	488	1%				40	659	6%				20	352	6%	14	388	4%	3	200	2%	19	772	2%	18	351	5%	6	323	2%	11	249	4%	3	119	3%		79					7	70	10%	0	38	0%		6			55		0	149	0%	0	34	0%	7	82	9%	3	78	4%	2	58	3%	1	48	2%	1	100	1%	6	303	2%	4	17	24%	3	76	4%	1	87	1%	7	609	1%	6	134	4%	1	100	1%	12	104	12%	1	54	2%				6	200	3%	3	26	12%	2	67	3%	4	56	7%	9	190	5%	0	18	0%	13	340	4%	1	33	3%	3	109	3%	11	548	2%	0	6	0%				2	66	3%		50		17	237	7%	51	1837	3%	6	104	6%	7	113	6%	0	69	0%	7	58	12%	4	69	6%	3			5	27	19%	5			1	102	1%	6	122	5%	19	314	6%	0	10	0%	3	12	25%	1	28	4%	3	13	23%	21	541		7	163	4%

Now when I look at this data, I see a few things that I know need done. I would like the Kia/Hyundais, All, and Percent values to be in the same column, and I would like the locations to be a column as well, where there is only 1 column that truly has a number value.

Based on the original data, my thought was to get the locations (“variable”) in rows rather than columns -> transpose data -> change blank values to NA -> fill down those rows -> pivot them back to final long format in order to have 4 key columns: Location, Car, Date, and Value.

CarBreaksT <- CarBreakins |>
  mutate(
    across(
          1,
          ~ if_else(
            is.na(.x) | .x == "", "Car", .x))) |>
  pivot_longer(
    -1, 
    names_to = "variable") 


CarBreaksT2 <- CarBreaksT|> 
  pivot_wider(
    names_from = 1, 
    values_from = value) |> 
  mutate(
    variable = if_else(
      str_detect(variable, "^X\\.\\d+$"), NA, variable)) |>
  fill(variable, .direction = "down")


CarBreaksT3 <- CarBreaksT2 |> 
  pivot_longer(!variable:Car) |> 
  rename( Location = variable, Date = name, ) |>
  mutate(
        value = str_remove(value, "%"),
        value = as.integer(value),
        Car = str_squish(Car),
        Date = my(Date))

Warning: There was 1 warning in `mutate()`.
ℹ In argument: `value = as.integer(value)`.
Caused by warning:
! NAs introduced by coercion

kable(head(CarBreaksT))

X	variable	value
Car	Denver	Kia/Hyundais
Car	X.1	All
Car	X.2	Percent
Car	El.Paso	Kia/Hyundais
Car	X.3	All
Car	X.4	Percent

kable(head(CarBreaksT2))

variable	Car	Dec/19	Jan/20	Feb/20	Mar/20	Apr/20	May/20	Jun/20	Jul/20	Aug/20	Sep/20	Oct/20	Nov/20	Dec/20	Jan/21	Feb/21	Mar/21	Apr/21	May/21	Jun/21	Jul/21	Aug/21	Sep/21	Oct/21	Nov/21	Dec/21	Jan/22	Feb/22	Mar/22	Apr/22	May/22	Jun/22	Jul/22	Aug/22	Sep/22	Oct/22	Nov/22	Dec/22	Jan/23	Feb/23	Mar/23	Apr/23	May/23	Jun/23	Jul/23	Aug/23
Denver	Kia/Hyundais	48	21	28	35	39	37	57	57	85	107	112	155	163	209	158	166	215	242	202	174	214	195	159	231	297	327	361	427	345	356	344	464	386	427	401	339	391	424	298	209	194	285	308	233
Denver	All	615	519	402	508	616	692	747	786	840	916	942	948	1,014	1,039	927	948	979	1,068	1,058	1,063	1,129	1,045	961	1,056	1,129	1,161	1,221	1,301	1,118	1,215	1,110	1,198	1,176	1,229	1,243	1,075	1,070	1,170	940	917	838	964	1,116	1,005
Denver	Percent	8%	4%	7%	7%	6%	5%	8%	7%	10%	12%	12%	16%	16%	20%	17%	18%	22%	23%	19%	16%	19%	19%	17%	22%	26%	28%	30%	33%	31%	29%	31%	39%	33%	35%	32%	32%	37%	36%	32%	23%	23%	30%	28%	23%
El.Paso	Kia/Hyundais	13	9	5	6	1	1	8	5	4	2	3	2	6	5	5	9	7	5	3	5	8	8	4	5	5	8	6	5	4	9	10	17	25	8	10	13	11	6	17	15	14	17	44	33	24
El.Paso	All	103	95	64	75	57	62	77	62	83	73	84	72	111	111	90	81	87	66	65	102	87	89	82	86	93	94	104	133	137	142	128	125	119	137	120	128	129	150	121	168	157	183	192	144	187
El.Paso	Percent	13%	9%	8%	8%	2%	2%	10%	8%	5%	3%	4%	3%	5%	5%	6%	11%	8%	8%	5%	5%	9%	9%	5%	6%	5%	9%	6%	4%	3%	6%	8%	14%	21%	6%	8%	10%	9%	4%	14%	9%	9%	9%	23%	23%	13%

kable(head(CarBreaksT3))

Location	Car	Date	value
Denver	Kia/Hyundais	2019-12-01	48
Denver	Kia/Hyundais	2020-01-01	21
Denver	Kia/Hyundais	2020-02-01	28
Denver	Kia/Hyundais	2020-03-01	35
Denver	Kia/Hyundais	2020-04-01	39
Denver	Kia/Hyundais	2020-05-01	37

Once in this long format, I then wanted to continue my pipeline in a few directions.

one thing I am interested in seeing plots is how total hijacks changed overtime. Do we see more hijacks overall due to the ease of hijacking with this Kia/Hyundai method?

Analysis1 <- CarBreaksT3 |>
  pivot_wider(names_from = Car, values_from = value) |>
  group_by(Date) |>
  summarize( KiaHyundaiProportion= sum(`Kia/Hyundais`, na.rm = TRUE)/
                               sum(`All`, na.rm = TRUE),
             KiaHyundaiProportion = round(KiaHyundaiProportion, digits = 2),
             BreakIns = sum(`All`, na.rm = TRUE))

kable(Analysis1)

Date	KiaHyundaiProportion	BreakIns
2019-12-01	0.05	11147
2020-01-01	0.04	12775
2020-02-01	0.04	11454
2020-03-01	0.04	11860
2020-04-01	0.04	12383
2020-05-01	0.04	12845
2020-06-01	0.04	14499
2020-07-01	0.04	15030
2020-08-01	0.05	14626
2020-09-01	0.05	13891
2020-10-01	0.05	14968
2020-11-01	0.07	15113
2020-12-01	0.08	14645
2021-01-01	0.09	15597
2021-02-01	0.08	14511
2021-03-01	0.09	14835
2021-04-01	0.11	14426
2021-05-01	0.11	14967
2021-06-01	0.10	14967
2021-07-01	0.11	15553
2021-08-01	0.12	16147
2021-09-01	0.10	15957
2021-10-01	0.09	18447
2021-11-01	0.11	16845
2021-12-01	0.11	16951
2022-01-01	0.10	17111
2022-02-01	0.11	14897
2022-03-01	0.12	16195
2022-04-01	0.12	15516
2022-05-01	0.12	16177
2022-06-01	0.15	16569
2022-07-01	0.20	18099
2022-08-01	0.20	18191
2022-09-01	0.21	18253
2022-10-01	0.26	20035
2022-11-01	0.27	18574
2022-12-01	0.29	19266
2023-01-01	0.35	18938
2023-02-01	0.36	17982
2023-03-01	0.35	18884
2023-04-01	0.36	18854
2023-05-01	0.37	20939
2023-06-01	0.38	19861
2023-07-01	0.39	21030
2023-08-01	0.38	18479

ggplot(data = Analysis1, aes(x = Date, y = KiaHyundaiProportion)) + geom_line() + labs(title = "Proportion of Kia/Hyundai Break-ins Over Time")

Analysis2 <- Analysis1 |>
  filter(Date >= as.Date("2021-01-01"))

kable(Analysis2)

Date	KiaHyundaiProportion	BreakIns
2021-01-01	0.09	15597
2021-02-01	0.08	14511
2021-03-01	0.09	14835
2021-04-01	0.11	14426
2021-05-01	0.11	14967
2021-06-01	0.10	14967
2021-07-01	0.11	15553
2021-08-01	0.12	16147
2021-09-01	0.10	15957
2021-10-01	0.09	18447
2021-11-01	0.11	16845
2021-12-01	0.11	16951
2022-01-01	0.10	17111
2022-02-01	0.11	14897
2022-03-01	0.12	16195
2022-04-01	0.12	15516
2022-05-01	0.12	16177
2022-06-01	0.15	16569
2022-07-01	0.20	18099
2022-08-01	0.20	18191
2022-09-01	0.21	18253
2022-10-01	0.26	20035
2022-11-01	0.27	18574
2022-12-01	0.29	19266
2023-01-01	0.35	18938
2023-02-01	0.36	17982
2023-03-01	0.35	18884
2023-04-01	0.36	18854
2023-05-01	0.37	20939
2023-06-01	0.38	19861
2023-07-01	0.39	21030
2023-08-01	0.38	18479

ggplot(data = Analysis2, aes(x = Date, y = BreakIns)) + geom_line() + labs(title = "Sum of Car Break-Ins Over Time")

Given the contextual knowledge that the Kia Boys hijacking method was popularized around mid 2022, the first plot makes sense. We see a sharp spike in proportion of cars Hijacked that were Kia/Hundai, to the point where it almost reaches half of car breakins by mid 2023.

The second plot looks at total count of car breakins to see if there is a spike around when the Kia Boys method was popularized. The valid data for this plot begins 2021 so we lose a bit of context, but we can see what looks like a spike mid 2022, which also correlates with nicer weather, as breaking into a car is no fun when it’s dark and cold! I’d be curious of how this data continued to change from 2023-2026.

My next analysis will get more granular to look at the different cities to see which cities were affected the most. Perhaps using some window functions as well to get an idea of change over time. I used Claude to generate locational buckets that I could more easily analyze.

# Cities in the data without a state abbreviation
city_state <- c(
  "Atlanta" = "GA", "Bakersfield" = "CA", "Chicago" = "IL",
  "Cincinatti" = "OH", "Dallas" = "TX", "Denver" = "CO",
  "El Paso" = "TX", "Fort Worth" = "TX", "Irving Texas" = "TX",
  "Louisville" = "KY", "Portland" = "OR", "Sacramento" = "CA",
  "San Antonio" = "TX", "San Diego" = "CA", "San Francisco" = "CA",
  "Virginia Beach" = "VA", "Washington D C" = "DC"
)

Analysis3 <- CarBreaksT3 |>
  mutate(
    key    = Location |> str_replace_all("[.,]", " ") |> str_squish(),
    state  = coalesce(str_extract(key, "\\b[A-Z]{2}$"), city_state[key]),
    region = as.character(state.region[match(state, state.abb)]),
    region = case_when(
      state == "DC"             ~ "South",
      region == "North Central" ~ "Midwest",
      .default = region
    )
  ) |>
  select(-key) |>
  pivot_wider(names_from = Car, values_from = value) |>
  group_by(Date, region) |>
  summarize( KiaHyundaiProportion= sum(`Kia/Hyundais`, na.rm = TRUE)/
               sum(`All`, na.rm = TRUE),
             KiaHyundaiProportion = round(KiaHyundaiProportion, digits = 2)) |>
  group_by(region) |>
  arrange(Date, .by_group = TRUE) |>
  mutate(
    RateOfChange = KiaHyundaiProportion - lag(KiaHyundaiProportion)
  )

`summarise()` has regrouped the output.
ℹ Summaries were computed grouped by Date and region.
ℹ Output is grouped by Date.
ℹ Use `summarise(.groups = "drop_last")` to silence this message.
ℹ Use `summarise(.by = c(Date, region))` for per-operation grouping
  (`?dplyr::dplyr_by`) instead.

kable(Analysis3)

Date	region	KiaHyundaiProportion	RateOfChange
2019-12-01	Midwest	0.06	NA
2020-01-01	Midwest	0.06	0.00
2020-02-01	Midwest	0.06	0.00
2020-03-01	Midwest	0.07	0.01
2020-04-01	Midwest	0.06	-0.01
2020-05-01	Midwest	0.07	0.01
2020-06-01	Midwest	0.06	-0.01
2020-07-01	Midwest	0.06	0.00
2020-08-01	Midwest	0.05	-0.01
2020-09-01	Midwest	0.06	0.01
2020-10-01	Midwest	0.07	0.01
2020-11-01	Midwest	0.12	0.05
2020-12-01	Midwest	0.18	0.06
2021-01-01	Midwest	0.21	0.03
2021-02-01	Midwest	0.18	-0.03
2021-03-01	Midwest	0.22	0.04
2021-04-01	Midwest	0.26	0.04
2021-05-01	Midwest	0.24	-0.02
2021-06-01	Midwest	0.24	0.00
2021-07-01	Midwest	0.26	0.02
2021-08-01	Midwest	0.27	0.01
2021-09-01	Midwest	0.22	-0.05
2021-10-01	Midwest	0.19	-0.03
2021-11-01	Midwest	0.20	0.01
2021-12-01	Midwest	0.19	-0.01
2022-01-01	Midwest	0.18	-0.01
2022-02-01	Midwest	0.17	-0.01
2022-03-01	Midwest	0.19	0.02
2022-04-01	Midwest	0.20	0.01
2022-05-01	Midwest	0.20	0.00
2022-06-01	Midwest	0.27	0.07
2022-07-01	Midwest	0.33	0.06
2022-08-01	Midwest	0.33	0.00
2022-09-01	Midwest	0.35	0.02
2022-10-01	Midwest	0.42	0.07
2022-11-01	Midwest	0.47	0.05
2022-12-01	Midwest	0.42	-0.05
2023-01-01	Midwest	0.42	0.00
2023-02-01	Midwest	0.44	0.02
2023-03-01	Midwest	0.43	-0.01
2023-04-01	Midwest	0.42	-0.01
2023-05-01	Midwest	0.41	-0.01
2023-06-01	Midwest	0.37	-0.04
2023-07-01	Midwest	0.35	-0.02
2023-08-01	Midwest	0.34	-0.01
2019-12-01	Northeast	0.08	NA
2020-01-01	Northeast	0.08	0.00
2020-02-01	Northeast	0.09	0.01
2020-03-01	Northeast	0.05	-0.04
2020-04-01	Northeast	0.09	0.04
2020-05-01	Northeast	0.07	-0.02
2020-06-01	Northeast	0.12	0.05
2020-07-01	Northeast	0.07	-0.05
2020-08-01	Northeast	0.10	0.03
2020-09-01	Northeast	0.06	-0.04
2020-10-01	Northeast	0.05	-0.01
2020-11-01	Northeast	0.10	0.05
2020-12-01	Northeast	0.09	-0.01
2021-01-01	Northeast	0.10	0.01
2021-02-01	Northeast	0.11	0.01
2021-03-01	Northeast	0.08	-0.03
2021-04-01	Northeast	0.09	0.01
2021-05-01	Northeast	0.10	0.01
2021-06-01	Northeast	0.08	-0.02
2021-07-01	Northeast	0.07	-0.01
2021-08-01	Northeast	0.08	0.01
2021-09-01	Northeast	0.07	-0.01
2021-10-01	Northeast	0.07	0.00
2021-11-01	Northeast	0.07	0.00
2021-12-01	Northeast	0.07	0.00
2022-01-01	Northeast	0.08	0.01
2022-02-01	Northeast	0.08	0.00
2022-03-01	Northeast	0.08	0.00
2022-04-01	Northeast	0.07	-0.01
2022-05-01	Northeast	0.08	0.01
2022-06-01	Northeast	0.09	0.01
2022-07-01	Northeast	0.17	0.08
2022-08-01	Northeast	0.23	0.06
2022-09-01	Northeast	0.20	-0.03
2022-10-01	Northeast	0.27	0.07
2022-11-01	Northeast	0.24	-0.03
2022-12-01	Northeast	0.35	0.11
2023-01-01	Northeast	0.57	0.22
2023-02-01	Northeast	0.60	0.03
2023-03-01	Northeast	0.51	-0.09
2023-04-01	Northeast	0.55	0.04
2023-05-01	Northeast	0.59	0.04
2023-06-01	Northeast	0.54	-0.05
2023-07-01	Northeast	0.54	0.00
2023-08-01	Northeast	0.51	-0.03
2019-12-01	South	0.07	NA
2020-01-01	South	0.04	-0.03
2020-02-01	South	0.04	0.00
2020-03-01	South	0.05	0.01
2020-04-01	South	0.04	-0.01
2020-05-01	South	0.04	0.00
2020-06-01	South	0.04	0.00
2020-07-01	South	0.04	0.00
2020-08-01	South	0.04	0.00
2020-09-01	South	0.04	0.00
2020-10-01	South	0.04	0.00
2020-11-01	South	0.04	0.00
2020-12-01	South	0.04	0.00
2021-01-01	South	0.06	0.02
2021-02-01	South	0.05	-0.01
2021-03-01	South	0.06	0.01
2021-04-01	South	0.05	-0.01
2021-05-01	South	0.04	-0.01
2021-06-01	South	0.05	0.01
2021-07-01	South	0.05	0.00
2021-08-01	South	0.05	0.00
2021-09-01	South	0.05	0.00
2021-10-01	South	0.06	0.01
2021-11-01	South	0.05	-0.01
2021-12-01	South	0.06	0.01
2022-01-01	South	0.05	-0.01
2022-02-01	South	0.07	0.02
2022-03-01	South	0.06	-0.01
2022-04-01	South	0.06	0.00
2022-05-01	South	0.06	0.00
2022-06-01	South	0.08	0.02
2022-07-01	South	0.12	0.04
2022-08-01	South	0.11	-0.01
2022-09-01	South	0.13	0.02
2022-10-01	South	0.20	0.07
2022-11-01	South	0.19	-0.01
2022-12-01	South	0.25	0.06
2023-01-01	South	0.38	0.13
2023-02-01	South	0.43	0.05
2023-03-01	South	0.45	0.02
2023-04-01	South	0.45	0.00
2023-05-01	South	0.46	0.01
2023-06-01	South	0.47	0.01
2023-07-01	South	0.48	0.01
2023-08-01	South	0.50	0.02
2019-12-01	West	0.04	NA
2020-01-01	West	0.03	-0.01
2020-02-01	West	0.03	0.00
2020-03-01	West	0.04	0.01
2020-04-01	West	0.03	-0.01
2020-05-01	West	0.03	0.00
2020-06-01	West	0.03	0.00
2020-07-01	West	0.04	0.01
2020-08-01	West	0.05	0.01
2020-09-01	West	0.05	0.00
2020-10-01	West	0.05	0.00
2020-11-01	West	0.06	0.01
2020-12-01	West	0.06	0.00
2021-01-01	West	0.07	0.01
2021-02-01	West	0.06	-0.01
2021-03-01	West	0.06	0.00
2021-04-01	West	0.09	0.03
2021-05-01	West	0.10	0.01
2021-06-01	West	0.09	-0.01
2021-07-01	West	0.09	0.00
2021-08-01	West	0.11	0.02
2021-09-01	West	0.08	-0.03
2021-10-01	West	0.07	-0.01
2021-11-01	West	0.11	0.04
2021-12-01	West	0.11	0.00
2022-01-01	West	0.11	0.00
2022-02-01	West	0.13	0.02
2022-03-01	West	0.13	0.00
2022-04-01	West	0.14	0.01
2022-05-01	West	0.14	0.00
2022-06-01	West	0.14	0.00
2022-07-01	West	0.18	0.04
2022-08-01	West	0.18	0.00
2022-09-01	West	0.18	0.00
2022-10-01	West	0.19	0.01
2022-11-01	West	0.19	0.00
2022-12-01	West	0.23	0.04
2023-01-01	West	0.25	0.02
2023-02-01	West	0.24	-0.01
2023-03-01	West	0.22	-0.02
2023-04-01	West	0.22	0.00
2023-05-01	West	0.24	0.02
2023-06-01	West	0.29	0.05
2023-07-01	West	0.32	0.03
2023-08-01	West	0.28	-0.04

ggplot(data = Analysis3, aes(x = Date, y = KiaHyundaiProportion, color = region)) + geom_line() + labs(title = "Proportion of Kia/Hyundai Break-ins Over Time by Region")

ggplot(data = Analysis3, aes(x = Date, y = RateOfChange, color = region)) + geom_line() + labs(title = "Change Rate of Kia/Hyundai Break-ins Over Time by Region")

Warning: Removed 4 rows containing missing values or values outside the scale range
(`geom_line()`).

Conclusions

From this analysis, we can see how there is a strong correlation between the virality of the Kia Boys trend and an increase in the proportion of break-ins, with a potential correlation to frequency of break-ins as well.

You can see some more granular analysis with the regions broken out, with plots that look both at proportions over time as well as the change of those proportions over time. Both tell a story of how different regions saw this break-in method grow, with the Northeast and the South having the largest month-by-month spikes, but the Midwest having a more constant growth due to it being the origin of the method.

Having the data in a long format initially allowed for a cleaner pipeline. It allows for the original dataframe to be very long and simple, and from there one can pivot wider to accomplish whatever they need from the data.