data reshape query

Sanaullah Farooq

Join Date: Mar 2016

Posts: 107
#1

data reshape query

31 Oct 2018, 20:00

Hi
I have downloaded corporate governance data of yearly frequency from Bloomberg terminals. I want to reshape it in the panel data formation. Could someone please help me with that? Blow I have shown the current arrangement of this data for two companies. Help would be appreciated. thanks.
Tags: None
Marcos Almeida

Join Date: Apr 2014

Posts: 4047
#2

31 Oct 2018, 20:47

I believe you must know, since you have more than 7 dozen posts in this Forum and, quite probably, you have already read the FAQ, but I wish to underline that snapshots are not the recommended approach to share data. Sorry for repeating this again, but using code delimiters (or dataex) is the recommended approach to entice a truly helpful reply.

Best regards,

Marcos
Comment

Sanaullah Farooq

Join Date: Mar 2016
Posts: 107

31 Oct 2018, 21:20

My apologies, please review the data as follows in proper format. Thanks.

Code:

 Firm ID
Variable
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017

1218069D LN Equity
NUMBER_OF_WOMEN_ON_BOARD
.
.
.
.
.
.
.
.
1
1
1
1
1
1
1


PCT_WOMEN_ON_BOARD
.
.
0
0
0
10
11.11
11.11
11.111
11.111
11.111
11.111
11.111
11.111
11.111


NUMBER_OF_FEMALE_EXECUTIVES
.
.
.
.
.
.
.
.
1
1
1
1
1
1
1


PERCENTAGE_OF_FEMALE_EXECUTIVES
.
.
.
.
.
.
.
.
25
25
25
25
25
25
25


FEMALE_CEO_OR_EQUIVALENT
.
.
.
.
.
.
.
.
0
0
0
0
0
0
0


FEMALE_CHAIRPERSON_OR_EQUIVALENT
.
.
.
.
.
.
.
.
0
0
0
0
0
0
0


PCT_WOMEN_ON_BOARD
.
.
.
.
.
0
0
0
0
0
0
0
0
0
0

1343467D LN Equity
NUMBER_OF_WOMEN_ON_BOARD
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1


PCT_WOMEN_ON_BOARD
12.5
12.5
12.5
12.5
12.5
12.5
12.5
12.5
12.5
12.5
12.5
12.5
12.5
12.5
12.5


NUMBER_OF_FEMALE_EXECUTIVES
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0


PERCENTAGE_OF_FEMALE_EXECUTIVES
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0


FEMALE_CEO_OR_EQUIVALENT
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0


FEMALE_CHAIRPERSON_OR_EQUIVALENT
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0

Comment

David Benson

Join Date: Oct 2018
Posts: 489

31 Oct 2018, 22:32

Sanaullah,

I got started on this, but then got stuck and I have to log off. But let me post a few things to help you get started on this (as well as help others who will come along to help you).

