Generate (population) value from growth rate

[elisabeth rieck]

Dear community,
I have population data on the years 1939, 1946, and 1964.
With the following code I managed to create the growth rate for the time period 1939-1946, 1946-1964, and 1939-1964:

gen pop_adjusted_39_46 = ((pop_ / pop_[_n-7])^(1/7))-1 if name == name[_n-7]
gen pop_adjusted_46_64 = ((pop_ / pop_[_n-18])^(1/18))-1 if name == name[_n-18]
gen pop_adjusted_39_64 = ((pop_ / pop_[_n-25])^(1/25))-1 if name == name[_n-25]


I now wish to add the growth rate to the population data so that I have a full data set with all population figures in each city in the time-frame 1933-1964.

For illustration, this is a tiny part of the data set, but it gives the idea.

[CODE]

* Example generated by -dataex-. For more info, type help dataex
clear
input str77 name float year long id float(county_id state_id) double(area lat lon) float(degurba urban unincorporated pop_) double(relocated_firms number_patents) float(d_pop_adjusted e_pop_adjusted f_pop_adjusted pop_adjusted)
"Abtsbessingen"            1933 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1934 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1935 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1936 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1937 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1938 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1939 16065001 16065 16 14.09 51.259592 10.762472 2 0 0   833 0 .          .           .           . .
"Abtsbessingen"            1940 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1941 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1942 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1943 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1944 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1945 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1946 16065001 16065 16 14.09 51.259592 10.762472 2 0 0  1234 0 .  .05774615           .           . .
"Abtsbessingen"            1947 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1948 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1949 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1950 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1951 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1952 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1953 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1954 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1955 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1956 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1957 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1958 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1959 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1960 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1961 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1962 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1963 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 .          .           .           . .
"Abtsbessingen"            1964 16065001 16065 16 14.09 51.259592 10.762472 2 0 0   910 0 .          . -.016778294  .003542699 .
"Admannshagen-Bargeshagen" 1933 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1934 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1935 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1936 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1937 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1938 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1939 13072001 13072 13 15.77 54.111554 11.984106 2 0 0   616 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1940 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1941 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1942 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1943 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1944 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1945 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1946 13072001 13072 13 15.77 54.111554 11.984106 2 0 0  1296 0 .  .11210637           .           . .
"Admannshagen-Bargeshagen" 1947 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1948 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1949 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1950 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1951 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1952 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1953 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1954 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1955 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1956 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1957 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1958 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1959 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1960 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1961 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1962 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1963 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 .          .           .           . .
"Admannshagen-Bargeshagen" 1964 13072001 13072 13 15.77 54.111554 11.984106 2 0 0   845 0 .          . -.023481106  .012723855 .

Any help is appreciated. Thank you!

[Clyde Schechter]

I would skip the commands you show in #1 and do this instead:

local era1 1939 1946
local era2 1946 1964
local era3 1939 1964

sort name year
forvalues i = 1/3 {
    local begin: word 1 of `era`i''
    local end: word 2 of `era`i''
    by name (year): egen initial = max(cond(year == `begin'), pop_, .)
    by name (year): egen final = max(cond(year == `end'), pop_, .)
    gen annualized_growth_era`i' = (final/initial)^(1/(`end'-`begin'))
    label var annualized_growth_era`i' "Growth rate from `begin' to `end'"
    drop initial final
}

Note: In your example data, for the city Admannshagen-Bargeshagen, the variable pop_ is missing in 1964, so only the 1939-1946 growth rate can be calculated for it.

[elisabeth rieck]

This works nicely, thank you very much, Clyde.

This is the result,

* Example generated by -dataex-. For more info, type help dataex
clear
input str77 name float year long id float(county_id state_id) double(area lat lon) float(degurba urban unincorporated pop_) double(relocated_firms number_patents) float(annualized_growth_era1 annualized_growth_era2 annualized_growth_era3 pop_adjusted)
"Abtsbessingen"            1933 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1934 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1935 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1936 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1937 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1938 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1939 16065001 16065 16 14.09 51.259592 10.762472 2 0 0   833 0 . 1.0577462 .9832217 1.0035427   833
"Abtsbessingen"            1940 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1941 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1942 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1943 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1944 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1945 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1946 16065001 16065 16 14.09 51.259592 10.762472 2 0 0  1234 0 . 1.0577462 .9832217 1.0035427  1234
"Abtsbessingen"            1947 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1948 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1949 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1950 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1951 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1952 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1953 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1954 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1955 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1956 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1957 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1958 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1959 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1960 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1961 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1962 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1963 16065001 16065 16 14.09 51.259592 10.762472 2 0 0     . 0 . 1.0577462 .9832217 1.0035427     .
"Abtsbessingen"            1964 16065001 16065 16 14.09 51.259592 10.762472 2 0 0   910 0 . 1.0577462 .9832217 1.0035427   910
"Admannshagen-Bargeshagen" 1933 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1934 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1935 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1936 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1937 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1938 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1939 13072001 13072 13 15.77 54.111554 11.984106 2 0 0   616 0 . 1.1121063 .9765189 1.0127238   616
"Admannshagen-Bargeshagen" 1940 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1941 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1942 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1943 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1944 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1945 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1946 13072001 13072 13 15.77 54.111554 11.984106 2 0 0  1296 0 . 1.1121063 .9765189 1.0127238  1296
"Admannshagen-Bargeshagen" 1947 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1948 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1949 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1950 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1951 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1952 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1953 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1954 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1955 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1956 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1957 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1958 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1959 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1960 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1961 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1962 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1963 13072001 13072 13 15.77 54.111554 11.984106 2 0 0     . 0 . 1.1121063 .9765189 1.0127238     .
"Admannshagen-Bargeshagen" 1964 13072001 13072 13 15.77 54.111554 11.984106 2 0 0   845 0 . 1.1121063 .9765189 1.0127238   845
end
label values degurba mdegurba
label def mdegurba 2 "Towns and suburbs (intermediate density areas)", modify

How do I proceed from here? How do I fill the missing year variables according to the growth rates?

[Clyde Schechter]

Ah, I now realize that I did not grasp the implications when you said "...so that I have a full data set with all population figures in each city in the time-frame 1933-1964."

Even assuming it is OK to impute the missing values of population by assuming constant growth, I would not do it the way you are going about it. For one thing, it only works when the population values are missing in all and only the years other than 1939, 1946, and 1964. Maybe that is true in your data, but if there are any exceptions, then the growth rates for those eras are inapplicable to them. The following approach will work without exceptions to fill in missing values of population anytime they fall between two non-missing values. And they will always be consistent with all non-missing values, not just those in 3 specific years.

gen log_pop = log(pop_)
by name (year), sort: ipolate log_pop year, gen(imputed_pop)
replace imputed_pop = exp(imputed_pop)

Note that you can do this without computing the growth rates in the three eras. You are, of course, free to compute those growth rates anyway if they are of some other use in your work. But for this purpose, they are not needed.

[elisabeth rieck]

Thank you, Clyde. This is amazing and does the job. I will keep the growth rates - who knows what I might need them for.
I do have the population data for 1910 so I will just use them to ipolate accordingly and drop the year. That saves the time to epolate the years before 1939.
Thank you a lot!