R Notebook

{r} #Install Packages

```{r} #install.packages(“tidyverse”) #install.packages(“psych”) #install.packages(“ggplot2”) #install.packages(“reshape2”) #install.packages(“corrplot”) #install.packages(“ggcorrplot”) #install.packages(“caret”)

# Libraries
```{r}
library(tidyverse)
library(psych)
library(ggplot2)
library(reshape2)
library(corrplot)
library(ggcorrplot)

Read File

{r} income <- read.csv("XYZ LLC 2021 PL.csv")

View

{r} #view (income)

Structure of the Dataset

{r} str(income)

Remove First 3 Rows

{r} income <- income[-c(1:3), ]

View

{r} #view(income)

View Headers

{r} head(income)

#Column Names {r} print(colnames(income))

#Count Missing Values in Each Column {r} print(sapply(income, function(x) sum(is.na(x)))) # Eliminate the NA Rows {r} income <- na.omit(income)

View

{r} view(income)

Summary

{r} summary(income)

Structure of the New Dataset

{r} str(income)

Change The headers into a date structure

{r} Col_headers <- seq(as.Date("2022-01-01"), as.Date("2023-04-01"), by = "month")

{r} names(income)[-1] <- format(Col_headers, "%B %Y")

Bar Chart

```{r} # Extract the “Net Income” net_income <- as.numeric(income[nrow(income), -1])

Calculate the color of the bars (green if positive, red if negative)

colors <- ifelse(net_income >= 0, “green”, “red”)

Create the bar plot

barplot(net_income, col = colors, # Color the bars based on the value of net_income main = “Net Income”, xlab = “Date”, ylab = “Net Income”, names.arg = colnames(income)[-1], # Use dates as x-axis labels las = 2) # Rotate the x-axis labels

Add a legend

legend(“topright”, legend = c(“Positive”, “Negative”), fill = c(“green”, “red”))

```{r}
# Create the barplot
barplot(net_income, 
        col = colors,  # Color the bars based on the value of net_income
        main = "Net Income", 
        xlab = "Date", 
        ylab = "Net Income",
        names.arg = colnames(income)[-1],  # Use dates as x-axis labels
        las = 2)  # Rotate the x-axis labels

# Add a legend
legend("topright", 
       legend = c("Positive", "Negative"), 
       fill = c("green", "red"))

# Calculate the tendency line
tendency_line <- lm(net_income ~ seq_along(net_income))

# Plot the tendency line
abline(tendency_line, col = "blue", lty = 2)

#Line Chart

```{r} # Extract column names (months) month_names <- colnames(income)[-1] # Exclude the first column containing unit names

Extract values from the Total Rental Income row (excluding the first column)

total_income <- as.numeric(income[8, -1])

Create the line plot

plot(1:length(month_names), total_income, type = “o”, xlab = “Months”, ylab = “Total Rental Income”, main = “Total Rental Income per Month”, xaxt = “n”) # Disable x-axis labels

Add month labels on the x-axis

axis(1, at = 1:length(month_names), labels = month_names, las = 2)

```{r}
# Extract column names (months)
month_names <- colnames(income)[-1]  # Exclude the first column containing unit names

# Extract values from the Total Rental Income row (excluding the first column)
total_income <- as.numeric(income[8, -1])

# Create the line plot with nicer aesthetics
plot(1:length(month_names), total_income, type = "o", 
     xlab = "Months", ylab = "Total Rental Income (in USD)", 
     main = "Total Rental Income per Month",
     col = "blue",            # Line color
     pch = 16,                # Point shape
     lwd = 2,                 # Line width
     ylim = c(0, max(total_income) * 1.1),  # Adjust y-axis limits
     xaxt = "n")              # Disable x-axis labels

# Add month labels on the x-axis with rotated labels
axis(1, at = 1:length(month_names), labels = month_names, las = 2, cex.axis = 0.8)

# Add gridlines
grid()

# Add a legend
legend("topright", 
       legend = c("Total Rental Income"), 
       col = "blue", 
       lty = 1, 
       pch = 16, 
       cex = 0.8,
       bg = "white")

Bar Expenses Chart

{r} # Transponer el conjunto de datos para que las filas se conviertan en columnas transposed_data <- t(income) #view(transposed_data)

{r} colnames(transposed_data) <- transposed_data[1, ] transposed_data <- transposed_data[-1, ]

{r} #view(transposed_data)

