Reshaping from long to wide using around nine variables

[Samuel Danilola]

Dear all,

I am using Stata 16 and trying to reshape this dataset to wide using coy_id as the identifier. What I want to achieve is a unique observation where each "coyid" in a particular year has all its "investorid"s, "f_investorid"s, and the respective countries denoted by "invctny" for the investorids and "f_invctny" for "f_investorid" for each round, invmt_stg.

I am expecting the variables of the reshaped dataset to look like "coyid year invmt_stg round1investorid round1invctny round1inv_fou_date round1f_investorid round1f_invctny round2investorid round2invctny round2f_investorid round2f_invctny round2inv_fou_date...."

I used "reshape wide investor f_investor invctny f_invctny, i(coy_id year invmt_stg inv_fou_date f_inv_found) j(round) string", but I obtained the error "values of variable round not unique within coy_id year invmt_stg inv_fou_date f_inv_found".

The sample dataset is given below.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(coyid investorid) str24 invctny str10 inv_fou_date float f_investorid str10 f_inv_found str24 f_invctny int year str1 round long invmt_stg
 1 134 "Morocco"              "  1/1/2011"  916 "  1/1/2011" "Morocco"              2011 "1"  6
 2  36 "United Arab Emirates" "  1/1/2002"   49 "  1/1/2010" "United Arab Emirates" 2010 "1" 14
 3 973 "Botswana"             "  1/1/2002"  296 "  1/1/2003" "Botswana"             2004 "1"  5
 4 948 "Nigeria"              "  1/1/1960" 1267 "  1/1/1960" "Nigeria"              2021 "1"  4
 4 234 "Nigeria"              "  1/1/2019"  361 "  1/1/2019" "Nigeria"              2021 "1"  4
 4  93 "United States"        " 2/14/2000"  175 "  1/1/2000" "United States"        2021 "1"  4
 4 788 "Nigeria"              "  1/1/2021" 1073 "  1/1/2021" "Nigeria"              2021 "1"  4
 5 948 ""                     "  1/1/1969" 1267 "  1/1/1969" ""                     1998 "1"  5
 5 221 "Togo"                 "  1/1/1997"  345 "  1/1/1998" "Togo"                 2005 "2"  5
 6 948 ""                     "  1/1/1969" 1267 "  1/1/1969" ""                     2005 "1"  6
 6 236 "United States"        "  1/1/2001"  366 "  1/1/2001" "United States"        2008 "2" 12
 6  23 "Germany"              "  1/1/2007"   37 "  1/1/2007" "Germany"              2011 "3" 10
 7  87 "Tunisia"              "  7/1/2007" 1014 "  1/1/2007" "Tunisia"              2011 "1"  4
 8 128 "United Kingdom"       "  7/1/2001"  211 "  1/1/1997" "United Kingdom"       1997 "1"  5
 8 948 "Netherlands"          "  1/1/1960" 1267 "  1/1/1960" "Netherlands"          2010 "2"  1
 8 883 "Sweden"               "  1/1/1979" 1183 "  1/1/1979" "Sweden"               2013 "3"  7
 8 510 "United States"        "  1/1/1956"  725 "  1/1/1956" "United States"        2013 "4"  1
 9 128 "United Kingdom"       "  7/1/2001"  603 "  1/1/1993" "United Kingdom"       1994 "1"  5
