The Grid Under Pressure
Comparing Underground Feeder Demand and Capacity
Mohd Afwan Shaikh
Introduction
For my third dataset, I selected underground electrical feeder data from National Grid’s New York System Data Portal. Electricity demand can change from year to year, and understanding how heavily electrical feeders are used is important for evaluating infrastructure capacity.
I want to investigate which underground feeders experienced increases in peak demand and which were operating closest to their reported summer ratings.
Data Source
The dataset comes from National Grid’s New York System Data Portal, using the underground feeder layer.
- Dataset: National Grid Underground Feeders (UG)
- Source: National Grid New York System Data Portal
The downloaded dataset contains 87 records and 14 attributes, including feeder identifiers, substation information, operating voltage, summer ratings, peak amperage, and percentage-of-rating values for the current and previous reporting periods.
The CSV contains feeder attributes rather than geographic line geometry.
Research Question
Which underground feeders experienced the greatest increase in peak electrical demand between the previous and current reporting periods, and which feeders were closest to their summer capacity ratings?
Data Tidying Approach
The original dataset was in wide format because peak amperage and percentage-of-rating measurements for the current and previous reporting periods were stored in separate columns.
I used pivot_longer() from the tidyr
package to reorganize these measurements into a long format with
variables such as Period, PeakAmps, and
PercentRating.
I also used dplyr to select and rename important
columns, check for missing values, and identify duplicate feeder IDs.
The dataset contained no missing values or duplicate feeder
identifiers.
I preserved the original CSV and completed the cleaning in R. The tidy dataset contained 174 rows, representing two reporting periods for each of the 87 feeders.
Analysis Approach
I calculated the difference in peak amperage between the previous and current reporting periods for each feeder. I also compared each feeder’s reported percentage of summer capacity.
I identified the feeders with the largest increases in peak demand and those operating closest to their summer ratings.
I created bar charts to compare demand changes and capacity utilization. I also created a scatterplot to examine the relationship between demand growth and capacity utilization.
Data Preparation and Exploration
Load Required Packages
library(tidyverse)
library(knitr)Import the Original Dataset
grid_raw <- read_csv(
"data/national_grid_ug_wide.csv",
show_col_types = FALSE
)
dim(grid_raw)## [1] 87 14names(grid_raw)## [1] "OBJECTID" "MASTER_CDF"
## [3] "SUBSTATION_ID" "SUBSTATION_NAME"
## [5] "OPERATING_VOLTAGE" "FEEDER_SN_RATING_AMPS"
## [7] "SHAPE_Length" "PEAK_AMPS_CURRENT_YEAR"
## [9] "PCT_RATING_CURRENT_YEAR" "PEAK_AMPS_LAST_YEAR"
## [11] "PCT_RATING_LAST_YEAR" "Construction"
## [13] "Historical_Load_Curve_Extract" "Forecast_Load_Curve_Extract"head(grid_raw, 10) %>%
kable(caption = "Original National Grid Underground Feeder Data")Examine the Data Structure
glimpse(grid_raw)## Rows: 87
## Columns: 14
## $ OBJECTID <dbl> 81, 82, 83, 84, 217, 239, 280, 309, 360,…
## $ MASTER_CDF <chr> "36_01_16261", "36_01_16262", "36_01_162…
## $ SUBSTATION_ID <dbl> 318214323, 318214323, 318214323, 3199042…
## $ SUBSTATION_NAME <chr> "162 STATION 162", "162 STATION 162", "1…
## $ OPERATING_VOLTAGE <dbl> 4.16, 4.16, 4.16, 13.20, 4.16, 4.16, 4.1…
## $ FEEDER_SN_RATING_AMPS <dbl> 266, 266, 266, 313, 300, 266, 256, 300, …
## $ SHAPE_Length <dbl> 724.2283, 194.1960, 605.2643, 2297.0247,…
## $ PEAK_AMPS_CURRENT_YEAR <dbl> 50.00, 50.00, 50.00, 40.33, 33.00, 100.0…
## $ PCT_RATING_CURRENT_YEAR <dbl> 18.80, 18.80, 18.80, 12.88, 11.00, 37.59…
## $ PEAK_AMPS_LAST_YEAR <dbl> 50.000000, 50.000000, 50.000000, 60.0000…
## $ PCT_RATING_LAST_YEAR <dbl> 18.80, 18.80, 18.80, 19.17, 0.01, 25.06,…
## $ Construction <chr> "UG", "UG", "UG", "UG", "UG", "UG", "UG"…
## $ Historical_Load_Curve_Extract <chr> "https://systemdataportal.nationalgrid.c…
## $ Forecast_Load_Curve_Extract <chr> "https://systemdataportal.nationalgrid.c…colSums(is.na(grid_raw))## OBJECTID MASTER_CDF
## 0 0
## SUBSTATION_ID SUBSTATION_NAME
## 0 0
## OPERATING_VOLTAGE FEEDER_SN_RATING_AMPS
## 0 0
## SHAPE_Length PEAK_AMPS_CURRENT_YEAR
## 0 0
## PCT_RATING_CURRENT_YEAR PEAK_AMPS_LAST_YEAR
## 0 0
## PCT_RATING_LAST_YEAR Construction
## 0 0
## Historical_Load_Curve_Extract Forecast_Load_Curve_Extract
## 0 0grid_raw %>%
summarise(
TotalRows = n(),
DuplicateRows = sum(duplicated(.))
) %>%
kable(caption = "Initial Data Quality Check")| TotalRows | DuplicateRows |
|---|---|
| 87 | 0 |
Check Missing Values and Feeder Identifiers
Before cleaning the dataset, I examined missing values and checked whether feeder identifiers appeared more than once.
# Missing values
colSums(is.na(grid_raw))## OBJECTID MASTER_CDF
## 0 0
## SUBSTATION_ID SUBSTATION_NAME
## 0 0
## OPERATING_VOLTAGE FEEDER_SN_RATING_AMPS
## 0 0
## SHAPE_Length PEAK_AMPS_CURRENT_YEAR
## 0 0
## PCT_RATING_CURRENT_YEAR PEAK_AMPS_LAST_YEAR
## 0 0
## PCT_RATING_LAST_YEAR Construction
## 0 0
## Historical_Load_Curve_Extract Forecast_Load_Curve_Extract
## 0 0# Check duplicate feeder identifiers
grid_raw %>%
count(MASTER_CDF, sort = TRUE) %>%
filter(n > 1) %>%
kable(caption = "Repeated Feeder Identifiers")| MASTER_CDF | n |
|---|
# Check important measurement columns
grid_raw %>%
select(
MASTER_CDF,
SUBSTATION_NAME,
FEEDER_SN_RATING_AMPS,
PEAK_AMPS_CURRENT_YEAR,
PCT_RATING_CURRENT_YEAR,
PEAK_AMPS_LAST_YEAR,
PCT_RATING_LAST_YEAR
) %>%
head(10) %>%
kable(caption = "Feeder Demand and Capacity Measurements")| MASTER_CDF | SUBSTATION_NAME | FEEDER_SN_RATING_AMPS | PEAK_AMPS_CURRENT_YEAR | PCT_RATING_CURRENT_YEAR | PEAK_AMPS_LAST_YEAR | PCT_RATING_LAST_YEAR |
|---|---|---|---|---|---|---|
| 36_01_16261 | 162 STATION 162 | 266 | 50.00 | 18.80 | 50.000000 | 18.80 |
| 36_01_16262 | 162 STATION 162 | 266 | 50.00 | 18.80 | 50.000000 | 18.80 |
| 36_01_16263 | 162 STATION 162 | 266 | 50.00 | 18.80 | 50.000000 | 18.80 |
| 36_01_20552 | 205 ERIE DRIVE | 313 | 40.33 | 12.88 | 60.000000 | 19.17 |
| 36_01_2961 | 29 POTOMAC | 300 | 33.00 | 11.00 | 0.020000 | 0.01 |
| 36_01_3067 | 30 SPILLMAN | 266 | 100.00 | 37.59 | 66.666664 | 25.06 |
| 36_01_3471 | 34 BEST ST. | 256 | 165.00 | 64.45 | 127.430000 | 49.78 |
| 36_01_3767 | 37 HUDSON AVE | 300 | 94.00 | 31.33 | 15.683333 | 5.23 |
| 36_01_4169 | 41 STATION 41 | 247 | 13.00 | 5.26 | 9.083333 | 3.68 |
| 36_01_4664 | 46 STATION 46 | 252 | 14.33 | 5.69 | 13.976667 | 5.55 |
Cleaning the National Grid Dataset
The original dataset contained 87 records and 14 columns. I checked for missing values and repeated feeder identifiers. There were no missing values, and each feeder identifier was unique.
The peak amperage and percentage-of-rating columns were already numeric, so no additional numeric conversion was needed. I selected the important columns and renamed them to make the analysis easier.
grid_clean <- grid_raw %>%
transmute(
FeederID = MASTER_CDF,
Substation = SUBSTATION_NAME,
Voltage = OPERATING_VOLTAGE,
SummerRating = FEEDER_SN_RATING_AMPS,
PeakAmpsCurrent = PEAK_AMPS_CURRENT_YEAR,
PeakAmpsLast = PEAK_AMPS_LAST_YEAR,
PercentRatingCurrent = PCT_RATING_CURRENT_YEAR,
PercentRatingLast = PCT_RATING_LAST_YEAR
)
dim(grid_clean)## [1] 87 8head(grid_clean, 10) %>%
kable(caption = "Cleaned National Grid Feeder Dataset")| FeederID | Substation | Voltage | SummerRating | PeakAmpsCurrent | PeakAmpsLast | PercentRatingCurrent | PercentRatingLast |
|---|---|---|---|---|---|---|---|
| 36_01_16261 | 162 STATION 162 | 4.16 | 266 | 50.00 | 50.000000 | 18.80 | 18.80 |
| 36_01_16262 | 162 STATION 162 | 4.16 | 266 | 50.00 | 50.000000 | 18.80 | 18.80 |
| 36_01_16263 | 162 STATION 162 | 4.16 | 266 | 50.00 | 50.000000 | 18.80 | 18.80 |
| 36_01_20552 | 205 ERIE DRIVE | 13.20 | 313 | 40.33 | 60.000000 | 12.88 | 19.17 |
| 36_01_2961 | 29 POTOMAC | 4.16 | 300 | 33.00 | 0.020000 | 11.00 | 0.01 |
| 36_01_3067 | 30 SPILLMAN | 4.16 | 266 | 100.00 | 66.666664 | 37.59 | 25.06 |
| 36_01_3471 | 34 BEST ST. | 4.16 | 256 | 165.00 | 127.430000 | 64.45 | 49.78 |
| 36_01_3767 | 37 HUDSON AVE | 4.16 | 300 | 94.00 | 15.683333 | 31.33 | 5.23 |
| 36_01_4169 | 41 STATION 41 | 4.16 | 247 | 13.00 | 9.083333 | 5.26 | 3.68 |
| 36_01_4664 | 46 STATION 46 | 4.16 | 252 | 14.33 | 13.976667 | 5.69 | 5.55 |
Tidying the National Grid Dataset
The original dataset stored current-year and previous-year
measurements in separate columns. I used pivot_longer() to
create a tidy dataset with one row for each feeder and reporting
period.
This made it easier to compare peak amperage and percentage of summer rating across the two periods.
grid_tidy <- grid_clean %>%
pivot_longer(
cols = c(
PeakAmpsCurrent,
PeakAmpsLast,
PercentRatingCurrent,
PercentRatingLast
),
names_to = c(".value", "Period"),
names_pattern = "(PeakAmps|PercentRating)(Current|Last)"
) %>%
mutate(
Period = recode(
Period,
"Last" = "Previous Year",
"Current" = "Current Year"
),
Period = factor(
Period,
levels = c("Previous Year", "Current Year")
)
) %>%
arrange(FeederID, Period)
dim(grid_tidy)## [1] 174 7head(grid_tidy, 10) %>%
kable(caption = "Tidy National Grid Feeder Dataset")| FeederID | Substation | Voltage | SummerRating | Period | PeakAmps | PercentRating |
|---|---|---|---|---|---|---|
| 36_01_16261 | 162 STATION 162 | 4.16 | 266 | Previous Year | 50.00 | 18.80 |
| 36_01_16261 | 162 STATION 162 | 4.16 | 266 | Current Year | 50.00 | 18.80 |
| 36_01_16262 | 162 STATION 162 | 4.16 | 266 | Previous Year | 50.00 | 18.80 |
| 36_01_16262 | 162 STATION 162 | 4.16 | 266 | Current Year | 50.00 | 18.80 |
| 36_01_16263 | 162 STATION 162 | 4.16 | 266 | Previous Year | 50.00 | 18.80 |
| 36_01_16263 | 162 STATION 162 | 4.16 | 266 | Current Year | 50.00 | 18.80 |
| 36_01_20552 | 205 ERIE DRIVE | 13.20 | 313 | Previous Year | 60.00 | 19.17 |
| 36_01_20552 | 205 ERIE DRIVE | 13.20 | 313 | Current Year | 40.33 | 12.88 |
| 36_01_2961 | 29 POTOMAC | 4.16 | 300 | Previous Year | 0.02 | 0.01 |
| 36_01_2961 | 29 POTOMAC | 4.16 | 300 | Current Year | 33.00 | 11.00 |
Calculating Changes in Peak Demand
I calculated the difference between current-year and previous-year peak amperage for each feeder. A positive value indicates an increase in peak demand, while a negative value indicates a decrease.
I also calculated the change in percentage of summer rating to compare capacity utilization across the two periods.
grid_changes <- grid_clean %>%
mutate(
PeakAmpsChange = PeakAmpsCurrent - PeakAmpsLast,
PercentRatingChange = PercentRatingCurrent - PercentRatingLast
)
grid_changes %>%
arrange(desc(PeakAmpsChange)) %>%
select(
FeederID,
Substation,
PeakAmpsLast,
PeakAmpsCurrent,
PeakAmpsChange,
PercentRatingCurrent
) %>%
head(10) %>%
kable(
digits = 2,
caption = "Feeders with the Largest Increases in Peak Amperage"
)| FeederID | Substation | PeakAmpsLast | PeakAmpsCurrent | PeakAmpsChange | PercentRatingCurrent |
|---|---|---|---|---|---|
| 36_11_24352 | TEMPLE ST. | 40.86 | 322.00 | 281.14 | 74.88 |
| 36_11_24354 | TEMPLE ST. | 0.00 | 140.00 | 140.00 | 51.47 |
| 36_01_3767 | 37 HUDSON AVE | 15.68 | 94.00 | 78.32 | 31.33 |
| 36_01_3471 | 34 BEST ST. | 127.43 | 165.00 | 37.57 | 64.45 |
| 36_01_3067 | 30 SPILLMAN | 66.67 | 100.00 | 33.33 | 37.59 |
| 36_01_2961 | 29 POTOMAC | 0.02 | 33.00 | 32.98 | 11.00 |
| 36_11_24358 | TEMPLE ST. | 0.00 | 32.00 | 32.00 | 9.88 |
| 36_17_65146 | TERMINA | 73.01 | 102.00 | 28.99 | 28.49 |
| 36_30_16406 | TRINITY | 156.24 | 176.00 | 19.76 | 55.35 |
| 36_17_65145 | TERMINA | 62.75 | 80.33 | 17.58 | 22.76 |
Data Visualization
Visualization 1: Largest Increases in Peak Demand
This visualization shows the ten underground feeders with the largest increases in peak amperage between the previous and current reporting periods.
grid_top10 <- grid_changes %>%
arrange(desc(PeakAmpsChange)) %>%
slice_head(n = 10)
ggplot(
grid_top10,
aes(
x = reorder(FeederID, PeakAmpsChange),
y = PeakAmpsChange,
fill = Substation
)
) +
geom_col() +
geom_text(
aes(label = round(PeakAmpsChange, 1)),
hjust = -0.1,
size = 3.5
) +
coord_flip() +
scale_y_continuous(
expand = expansion(mult = c(0, 0.15))
) +
labs(
title = "Underground Feeders with the Largest Demand Increases",
subtitle = "Change in peak amperage between reporting periods",
x = "Feeder ID",
y = "Increase in Peak Amperage (Amps)",
fill = "Substation"
) +
theme_minimal()
This chart shows that feeder 36_11_24352 at TEMPLE ST. had the largest increase in peak amperage, at approximately 281 amps. Another feeder at the same substation, 36_11_24354, had the second-largest increase at approximately 140 amps.
The results show that peak demand changes were not the same across all feeders. Some experienced much larger increases than others.
Visualization 2: Feeders Closest to Summer Capacity
This visualization compares the ten underground feeders with the highest reported current-year percentage of summer rating.
grid_capacity_top10 <- grid_changes %>%
arrange(desc(PercentRatingCurrent)) %>%
slice_head(n = 10)
ggplot(
grid_capacity_top10,
aes(
x = reorder(FeederID, PercentRatingCurrent),
y = PercentRatingCurrent,
fill = Substation
)
) +
geom_col() +
geom_text(
aes(label = paste0(round(PercentRatingCurrent, 1), "%")),
hjust = -0.1,
size = 3.5
) +
coord_flip() +
scale_y_continuous(
limits = c(0, 100)
) +
labs(
title = "Underground Feeders Closest to Summer Capacity",
subtitle = "Top 10 feeders by current-year percentage of summer rating",
x = "Feeder ID",
y = "Summer Rating Utilization (%)",
fill = "Substation"
) +
theme_minimal()
This chart shows that feeder 36_03_21557 at BUFF AVE STA had the highest reported summer-rating utilization at approximately 82.2%. Feeder 36_11_24352 at TEMPLE ST. followed at approximately 74.9%.
Although these feeders had relatively high utilization compared with the others in the dataset, neither reached 100% of its reported summer rating.
Visualization 3: Demand Growth vs. Capacity Utilization
This visualization compares the change in peak amperage with the current percentage of summer rating for each underground feeder.
ggplot(
grid_changes,
aes(
x = PeakAmpsChange,
y = PercentRatingCurrent
)
) +
geom_point(
color = "steelblue",
alpha = 0.7,
size = 3
) +
geom_smooth(
method = "lm",
se = FALSE,
color = "darkred",
linetype = "dashed"
) +
geom_vline(
xintercept = 0,
linetype = "dotted"
) +
labs(
title = "Relationship Between Demand Growth and Capacity Utilization",
subtitle = "National Grid underground feeders",
x = "Change in Peak Amperage (Amps)",
y = "Current Summer Rating Utilization (%)"
) +
theme_minimal()## `geom_smooth()` using formula = 'y ~ x'
Each point represents an underground feeder. The horizontal axis shows the change in peak amperage, while the vertical axis shows how much of the reported summer rating was used.
This visualization helps determine whether feeders experiencing larger increases in demand also had higher capacity utilization. The dashed line summarizes the overall relationship, but it does not establish a cause-and-effect relationship.
Visualization 4: Peak Amperage by Reporting Period
This visualization compares peak amperage in the previous and current reporting periods for the ten feeders with the largest increases in demand.
grid_tidy %>%
filter(FeederID %in% grid_top10$FeederID) %>%
ggplot(
aes(
x = reorder(FeederID, PeakAmps, FUN = max),
y = PeakAmps,
fill = Period
)
) +
geom_col(position = "dodge") +
coord_flip() +
labs(
title = "Peak Amperage Comparison by Reporting Period",
subtitle = "Ten feeders with the largest increases in peak demand",
x = "Feeder ID",
y = "Peak Amperage (Amps)",
fill = "Reporting Period"
) +
theme_minimal()
This grouped bar chart compares peak amperage measurements for the same feeders across two reporting periods. It shows how much demand increased for each feeder and demonstrates how tidying the dataset makes comparisons easier.
Summary Statistics
grid_changes %>%
summarise(
TotalFeeders = n(),
AveragePeakChange = mean(PeakAmpsChange),
MaximumPeakIncrease = max(PeakAmpsChange),
AverageCapacityUsage = mean(PercentRatingCurrent),
MaximumCapacityUsage = max(PercentRatingCurrent)
) %>%
kable(
digits = 2,
caption = "Summary of Underground Feeder Demand and Capacity"
)| TotalFeeders | AveragePeakChange | MaximumPeakIncrease | AverageCapacityUsage | MaximumCapacityUsage |
|---|---|---|---|---|
| 87 | 4.82 | 281.14 | 32.5 | 82.18 |
Results and Findings
The analysis included 87 underground electrical feeders from National Grid. The original dataset contained 14 columns, and after cleaning and tidying, I created a dataset with 174 rows representing two reporting periods for each feeder.
The average change in peak amperage was an increase of 4.82 amps. However, some feeders experienced much larger increases than others.
Feeder 36_11_24352 at TEMPLE ST. had the largest increase in peak demand, approximately 281 amps. Another feeder at TEMPLE ST., 36_11_24354, had the second-largest increase at approximately 140 amps.
For summer capacity utilization, feeder 36_03_21557 at BUFF AVE STA had the highest reported utilization at 82.2%. Feeder 36_11_24352 at TEMPLE ST. was second at 74.9%.
Across all 87 feeders, the average current-year summer-rating utilization was approximately 32.5%. This was much lower than the highest reported utilization of 82.2%, showing that some feeders were operating much closer to their summer ratings than others.
An interesting finding is that feeder 36_11_24352 appeared in both comparisons. It had the largest increase in peak amperage and was also among the feeders closest to its summer rating.
These findings show why it is useful to examine both changes in electrical demand and capacity utilization when comparing underground feeders.
Challenges and Limitations
One limitation of this analysis is that the dataset contains only 87 underground feeders. Therefore, the findings do not represent the entire New York electrical grid.
Another limitation is that the dataset provides measurements for only two reporting periods. This makes it difficult to determine whether the changes are part of a longer-term trend.
The dataset also contains peak amperage and percentage-of-rating measurements but does not explain why demand increased or decreased. Factors such as weather, customer usage, and changes in electrical infrastructure could influence these measurements.
Although the dataset includes feeder identifiers and substation names, the downloaded CSV does not contain geographic line geometry for creating a detailed feeder map.
Finally, the dataset labels its measurements as current year and last year. Without confirming the reporting dates, I cannot assign specific calendar years to these values.
Conclusion
This project helped me understand how electrical demand and infrastructure capacity can be compared using real-world data.
By using dplyr and tidyr, I cleaned the
original National Grid dataset and converted it from wide format into
tidy format. This made it easier to compare peak amperage and
summer-rating utilization across reporting periods.
The results showed that some underground feeders experienced much larger increases in peak demand than others. Feeder 36_11_24352 at TEMPLE ST. had the largest increase, while feeder 36_03_21557 at BUFF AVE STA had the highest reported summer-rating utilization.
Overall, this analysis demonstrates how data cleaning, tidying, and visualization can help identify electrical feeders with relatively high demand growth or capacity utilization. These comparisons can support further investigation, although additional information would be needed to evaluate actual infrastructure risks.