Loading packages and dataset

pacotes <- c("readxl", "dplyr", "ggplot2")
lapply(pacotes, library, character.only = TRUE)
cars_train <- read_excel("cars_train.xlsx")

Analyses

Looking at the ten states with the highest volumes of observations for automatic 4-door sedans from Chevrolet

top_10_estados <- cars_train %>%
  filter(marca == "CHEVROLET" & cambio == "Automática" & tipo == "Sedã" & num_portas == 4) %>%
  group_by(estado_vendedor) %>%
  summarize(total_vendas = n()) %>%
  arrange(desc(total_vendas)) %>%
  head(10)
top_10_estados
## # A tibble: 10 × 2
##    estado_vendedor        total_vendas
##    <chr>                         <int>
##  1 São Paulo (SP)                  556
##  2 Rio de Janeiro (RJ)             123
##  3 Paraná (PR)                     115
##  4 Santa Catarina (SC)              94
##  5 Minas Gerais (MG)                91
##  6 Rio Grande do Sul (RS)           60
##  7 Goiás (GO)                       45
##  8 Bahia (BA)                       26
##  9 Paraíba (PB)                     24
## 10 Pernambuco (PE)                  11

Calculating the average price of automatic 4-door sedans from Chevrolet for the states with the highest volumes of observations

media_precos_estados <- top_10_estados %>%
  left_join(select(cars_train, estado_vendedor, preco), by = "estado_vendedor") %>%
  group_by(estado_vendedor) %>%
  summarize(media_preco = mean(preco)) %>%
  arrange(desc(media_preco))
media_precos_estados
## # A tibble: 10 × 2
##    estado_vendedor        media_preco
##    <chr>                        <dbl>
##  1 Goiás (GO)                 153416.
##  2 Paraná (PR)                145463.
##  3 Rio Grande do Sul (RS)     141426.
##  4 Rio de Janeiro (RJ)        134960.
##  5 Minas Gerais (MG)          134882.
##  6 Santa Catarina (SC)        134443.
##  7 São Paulo (SP)             129757.
##  8 Bahia (BA)                 129286.
##  9 Pernambuco (PE)            117126.
## 10 Paraíba (PB)                94476.

Making a boxplot graph of the prices in relation to the states with the highest volumes of observations

siglas_estados <- c("BA", "GO", "MG", "PB", "PR", "PE", "RJ", "RS", "SC", "SP" )
carros_top_10 <- cars_train %>%
  filter(estado_vendedor %in% top_10_estados$estado_vendedor)
ggplot(carros_top_10, aes(x = estado_vendedor, y = preco)) +
  geom_boxplot() +
  labs(title = "Boxplot graph of prices per state (Top 10 States)",
       x = "State",
       y = "Price") +
  scale_x_discrete(labels = siglas_estados) +
  scale_y_continuous(labels = scales::comma, breaks = c(250000, 500000, 750000, 1000000))

Considering the analyses carried out, we observe that the states of São Paulo, Rio de Janeiro, and Paraná have the highest volumes of observations for automatic four-door sedans from Chevrolet. However, as part of the objective is to disregard the state of São Paulo, the states of Rio de Janeiro and Paraná are shown to be the best options for selling these sedans.

When comparing Rio de Janeiro and Paraná, we verify that the average price of cars with these characteristics is higher in Paraná. This indicates that the profit would probably be higher in this state. Although the state of Goiás presents a higher average price, its volume of observations is less than half the volume of Paraná. For this reason, Paraná still shows itself as the best choice for selling automatic four-door sedans from Chevrolet.

Additionally, the box plot of Paraná confirms a slightly higher average price, along with a good range of prices above average.

Based on these analyses, we conclude that the state of Paraná, excluding the state of São Paulo, is the best option for selling automatic four-door sedans from Chevrolet.