data.qmd

# Data

All data was obtained from the Wall Street Journal based on data from Payscale, Inc

## Description

1.  degrees-that-pay-back.csv

This file contains median salary data (starting, mid-career, percentiles) for various undergraduate majors such as Engineering, Business, Liberal Arts, Healthcare, etc. Useful for seeing salary trajectories and ranges for different majors.

2.  salaries-by-college-type.csv

This file shows median salaries (starting and mid-career) grouped by college type - Engineering colleges, Liberal Arts colleges, Ivy League schools, etc. Gives salary outcomes based on college types rather than majors specifically.

3.  salaries-by-region.csv

This file aggregates salaries by region - West, Midwest, Northeast, South. Shows range of salaries for graduates from schools in different geographic areas. Helps compare regional salary differences.

### Format:

-   Data is structured as CSV files with header rows naming each column, and subsequent rows containing the data values
-   There is a mix of text and numeric data in columns
-   Varying number of columns across the files (13-16 columns)

### Dimensions:

-   degrees-that-pay-back.csv: 32 rows x 16 columns
-   salaries-by-college-type.csv: 130 rows x 12 columns
-   salaries-by-region.csv: 344 rows x 12 columns

### Issues:

-   Some inconsistent formatting (e.g. school names sometimes abbreviated, use of "---" instead of "N/A")
-   Salary columns contain string values with "\$" and "," formatting rather than standardized numeric values
-   It is not clear what year(s) the salary data represents

## Missing value analysis

We took a look at the % of missing values per column in each table (all the code is below the analysis points):

1.  degrees-that-pay-back.csv:

-   No missing values

2.  salaries-by-college-type.csv:

-   Mid-Career 10th Percentile Salary: approx 14% missing
-   Mid-Career 90th Percentile Salary: approx 26% missing
-   Other columns are complete

3.  salaries-by-region.csv:

-   Mid-Career 10th Percentile Salary: approx 14% missing
-   Mid-Career 90th Percentile Salary: approx 14% missing
-   Other columns are complete

So the main missing data is related to the salary range percentiles in some cases. We could consider dropping those columns or trying imputation methods to fill them in for analysis.

## CODE:

```{r}
# install.packages(c('viridis', 'tidyverse', 'reshape2'))
```

```{r}
degrees_that_pay_back <- read.csv("data//raw_data//degrees-that-pay-back.csv")

salaries_by_college_type <- read.csv("data//raw_data//salaries-by-college-type.csv")
  
salaries_by_region <- read.csv("data//raw_data//salaries-by-region.csv")
```

### Exploring the data

#### Table : degrees_that_pay_back

```{r}
for (col in colnames(degrees_that_pay_back)){
  print(col)
}
```

```{r}
head(degrees_that_pay_back,5)
```

#### Table : salaries_by_college_type

```{r}
for (col in colnames(salaries_by_college_type)){
  print(col)
}
```

```{r}
head(salaries_by_college_type,5)
```

#### Table : salaries_by_region

```{r}
for (col in colnames(salaries_by_region)){
  print(col)
}
```

```{r}
head(salaries_by_region,5)
```

### Missing Value Analysis

```{r}
cat("For degrees_that_pay_back:", "\n", "\n")
degrees_missing <- sapply(degrees_that_pay_back, function(x) {
  mean(x == "N/A") * 100 
})

print(degrees_missing)
```

```{r}
cat("For salaries_by_college_type:", "\n", "\n")
college_missing <- sapply(salaries_by_college_type, function(x) {
  mean(x == "N/A") * 100 
})

print(college_missing)
```

```{r}
cat("For salaries_by_region:", "\n", "\n")
region_missing <- sapply(salaries_by_region, function(x) {
  mean(x == "N/A") * 100 
})

print(region_missing)
```