1. I pasted your data above (but deleted the "LN Equity" (it must have been a hidden column in your screenshot).

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 FirmID str32 Variable double(C D E F G H I J K L M N O P Q)
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"            .    .    .    .    .    .     .     .      1      1      1      1      1      1      1
""         "PCT_WOMEN_ON_BOARD"                  .    .    0    0    0   10 11.11 11.11 11.111 11.111 11.111 11.111 11.111 11.111 11.111
""         "NUMBER_OF_FEMALE_EXECUTIVES"         .    .    .    .    .    .     .     .      1      1      1      1      1      1      1
""         "PERCENTAGE_OF_FEMALE_EXECUTIVES"     .    .    .    .    .    .     .     .     25     25     25     25     25     25     25
""         "FEMALE_CEO_OR_EQUIVALENT"            .    .    .    .    .    .     .     .      0      0      0      0      0      0      0
""         "FEMALE_CHAIRPERSON_OR_EQUIVALENT"    .    .    .    .    .    .     .     .      0      0      0      0      0      0      0
""         "PCT_WOMEN_ON_BOARD"                  .    .    .    .    .    0     0     0      0      0      0      0      0      0      0
"1343467D" "NUMBER_OF_WOMEN_ON_BOARD"            1    1    1    1    1    1     1     1      1      1      1      1      1      1      1
""         "PCT_WOMEN_ON_BOARD"               12.5 12.5 12.5 12.5 12.5 12.5  12.5  12.5   12.5   12.5   12.5   12.5   12.5   12.5   12.5
""         "NUMBER_OF_FEMALE_EXECUTIVES"         0    0    0    0    0    0     0     0      0      0      0      0      0      0      0
""         "PERCENTAGE_OF_FEMALE_EXECUTIVES"     0    0    0    0    0    0     0     0      0      0      0      0      0      0      0
""         "FEMALE_CEO_OR_EQUIVALENT"            0    0    0    0    0    0     0     0      0      0      0      0      0      0      0
""         "FEMALE_CHAIRPERSON_OR_EQUIVALENT"    0    0    0    0    0    0     0     0      0      0      0      0      0      0      0
end

Note that Stata doesn't allow variables to start with numbers, so it converted the years to "C" "D" "E" "F", etc. Also note that your Excel data has two entries for PCT_WOMEN_ON_BOARD (and they differ).

Code:

* Rename all of the variables
local temp_year = 2003
foreach var of varlist C-Q {

    rename `var' year_`temp_year'
    local temp_year = `temp_year' + 1
    display `temp_year'
 }

* Carrying forward the firm_id
replace FirmID = FirmID[_n-1] if FirmID==""

* Initial attempt to reshape (& found the duplicate)
reshape long year_, i( FirmID Variable) j(year)
reshape error

/*
NOTE: Below is in red because the Stata error was in red
variable id does not uniquely identify the observations
    Your data are currently wide.  You are performing a reshape long.  You specified i(FirmID Variable) and j(year).  In the current wide
    form, variable FirmID Variable should uniquely identify the observations.  Remember this picture:

         long                                wide
        +---------------+                   +------------------+
        | i   j   a   b |                   | i   a1 a2  b1 b2 |
        |---------------| <--- reshape ---> |------------------|
        | 1   1   1   2 |                   | 1   1   3   2  4 |
        | 1   2   3   4 |                   | 2   5   7   6  8 |
        | 2   1   5   6 |                   +------------------+
        | 2   2   7   8 |
        +---------------+
    Type reshape error for a list of the problem observations.
r(9);

. reshape error
(note: j = 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017)

i (FirmID Variable) indicates the top-level grouping such as subject id.

The data are currently in the wide form; there should be a single
observation per i.

2 of 13 observations have duplicate i values:

     +-------------------------------+
     |   FirmID             Variable |
     |-------------------------------|
  5. | 1218069D   PCT_WOMEN_ON_BOARD |
  6. | 1218069D   PCT_WOMEN_ON_BOARD |
     +-------------------------------+

(data now sorted by FirmID Variable)
*/

I then dropped observation 5 and reshaped it. And that is where I ran out of time.

Code:

gen id = _n

ssc install placevar
placevar id, first
drop if id==5    // check to make sure id==5 is the right duplicate to drop
reshape long year_,  i(FirmID Variable) j(year)
rename year_  value

Once that was done, the data look like this. But I don't know how to now move the various variables (i.e. "PCT_WOMEN_ON_BOARD") within Variable to columns

Code:

. desc

Contains data
  obs:           180                          
 vars:             5                          
 size:         9,720                          
---------------------------------------------------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
---------------------------------------------------------------------------------------------------------------------------------------------
FirmID          str8    %9s                   Firm ID
Variable        str32   %32s                  Variable
year            int     %9.0g                
id              float   %9.0g                
value           double  %10.0g                
---------------------------------------------------------------------------------------------------------------------------------------------
Sorted by: FirmID  Variable  year
     Note: Dataset has changed since last saved.

. tabulate Variable

                        Variable |      Freq.     Percent        Cum.
---------------------------------+-----------------------------------
        FEMALE_CEO_OR_EQUIVALENT |         30       16.67       16.67
FEMALE_CHAIRPERSON_OR_EQUIVALENT |         30       16.67       33.33
     NUMBER_OF_FEMALE_EXECUTIVES |         30       16.67       50.00
        NUMBER_OF_WOMEN_ON_BOARD |         30       16.67       66.67
              PCT_WOMEN_ON_BOARD |         30       16.67       83.33
 PERCENTAGE_OF_FEMALE_EXECUTIVES |         30       16.67      100.00
---------------------------------+-----------------------------------
                           Total |        180      100.00

The Dataex command with the data in its "halfway" long format is:

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 FirmID str32 Variable int year float id double value
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2003  5  .
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2004  5  .
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2005  5  .
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2006  5  .
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2007  5  .
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2008  5  .
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2009  5  .
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2010  5  .
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2011  5  0
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2012  5  0
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2013  5  0
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2014  5  0
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2015  5  0
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2016  5  0
"1218069D" "FEMALE_CEO_OR_EQUIVALENT"         2017  5  0
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2003  6  .
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2004  6  .
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2005  6  .
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2006  6  .
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2007  6  .
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2008  6  .
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2009  6  .
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2010  6  .
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2011  6  0
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2012  6  0
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2013  6  0
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2014  6  0
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2015  6  0
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2016  6  0
"1218069D" "FEMALE_CHAIRPERSON_OR_EQUIVALENT" 2017  6  0
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2003  3  .
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2004  3  .
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2005  3  .
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2006  3  .
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2007  3  .
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2008  3  .
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2009  3  .
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2010  3  .
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2011  3  1
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2012  3  1
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2013  3  1
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2014  3  1
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2015  3  1
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2016  3  1
"1218069D" "NUMBER_OF_FEMALE_EXECUTIVES"      2017  3  1
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2003  1  .
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2004  1  .
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2005  1  .
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2006  1  .
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2007  1  .
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2008  1  .
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2009  1  .
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2010  1  .
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2011  1  1
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2012  1  1
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2013  1  1
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2014  1  1
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2015  1  1
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2016  1  1
"1218069D" "NUMBER_OF_WOMEN_ON_BOARD"         2017  1  1
"1218069D" "PCT_WOMEN_ON_BOARD"               2003  7  .
"1218069D" "PCT_WOMEN_ON_BOARD"               2004  7  .
"1218069D" "PCT_WOMEN_ON_BOARD"               2005  7  .
"1218069D" "PCT_WOMEN_ON_BOARD"               2006  7  .
"1218069D" "PCT_WOMEN_ON_BOARD"               2007  7  .
"1218069D" "PCT_WOMEN_ON_BOARD"               2008  7  0
"1218069D" "PCT_WOMEN_ON_BOARD"               2009  7  0
"1218069D" "PCT_WOMEN_ON_BOARD"               2010  7  0
"1218069D" "PCT_WOMEN_ON_BOARD"               2011  7  0
"1218069D" "PCT_WOMEN_ON_BOARD"               2012  7  0
"1218069D" "PCT_WOMEN_ON_BOARD"               2013  7  0
"1218069D" "PCT_WOMEN_ON_BOARD"               2014  7  0
"1218069D" "PCT_WOMEN_ON_BOARD"               2015  7  0
"1218069D" "PCT_WOMEN_ON_BOARD"               2016  7  0
"1218069D" "PCT_WOMEN_ON_BOARD"               2017  7  0
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2003  4  .
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2004  4  .
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2005  4  .
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2006  4  .
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2007  4  .
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2008  4  .
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2009  4  .
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2010  4  .
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2011  4 25
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2012  4 25
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2013  4 25
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2014  4 25
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2015  4 25
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2016  4 25
"1218069D" "PERCENTAGE_OF_FEMALE_EXECUTIVES"  2017  4 25
"1343467D" "FEMALE_CEO_OR_EQUIVALENT"         2003 12  0
"1343467D" "FEMALE_CEO_OR_EQUIVALENT"         2004 12  0
"1343467D" "FEMALE_CEO_OR_EQUIVALENT"         2005 12  0
"1343467D" "FEMALE_CEO_OR_EQUIVALENT"         2006 12  0
"1343467D" "FEMALE_CEO_OR_EQUIVALENT"         2007 12  0
"1343467D" "FEMALE_CEO_OR_EQUIVALENT"         2008 12  0
"1343467D" "FEMALE_CEO_OR_EQUIVALENT"         2009 12  0
"1343467D" "FEMALE_CEO_OR_EQUIVALENT"         2010 12  0
"1343467D" "FEMALE_CEO_OR_EQUIVALENT"         2011 12  0
"1343467D" "FEMALE_CEO_OR_EQUIVALENT"         2012 12  0
end

Hopefully that helps (both you and others trying to help!)

Last edited by David Benson; 31 Oct 2018, 22:34.

Comment

Yu Hu

Join Date: Oct 2018

Posts: 4
#5

31 Oct 2018, 22:43

Try this:
First I noticed your data is in excel. Rename in excel every year value "year####" (e.g., rename 2003 as year2003). Then import to Stata and do the following:

Code:

replace FirmID = FirmID[_n-1] if mi(FirmID) egen g1=group(FirmID Variable) reshape long year, i(g1) j(date) egen g2 = group(FirmID date) drop g1 reshape wide year, i(g2) j(Variable) string rename year* *
2 likes
Comment

Announcement