Creating a combined averaged using a panel dataset

[Daniel McAdams]

I have a panel dataset in vertical form with 6 countries; each shows values for 14 indicators between 2000 and 2020. There is a column that shows the indicator "value" for each "year" and another column that takes the value of the most recent value ("lastvalue") between 2015-2020 (or"lastyear"), thus, this value repeats itself in 20 rows).

Here is an example for only one country Antigua and Barbuda (ATG)
Code:
* Example generated by -dataex-. For more info, type help dataex clear input str3 countrycode str20 indicatorcode int year double value int lastyear double lastvalue "ATG" "FX.OWN.TOTL.FE.ZS" 2000 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2001 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2002 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2003 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2004 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2005 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2006 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2007 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2008 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2009 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2010 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2011 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2012 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2013 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2014 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2015 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2016 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2017 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2018 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2019 . . . "ATG" "FX.OWN.TOTL.FE.ZS" 2020 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2000 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2001 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2002 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2003 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2004 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2005 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2006 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2007 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2008 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2009 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2010 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2011 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2012 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2013 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2014 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2015 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2016 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2017 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2018 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2019 . . . "ATG" "FX.OWN.TOTL.MA.ZS" 2020 . . . "ATG" "IC.FRM.FEMO.ZS" 2000 . . . "ATG" "IC.FRM.FEMO.ZS" 2001 . . . "ATG" "IC.FRM.FEMO.ZS" 2002 . . . "ATG" "IC.FRM.FEMO.ZS" 2003 . . . "ATG" "IC.FRM.FEMO.ZS" 2004 . . . "ATG" "IC.FRM.FEMO.ZS" 2005 . . . "ATG" "IC.FRM.FEMO.ZS" 2006 . . . "ATG" "IC.FRM.FEMO.ZS" 2007 . . . "ATG" "IC.FRM.FEMO.ZS" 2008 . . . "ATG" "IC.FRM.FEMO.ZS" 2009 . . . "ATG" "IC.FRM.FEMO.ZS" 2010 21.3 . . "ATG" "IC.FRM.FEMO.ZS" 2011 . . . "ATG" "IC.FRM.FEMO.ZS" 2012 . . . "ATG" "IC.FRM.FEMO.ZS" 2013 . . . "ATG" "IC.FRM.FEMO.ZS" 2014 . . . "ATG" "IC.FRM.FEMO.ZS" 2015 . . . "ATG" "IC.FRM.FEMO.ZS" 2016 . . . "ATG" "IC.FRM.FEMO.ZS" 2017 . . . "ATG" "IC.FRM.FEMO.ZS" 2018 . . . "ATG" "IC.FRM.FEMO.ZS" 2019 . . . "ATG" "IC.FRM.FEMO.ZS" 2020 . . . "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2000 . 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2001 . 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2002 . 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2003 . 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2004 . 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2005 . 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2006 . 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2007 . 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2008 96.2264175415039 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2009 78.1376495361328 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2010 107.908851623535 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2011 112.236839294434 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2012 106.744491577148 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2013 95.4545516967773 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2014 91.2582778930664 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2015 99.1847763061523 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2016 115.650970458984 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2017 104.237289428711 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2018 102.011489868164 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2019 . 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.FE.ZS" 2020 . 2018 102.01148986816406 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2000 . 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2001 . 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2002 . 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2003 . 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2004 . 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2005 . 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2006 . 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2007 . 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2008 79.2866897583008 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2009 88.0920181274414 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2010 83.8666687011719 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2011 93.8642272949219 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2012 99.3573303222656 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2013 80.2564086914063 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2014 85.8257522583008 2018 95.35211181640625 "ATG" "SE.SEC.CMPT.LO.MA.ZS" 2015 92.4202117919922 2018 95.35211181640625 end
I would like to average the values for all the 6 countries and create a new combined countrycode. This means that I want a new set of rows for the new "averagedcountries" with
1. average "value" for each indicator (14) for each "year" (between 2000-2020).
2. average "lastvalue" for each indicator for the "recentvalue" (between 2015-2020).

I would appreciate any guidance! Thank you

[Nick Cox]

Please edit #1 to include CODE delimiters.

[Daniel McAdams]

Sorry!

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 countrycode str20 indicatorcode int year double value int lastyear double lastvalue
"ATG" "FX.OWN.TOTL.FE.ZS"    2000                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2001                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2002                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2003                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2004                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2005                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2006                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2007                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2008                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2009                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2010                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2011                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2012                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2013                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2014                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2015                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2016                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2017                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2018                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2019                .    .                  .
"ATG" "FX.OWN.TOTL.FE.ZS"    2020                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2000                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2001                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2002                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2003                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2004                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2005                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2006                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2007                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2008                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2009                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2010                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2011                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2012                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2013                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2014                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2015                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2016                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2017                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2018                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2019                .    .                  .
"ATG" "FX.OWN.TOTL.MA.ZS"    2020                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2000                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2001                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2002                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2003                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2004                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2005                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2006                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2007                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2008                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2009                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2010             21.3    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2011                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2012                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2013                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2014                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2015                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2016                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2017                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2018                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2019                .    .                  .
"ATG" "IC.FRM.FEMO.ZS"       2020                .    .                  .
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2000                . 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2001                . 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2002                . 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2003                . 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2004                . 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2005                . 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2006                . 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2007                . 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2008 96.2264175415039 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2009 78.1376495361328 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2010 107.908851623535 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2011 112.236839294434 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2012 106.744491577148 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2013 95.4545516967773 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2014 91.2582778930664 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2015 99.1847763061523 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2016 115.650970458984 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2017 104.237289428711 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2018 102.011489868164 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2019                . 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.FE.ZS" 2020                . 2018 102.01148986816406
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2000                . 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2001                . 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2002                . 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2003                . 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2004                . 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2005                . 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2006                . 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2007                . 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2008 79.2866897583008 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2009 88.0920181274414 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2010 83.8666687011719 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2011 93.8642272949219 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2012 99.3573303222656 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2013 80.2564086914063 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2014 85.8257522583008 2018  95.35211181640625
"ATG" "SE.SEC.CMPT.LO.MA.ZS" 2015 92.4202117919922 2018  95.35211181640625
end

[Nick Cox]

I can't quite follow this but wanting extra rows (in Stata terms, observations) for totals is spreadsheet thinking, and not Stata thinking -- it will to complicate all later analyses, because you need to worry about including or excluding them thereafter.

Something like

egen mean = mean(value), by(year indicator_code)

may indicate technique.

For most Stata purposes you need a wide(r) layout here, as would be obtained by (say)

gen vartext = strtoname(indicatorcode)
drop indicatorcode last*
reshape wide value , i(countrycode year) j(vartext) string
rename value* *

[Daniel Feenberg]

Are these close?

* Mean of values over countries by indicatorcode.
sort year indicatorcode
collapse (mean) value,by(year indicatorcode)

and

* Mean of value over countries by indicatorcode of last available year. 
keep if value!=.
sort countrycode year
by countrycode: keep if _n==_N
sort indicatorcode
collapse (mean) value,by(indicatorcode)

-egen- is an alternative to -collapse- also worth learning about. You always want to think about what is happening to missing values anytime you do transformations across observations.

[Daniel McAdams]

Thank you to both! This exactly what I needed!