Re-organizing a data set to apply the "tsset" comman on a year variable

[Aziz Essouaied]

Hello Stata people;

I'm working with this data currently:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Year byte Flow_ID str9 Flow double Trade_Value_in_USD
2000 1 `""Imports""' 127534440000
2000 2 `""Exports""' 147399947000
2001 1 `""Imports""' 113766789000
2001 2 `""Exports""' 140564413000
2002 1 `""Imports""' 106556726000
2002 2 `""Exports""' 141897655000
2003 1 `""Imports""' 105360687000
2003 2 `""Exports""' 144293359000
2004 1 `""Imports""' 110826709000
2004 2 `""Exports""' 164521988000
2005 1 `""Imports""' 118547336000
2005 2 `""Exports""' 183562840000
2006 1 `""Imports""' 130311031000
2006 2 `""Exports""' 211799379000
2007 1 `""Imports""' 139472838000
2007 2 `""Exports""' 223133260000
2008 1 `""Imports""' 151334594000
2008 2 `""Exports""' 233522733000
2009 1 `""Imports""' 112433823000
2009 2 `""Exports""' 185101149000
2010 1 `""Imports""' 145007354000
2010 2 `""Exports""' 238684425000
2011 1 `""Imports""' 174356033000
2011 2 `""Exports""' 274426522000
2012 1 `""Imports""' 185109813000
2012 2 `""Exports""' 287842157000
2013 1 `""Imports""' 187261916000
2013 2 `""Exports""' 299439153000
2014 1 `""Imports""' 195281872000
2014 2 `""Exports""' 318367007000
2015 1 `""Imports""' 186812404000
2015 2 `""Exports""' 308869536000
2016 1 `""Imports""' 179517794000
2016 2 `""Exports""' 302580855000
2017 1 `""Imports""' 194554589000
2017 2 `""Exports""' 326868567000
2018 1 `""Imports""' 215828019000
2018 2 `""Exports""' 3.582813e+11
2019 1 `""Imports""' 205725211000
2019 2 `""Exports""' 370766932000
2020 1 `""Imports""' 167762159000
2020 2 `""Exports""' 338701084000
2021 1 `""Imports""' 220984657000
2021 2 `""Exports""' 398989265000
2022 1 `""Imports""' 265026221000
2022 2 `""Exports""' 472702571000
2023 1 `""Imports""' 255439901000
2023 2 `""Exports""' 490187337000
2024 1 `""Imports""' 231680687000
2024 2 `""Exports""' 469603418000
end

The first variable is the year, and as you can see, each year is repeated two times, that's because for each year, there are two measures being measured: "Exports" and "Imports". The second variable is an ID for each Exports or Imports, the third variable is the name of the measure, and the fourth variable is the value of that measure ("Exports" or "Imports") in USD$.

My goal here is to reshape or re-organize my data in order to draw the evolution of Exports and Imports by year. Already, I want to delete these ("") from the names of the measures, and also to reshape my data to have both the Exports and the Imports measure on the Y axis.

Is there anyone who could help with this? With many thanks!

[Chen Samulsion]

You need NOT to reshape your data because tsset allows to specifiy panelvar, see help tsset. Use the following code:

tsset Flow_ID Year
replace Flow=subinstr(Flow,`"""',"",.)
twoway line Trade_Value_in_USD Year if Flow_ID==1 || line Trade_Value_in_USD Year if Flow_ID==2 , sort

[Chen Samulsion]

If you dislike that tsline ignore your panelvar setting, you can use tsgraph (SSC) as an alternative which is a wrapper of twoway connected. You can modify the ado file to get a modern scheme.

tsset Flow_ID Year
label define Flow_ID 1 "Imports" 2 "Exports"
label values Flow_ID Flow_ID
tsgraph Trade_Value_in_USD

[Aziz Essouaied]

Chen Samulsion Thanks for the response. The thing is that I do believe I need to reshape my data, because, along with the graph that I do want to draw, I also want to generate a variable about the trade balance (Exports - Imports). I thought I've mentionned that in my previous post, but I guess I've forgotten, I do apologize for that.

Thanks for the help.

[Chen Samulsion]

drop Flow
reshape wide Trade_Value_in_USD, i(Year) j(Flow_ID)
rename Trade_Value_in_USD1 Imports
rename Trade_Value_in_USD2 Exports
twoway line Imports Exports Year

Or if you want to retain variable Flow, just type:

reshape wide Trade_Value_in_USD Flow, i(Year) j(Flow_ID)

[Nick Cox]

I don't think that treating imports and exports as separate panels is likely to help with anything other than some graphics. And it's not needed any way for the graph that seems wanted here, which can be produced directly.

Here's fairly complete code with some extra graphical twists. I have written this old-style (not in terms of Stata 18 up) because of a memory that you (Aziz) are using an old version of Stata, but you are expected to make that clear in every post.

mylabels is from the Stata Journal, but isn't needed as you could always spell out labels in an option such as

yla(1e11 "100")

with more numbers and text as needed.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Year byte Flow_ID str9 Flow double Trade_Value_in_USD
2000 1 `""Imports""' 127534440000
2000 2 `""Exports""' 147399947000
2001 1 `""Imports""' 113766789000
2001 2 `""Exports""' 140564413000
2002 1 `""Imports""' 106556726000
2002 2 `""Exports""' 141897655000
2003 1 `""Imports""' 105360687000
2003 2 `""Exports""' 144293359000
2004 1 `""Imports""' 110826709000
2004 2 `""Exports""' 164521988000
2005 1 `""Imports""' 118547336000
2005 2 `""Exports""' 183562840000
2006 1 `""Imports""' 130311031000
2006 2 `""Exports""' 211799379000
2007 1 `""Imports""' 139472838000
2007 2 `""Exports""' 223133260000
2008 1 `""Imports""' 151334594000
2008 2 `""Exports""' 233522733000
2009 1 `""Imports""' 112433823000
2009 2 `""Exports""' 185101149000
2010 1 `""Imports""' 145007354000
2010 2 `""Exports""' 238684425000
2011 1 `""Imports""' 174356033000
2011 2 `""Exports""' 274426522000
2012 1 `""Imports""' 185109813000
2012 2 `""Exports""' 287842157000
2013 1 `""Imports""' 187261916000
2013 2 `""Exports""' 299439153000
2014 1 `""Imports""' 195281872000
2014 2 `""Exports""' 318367007000
2015 1 `""Imports""' 186812404000
2015 2 `""Exports""' 308869536000
2016 1 `""Imports""' 179517794000
2016 2 `""Exports""' 302580855000
2017 1 `""Imports""' 194554589000
2017 2 `""Exports""' 326868567000
2018 1 `""Imports""' 215828019000
2018 2 `""Exports""' 3.582813e+11
2019 1 `""Imports""' 205725211000
2019 2 `""Exports""' 370766932000
2020 1 `""Imports""' 167762159000
2020 2 `""Exports""' 338701084000
2021 1 `""Imports""' 220984657000
2021 2 `""Exports""' 398989265000
2022 1 `""Imports""' 265026221000
2022 2 `""Exports""' 472702571000
2023 1 `""Imports""' 255439901000
2023 2 `""Exports""' 490187337000
2024 1 `""Imports""' 231680687000
2024 2 `""Exports""' 469603418000
end

mylabels 100(100)500, myscale(@*1e9)  local(yla)


line Trade_Value_in_USD Year if Flow_ID == 1, lc(red) yla(, ang(h)) ///
|| line Trade_Value_in_USD Year if Flow_ID == 2, lc(blue) yla(`yla') ytitle(Trade (billion USD)) ///
|| scatter Trade_Value_in_USD Year if Year == 2024 & Flow_ID == 1, ms(none) mla(Flow) mlabcolor(red) ///
|| scatter Trade_Value_in_USD Year if Year == 2024 & Flow_ID == 2, ms(none) mla(Flow) mlabcolor(blue) ///
xsc(r(. 2026.2)) legend(off) xtitle("")

The suggested twists are

* rescaling the y axis labels

* removing the legend in favour of direct labelling of variables

* cutting the redundant title "Year" for the x axis. Any readers who need to be told that 2020(5)2025 mean years is in the wrong place.

If a reshape is needed, it is a standard reshape wide with code such as

 
drop Flow 
reshape wide Trade_Value_in_USD, i(Year) j(Flow_ID)
rename (T*) (Imports Exports)