10 513 "Mauritius"            " 4/30/2002"  732 "  1/1/2002" "France"               2005 "1"  5
11  52 "Mauritius"            "10/23/2002"   91 "  1/1/2016" "Mauritius"            2016 "1"  6
11 802 "Saudi Arabia"         "  7/1/1987" 1184 "  1/1/2003" "Saudi Arabia"         2021 "2" 16
12  47 "United Kingdom"       "  1/1/1985"   78 "  1/1/2004" "United Kingdom"       2014 "1"  6
13 271 "Australia"            "  1/1/2017"  858 "  1/1/2021" "Australia"            2021 "1"  5
14  60 "Uganda"               "  1/1/2005" 1008 "  1/1/2006" "Uganda"               2008 "1"  5
15 754 "France"               " 8/30/1977" 1032 "  1/1/1977" "France"               2021 "1"  6
15  52 "Mauritius"            "10/23/2002"   91 "  1/1/2016" "Mauritius"            2021 "1"  6
16  36 "United Arab Emirates" "  1/1/2002"  217 "  1/1/2003" "Ghana"                2006 "1"  1
16  36 "United Arab Emirates" "  1/1/2002"  217 "  1/1/2003" "Ghana"                2012 "2"  2
17 571 "Mauritius"            " 1/31/2007"  801 "  1/1/2008" "South Africa"         2011 "1"  7
18 128 "United Kingdom"       "  7/1/2001"  211 "  1/1/1997" "United Kingdom"       1997 "1"  5
18 183 "United Kingdom"       "  1/1/1948"  291 "  1/1/1948" "United Kingdom"       2016 "2" 12
19 470 "United Kingdom"       "  1/1/2004"  661 "  1/1/2014" "United Kingdom"       2014 "1"  1
19 571 "Mauritius"            " 1/31/2007"  800 "  1/1/2015" "South Africa"         2018 "2"  1
20 759 "Egypt"                "  1/1/2004"  362 "  1/1/2004" "Egypt"                2004 "1"  1
21 759 "Egypt"                "  1/1/2004"  362 "  1/1/2004" "Egypt"                2004 "1"  1
21 759 "Egypt"                "  1/1/2004"  362 "  1/1/2004" "Egypt"                2010 "2"  1
21 759 "Egypt"                "  1/1/2004"  362 "  1/1/2004" "Egypt"                2015 "3" 13
22 980 "Nigeria"              "  1/1/2008" 1304 "  1/1/2008" "Nigeria"              2020 "1"  6
23 650 "Netherlands"          "  1/1/1970"  920 "  1/1/1970" "Netherlands"          2007 "1" 18
23 510 "United States"        "  1/1/1956"  725 "  1/1/1956" "United States"        2007 "1" 18
23 291 "Germany"              "01/01/1870"  421 "01/01/1870" "Germany"              2007 "1" 18
24 948 "Tunisia"              "  1/1/1960" 1267 "  1/1/1960" "Tunisia"              2011 "1"  7
24 498 "South Africa"         "  1/1/1940" 1294 "  1/1/2008" "South Africa"         2011 "1"  7
24 507 "South Africa"         "  1/1/2007"  520 "  1/1/2008" "South Africa"         2011 "1"  7
25 948 "United Kingdom"       "  1/1/1960" 1267 "  1/1/1960" "United Kingdom"       2021 "1"  4
25 948 "Nigeria"              "  1/1/1960" 1267 "  1/1/1960" "Nigeria"              2021 "1"  4
25 502 "Nigeria"              "  1/1/2020"  717 "  1/1/2020" "Nigeria"              2021 "1"  4
25 948 ""                     "  1/1/1969" 1267 "  1/1/1969" ""                     2021 "1"  4
26 183 "United Kingdom"       "  1/1/1948"  291 "  1/1/1948" "United Kingdom"       2019 "1"  3
27 948 "South Africa"         "  1/1/1960" 1267 "  1/1/1960" "South Africa"         2011 "1"  6
28 940 "Nigeria"              "  5/1/2005" 1258 "  1/1/2004" "Nigeria"              2002 "1" 18
28 940 "Nigeria"              "  5/1/2005" 1258 "  1/1/2004" "Nigeria"              2006 "2"  5
29 974 "Nigeria"              "  1/1/2004" 1297 "  1/1/2004" "Nigeria"              2004 "1"  5
30  61 "Nigeria"              "  9/1/1997" 1087 "  1/1/2001" "Nigeria"              2003 "1"  5
31  61 "Nigeria"              "  9/1/1997" 1086 "  1/1/2003" "Nigeria"              2005 "1" 18
31  61 "Nigeria"              "  9/1/1997" 1087 "  1/1/2001" "Nigeria"              2005 "1" 18
31 510 "United States"        "  1/1/1956"  724 "  1/1/2000" "United States"        2005 "2"  5
32 538 "South Africa"         "  1/1/2018"  764 "  1/1/2019" "South Africa"         2021 "1" 14
33  47 "United Kingdom"       "  1/1/1985"   75 "  1/1/1996" "United Kingdom"       2004 "1" 18
34 470 "United Kingdom"       "  1/1/2004"  659 "  1/1/2015" "United Kingdom"       2015 "1"  1
34 470 "United Kingdom"       "  1/1/2004"  659 "  1/1/2015" "United Kingdom"       2020 "2"  1
35 123 "South Africa"         "  1/1/2012"  205 "  1/1/2012" "South Africa"         2021 "1"  1
36  47 "United Kingdom"       "  1/1/1985"   76 "  1/1/2004" "South Africa"         2005 "1" 19
36 259 "Canada"               "  1/1/1999"  312 "  1/1/2004" "Canada"               2005 "1" 19
36  47 "United Kingdom"       "  1/1/1985"   75 "  1/1/1996" "United Kingdom"       2005 "1" 19
37 495 "South Africa"         "  1/1/2017"  708 "  1/1/2017" "South Africa"         2021 "1"  1
38 971 "South Africa"         "  1/1/2004"  709 "  1/1/2008" "South Africa"         2011 "1"  5
39 743 "Nigeria"              "  1/1/2021" 1268 "  1/1/2021" "Nigeria"              2021 "1"  7
40 948 "United Kingdom"       "  1/1/1960" 1267 "  1/1/1960" "United Kingdom"       2011 "1"  5
40 650 "Netherlands"          "  1/1/1970"  920 "  1/1/1970" "Netherlands"          2011 "1"  5
40 883 "Sweden"               "  1/1/1979" 1183 "  1/1/1979" "Sweden"               2011 "1"  5
41 883 "Sweden"               "  1/1/1979" 1183 "  1/1/1979" "Sweden"               2006 "1"  4
41 948 "Sweden"               "  1/1/1960" 1267 "  1/1/1960" "Sweden"               2006 "1"  4
42  13 "United Kingdom"       "  1/1/2015"   21 "  1/1/2015" "United Kingdom"       2016 "1"  7
42  45 "Switzerland"          "12/15/2005"   72 "  1/1/2005" "Switzerland"          2016 "1"  7
43 883 "Sweden"               "  1/1/1979" 1183 "  1/1/1979" "Sweden"               2008 "1"  4
44 357 "Luxembourg"           "  1/1/1957"  531 "  1/1/2002" "Luxembourg"           2008 "1" 19
45 104 "United Kingdom"       "  1/1/2014"  188 "  1/1/2018" "United Kingdom"       2021 "1"  1
45 510 "United States"        "  1/1/1956"  694 "  1/1/2015" "United States"        2021 "1"  1
45 510 "United States"        "  1/1/1956"  725 "  1/1/1956" "United States"        2021 "1"  1
46 948 "United Kingdom"       "  1/1/1960" 1267 "  1/1/1960" "United Kingdom"       2020 "1"  6
46 292 "United Kingdom"       "  1/1/2007"  117 "  1/1/2019" "United Kingdom"       2020 "1"  6
47 437 "United States"        "  1/1/1986"  620 "  1/1/1986" "United States"        2019 "1"  1
48  10 "South Africa"         " 3/31/2009"   16 "  1/1/2010" "South Africa"         2017 "1"  4
48 822 "Kenya"                "  1/1/2012" 1113 "  1/1/2012" "Kenya"                2017 "1"  4
48 948 "United States"        "  1/1/1969" 1267 "  1/1/1985" "United States"        2018 "2"  5
48  65 "United States"        "  1/1/2014"  131 "  1/1/2014" "United States"        2018 "2"  5
48 648 "South Africa"         "  1/1/2003"  917 "  1/1/2004" "South Africa"         2018 "2"  5
48 948 "South Africa"         "  1/1/1960" 1267 "  1/1/1960" "South Africa"         2018 "2"  5
48  10 "South Africa"         " 3/31/2009"   16 "  1/1/2010" "South Africa"         2018 "2"  5
48 822 "Kenya"                "  1/1/2012" 1113 "  1/1/2012" "Kenya"                2018 "2"  5
48 948 "United States"        "  1/1/1968" 1267 "  1/1/1968" "United States"        2018 "2"  5
48 722 "South Africa"         "  1/1/2014"  997 "  1/1/2014" "South Africa"         2019 "3"  5
48 755 "Netherlands"          "  1/1/2015" 1033 "  1/1/2015" "South Africa"         2020 "4"  5
49 223 "Morocco"              "  1/1/2001"   66 "  1/1/2001" "Morocco"              2003 "1"  1
50 178 "South Africa"         "  1/1/1991" 1162 "  1/1/1998" "South Africa"         2000 "1" 12
50 367 "Canada"               " 5/23/2011"  532 "  1/1/2017" "United States"        2017 "2" 12
51 261 "Lebanon"              "  1/1/1998"  845 "  1/1/1969" "Lebanon"              1998 "1"  1
52 225 "Cameroon"             " 11/1/1998"  347 "  1/1/1999" "Cameroon"             2004 "1" 18
53 853 "Cameroon"             "  1/1/1990"  530 "  1/1/1990" "Cameroon"             2003 "1" 18
end
label values invmt_stg invmt_stg
label def invmt_stg 1 "Acquisition", modify
label def invmt_stg 2 "Acquisition for Expansion", modify
label def invmt_stg 3 "Bridge Loan", modify
label def invmt_stg 4 "Early Stage", modify
label def invmt_stg 5 "Expansion", modify
label def invmt_stg 6 "LBO", modify
label def invmt_stg 7 "Later Stage", modify
label def invmt_stg 10 "Open Market Purchase", modify
label def invmt_stg 12 "PIPE", modify
label def invmt_stg 13 "Pending Acquisition", modify
label def invmt_stg 14 "Real Estate", modify
label def invmt_stg 16 "Secondary Buyout", modify
label def invmt_stg 18 "Seed", modify
label def invmt_stg 19 "VC Partnership", modify

[Fei Wang]

Samuel, I don't think your idea works. For example, there are four observations for coyid 4, and all are round 1 - How could this subject be reshaped wide? Shouldn't that be round1, round2, round3 and round4?

[Samuel Danilola]

Thank you, Fei. Since it is one round and same year, they could all form different variables in observation one. So observation 1 could have four different columns for investorsid. Is it possible to achieve that?

[Fei Wang]

Samuel, I don't think it's possible. If the observation of coyid 4 has four different columns for investorsid, and as you said in #1, they will all be named "round1investorsid" -- Different variables cannot have the same names. Or maybe I misunderstood your request, and it would be helpful to share some concrete examples of what the dataset looks like after reshaping -- For example, how would the four rows of coyid 4 look like after reshaping?

[Samuel Danilola]

I see it is not possible. Thank you Fei. I would need to check my data again. Or maybe generate another unique identifier.

What I actually want would be "round1investor1", "round1investor2", "round1investor3", "round1investor4" as they are round 1 in the same year, 2021