Reshaping to long data

[Olivia Senlin]

Hi!

I want to reshape a dataset to long format, but I am unsure of how to do it. It appears like this when I import it from excel:
I know you do not like screenshots, but there are too many variables to show with dataex. But I want it in the format from another file that I have inserted here. (This is trade data with categories)

[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input str13 A str25 B str6 C str7 D str9 E str7(F G H) str9(I J K L M N O P Q R S T U V) 

This is how I want it to end up looking. But how do I deal with the year variable?
The categories are just translated.

[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input str25(exporter category) int year double(ImportTons sum_total)
"Argentina" "frozen whole"           2008                 .                  0
"Argentina" "frozen whole"           2001                 .                  0
"Argentina" "frozen whole"           2009                 .                  0
"Argentina" "frozen whole"           2016                 .                  0
"Argentina" "frozen whole"           2017                 .                  0
"Argentina" "frozen whole"           1999                 .                  0
"Argentina" "frozen whole"           2005                 .                  0
"Argentina" "frozen whole"           2006                 .                  0 

Thank you!

[William Lisowski]

The output of help dataex tells us you can specify variables you want included in your data extract, so you can provide an extract with just a few of your variables.

With that said, do you have any idea why all of your numeric data was imported as strings? That suggests problems with your Excel data or your import excel command.

[Olivia Senlin]

Hi William,
No I don't know why. I just use import excel code, firstrow.

Here is an extract of the data.

[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input str13 country str25 category str6 C str7 D str9 E str7(F G)
"Chile"         "fersk/kj¯lt hel"           "."      "."       "."         "."       ".023"   
""              "fersk/kj¯lt filet"         "."      "."       "."         "."       "."      
""              "fryst hel"                 "."      "."       "15.153"    "170.17"  "35.715" 
""              "fryst filet"               "."      "."       "."         "."       "."      
""              "r¯ykt hel og filet"        "."      "."       "."         "."       "."      
""              "tilberedt/konservert laks" "."      "."       "."         "."       "."      
"Argentina"     "fryst hel"                 "."      "."       "."         "."       "."      
"Uruguay"       "fryst hel"                 "."      "."       "."         "."       "."      
"FÊr¯yene"      "fersk/kj¯lt hel"           "."      "."       "."         "."       "."      
""              "fryst hel"                 "."      "."       "."         "."       "." 

[William Lisowski]

From your first post we know that variables C onward correspond to years 1998 onward, so the following uses the first 9 observations of your sample to show a helpful approach. For details on the rename command I used, see help rename group .

* Example generated by -dataex-. To install: ssc install dataex
clear
input str13 country str25 category str6 C str7 D str9 E str7(F G)
"Chile"         "fersk/kj¯lt hel"           "."      "."       "."         "."       ".023"   
""              "fersk/kj¯lt filet"         "."      "."       "."         "."       "."      
""              "fryst hel"                 "."      "."       "15.153"    "170.17"  "35.715" 
""              "fryst filet"               "."      "."       "."         "."       "."      
""              "r¯ykt hel og filet"        "."      "."       "."         "."       "."      
""              "tilberedt/konservert laks" "."      "."       "."         "."       "."      
"Argentina"     "fryst hel"                 "."      "."       "."         "."       "."      
"Uruguay"       "fryst hel"                 "."      "."       "."         "."       "."      
end 
generate seq = _n
replace country = country[_n-1] if country==""
rename (C-G) it(####), addnumber(1998)
reshape long it , i(seq) j(year)
replace it = "" if it=="."
destring it, generate(ImportTons)  
drop it
describe
list if category=="fryst hel", sepby(country) abbreviate(12)

. reshape long it , i(seq) j(year)
(note: j = 1998 1999 2000 2001 2002)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                        8   ->      40
Number of variables                   8   ->       5
j variable (5 values)                     ->   year
xij variables:
               it1998 it1999 ... it2002   ->   it
-----------------------------------------------------------------------------

. describe

Contains data
  obs:            40                          
 vars:             5                          
 size:         2,080                          
------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
------------------------------------------------------------------------------------------------
seq             float   %9.0g                 
year            int     %9.0g                 
country         str13   %13s                  
category        str25   %25s                  
ImportTons      double  %10.0g                
------------------------------------------------------------------------------------------------
Sorted by: seq  year
     Note: Dataset has changed since last saved.

. list if category=="fryst hel", sepby(country) abbreviate(12)

     +-------------------------------------------------+
     | seq   year     country    category   ImportTons |
     |-------------------------------------------------|
 11. |   3   1998       Chile   fryst hel            . |
 12. |   3   1999       Chile   fryst hel            . |
 13. |   3   2000       Chile   fryst hel       15.153 |
 14. |   3   2001       Chile   fryst hel       170.17 |
 15. |   3   2002       Chile   fryst hel       35.715 |
     |-------------------------------------------------|
 31. |   7   1998   Argentina   fryst hel            . |
 32. |   7   1999   Argentina   fryst hel            . |
 33. |   7   2000   Argentina   fryst hel            . |
 34. |   7   2001   Argentina   fryst hel            . |
 35. |   7   2002   Argentina   fryst hel            . |
     |-------------------------------------------------|
 36. |   8   1998     Uruguay   fryst hel            . |
 37. |   8   1999     Uruguay   fryst hel            . |
 38. |   8   2000     Uruguay   fryst hel            . |
 39. |   8   2001     Uruguay   fryst hel            . |
 40. |   8   2002     Uruguay   fryst hel            . |
     +-------------------------------------------------+

[Olivia Senlin]

Hi again, wow great. Thank you so much!
It worked perfectly!