Reshaping data

[Henk Janssen]

Dear,

I need to transform the following data:

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 ISIN str16 Type int year str45 value str109 Name float dup str10 cusip
"AEA000201011" "ESG Compensation" 2008 "NA"                "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG Compensation" 2009 "NA"                "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG Compensation" 2010 "NA"                "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG Compensation" 2011 "NA"                "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG Compensation" 2012 "NA"                "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG Compensation" 2013 "NA"                "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG Compensation" 2014 "N   "              "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG Compensation" 2015 "N   "              "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG Compensation" 2016 "N   "              "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG Compensation" 2017 "N   "              "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG Compensation" 2018 "N   "              "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG Compensation" 2019 "N   "              "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG Compensation" 2020 "NA"                "ABU DHABI COML.BANK - Compensation Policy Elements/Policy ESG Related Compensation"    0 "EA00020101"
"AEA000201011" "ESG score"        2008 "NA"                "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "ESG score"        2009 "NA"                "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "ESG score"        2010 "NA"                "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "ESG score"        2011 "NA"                "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "ESG score"        2012 "NA"                "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "ESG score"        2013 "NA"                "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "ESG score"        2014 "48.69"             "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "ESG score"        2015 "54.26"             "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "ESG score"        2016 "63.8"              "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "ESG score"        2017 "66.44"             "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "ESG score"        2018 "70.73999999999999" "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "ESG score"        2019 "66.92"             "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "ESG score"        2020 "NA"                "ABU DHABI COML.BANK - ESG Score"                                                       0 "EA00020101"
"AEA000201011" "Social Pillar"    2008 "NA"                "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000201011" "Social Pillar"    2009 "NA"                "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000201011" "Social Pillar"    2010 "NA"                "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000201011" "Social Pillar"    2011 "NA"                "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000201011" "Social Pillar"    2012 "NA"                "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000201011" "Social Pillar"    2013 "NA"                "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000201011" "Social Pillar"    2014 "45.3"              "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000201011" "Social Pillar"    2015 "47.82"             "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000201011" "Social Pillar"    2016 "63.22"             "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000201011" "Social Pillar"    2017 "64.94"             "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000201011" "Social Pillar"    2018 "65.98999999999999" "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000201011" "Social Pillar"    2019 "62.92"             "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000201011" "Social Pillar"    2020 "NA"                "ABU DHABI COML.BANK - Social Pillar Score"                                             0 "EA00020101"
"AEA000801018" "ESG Compensation" 2008 "NA"                "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG Compensation" 2009 "NA"                "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG Compensation" 2010 "NA"                "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG Compensation" 2011 "NA"                "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG Compensation" 2012 "NA"                "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG Compensation" 2013 "NA"                "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG Compensation" 2014 "NA"                "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG Compensation" 2015 "NA"                "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG Compensation" 2016 "NA"                "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG Compensation" 2017 "NA"                "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG Compensation" 2018 "NA"                "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG Compensation" 2019 "N   "              "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG Compensation" 2020 "NA"                "ABU DHABI ISLAMIC BANK - Compensation Policy Elements/Policy ESG Related Compensation" 0 "EA00080101"
"AEA000801018" "ESG score"        2008 "NA"                "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "ESG score"        2009 "NA"                "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "ESG score"        2010 "NA"                "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "ESG score"        2011 "NA"                "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "ESG score"        2012 "NA"                "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "ESG score"        2013 "NA"                "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "ESG score"        2014 "NA"                "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "ESG score"        2015 "NA"                "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "ESG score"        2016 "NA"                "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "ESG score"        2017 "NA"                "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "ESG score"        2018 "NA"                "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "ESG score"        2019 "31.74"             "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "ESG score"        2020 "NA"                "ABU DHABI ISLAMIC BANK - ESG Score"                                                    0 "EA00080101"
"AEA000801018" "Social Pillar"    2008 "NA"                "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA000801018" "Social Pillar"    2009 "NA"                "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA000801018" "Social Pillar"    2010 "NA"                "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA000801018" "Social Pillar"    2011 "NA"                "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA000801018" "Social Pillar"    2012 "NA"                "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA000801018" "Social Pillar"    2013 "NA"                "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA000801018" "Social Pillar"    2014 "NA"                "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA000801018" "Social Pillar"    2015 "NA"                "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA000801018" "Social Pillar"    2016 "NA"                "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA000801018" "Social Pillar"    2017 "NA"                "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA000801018" "Social Pillar"    2018 "NA"                "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA000801018" "Social Pillar"    2019 "29.55"             "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA000801018" "Social Pillar"    2020 "NA"                "ABU DHABI ISLAMIC BANK - Social Pillar Score"                                          0 "EA00080101"
"AEA001501013" "ESG Compensation" 2008 "NA"                "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG Compensation" 2009 "NA"                "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG Compensation" 2010 "NA"                "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG Compensation" 2011 "NA"                "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG Compensation" 2012 "NA"                "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG Compensation" 2013 "NA"                "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG Compensation" 2014 "N   "              "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG Compensation" 2015 "N   "              "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG Compensation" 2016 "N   "              "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG Compensation" 2017 "N   "              "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG Compensation" 2018 "N   "              "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG Compensation" 2019 "NA"                "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG Compensation" 2020 "NA"                "ARABTEC HOLDING - Compensation Policy Elements/Policy ESG Related Compensation"        0 "EA00150101"
"AEA001501013" "ESG score"        2008 "NA"                "ARABTEC HOLDING - ESG Score"                                                           0 "EA00150101"
"AEA001501013" "ESG score"        2009 "NA"                "ARABTEC HOLDING - ESG Score"                                                           0 "EA00150101"
"AEA001501013" "ESG score"        2010 "NA"                "ARABTEC HOLDING - ESG Score"                                                           0 "EA00150101"
"AEA001501013" "ESG score"        2011 "NA"                "ARABTEC HOLDING - ESG Score"                                                           0 "EA00150101"
"AEA001501013" "ESG score"        2012 "NA"                "ARABTEC HOLDING - ESG Score"                                                           0 "EA00150101"
"AEA001501013" "ESG score"        2013 "NA"                "ARABTEC HOLDING - ESG Score"                                                           0 "EA00150101"
"AEA001501013" "ESG score"        2014 "13.47"             "ARABTEC HOLDING - ESG Score"                                                           0 "EA00150101"
"AEA001501013" "ESG score"        2015 "14.63"             "ARABTEC HOLDING - ESG Score"                                                           0 "EA00150101"
"AEA001501013" "ESG score"        2016 "19.7"              "ARABTEC HOLDING - ESG Score"                                                           0 "EA00150101"
end

I basically need to split the Type column into three different columns for ESG Compensation/ESG Score/Social Pillar, under which then the values in column 'value' have to be put per year. Is there an easy way to achieve this?

[Clyde Schechter]

This is basically a -reshape- job, but the data needs some tweaking to get ready for that. The variable Name already reflects the contents of Type, and will get in the way of the reshape. So that has to be stripped down to just the name of the firm. And you cannot have variables with names like "ESG Compensation" etc., because embedded blanks are not legal in variable names. So those have to be cleaned up as well. Finally, although this will not affect the ability to do the -reshape-, the variable value seems to be intended to be numeric, but it is currently a string, with missing values represented by "NA" and, mysteriously, also by "N ". While you can proceed with the -reshape- without changing that, you will then have to do three separate reshapes later if you plan to ever do any calculations with these numbers, so better to get it out of the way now.

replace Type = strtoname(lower(Type))
replace Name = substr(Name, 1, strpos(Name, " - ")-1)
compress Name
destring value, ignore("NA") replace
reshape wide value, i(ISIN year) j(Type) string
rename value* *

[Henk Janssen]

-Clyde
Thanks for the perfect help, this seems to be exactly what I needed.