Fill in data with values to create (an adjacency) matrix

[John Egyir]

Hi,
This is my first post on this forum, and I hope some experts will guide me to address my problem. I am attempting to fill a dataset with values to create (an adjacency) matrix. However, I have not been successful.

I have data for several countries (24). For each country, I have respondent birth year from 1930-2020. I also have an average severe drought shock in each year sdrght_ru1930-sdrght_ru2020.

Here is an example of my dataset please:

* Example generated by -dataex-. For more info, type help dataex
*dataex countryname respbirthyear sdrght_ru1930- sdrght_ru1935 if countryname=="Benin" | countryname=="Burkina Faso"

clear
input str32 countryname int respbirthyear float(sdrght_ru1930 sdrght_ru1931 sdrght_ru1932 sdrght_ru1933 sdrght_ru1934 sdrght_ru1935)
"Benin"        1930 0          0 -.4085213 -.0992017          0 -.029640606
"Benin"        1931 .          .         .         .          .           .
"Benin"        1932 .          .         .         .          .           .
"Benin"        1933 .          .         .         .          .           .
"Benin"        1934 .          .         .         .          .           .
"Benin"        1935 .          .         .         .          .           .
"Benin"        1936 .          .         .         .          .           .
"Benin"        1937 .          .         .         .          .           .
"Benin"        1938 .          .         .         .          .           .
"Benin"        1939 .          .         .         .          .           .
"Benin"        1940 .          .         .         .          .           .
"Benin"        1941 .          .         .         .          .           .
"Benin"        1942 .          .         .         .          .           .
"Benin"        1943 .          .         .         .          .           .
"Benin"        1944 .          .         .         .          .           .
"Benin"        1945 .          .         .         .          .           .
"Benin"        1946 .          .         .         .          .           .
"Benin"        1947 .          .         .         .          .           .
"Benin"        1948 .          .         .         .          .           .
"Benin"        1949 .          .         .         .          .           .
"Benin"        1950 .          .         .         .          .           .
"Benin"        1951 .          .         .         .          .           .
"Benin"        1952 .          .         .         .          .           .
"Benin"        1953 .          .         .         .          .           .
"Benin"        1954 .          .         .         .          .           .
"Benin"        1955 .          .         .         .          .           .
"Benin"        1956 .          .         .         .          .           .
"Benin"        1957 .          .         .         .          .           .
"Benin"        1958 .          .         .         .          .           .
"Benin"        1959 .          .         .         .          .           .
"Benin"        1960 .          .         .         .          .           .
"Benin"        1961 .          .         .         .          .           .
"Benin"        1962 .          .         .         .          .           .
"Benin"        1963 .          .         .         .          .           .
"Benin"        1964 .          .         .         .          .           .
"Benin"        1965 .          .         .         .          .           .
"Benin"        1966 .          .         .         .          .           .
"Benin"        1967 .          .         .         .          .           .
"Benin"        1968 .          .         .         .          .           .
"Benin"        1969 .          .         .         .          .           .
"Benin"        1970 .          .         .         .          .           .
"Benin"        1971 .          .         .         .          .           .
"Benin"        1972 .          .         .         .          .           .
"Benin"        1973 .          .         .         .          .           .
"Benin"        1974 .          .         .         .          .           .
"Benin"        1975 .          .         .         .          .           .
"Benin"        1976 .          .         .         .          .           .
"Benin"        1977 .          .         .         .          .           .
"Benin"        1978 .          .         .         .          .           .
"Benin"        1979 .          .         .         .          .           .
"Benin"        1980 .          .         .         .          .           .
"Benin"        1981 .          .         .         .          .           .
"Benin"        1982 .          .         .         .          .           .
"Benin"        1983 .          .         .         .          .           .
"Benin"        1984 .          .         .         .          .           .
"Benin"        1985 .          .         .         .          .           .
"Benin"        1986 .          .         .         .          .           .
"Benin"        1987 .          .         .         .          .           .
"Benin"        1988 .          .         .         .          .           .
"Benin"        1989 .          .         .         .          .           .
"Benin"        1990 .          .         .         .          .           .
"Benin"        1991 .          .         .         .          .           .
"Benin"        1992 .          .         .         .          .           .
"Benin"        1993 .          .         .         .          .           .
"Benin"        1994 .          .         .         .          .           .
"Benin"        1995 .          .         .         .          .           .
"Benin"        1996 .          .         .         .          .           .
"Benin"        1997 .          .         .         .          .           .
"Benin"        1998 .          .         .         .          .           .
"Benin"        1999 .          .         .         .          .           .
"Benin"        2000 .          .         .         .          .           .
"Benin"        2001 .          .         .         .          .           .
"Benin"        2002 .          .         .         .          .           .
"Benin"        2003 .          .         .         .          .           .
"Benin"        2004 .          .         .         .          .           .
"Benin"        2005 .          .         .         .          .           .
"Benin"        2006 .          .         .         .          .           .
"Benin"        2007 .          .         .         .          .           .
"Benin"        2008 .          .         .         .          .           .
"Benin"        2009 .          .         .         .          .           .
"Benin"        2010 .          .         .         .          .           .
"Benin"        2011 .          .         .         .          .           .
"Benin"        2012 .          .         .         .          .           .
"Benin"        2013 .          .         .         .          .           .
"Benin"        2014 .          .         .         .          .           .
"Benin"        2015 .          .         .         .          .           .
"Benin"        2016 .          .         .         .          .           .
"Benin"        2017 .          .         .         .          .           .
"Benin"        2018 .          .         .         .          .           .
"Benin"        2019 .          .         .         .          .           .
"Benin"        2020 .          .         .         .          .           .
"Burkina Faso" 1930 0 -.01673013         0         0 -.08167528           0
"Burkina Faso" 1931 .          .         .         .          .           .
"Burkina Faso" 1932 .          .         .         .          .           .
"Burkina Faso" 1933 .          .         .         .          .           .
"Burkina Faso" 1934 .          .         .         .          .           .
"Burkina Faso" 1935 .          .         .         .          .           .
"Burkina Faso" 1936 .          .         .         .          .           .
"Burkina Faso" 1937 .          .         .         .          .           .
"Burkina Faso" 1938 .          .         .         .          .           .
end

According to the above data, at age zero, a respondent born in 1930 experienced an average drought shock of 0. At age 1, she also experienced an average drought shock of 0. At age 2, she experienced an average drought shock of -.4085213. Following this, I would like to fill in the space of the other birth years for each country. So that, at age 0, a respondent born in 1931 will have an average drought shock of 0. For this same respondent, at age 1, the average drought shock should be -.4085213.

Any assistance offered would be much appreciated.

Thank you in advance!
John Egyir

[Clyde Schechter]

I'm not completely clear on what you want, but I think it's this:

reshape long sdrght_ru, i(countryname respbirthyear) j(drought_year)
keep if respbirthyear <= drought_year
by countryname drought_year (respbirthyear), sort: replace sdrght_ru = sdrght_ru[_n-1] if _n > 1
reshape wide

[John Egyir]

Dear Professor Schechter,

Thank you very much for the quick response. Please I have tried the code you provided and it is close to what I am looking for. However, I would like the output to look as in the format below. This will help to easily rename sdrght_run1930 to age0, sdrght_ru1931 to age1, sdrght_ru1932 to age2 and so on. Is it possible to modify the code please? I am sorry, I should made this clear in my first post.

Output structure:

* Example generated by -dataex-. For more info, type help dataex
*dataex countryname respbirthyear sdrght_ru1930- sdrght_ru1935 if countryname=="Benin" | countryname=="Burkina Faso"

