library(tidyverse)
library(janitor)
bihar <- read_csv("C:/Users/HP-LT/OneDrive - National Law School of India University Bangalore/Documents/groupA.xlsx - Sheet1.csv") |>
clean_names()
# helper: recode 1/2 status columns, sending anything else to an explicit "Unknown"
recode_status <- function(x) {
case_when(
x == 1 ~ "Available",
x == 2 ~ "Not available",
TRUE ~ "Unknown"
)
}
analysis_cols <- c(
"total_population_of_village", "total_male_population_of_village",
"total_female_population_of_village", "nearest_statutory_town_distance_in_km",
"govt_primary_school_status_a_1_na_2", "govt_secondary_school_status_a_1_na_2",
"tap_water_treated_status_a_1_na_2", "covered_well_status_a_1_na_2",
"hand_pump_status_a_1_na_2", "tube_wells_borehole_status_a_1_na_2",
"river_canal_status_a_1_na_2", "black_topped_pucca_road_status_a_1_na_2"
)
n_total <- nrow(bihar)
n_uninhab <- sum(bihar$total_population_of_village == 0)
bihar_inhab <- bihar |> filter(total_population_of_village > 0)
n_inhab <- nrow(bihar_inhab)
# how many missing values remain in the analysis columns once uninhabited villages are gone
n_na_inhab <- sum(is.na(bihar_inhab[analysis_cols]))Bihar Villages, By the Numbers
A data profile using the 2011 Village Directory
Setup
bihar |>
mutate(occupancy = if_else(total_population_of_village == 0,
"Uninhabited (empty)", "Inhabited")) |>
count(occupancy) |>
ggplot(aes(x = occupancy, y = n, fill = occupancy)) +
geom_col(width = 0.6) +
geom_text(aes(label = n), vjust = -0.4, size = 4) +
scale_fill_brewer(palette = "Set2") +
labs(x = NULL, y = "Number of villages") +
guides(fill = "none") +
theme_minimal()
The sample contains 448 villages, of which 58 have a recorded population of zero. These are uninhabited villages (forest blocks and the like) that are blank on every other measured column, so I set them aside and work from the 390 inhabited villages. Because those 58 empty rows were the only source of missingness, excluding them leaves 0 missing values across the columns analysed below — every statistic that follows is therefore based on all 390 inhabited villages unless stated otherwise.
Task 1 — The Village Profile
pop <- bihar_inhab$total_population_of_village
n_pop <- sum(!is.na(pop))
pop_mean <- mean(pop, na.rm = TRUE)
pop_median <- median(pop, na.rm = TRUE)
pop_sd <- sd(pop, na.rm = TRUE)
pop_iqr <- IQR(pop, na.rm = TRUE)
pop_mode <- as.numeric(names(which.max(table(pop))))
skew_word <- if (pop_mean > pop_median * 1.05) {
"right-skewed"
} else if (pop_mean < pop_median * 0.95) {
"left-skewed"
} else {
"roughly symmetric"
}
bihar_inhab <- bihar_inhab |>
mutate(sex_ratio = total_female_population_of_village /
total_male_population_of_village * 1000)
n_sex <- sum(!is.na(bihar_inhab$sex_ratio))
sex_ratio_mean <- mean(bihar_inhab$sex_ratio, na.rm = TRUE)
tibble(n = n_pop, mean = pop_mean, median = pop_median,
sd = pop_sd, IQR = pop_iqr, mode = pop_mode)# A tibble: 1 × 6
n mean median sd IQR mode
<int> <dbl> <dbl> <dbl> <dbl> <dbl>
1 390 2349. 1466. 2879. 2191. 513ggplot(bihar_inhab, aes(x = total_population_of_village)) +
geom_histogram(bins = 30, fill = "#66C2A5", colour = "white") +
labs(x = "Village population", y = "Number of villages") +
theme_minimal()
Our typical village has about 1466 people (using the median as the measure of “typical”, because the distribution is right-skewed and the mean of 2349 is dragged upward by a handful of very large villages; based on n = 390 villages with non-missing data). The population distribution is right-skewed. The sex ratio in our sample averages 928 females per 1,000 males (n = 390).
Task 2 — Population and School Access
bihar_inhab <- bihar_inhab |>
mutate(sec_school = recode_status(govt_secondary_school_status_a_1_na_2))
sec_counts <- bihar_inhab |> count(sec_school)
sec_summary <- bihar_inhab |>
filter(sec_school != "Unknown") |>
group_by(sec_school) |>
summarise(median_pop = median(total_population_of_village), n = n())
med_with <- sec_summary$median_pop[sec_summary$sec_school == "Available"]
med_without <- sec_summary$median_pop[sec_summary$sec_school == "Not available"]
n_unknown_sec <- sum(bihar_inhab$sec_school == "Unknown")
sec_counts# A tibble: 2 × 2
sec_school n
<chr> <int>
1 Available 52
2 Not available 338sec_summary# A tibble: 2 × 3
sec_school median_pop n
<chr> <dbl> <int>
1 Available 2672 52
2 Not available 1301 338bihar_inhab |>
filter(sec_school != "Unknown") |>
ggplot(aes(x = sec_school, y = total_population_of_village, fill = sec_school)) +
geom_boxplot(alpha = 0.8) +
scale_fill_brewer(palette = "Set2") +
labs(x = "Government secondary school", y = "Village population") +
guides(fill = "none") +
theme_minimal()
Villages with a government secondary school have a median population of 2672, compared with 1301 for villages without one (0 villages were Unknown/missing on this variable and excluded). Both groups have high-population outliers, and the with-school group’s box sits clearly higher — larger villages are more likely to have a secondary school.
Task 3 — Size and Distance from Town
n_dist <- bihar_inhab |>
filter(!is.na(total_population_of_village),
!is.na(nearest_statutory_town_distance_in_km)) |>
nrow()
dist_cor <- cor(bihar_inhab$total_population_of_village,
bihar_inhab$nearest_statutory_town_distance_in_km,
use = "complete.obs")
rel_word <- if (dist_cor <= -0.2) {
"tend to be closer to"
} else if (dist_cor >= 0.2) {
"tend to be farther from"
} else {
"show no clear relationship with"
}
outlier <- bihar_inhab |> slice_max(nearest_statutory_town_distance_in_km, n = 1)
outlier_name <- outlier$village_name
outlier_dist <- outlier$nearest_statutory_town_distance_in_km
outlier_pop <- outlier$total_population_of_villagebihar_inhab |>
filter(sec_school != "Unknown") |>
ggplot(aes(x = total_population_of_village,
y = nearest_statutory_town_distance_in_km,
colour = sec_school)) +
geom_point(alpha = 0.8) +
scale_colour_brewer(palette = "Set2") +
labs(x = "Village population", y = "Distance to nearest town (km)",
colour = "Secondary school") +
theme_minimal()
Looking at the scatter plot, larger villages show no clear relationship with the nearest town (Pearson r = 0, based on n = 390 villages with both variables present). One outlier worth noting is Moradih, which stands out because it sits 75 km from the nearest town — the farthest in the sample — despite a population of only 1,799.
Task 4 — Do Schools and Roads Travel Together?
bihar_inhab <- bihar_inhab |>
mutate(
prim_school = recode_status(govt_primary_school_status_a_1_na_2),
paved_road = recode_status(black_topped_pucca_road_status_a_1_na_2)
)
xtab_all <- bihar_inhab |> count(paved_road, prim_school)
known <- bihar_inhab |>
filter(prim_school != "Unknown", paved_road != "Unknown")
row_pct <- known |>
group_by(paved_road) |>
summarise(pct_primary = mean(prim_school == "Available") * 100, n = n())
pct_road_yes <- row_pct$pct_primary[row_pct$paved_road == "Available"]
pct_road_no <- row_pct$pct_primary[row_pct$paved_road == "Not available"]
n_excl_4 <- nrow(bihar_inhab) - nrow(known)
xtab_all# A tibble: 4 × 3
paved_road prim_school n
<chr> <chr> <int>
1 Available Available 218
2 Available Not available 26
3 Not available Available 114
4 Not available Not available 32row_pct# A tibble: 2 × 3
paved_road pct_primary n
<chr> <dbl> <int>
1 Available 89.3 244
2 Not available 78.1 146known |>
count(paved_road, prim_school) |>
ggplot(aes(x = paved_road, y = n, fill = prim_school)) +
geom_col(position = "fill", alpha = 0.9) +
scale_y_continuous(labels = scales::percent) +
scale_fill_brewer(palette = "Set2") +
labs(x = "Paved (black-topped) road", y = "Share of villages",
fill = "Primary school") +
theme_minimal()
Among villages with a black-topped road, 89% also have a government primary school. Among villages without a paved road, 78% have one. (0 villages were Unknown on one or both variables and were excluded.) Primary schools are near-universal in both groups, so in this sample school access and road access are largely independent — a village doesn’t need a paved road to have a school.
Task 5 — What Do Villages Actually Drink?
water <- bihar_inhab |>
transmute(
`Treated tap water` = recode_status(tap_water_treated_status_a_1_na_2),
`Hand pump` = recode_status(hand_pump_status_a_1_na_2),
`Tube well/borehole` = recode_status(tube_wells_borehole_status_a_1_na_2),
`Covered well` = recode_status(covered_well_status_a_1_na_2),
`River/canal` = recode_status(river_canal_status_a_1_na_2)
) |>
pivot_longer(everything(), names_to = "source", values_to = "status") |>
mutate(source = factor(source, levels = c(
"Treated tap water", "Hand pump", "Tube well/borehole",
"Covered well", "River/canal"
)))
water_pct <- water |>
filter(status != "Unknown") |>
group_by(source) |>
summarise(pct_available = mean(status == "Available") * 100, n = n()) |>
arrange(desc(pct_available))
most_source <- as.character(water_pct$source[1])
most_pct <- water_pct$pct_available[1]
least_source <- as.character(water_pct$source[nrow(water_pct)])
least_pct <- water_pct$pct_available[nrow(water_pct)]
water_unknown_pct <- bihar_inhab |>
transmute(any_unknown = if_any(
all_of(c("tap_water_treated_status_a_1_na_2", "hand_pump_status_a_1_na_2",
"tube_wells_borehole_status_a_1_na_2", "covered_well_status_a_1_na_2",
"river_canal_status_a_1_na_2")),
~ recode_status(.x) == "Unknown")) |>
summarise(p = mean(any_unknown) * 100) |>
pull(p)
water_pct# A tibble: 5 × 3
source pct_available n
<fct> <dbl> <int>
1 Hand pump 100 390
2 Tube well/borehole 42.1 390
3 River/canal 35.9 390
4 Covered well 8.21 390
5 Treated tap water 0 390water |>
filter(status != "Unknown") |>
ggplot(aes(x = source, fill = status)) +
geom_bar(position = "fill", alpha = 0.9) +
scale_y_continuous(labels = scales::percent) +
scale_fill_manual(values = c("Available" = "#66C2A5",
"Not available" = "#FC8D62")) +
labs(x = "Water source", y = "Share of villages", fill = NULL) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 30, hjust = 1))
The most common drinking-water source in our sample is Hand pump (available in 100% of villages); the least common is Treated tap water (available in only 0% of villages). 0% of villages were Unknown on at least one water-source column. This suggests rural Bihar leans almost entirely on groundwater drawn by hand pumps and tube wells, with treated piped supply effectively absent.
Task 6 — Does Distance from Town Shrink as Villages Grow?
qs <- quantile(bihar_inhab$total_population_of_village,
probs = c(0.25, 0.5, 0.75), na.rm = TRUE)
bihar_inhab <- bihar_inhab |>
mutate(pop_quartile = case_when(
total_population_of_village <= qs[1] ~ "Q1 (smallest)",
total_population_of_village <= qs[2] ~ "Q2",
total_population_of_village <= qs[3] ~ "Q3",
TRUE ~ "Q4 (largest)"
),
pop_quartile = factor(pop_quartile,
levels = c("Q1 (smallest)", "Q2", "Q3", "Q4 (largest)")))
quartile_dist <- bihar_inhab |>
group_by(pop_quartile) |>
summarise(mean_dist = mean(nearest_statutory_town_distance_in_km, na.rm = TRUE),
n = n())
d_q1 <- quartile_dist$mean_dist[quartile_dist$pop_quartile == "Q1 (smallest)"]
d_q4 <- quartile_dist$mean_dist[quartile_dist$pop_quartile == "Q4 (largest)"]
trend_word <- if (d_q4 < d_q1 * 0.9) {
"falls"
} else if (d_q4 > d_q1 * 1.1) {
"rises"
} else {
"has no clear trend"
}
quartile_dist# A tibble: 4 × 3
pop_quartile mean_dist n
<fct> <dbl> <int>
1 Q1 (smallest) 24.7 98
2 Q2 22.2 97
3 Q3 18.1 97
4 Q4 (largest) 21.2 98ggplot(quartile_dist, aes(x = pop_quartile, y = mean_dist)) +
geom_line(aes(group = 1), colour = "grey65", linewidth = 0.8) +
geom_point(aes(colour = pop_quartile), size = 4) +
scale_colour_brewer(palette = "Set2") +
labs(x = "Population quartile", y = "Mean distance to nearest town (km)") +
guides(colour = "none") +
theme_minimal()
Moving from the smallest population quartile to the largest, average distance to the nearest town falls, going from about 24.7 km to about 21.2 km.
Finishing reflection
The gap that surprised me most was that treated tap water is recorded as not available in every single inhabited village while hand pumps are available in all of them — a column that looks like a variable but is really a constant. Before trusting any conclusion built on it, I’d want to know whether “treated tap water” was genuinely absent across this district or simply not captured in this edition of the directory, since a coding or coverage gap would tell a very different story from a real one.