Data transformation - transform data from columns and interpolate missing values

Question

I have data about number of people living in Wrocław:

Pop <- data.frame(Year = c(850, 1000, 1200,  1300,  1350,  1318,  1327,  1329), 
                    Pop  = c(800, 2250, 5000, 13500, 14000, 13600, 12000, 15950))

Is there a way to change it, that each year is separate row and the data is interpolated?

Pop_long <- data.frame(Year = 850:1329, Pop = 850, ....)

The interpolation would be linear. I have done if, but I bet there is a better way:

Pop <- Pop
  mutate(Year_lead = lead(Year),
         Pop_lead  = lead(Pop),
         Year_diff = Year_lead - Year,
         Pop_diff  = Pop_lead  - Pop,
         Pop_add   = Pop_diff / Year_diff) %>%
  select(Year, Pop, Pop_add) 

Pop_long <- data.frame(Year = 850:1329) %>%
  merge(Pop, all.x = T)    

for(i in 1:nrow(Pop_long)){ 
  if(is.na(Pop_long[i, "Pop"])) {
    Pop_long[i, "Pop"]     <- Pop_long[i - 1, "Pop_add"] + Pop_long[i - 1, "Pop"] 
    Pop_long[i, "Pop_add"] <- Pop_long[i - 1, "Pop_add"] 
  }
}

Is it correct that in your final output the maximum year is `1329` and not `1350` as in `Pop`? See plot below. — markus, Jun 09 '18 at 21:15
@markus - my mistake, I want end result to finish on 1329 so i should have filtered above data - thanks — AAAA, Jun 09 '18 at 22:01

markus · Accepted Answer · 2018-06-09T21:28:37.453

3

You could use complete from tidyr and na.approx from zoo.

library(tidyr)
library(dplyr)
library(zoo)
Pop_long <- Pop %>% 
 complete(., Year = 850:1329) %>% 
 # complete(., Year = min(Year):max(Year)) %>%
 mutate(Pop = na.approx(Pop))

Pop_long
# A tibble: 480 x 2
#    Year   Pop
#   <dbl> <dbl>
# 1  850.  800.
# 2  851.  810.
# 3  852.  819.
# 4  853.  829.
# 5  854.  839.
# 6  855.  848.
# 7  856.  858.
# 8  857.  868.
# 9  858.  877.
#10  859.  887.
# ... with 470 more rows

library(ggplot2)
ggplot(data = Pop_long, aes(Year, Pop)) + 
  geom_line() +
  geom_point(data = Pop, col = "red")

edited Jun 09 '18 at 21:28

answered Jun 09 '18 at 21:05

markus

25,843
5
39
58

1

You need to load `dplyr` for `mutate` – Tung Jun 09 '18 at 21:11
@Tung, thanks for the pointer. Inculded the package in the answer. – markus Jun 09 '18 at 21:24

Data transformation - transform data from columns and interpolate missing values

1 Answers1