long to wide transformation of panel data with many variables

**mradzik79** · 04-01-2012, 04:07 AM

Hello, I would be very much obliged if you could let me know how to transform the long format of panel data, like this:

country year gdp taxes gini ...
Chile 1970 4.2 17 45
Chile 1971 4.8 18 46
.
.
.
Chile 2010 8.1 26 43
USA 1970 15.2 23 36
USA 1971 16.3 24 35
.
.
.
USA 2010 26.8 37 39
.
.
.

into the wide format:

country vars 1970 1971 ... 2010
Chile gdp 4.2 4.8 ... 8.1
Chile taxes 17 18 ... 26
Chile gini 45 46 ... 43
. .
. .
. .
USA gdp 15.2 16.3 ... 26.8
USA taxes 23 24 ... 37
USA gini 36 35 ... 39
. .
. .
. .

So, many countries, many variables and... yeah, it would save a lot of time if you could help me

Kind regards and thanks in advance.

**stanleydgromjr** · 04-01-2012, 05:25 AM

mradzik79,

Welcome to the Excel Forum.

It is difficult to determine what cells, rows, columns, your raw data is in.

It is difficult to determine what cells, rows, columns, your results are in.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

**mradzik79** · 04-01-2012, 06:01 AM

Thanks for the reply and please find attached the sample workbook. Of course there is much more countries, variables and years in the real one.

Best regards

**stanleydgromjr** · 04-07-2012, 07:24 PM

mradzik79,

Thanks for the workbook.

Detach/open workbook ReorgData - mradzik79 - EF822571 - SDG15.xlsm and run the ReorgData macro.

If you want to use the macro on another workbook:

With your raw data on worksheet 1 (the left most worksheet when viewing the list of worksheet tabs), already grouped/sorted per your example.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

**jindon** · 04-08-2012, 01:35 AM

..........

**jindon** · 04-08-2012, 02:03 AM

try

Please Login or Register  to view this content.

**stanleydgromjr** · 04-08-2012, 08:39 AM

jindon,

As always, WOW!!!!

I am learning how to use arrays to manipulate data.

But, my code took 0.344 seconds, and yours took 0.016 seconds.

Another one for my archives, and to study how your code works.

**rloiselle** · 10-05-2012, 12:35 PM

Hi- I am new to VBA and have a similar problem to this user. I tried this code and it worked great for my needs as well! However, I have an additional column that I'd be interested in grouping sets of data by. Following the previous example:

Month country year gdp taxes gini
1 Chile 1970 4.2 17 45
2 Chile 1970 3.2 16 44
1 Chile 1971 4.8 18 46
2 Chile 1971 4.05 17.25 45.25
1 Chile 1972 8.1 26 43
2 Chile 1972 10.4 28.3 45.3
1 USA 1970 15.2 23 36
2 USA 1970 14.9 22.7 35.7
1 USA 1971 16.3 24 35
2 USA 1971 17.5 25.2 36.2
1 USA 1972 26.8 37 39
2 USA 1972 26.46 36.66 38.66

How could I get the code to maintain the results but perform the analysis separately for each unique month? Below are the results I am looking for:

Month 1 country 1970 1971 1972
Month 1 Chile gdp 4.2 4.8 8.1
Month 1 Chile taxes 17 18 26
Month 1 Chile gini 45 46 43
Month 1 USA gdp 15.2 16.3 26.8
Month 1 USA taxes 23 24 37
Month 1 USA gini 36 35 39
Month 2 Chile gdp 3.2 3.8 10.4
Month 2 Chile taxes 16 17 28.3
Month 2 Chile gini 44 45 45.3
Month 2 USA gdp 14.9 16.1 15.76
Month 2 USA taxes 22.7 23.9 23.56
Month 2 USA gini 35.7 36.9 36.56

Thanks in advance for any help!

**jindon** · 10-05-2012, 02:44 PM

rloiselle

Rule says

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

So, you need to open a new thread...

long to wide transformation of panel data with many variables

LinkBack

Thread Tools

Rate This Thread

Display

long to wide transformation of panel data with many variables

Re: long to wide transformation of panel data with many variables

Re: long to wide transformation of panel data with many variables

Re: long to wide transformation of panel data with many variables

Re: long to wide transformation of panel data with many variables

Re: long to wide transformation of panel data with many variables

Re: long to wide transformation of panel data with many variables

Re: long to wide transformation of panel data with many variables

Re: long to wide transformation of panel data with many variables

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions