Reshaping panel data in a long format

[Jala Youssef]

Hi everyone,

I have a panel dataset on exchange rate classification (monthly by country) in a format that is not long nor wide Stata format. Please find below an example. Each country is in a separate column (I am adding here an example with few countries from my data).

I would be grateful if you could advise how to reshape this to a long Stata format.

Many thanks in advance.

Jala

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 month byte(AFG ALB DZA AGO ATG ARG ARM)
"1946M1"  1 6 1 1 1 3 1
"1946M2"  1 6 1 1 1 3 1
"1946M3"  1 6 1 1 1 3 1
"1946M4"  1 6 1 1 1 3 1
"1946M5"  1 6 1 1 1 3 1
"1946M6"  1 6 1 1 1 3 1
"1946M7"  1 6 1 1 1 3 1
"1946M8"  1 6 1 1 1 3 1
"1946M9"  1 6 1 1 1 3 1
"1946M10" 1 6 1 1 1 3 1
"1946M11" 1 6 1 1 1 3 1
"1946M12" 1 6 1 1 1 3 1
"1947M1"  1 6 1 1 1 3 1
"1947M2"  1 6 1 1 1 3 1
"1947M3"  1 6 1 1 1 3 1
"1947M4"  1 6 1 1 1 3 1
"1947M5"  1 6 1 1 1 3 1
"1947M6"  1 6 1 1 1 3 1
"1947M7"  1 6 1 1 1 3 1
"1947M8"  1 6 1 1 1 3 1
"1947M9"  1 6 1 1 1 3 1
"1947M10" 1 6 1 1 1 3 1
"1947M11" 1 6 1 1 1 3 1
"1947M12" 1 6 1 1 1 3 1
"1948M1"  1 6 1 1 1 3 1
"1948M2"  1 6 5 1 1 3 1
"1948M3"  1 6 5 1 1 3 1
"1948M4"  1 6 5 1 1 3 1
"1948M5"  1 6 5 1 1 3 1
"1948M6"  1 6 5 1 1 3 1
"1948M7"  1 6 5 1 1 3 1
"1948M8"  1 6 5 1 1 3 1
"1948M9"  1 6 5 1 1 3 1
"1948M10" 1 6 5 1 1 3 1
"1948M11" 1 6 5 1 1 3 1
"1948M12" 1 6 5 1 1 3 1
"1949M1"  1 6 5 1 1 3 1
"1949M2"  1 6 5 1 1 3 1
"1949M3"  1 6 5 1 1 3 1
"1949M4"  1 6 5 1 1 3 1
"1949M5"  1 6 5 1 1 3 1
"1949M6"  1 6 5 1 1 3 1
"1949M7"  1 6 5 1 1 3 1
"1949M8"  1 6 5 1 1 3 1
"1949M9"  1 6 5 1 1 3 1
"1949M10" 1 6 1 1 1 3 1
"1949M11" 1 6 1 1 1 3 1
"1949M12" 1 6 1 1 1 3 1
"1950M1"  1 6 1 1 1 3 1
"1950M2"  1 6 1 1 1 3 1
"1950M3"  1 6 1 1 1 3 1
"1950M4"  1 6 1 1 1 3 1
"1950M5"  1 6 1 1 1 3 1
"1950M6"  1 6 1 1 1 3 1
"1950M7"  1 6 1 1 1 5 1
"1950M8"  1 6 1 1 1 5 1
"1950M9"  1 6 1 1 1 5 1
"1950M10" 1 6 1 1 1 5 1
"1950M11" 1 6 1 1 1 5 1
"1950M12" 1 6 1 1 1 5 1
"1951M1"  1 6 1 1 1 5 1
"1951M2"  1 6 1 1 1 5 1
"1951M3"  1 6 1 1 1 5 1
"1951M4"  1 6 1 1 1 5 1
"1951M5"  1 6 1 1 1 5 1
"1951M6"  1 6 1 1 1 5 1
"1951M7"  1 6 1 1 1 5 1
"1951M8"  1 6 1 1 1 5 1
"1951M9"  1 6 1 1 1 5 1
"1951M10" 1 6 1 1 1 5 1
"1951M11" 1 6 1 1 1 5 1
"1951M12" 1 6 1 1 1 5 1
"1952M1"  1 6 1 1 1 5 1
"1952M2"  6 6 1 1 1 5 1
"1952M3"  6 6 1 1 1 5 1
"1952M4"  6 6 1 1 1 5 1
"1952M5"  6 6 1 1 1 5 1
"1952M6"  6 6 1 1 1 3 1
"1952M7"  6 6 1 1 1 3 1
"1952M8"  6 6 1 1 1 3 1
"1952M9"  6 6 1 1 1 3 1
"1952M10" 6 6 1 1 1 3 1
"1952M11" 6 6 1 1 1 3 1
"1952M12" 6 6 1 1 1 3 1
"1953M1"  6 6 1 1 1 3 1
"1953M2"  6 6 1 1 1 3 1
"1953M3"  6 6 1 1 1 3 1
"1953M4"  6 6 1 1 1 3 1
"1953M5"  6 6 1 1 1 3 1
"1953M6"  6 6 1 1 1 3 1
"1953M7"  6 6 1 1 1 3 1
"1953M8"  6 6 1 1 1 3 1
"1953M9"  6 6 1 1 1 3 1
"1953M10" 6 6 1 1 1 3 1
"1953M11" 6 6 1 1 1 3 1
"1953M12" 6 6 1 1 1 3 1
"1954M1"  6 6 1 1 1 3 1
"1954M2"  6 6 1 1 1 3 1
"1954M3"  6 6 1 1 1 3 1
"1954M4"  6 6 1 1 1 3 1
end

[Ken Chui]

This should work:

gen temp_i = _n
foreach x of varlist AFG-ARM{
    rename `x' y`x'
}

reshape long y, i(temp_i) j(country, string)
drop temp_i

And by the way, this is the code to reformat the date that Stata can recognize:

gen monthdate = monthly(month, "YM")
format monthdate %tm

[Clyde Schechter]

The -foreach- loop can be dispensed with by relying on -rename-'s ability to handle groups of variables:

rename (AFG-ARM) y=

[Jala Youssef]

Thanks a lot Ken Chui and Clyde Schechter for the very useful suggestions, much appreciated. They work perfectly.

Jala

[Nick Cox]

FWIW, I would call the layout in #1 a wide layout.

(My use of the word layout I owe to Clyde Schechter: there is not much wrong with the word format except that it is overloaded: does anyone mean

(1) file format (bytes and bits, binary or text, particular extensions)

(2) data structure or layout

(3) variable or storage type

or

(4) display format? Usually it's not really obscure what is meant with some context, but layout is a great word to use here.)