clear
input str24 countryname int respbirthyear float(sdrght_ru1930 sdrght_ru1931 sdrght_ru1932 sdrght_ru1933 sdrght_ru1934 sdrght_ru1935)
"Benin"        1930           0           0   -.4085213   -.0992017           0 -.029640606
"Benin"        1931           0   -.4085213   -.0992017           0 -.029640606  -.04698559
"Benin"        1932   -.4085213   -.0992017           0 -.029640606  -.04698559  -1.1490527
"Benin"        1933   -.0992017           0 -.029640606  -.04698559  -1.1490527  -2.8909955
"Benin"        1934           0 -.029640606  -.04698559  -1.1490527  -2.8909955   -.3518929
"Benin"        1935 -.029640606  -.04698559  -1.1490527  -2.8909955   -.3518929   -.1591048
"Benin"        1936  -.04698559  -1.1490527  -2.8909955   -.3518929   -.1591048    -.340324
"Benin"        1937  -1.1490527  -2.8909955   -.3518929   -.1591048    -.340324   -4.150266
"Benin"        1938  -2.8909955   -.3518929   -.1591048    -.340324   -4.150266   -.9165701
"Benin"        1939   -.3518929   -.1591048    -.340324   -4.150266   -.9165701   -.2002048
"Benin"        1940   -.1591048    -.340324   -4.150266   -.9165701   -.2002048   -12.30605
"Benin"        1941    -.340324   -4.150266   -.9165701   -.2002048   -12.30605  -18.576687
"Benin"        1942   -4.150266   -.9165701   -.2002048   -12.30605  -18.576687  -14.424256
"Benin"        1943   -.9165701   -.2002048   -12.30605  -18.576687  -14.424256   -2.783967
"Benin"        1944   -.2002048   -12.30605  -18.576687  -14.424256   -2.783967   -7.760369
"Benin"        1945   -12.30605  -18.576687  -14.424256   -2.783967   -7.760369  -1.0873688
"Benin"        1946  -18.576687  -14.424256   -2.783967   -7.760369  -1.0873688  -.17664407
"Benin"        1947  -14.424256   -2.783967   -7.760369  -1.0873688  -.17664407  -.17234455
"Benin"        1948   -2.783967   -7.760369  -1.0873688  -.17664407  -.17234455           0
"Benin"        1949   -7.760369  -1.0873688  -.17664407  -.17234455           0           0
"Benin"        1950  -1.0873688  -.17664407  -.17234455           0           0           0
"Benin"        1951  -.17664407  -.17234455           0           0           0           0
"Benin"        1952  -.17234455           0           0           0           0           0
"Benin"        1953           0           0           0           0           0   -4.493183
"Benin"        1954           0           0           0           0   -4.493183   -.7349762
"Benin"        1955           0           0           0   -4.493183   -.7349762           0
"Benin"        1956           0           0   -4.493183   -.7349762           0  -1.1365522
"Benin"        1957           0   -4.493183   -.7349762           0  -1.1365522  -1.2308358
"Benin"        1958   -4.493183   -.7349762           0  -1.1365522  -1.2308358           0
"Benin"        1959   -.7349762           0  -1.1365522  -1.2308358           0  -.21635024
"Benin"        1960           0  -1.1365522  -1.2308358           0  -.21635024  -.12502663
"Benin"        1961  -1.1365522  -1.2308358           0  -.21635024  -.12502663  -.15606284
"Benin"        1962  -1.2308358           0  -.21635024  -.12502663  -.15606284           0
"Benin"        1963           0  -.21635024  -.12502663  -.15606284           0  -.04761111
"Benin"        1964  -.21635024  -.12502663  -.15606284           0  -.04761111           0
"Benin"        1965  -.12502663  -.15606284           0  -.04761111           0           0
"Benin"        1966  -.15606284           0  -.04761111           0           0   -.6055493
"Benin"        1967           0  -.04761111           0           0   -.6055493   -.6909901
"Benin"        1968  -.04761111           0           0   -.6055493   -.6909901  -10.695824
"Benin"        1969           0           0   -.6055493   -.6909901  -10.695824   -5.256114
"Benin"        1970           0   -.6055493   -.6909901  -10.695824   -5.256114           0
"Benin"        1971   -.6055493   -.6909901  -10.695824   -5.256114           0   -.7298628
"Benin"        1972   -.6909901  -10.695824   -5.256114           0   -.7298628  -14.674125
"Benin"        1973  -10.695824   -5.256114           0   -.7298628  -14.674125   -7.837372
"Benin"        1974   -5.256114           0   -.7298628  -14.674125   -7.837372  -2.0912135
"Benin"        1975           0   -.7298628  -14.674125   -7.837372  -2.0912135  -.10600404
"Benin"        1976   -.7298628  -14.674125   -7.837372  -2.0912135  -.10600404  -1.6029234
"Benin"        1977  -14.674125   -7.837372  -2.0912135  -.10600404  -1.6029234   -4.175712
"Benin"        1978   -7.837372  -2.0912135  -.10600404  -1.6029234   -4.175712    -31.5582
"Benin"        1979  -2.0912135  -.10600404  -1.6029234   -4.175712    -31.5582    -50.1997
"Benin"        1980  -.10600404  -1.6029234   -4.175712    -31.5582    -50.1997   -37.27541
"Benin"        1981  -1.6029234   -4.175712    -31.5582    -50.1997   -37.27541  -30.449465
"Benin"        1982   -4.175712    -31.5582    -50.1997   -37.27541  -30.449465   -24.59466
"Benin"        1983    -31.5582    -50.1997   -37.27541  -30.449465   -24.59466   -5.782489
"Benin"        1984    -50.1997   -37.27541  -30.449465   -24.59466   -5.782489  -2.6390054
"Benin"        1985   -37.27541  -30.449465   -24.59466   -5.782489  -2.6390054  -1.9927628
"Benin"        1986  -30.449465   -24.59466   -5.782489  -2.6390054  -1.9927628    -.412429
"Benin"        1987   -24.59466   -5.782489  -2.6390054  -1.9927628    -.412429  -.22804126
"Benin"        1988   -5.782489  -2.6390054  -1.9927628    -.412429  -.22804126  -2.9953146
"Benin"        1989  -2.6390054  -1.9927628    -.412429  -.22804126  -2.9953146   -1.186087
"Benin"        1990  -1.9927628    -.412429  -.22804126  -2.9953146   -1.186087           0
"Benin"        1991    -.412429  -.22804126  -2.9953146   -1.186087           0           0
"Benin"        1992  -.22804126  -2.9953146   -1.186087           0           0   -1.385174
"Benin"        1993  -2.9953146   -1.186087           0           0   -1.385174   -.4485265
"Benin"        1994   -1.186087           0           0   -1.385174   -.4485265           0
"Benin"        1995           0           0   -1.385174   -.4485265           0           0
"Benin"        1996           0   -1.385174   -.4485265           0           0   -6.613919
"Benin"        1997   -1.385174   -.4485265           0           0   -6.613919  -17.249788
"Benin"        1998   -.4485265           0           0   -6.613919  -17.249788           0
"Benin"        1999           0           0   -6.613919  -17.249788           0  -.17449634
"Benin"        2000           0   -6.613919  -17.249788           0  -.17449634           0
"Benin"        2001   -6.613919  -17.249788           0  -.17449634           0  -1.5038836
"Benin"        2002  -17.249788           0  -.17449634           0  -1.5038836           0
"Benin"        2003           0  -.17449634           0  -1.5038836           0           0
"Benin"        2004  -.17449634           0  -1.5038836           0           0           0
"Benin"        2005           0  -1.5038836           0           0           0           0
"Benin"        2006  -1.5038836           0           0           0           0   -.2433045
"Benin"        2007           0           0           0           0   -.2433045           0
"Benin"        2008           0           0           0   -.2433045           0    -.815874
"Benin"        2009           0           0   -.2433045           0    -.815874    -.677555
"Benin"        2010           0   -.2433045           0    -.815874    -.677555    -7.50167
"Benin"        2011   -.2433045           0    -.815874    -.677555    -7.50167  -2.2052586
"Benin"        2012           0    -.815874    -.677555    -7.50167  -2.2052586   -6.603125
"Benin"        2013    -.815874    -.677555    -7.50167  -2.2052586   -6.603125  -2.4779775
"Benin"        2014    -.677555    -7.50167  -2.2052586   -6.603125  -2.4779775           0
"Benin"        2015    -7.50167  -2.2052586   -6.603125  -2.4779775           0           0
"Benin"        2016  -2.2052586   -6.603125  -2.4779775           0           0           .
"Benin"        2017   -6.603125  -2.4779775           0           0           .           .
"Benin"        2018  -2.4779775           0           0           .           .           .
"Benin"        2019           0           0           .           .           .           .
"Benin"        2020           0           .           .           .           .           .
"Burkina Faso" 1930           0  -.01673013           0           0  -.08167528           0
"Burkina Faso" 1931  -.01673013           0           0  -.08167528           0           0
"Burkina Faso" 1932           0           0  -.08167528           0           0   -.6474568
"Burkina Faso" 1933           0  -.08167528           0           0   -.6474568  -.23951283
"Burkina Faso" 1934  -.08167528           0           0   -.6474568  -.23951283           0
"Burkina Faso" 1935           0           0   -.6474568  -.23951283           0  -.07854635
"Burkina Faso" 1936           0   -.6474568  -.23951283           0  -.07854635  -.13789749
"Burkina Faso" 1937   -.6474568  -.23951283           0  -.07854635  -.13789749  -.22021614
"Burkina Faso" 1938  -.23951283           0  -.07854635  -.13789749  -.22021614 -.008928388
end

Thank you!
John Egyir

[Clyde Schechter]

reshape long sdrght_ru, i(countryname respbirthyear) j(exposure_year)
gen age = exposure_year - respbirthyear
keep if exposure_year >= respbirthyear
rename sdrght_ru exposure

by countryname exposure_year (respbirthyear), sort: replace exposure = exposure[_n-1] if _n > 1
drop exposure_year
reshape wide exposure, i(countryname respbirthyear) j(age)
rename exposure* exposure_at_age_*

[John Egyir]

Thank you very much, Professor Schechter. The code works perfectly.


John Egyir