# Interpolating missing data given county and state totals

Problem:

I have population data for states and their constituent counties over several years. Each row is uniquely identified by a state/county and a year. There are four population columns: total, white, black, and other. The total column is never missing, but the race columns are randomly missing.

I need to interpolate the missing data such that two criteria hold:

1. The race columns white, black, and other add to the total in each row; and
2. Counties within a state have a vertical column sum equal to the state value (when not missing) in any given year.

MRE:

Suppose state1 has three constituent counties: A, B, and C. Data set:

geography year total white black other
state1 2000 200 50 40 110
state1_countyA 2000 90 20 10 60
state1_countyB 2000 80 30 10 40
state1_countyC 2000 30 0 20 10
state1 2001 240 50 150
state1_countyA 2001 100 30
state1_countyB 2001 90
state1_countyC 2001 50 30
state1 2002 300 80 60 170
state1_countyA 2002 140 30 20 90
state1_countyB 2002 120 50 20 60
state1_countyC 2002 50 0 30 20

I am trying to interpolate missing data in 2001.

I initially tried interpolating the missing data using lag and lead racial shares. Using county A's black population as an example:

i. black share 2000: 10/90 = 11.11%

ii. black share 2002: 20/140 = 14.29%

iii. Interpolated black share 2001: (11.11% + 14.29%) / 2 = 12.70%

iv. Interpolated black count 2001: 12.70% * 100 = 12.70

Repeating this process for county B, we get 13.13. However, the sum of the counties, 12.70 + 13.13 + 30 = 55.83, does not equal the known total for state1, 50.

While my interpolation method meets criterion 1, it fails criterion 2.

Question:

Thus my question arises: Is there a better approach to interpolation here? Alternatively, is there an reasonable-seeming algorithm to adjust the interpolated values so that criterion 2 holds?

Bonus points for an answer written in Stata or R.

The key is to use a method that takes into account the relationship between the counties within a state. One such method is iterative proportional fitting (IPF). IPF is an algorithm that iteratively adjusts the values of a set of variables until they satisfy a set of constraints. In this case, the constraints are that the race columns white, black, and other add to the total in each row, and that counties within a state have a vertical column sum equal to the state value (when not missing) in any given year.

Here is an example of how to use IPF to interpolate the missing data in Stata:

use mydata, clear local states = distinct(state) foreach state in states' { local counties = distinct(state == state', geography) local state_total = total[i.state == state'] foreach county in counties' { local county_total = total[i.state == state' & i.geography == county'] local missing_values = missing(white black other)[i.state == state' & i.geography == county'] if missing_values' > 0 { replace white = . if missing(white)[i.state == state' & i.geography == county'] replace black = . if missing(black)[i.state == state' & i.geography == county'] replace other = . if missing(other)[i.state == state' & i.geography == county'] local weights = (total white black other) ipf weights, total county_total } } } save mydata, replace

Here is an example of how to use IPF to interpolate the missing data in R:

library(ipf)

data <- read.csv("mydata.csv") states <- unique(data$state) for (state in states) { counties <- data$$geography[data$$state == state] state_total <- data$$total[data$$state == state] for (county in counties) { county_data <- data[data$$state == state & data$$geography == county, ] missing_values <- is.na(county_data[, c("white", "black", "other")]) if (any(missing_values)) { county_data[, c("white", "black", "other")] <- ipf(county_data[, c("white", "black", "other")], sum(county_data$total))
}

data[data$$state == state & data$$geography == county, ] <- county_data
}
`

}

write.csv(data, "mydata_interpolated.csv")

Both codes iterate over the states in the dataset. For each state, it will identify the counties in that state and the state total for the missing year. For each county with missing values, it will replace the missing values with interpolated values using IPF. The weights for IPF will be the total population, white population, black population, and other population for the county.

This approach will ensure that the interpolated values meet both criteria 1 and 2.