Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • reshaping based on year month country for multiple files

    Hi all,

    I want to reshape 20 similar files that have same months, countries and years (2000 to 2017). The dataset I have is wide. Here, I have pasted data for just one variable (var).

    I tried several approaches.
    1). Transposing this matrix and then appending them. But, it didn't reshape.
    2). Made one variable for month and year as one variable (monthyear), then reshaped, didn't work.
    3). Tried joining all the 20 files together and then reshaped. Even that didn't work.


    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input str10 Date str3 var int Year str3 Month double(Austria Belgium Cyprus Estonia Finland France Germany Greece Ireland Italy Latvia Lithuania Luxembourg Malta Netherlands Portugal Slovakia Slovenia Spain xEuro)
    "2000-01-01" "UNE" 2000 "Jan" 4.1 6.4 3.9 12.6 8.4 8.8  8.1 8.9 4.3 8.4 13.2 13.8 1.9   5   3 4.3 14.8 5.4 10.6 8.2
    "2000-02-01" "UNE" 2000 "Feb"   4 6.1   4 13.7 8.5 8.6    8   9 4.1 8.4 13.2 13.9 1.9   5   3   4   15 5.4 10.4 8.1
    "2000-03-01" "UNE" 2000 "Mar" 3.9 5.9 4.2   13 8.5 8.5    8   9   4 8.4 13.2 14.1 1.9   5 2.9 3.9 15.2 5.5 10.3 8.1
    "2000-04-01" "UNE" 2000 "Apr" 3.6 5.9 4.6 13.2 8.3 8.5  7.9   9 4.1   8 13.2 14.6 1.9 4.9 2.9 3.8 15.3 5.7 10.3   8
    "2000-05-01" "UNE" 2000 "May" 3.5 5.8 4.6 12.7 8.1 8.4  7.9 8.9   4   8 13.3   15 1.9 4.8 2.8 3.8 15.6 5.8 10.2 7.9
    "2000-06-01" "UNE" 2000 "Jun" 3.4 5.7 4.2 12.9 7.9 8.3  7.9 8.9 3.9   8 13.3 15.2 1.9 4.7 2.8 3.8 15.9 5.9 10.2 7.9
    "2000-07-01" "UNE" 2000 "Jul" 3.6 5.6 3.9 12.7 7.8 8.2  7.8 8.8 3.8 7.8 13.3 15.3 1.9 4.6 2.7 3.8 16.2 5.7 10.2 7.8
    "2000-08-01" "UNE" 2000 "Aug" 3.6 5.7   4 13.1 7.9 8.1  7.8 8.8 3.6 7.8 13.2 15.3 1.9 4.6 2.7   4 15.2 5.5   10 7.8
    "2000-09-01" "UNE" 2000 "Sep" 3.6 5.7 4.2   13 7.9   8  7.8 8.7 3.5 7.8 13.3 15.2 1.8 4.5 2.6   4 15.1 5.3  9.9 7.7
    "2000-10-01" "UNE" 2000 "Oct" 3.5 5.8 4.3 12.7 7.9   8  7.7 8.2 3.3 7.6 13.4 14.9 1.7 4.4 2.6 3.9 15.2 5.2  9.8 7.6
    "2000-11-01" "UNE" 2000 "Nov" 3.4 5.7 4.1 13.1 7.8 7.9  7.7 8.3 3.3 7.6 13.3 14.9 1.6 4.4 2.5 3.6 15.4   5  9.6 7.6
    "2000-12-01" "UNE" 2000 "Dec" 3.4 5.5   4 12.9 7.7 7.9  7.7 8.3 3.3 7.6 13.2 15.2 1.5 4.3 2.5 3.7 15.4   5  9.4 7.5
    "2001-01-01" "UNE" 2001 "Jan" 3.4 5.2 3.5   13 7.9 7.8  7.6 8.3 3.3 7.5   13 16.2 1.4 4.2 2.5 3.7 15.7 4.8  9.1 7.5
    "2001-02-01" "UNE" 2001 "Feb" 3.4 5.1 3.2 12.6 7.9 7.8  7.6 8.3 3.3 7.5 12.7 16.3 1.4 4.1 2.4 3.8 15.6 4.8  8.9 7.4
    "2001-03-01" "UNE" 2001 "Mar" 3.5 5.1 3.1 12.7 7.5 7.7  7.6 8.3 3.2 7.5 12.4 16.2 1.4   4 2.4 3.8 15.6 4.7  8.9 7.4
    "2001-04-01" "UNE" 2001 "Apr" 3.6 5.4 3.5   13 7.4 7.7  7.6 8.4 3.1 7.4   12   16 1.4   4 2.4 3.8 15.8 4.6  8.8 7.4
    "2001-05-01" "UNE" 2001 "May" 3.7 5.4 3.8 12.4 7.6 7.6  7.6 8.4 3.1 7.4 11.9 15.9 1.4   4 2.4 3.8 15.7 4.6    9 7.4
    "2001-06-01" "UNE" 2001 "Jun" 3.8 5.3 3.6 11.7 7.4 7.6  7.7 8.4 3.2 7.4 11.8 15.7 1.4 3.9 2.3 3.8 15.7 4.6  8.8 7.4
    "2001-07-01" "UNE" 2001 "Jul" 3.7   5 3.3 12.1 7.2 7.5  7.7 8.5 3.4 7.3 11.9 15.4 1.4   4 2.3   4 15.7 4.6  8.8 7.4
    "2001-08-01" "UNE" 2001 "Aug" 3.7   5 3.1 11.6 7.5 7.5  7.8 8.4 3.5 7.3   12 15.3 1.5 4.1 2.4 3.9 15.8 4.7  8.9 7.4
    "2001-09-01" "UNE" 2001 "Sep" 3.8 5.4 3.3 11.8 7.8 7.5  7.8 8.4 3.5 7.3   12 15.4 1.5 4.1 2.4 3.8 15.9 4.8  8.8 7.4
    "2001-10-01" "UNE" 2001 "Oct" 3.8 5.9 3.5 10.7 7.5 7.4  7.9 8.7 3.4 7.2 12.7 16.1 1.6 4.1 2.4 3.8   16 4.9  8.8 7.4
    "2001-11-01" "UNE" 2001 "Nov" 3.9 6.1 3.4   11 7.8 7.4  7.9 8.7 3.4 7.2 12.8 16.1 1.6 4.1 2.4 3.8 15.9 5.1  8.9 7.4
    "2001-12-01" "UNE" 2001 "Dec" 4.1 6.2 3.1 10.7 7.4 7.4    8 8.7 3.4 7.2 12.4 15.9 1.6 4.2 2.5 3.8 15.6 5.2  9.1 7.5
    "2002-01-01" "UNE" 2002 "Jan" 4.2 6.1 2.7 10.7 7.5 7.3    8 8.6 3.5 6.9 11.8 15.6 1.7 4.5 2.5 3.9 15.5 5.3  9.4 7.4
    "2002-02-01" "UNE" 2002 "Feb" 4.2 6.1 2.5 10.8 7.5 7.2  8.1 8.6 3.5 6.9 11.6   15 1.7 4.7 2.6 3.9 15.4 5.3  9.8 7.5
    "2002-03-01" "UNE" 2002 "Mar" 4.1 6.1 2.5 10.5 7.1 7.2  8.1 8.6 3.6 6.9   12 14.2 1.8 4.7 2.6 4.1 15.3 5.3  9.7 7.5
    "2002-04-01" "UNE" 2002 "Apr" 3.9 6.1 2.9  9.9 7.4 7.2  8.2 8.3 3.5 7.2 12.5 13.2 1.9 4.6 2.7 4.2 15.3 5.1  9.7 7.5
    "2002-05-01" "UNE" 2002 "May" 3.8 6.2 3.1  9.7 7.9 7.2  8.2 8.3 3.6 7.2 12.5 12.4 2.1 4.6 2.8 4.4 15.4   5  9.7 7.6
    "2002-06-01" "UNE" 2002 "Jun" 3.8 6.2 2.9 10.4 6.9 7.2  8.3 8.3 3.6 7.2   12 11.9 2.1 4.6 2.9 4.7 15.4 4.9  9.8 7.6
    "2002-07-01" "UNE" 2002 "Jul"   4 6.3 2.8 10.7 7.5 7.3  8.5 8.3 3.5   7 10.6 11.8 2.2 4.8 2.9 4.9 15.5 4.8 10.1 7.7
    "2002-08-01" "UNE" 2002 "Aug"   4 6.3 2.9 10.5 7.5 7.3  8.6 8.3 3.5   7 10.2 11.8 2.3   5   3 4.8 15.5 4.8 10.2 7.7
    "2002-09-01" "UNE" 2002 "Sep" 4.1 6.5 3.2 10.6 7.5 7.3  8.7 8.3 3.7   7 10.3 11.8 2.3 4.9 3.1   5 15.5   5 10.1 7.8
    "2002-10-01" "UNE" 2002 "Oct" 4.1 6.7 3.5   11 7.6 7.2  8.8 8.2   4 6.9 11.1   12 2.4 4.8 3.2 5.4 15.4 5.2  9.9 7.8
    "2002-11-01" "UNE" 2002 "Nov" 4.1 6.9 3.4 10.7   7 7.3  8.9 8.1 4.2 6.9 11.4 11.9 2.5 4.8 3.3 5.6 15.4 5.2  9.9 7.8
    "2002-12-01" "UNE" 2002 "Dec" 4.1 6.8 3.3   11 7.4 7.5  9.1 8.1 4.1 6.9   11 11.8 2.5 4.8 3.3 5.6 15.4 5.2  9.9 7.9
    "2003-01-01" "UNE" 2003 "Jan" 4.1 6.7   3 10.4 7.5 7.1  9.2 7.9 3.9 7.1 10.3 11.7 2.6   5 3.3 5.7 15.2 5.2   10 7.9
    "2003-02-01" "UNE" 2003 "Feb" 4.2 6.6 2.9 10.2 6.9 7.1  9.3 7.9 3.8 7.1   10 11.5 2.7   5 3.4 5.8 15.1 5.3 10.1 7.9
    "2003-03-01" "UNE" 2003 "Mar" 4.2 6.6 3.2  9.8 7.3 7.1  9.4 7.9 3.6 7.1 10.1 11.5 2.8 4.9 3.6 5.8   15 5.5   10   8
    "2003-04-01" "UNE" 2003 "Apr" 4.3 6.7 3.7  9.4 7.6 7.2  9.5 7.9 3.7 6.7 10.5 11.3 2.9 4.7 3.7 5.9 14.7 5.6  9.9 7.9
    "2003-05-01" "UNE" 2003 "May" 4.4 6.7 3.8  9.7 7.4 7.2  9.5 7.9 3.7 6.7   11 11.3   3 4.7 3.7   6 14.5 5.6  9.8 7.9
    "2003-06-01" "UNE" 2003 "Jun" 4.4 6.7 3.7  9.1 7.7 7.2  9.5 7.9 3.8 6.7 11.3 11.2 3.1 4.8 3.8   6 14.4 5.6  9.9   8
    "2003-07-01" "UNE" 2003 "Jul" 4.3 6.6 3.6  8.6   7 7.2  9.5 7.8   4 6.8 11.3 11.2 3.2 5.5 3.9 5.9 14.5 5.5   10   8
    "2003-08-01" "UNE" 2003 "Aug" 4.3 6.6 3.5  8.3 6.7 7.2  9.5 7.8   4 6.8 11.2 11.1 3.3 5.7   4 5.8 14.8 5.5   10   8
    "2003-09-01" "UNE" 2003 "Sep" 4.3 6.7 3.8    8 7.3 7.3  9.5 7.8 3.9 6.8 10.9 10.9 3.4 5.4 4.1 5.9 15.1 5.4  9.9   8
    "2003-10-01" "UNE" 2003 "Oct" 4.4 6.7 4.1  8.3 7.2 7.4  9.5   8 3.7 6.8 10.3 10.7 3.5 5.5 4.2 5.9 15.2 5.5  9.8   8
    "2003-11-01" "UNE" 2003 "Nov" 4.5 6.8 3.9  8.2 7.1 7.5  9.5   8 3.7 6.8  9.9 10.6 3.6 5.4 4.3 5.9 15.6 5.4  9.7   8
    "2003-12-01" "UNE" 2003 "Dec" 4.6   7 3.8  8.5 7.3 7.6  9.5 8.1 3.8 6.8   10 10.5 3.7 5.2 4.4 5.7 16.1 5.3  9.8   8
    "2004-01-01" "UNE" 2004 "Jan" 4.4 7.3 3.4  8.5 7.3 7.5  9.5 9.3 4.1 6.7 10.5 10.4 3.9   5 4.4 5.6 16.4 5.2  9.7 8.1

    Can someone please suggest me how do I approach this reshape? Thank you!

    Best,

    Ritika

  • #2
    Code:
    foreach v of var Austria-xEuro {
    rename `v' une`v'
    }
    
    reshape long une, i(Date) j(country) s

    Comment


    • #3
      Thank you, Oyvind Snilsberg. It worked.

      Comment

      Working...
      X