```{r} library(ggplot2)

Select only the rows corresponding to expenses

expenses <- transposed_data[, c(“Bright Star Credit Union”, “Bank Charges - Other”, “Total Commission”, “Filing Fees”,“Total Electricity”, “Total Utilities”)]

Convert the selected columns to numeric

expenses <- apply(expenses, 2, as.numeric)

Calculate the sum of each expense column

total_expenses <- colSums(expenses)

Create a dataframe with the expense names and their totals

expenses_df <- data.frame(expense = names(total_expenses), total = total_expenses)

Create the bar plot with labels

ggplot(expenses_df, aes(x = expense, y = total, label = total)) + geom_bar(stat = “identity”, fill = “skyblue”) + geom_text(vjust = -0.5, size = 3) + # Add labels above the bars labs(title = “Total Expenses”, x = “Expense”, y = “Total”) + theme_minimal() + theme(axis.text.x = element_text(angle = 45, hjust = 1)) # Rotate x-axis labels for better readability

```{r}
# Ensure both expenses and net income have the same number of months
num_months <- min(length(month_names), length(total_expenses), length(total_income))

# Combine expenses and net income into a single dataframe
combined_data <- data.frame(
  Month = month_names[1:num_months],
  Expenses = total_expenses[1:num_months],
  Net_Income = total_income[1:num_months]
)

# Melt the data for easier plotting
combined_data <- melt(combined_data, id.vars = "Month")

# Create the combined bar plot
ggplot(combined_data, aes(x = Month, y = value, fill = variable)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Expenses vs Net Income", x = "Month", y = "Amount") +
  scale_fill_manual(values = c("Expenses" = "skyblue", "Net_Income" = "green"), 
                    labels = c("Expenses", "Net Income")) +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))  # Rotate x-axis labels for better readability

{r} # Extract relevant variables (expenses and income of the units) expenses <- transposed_data[, c("Bright Star Credit Union", "Bank Charges - Other", "Total Commission", "Filing Fees", "Total Electricity", "Total Utilities")] income_units <- transposed_data[, c("Unit 1115", "Unit 1126", "Unit 1215")]

{r} #View(income_units)

{r} expenses <- apply(expenses, 2, as.numeric) income_units <- apply(income_units, 2, as.numeric)

{r} # Calculate net income (total income - total expenses) net_income <- rowSums(income_units) - rowSums(expenses)

{r} # Create a data frame with predictor variables (expenses and income of the units) and the target variable (net income) model_data <- cbind(expenses, income_units, net_income) colnames(model_data) <- c("Bright_Star_Credit_Union", "Bank_Charges_Other", "Total_Commission", "Filing_Fees", "Total_Electricity", "Total_Utilities", "Unit_1115", "Unit_1126", "Unit_1215", "Net_Income")

{r} #install.packages("caret") library(caret) {r} class(model_data) {r} if (!inherits(model_data, "data.frame")) { model_data <- as.data.frame(model_data) }

{r} # Split the data into training and testing sets (80% training, 20% testing) set.seed(123) # for reproducibility train_index <- createDataPartition(model_data$Net_Income, p = 0.8, list = FALSE) train_data <- model_data[train_index, ] test_data <- model_data[-train_index, ]

{r} # Build a linear regression model net_income_model <- lm(Net_Income ~ ., data = train_data)

{r} # Evaluate the model summary(net_income_model) {r} # Make predictions on the testing set predictions <- predict(net_income_model, newdata = test_data)

{r} # Evaluate model performance (e.g., RMSE, R-squared) rmse <- sqrt(mean((test_data$Net_Income - predictions)^2)) rsquared <- cor(test_data$Net_Income, predictions)^2

{r} # Print RMSE and R-squared print(paste("RMSE:", rmse)) print(paste("R-squared:", rsquared))

{r} # Calculate residuals residuals <- residuals(net_income_model)

{r} predicted_values <- predict(net_income_model)

{r} # Create residual plot plot(predicted_values, residuals, xlab = "Predicted Values", ylab = "Residuals", main = "Residual Plot") abline(h = 0, col = "red") # Add horizontal line at y = 0

{r} # Create QQ plot qqnorm(residuals) qqline(residuals) {r} varImp(net_income_model)

```{r}

```{r}
# Ensure both expenses and net income have the same number of months
num_months <- min(length(month_names), length(total_expenses), length(total_income))

# Combine expenses and net income into a single dataframe
combined_data <- data.frame(
  Month = month_names[1:num_months],
  Expenses = total_expenses[1:num_months],
  Net_Income = total_income[1:num_months]
)

# Calculate the percentage of expenses over net income
combined_data$Expense_Percentage <- (combined_data$Expenses / combined_data$Net_Income) * 100

# Melt the data for easier plotting
combined_data <- melt(combined_data, id.vars = c("Month", "Expense_Percentage"))

# Create the combined bar plot with percentage labels
ggplot(combined_data, aes(x = Month, y = value, fill = variable)) +
  geom_bar(stat = "identity", position = "dodge") +
  geom_text(aes(label = paste(round(Expense_Percentage, 1), "%")), 
            position = position_dodge(width = 1), 
            vjust = -0.5, size = 3) +  # Add percentage labels above the bars
  labs(title = "Expenses vs Net Income", x = "Month", y = "Amount") +
  scale_fill_manual(values = c("Expenses" = "skyblue", "Net_Income" = "green"), 
                    labels = c("Expenses", "Net Income")) +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))  # Rotate x-axis labels for better readability