```{r}
library(tidyverse)

# Replacing "N/A" with NA in the entire data frame
degrees_that_pay_back <- degrees_that_pay_back %>% mutate_all(~ ifelse(. == "N/A", NA, .))
salaries_by_college_type <- salaries_by_college_type %>% mutate_all(~ ifelse(. == "N/A", NA, .))
salaries_by_region <- salaries_by_region %>% mutate_all(~ ifelse(. == "N/A", NA, .))

missing_percentage_degrees_that_pay_back <- colMeans(is.na(degrees_that_pay_back)) * 100
missing_percentage_salaries_by_college_type <- colMeans(is.na(salaries_by_college_type)) * 100
missing_percentage_salaries_by_region <- colMeans(is.na(salaries_by_region)) * 100

df_degrees_that_pay_back <- data.frame(Column = names(degrees_that_pay_back), Missing_Percentage = missing_percentage_degrees_that_pay_back)
df_salaries_by_college_type <- data.frame(Column = names(salaries_by_college_type), Missing_Percentage = missing_percentage_salaries_by_college_type)
df_salaries_by_region <- data.frame(Column = names(salaries_by_region), Missing_Percentage = missing_percentage_salaries_by_region)

combined_df <- bind_rows(
  mutate(df_degrees_that_pay_back, File = "degrees_that_pay_back"),
  mutate(df_salaries_by_college_type, File = "File 2"),
  mutate(df_salaries_by_region, File = "File 3")
)

ggplot(combined_df, aes(x = Column, y = Missing_Percentage, fill = File)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Percentage of Missing Values in Each Column",
       x = "Column",
       y = "Missing Percentage") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))
```

```{r}
library(tidyverse)
library(viridis)
library(tidyverse)
library(reshape2)
# Define function to load CSV files with 'N/A' as missing values
df1 <- read.csv("data//raw_data//salaries-by-college-type.csv", na.strings = "N/A")
df2 <- read.csv("data//raw_data//salaries-by-region.csv", na.strings = "N/A")

# Function to plot missing values
plot_missing_values <- function(df, title) {
  df %>%
    is.na() %>%
    melt() %>%
    ggplot(aes(x = Var2, y = Var1, fill = factor(value, labels = c("Present", "Missing")))) +
    geom_tile(color = "white") +
    scale_fill_manual(values = c("Present" = "Green", "Missing" = "Red")) +
    labs(title = title, x = "Columns", y = "Rows") +
    theme_minimal() +
    theme(axis.text.x = element_text(angle = 45, hjust = 1))+
    guides(fill = guide_legend(title = "Values"))
}

# Plot missing values for each column in the first CSV file
plot_missing_values(df1, "Missing Values in File 1")

# Plot missing values for each column in the second CSV file
plot_missing_values(df2, "Missing Values in File 2")
```

#### Observations from the graph

In both the tables, the missing values of both columns are occuring in the same rows.

## Cleaning up data and saving the cleaned csv files

```{r}
degrees_that_pay_back <- read.csv("data//raw_data//degrees-that-pay-back.csv")

salaries_by_college_type <- read.csv("data//raw_data//salaries-by-college-type.csv")
  
salaries_by_region <- read.csv("data//raw_data//salaries-by-region.csv")
```

```{r}
salaries_col <- salaries_by_college_type
salaries_col <- na.omit(salaries_col)

salaries_reg <- salaries_by_region
salaries_reg <- na.omit(salaries_reg)

degrees <- degrees_that_pay_back
degrees <- na.omit(degrees)
```

```{r}
# install.packages("dplyr")
library(dplyr)

# Converting character strings to numbers for plotting

salaries_col <- salaries_col %>%
  mutate_at(vars(Mid.Career.Median.Salary, Mid.Career.10th.Percentile.Salary, Mid.Career.25th.Percentile.Salary, Mid.Career.75th.Percentile.Salary, Mid.Career.90th.Percentile.Salary, Starting.Median.Salary), function(x) as.numeric(gsub("[\\$,]", "", x)))

salaries_reg <- salaries_reg %>%
  mutate_at(vars(Mid.Career.Median.Salary, Mid.Career.10th.Percentile.Salary, Mid.Career.25th.Percentile.Salary, Mid.Career.75th.Percentile.Salary, Mid.Career.90th.Percentile.Salary, Starting.Median.Salary), function(x) as.numeric(gsub("[\\$,]", "", x)))

degrees <- degrees %>%
  mutate_at(vars(Mid.Career.Median.Salary, Mid.Career.10th.Percentile.Salary, Mid.Career.25th.Percentile.Salary, Mid.Career.75th.Percentile.Salary, Mid.Career.90th.Percentile.Salary, Starting.Median.Salary), function(x) as.numeric(gsub("[\\$,]", "", x)))
```

```{r}
write.csv(degrees, "data//cleaned_data//degrees-that-pay-back-cleaned.csv", row.names = FALSE)

write.csv(salaries_col, "data//cleaned_data//salaries-by-college-type-cleaned.csv", row.names = FALSE)

write.csv(salaries_reg, "data//cleaned_data//salaries-by-region-cleaned.csv", row.names = FALSE)
```