Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Restructing the dataset varables

    Hello All,

    I have a dataset that contains variables regarding several firms. The variables are the following: Firm Name, Penalty Amount, Year, City, and Domain. My issue is that the same firm appears many times in different years for different occasions, whereas I would strongly like to have one line for each firm. I would highly appreciate if you could advise me if there is a way to restructure the dataset in a way so that I could have one line for each firm followed by all the characteristic in columns. Additionally, to differentiate the added columns, it would have been great if I could add some numbers before added variables, e.g., 1_Penaly Amount, 1_Year, 1City, etx.

    To bring you an example, let's say my row dataset looks like this:
    Firm Name Penalty Amount Year City Domain
    B 1000$ 2015 Barcelona Medical
    A 2500$ 2016 Tokyo Chemical
    A 1250$ 2003 Rio de Janeiro Chemical
    I want my dataset to look like the following:
    Firm Name Penalty Amount Year City Domain 1_Pentalty Amount 1_Year 1_City 1_Domain
    B 1000$ 2015 Barcelona Medical
    A 1250$ 2016 Tokyo Chemical 1250$ 2003 Rio de Janeiro Chemical
    I would really appreciate any support you can give.

    Best regards,
    Nick
    Tags: None
  • #2
    Hi Nick,
    If you provide a data example using the -dataex command, I can provide you with the code you need.

    Comment

    • #3
      Originally posted by Emil Alnor View Post
      Hi Nick,
      If you provide a data example using the -dataex command, I can provide you with the code you need.
      Hi Emil, thank you very much for your willingness. The dataset is huge, and my example was overly simplified; however, I show below the dataset retrieved by dataex.


      Code:
      * Example generated by -dataex-. For more info, type help dataex
clear
input strL company str14 subtractionfrompenalty int penaltyyear str60 city str42 domain
"ABC Supply Company,  Inc."                       "$0" 2007 "Columbia"        "export wholesalers"
"Advantage Logistics"                             "$0" 2001 "Oglesby"         "export wholesalers"
"Advantage Logistics"                             "$0" 2007 "Aurora"          "export wholesalers"
"Alliant Food Service, Inc."                      "$0" 2001 "Swedesboro"      "export wholesalers"
"Allied Building Products Corp."                  "$0" 2010 "East Rutherford" "export wholesalers"
"Allied Building Products Corp., Inc."            "$0" 2003 "West Allis"      "export wholesalers"
"ALLIED BUILDING PRODUCTS, CORP."                 "$0" 2000 "Cincinnat"       "export wholesalers"
"AMERICAN PROCUREMENT & LOGISTICS CO"             "$0" 2005 "EDEN PRAIRIE"    "export wholesalers"
"AmeriSourceBergen"                               "$0" 2003 "San Jose"        "export wholesalers"
"AmerisourceBergen"                               "$0" 2006 "Thorofare"       "export wholesalers"
"AmerisourceBergen Corporation"                   "$0" 2008 "Kent"            "export wholesalers"
"Amerisourcebergen Specialty Groups"              "$0" 2008 "Frisco"          "export wholesalers"
"Andersons, Inc."                                 "$0" 2015 ""                "export wholesalers"
"The Andersons Inc."                              "$0" 2007 ""                "export wholesalers"
"THE ANDERSONS, INC."                             "$0" 2016 ""                "export wholesalers"
"THE ANDERSONS, INC."                             "$0" 2011 ""                "export wholesalers"
"THE ANDERSONS, INC."                             "$0" 2010 ""                "export wholesalers"
"Anixter, Inc."                                   "$0" 2004 "Alsip"           "export wholesalers"
"Anixter International, Inc."                     "$0" 2011 "Erie"            "export wholesalers"
"Arrow Electronics Inc."                          "$0" 2006 ""                "export wholesalers"
"Associated Wholesale Grocers, Inc."              "$0" 2000 "Kansas City"     "export wholesalers"
"Avnet, Inc."                                     "$0" 2014 "Phoenix"         "export wholesalers"
"BASIC CHEMICAL SOLUTIONS"                        "$0" 2010 ""                "export wholesalers"
"Basic Chemical Solutions"                        "$0" 2005 ""                "export wholesalers"
"BASIC CHEMICAL SOLUTIONS"                        "$0" 2012 ""                "export wholesalers"
"Basin Transload"                                 "$0" 2013 ""                "export wholesalers"
"Basin Transload"                                 "$0" 2012 ""                "export wholesalers"
"Basin Transload, LLC"                            "$0" 2015 ""                "export wholesalers"
"Basin Transload, LLC"                            "$0" 2015 ""                "export wholesalers"
"Basin Transload, LLC"                            "$0" 2015 ""                "export wholesalers"
"Bozzuto's Inc."                                  "$0" 2000 "Cheshire"        "export wholesalers"
"Bozzuto's, Inc."                                 "$0" 2014 "Cheshire"        "export wholesalers"
"Bradco Supply Corp."                             "$0" 2005 "Melbourne"       "export wholesalers"
"Brenntag Mid-South Inc."                         "$0" 2004 ""                "export wholesalers"
"BRENNTAG MID-SOUTH, INC."                        "$0" 2014 ""                "export wholesalers"
"Brenntag Southwest, Inc."                        "$0" 2010 ""                "export wholesalers"
"Brenntag Canada Inc."                            "$0" 2004 ""                "export wholesalers"
"Brenntag Great Lakes, LLC"                       "$0" 2010 ""                "export wholesalers"
"Brenntag Northeast Inc."                         "$0" 2016 ""                "export wholesalers"
"Brenntag Southeast, Inc."                        "$0" 2010 ""                "export wholesalers"
"BRENNTAG SOUTHEAST, INC."                        "$0" 2014 ""                "export wholesalers"
"Builders First Source"                           "$0" 2007 "Arlington"       "export wholesalers"
"Builder's First Source-South Texas, L.P."        "$0" 2004 "Dallas"          "export wholesalers"
"Builders FirstSource"                            "$0" 2004 "North East"      "export wholesalers"
"C & S Wholesale Grocers, Inc."                   "$0" 2007 "Keene"           "export wholesalers"
"C & S Wholesale Grocers, Inc."                   "$0" 2008 "Keene"           "export wholesalers"
"C & S Wholesale Grocers, Inc."                   "$0" 2015 "Keene"           "export wholesalers"
"C & S Wholesale Grocers, Inc."                   "$0" 2005 "Middleburgh"     "export wholesalers"
"C& S Wholesale Grocers Inc."                     "$0" 2007 "Suffield"        "export wholesalers"
"C&S Wholesale Grocers, Inc."                     "$0" 2012 "Keene"           "export wholesalers"
"C&S Wholesale Grocers, Inc."                     "$0" 2015 ""                "export wholesalers"
"C&S Wholesale Grocers, Inc."                     "$0" 2013 "Du Bois"         "export wholesalers"
"Cardinal Health"                                 "$0" 2016 ""                "export wholesalers"
"Cardinal Health Inc."                            "$0" 2015 ""                "export wholesalers"
"Cardinal Health Inc."                            "$0" 2011 ""                "export wholesalers"
"Cardinal Health, Inc."                           "$0" 2007 ""                "export wholesalers"
"Charmer Industries Inc."                         "$0" 2007 "Astoria"         "export wholesalers"
"Chemicals & Solvents Inc"                        "$0" 2010 "ROANOKE"         "export wholesalers"
"CHEMICALS & SOLVENTS INC"                        "$0" 2007 "ROANOKE"         "export wholesalers"
"Chem-Solv Inc."                                  "$0" 2015 ""                "export wholesalers"
"Chemicals and Solvents, Inc. dba Chemsolv, Inc." "$0" 2011 "Roanoke"         "export wholesalers"
"Coastal Chem Inc."                               "$0" 2001 ""                "export wholesalers"
"Coastal Chem Inc."                               "$0" 2007 ""                "export wholesalers"
"Coastal Chem Inc."                               "$0" 2001 ""                "export wholesalers"
"Coastal Chem Inc."                               "$0" 2001 ""                "export wholesalers"
"Coastal Chem Inc."                               "$0" 2007 ""                "export wholesalers"
"Coastal Chemical Co., LLC"                       "$0" 2014 "Kilgore"         "export wholesalers"
"Coastal Lumber Company, Inc."                    "$0" 2002 "Bruceton Mills"  "export wholesalers"
"Computerlinks FZCO"                              "$0" 2013 ""                "export wholesalers"
"Convergys"                                       "$0" 2002 "Toledo"          "export wholesalers"
"Convergys Corporation"                           "$0" 2005 "Chattanooga"     "export wholesalers"
"Convergys Corporation"                           "$0" 2011 "Cincinnati"      "export wholesalers"
"Convergys Corporation"                           "$0" 2001 "Clarksville"     "export wholesalers"
"Convergys, Inc"                                  "$0" 2006 "Tucson"          "export wholesalers"
"Convergys Customer Management, Inc"              "$0" 2008 "Arnold"          "export wholesalers"
"Convergys Customer Management Group"             "$0" 2012 ""                "export wholesalers"
"Convergys Customer Managment Group Inc."         "$0" 2009 "Albuquerque"     "export wholesalers"
"Convergys Government Solutions"                  "$0" 2010 "Ogden"           "export wholesalers"
"DXP Enterprises, Inc."                           "$0" 2012 ""                "export wholesalers"
"Eby-Brown Co."                                   "$0" 2008 "Springfield"     "export wholesalers"
"Eby Brown Company"                               "$0" 2010 "Montgomery"      "export wholesalers"
"Eby-Brown, Co."                                  "$0" 2008 "Montgomery"      "export wholesalers"
"EMCO CHEMICAL DISTRIBUTORS, INC."                "$0" 2009 ""                "export wholesalers"
"FreshPoint Inc."                                 "$0" 2013 ""                "export wholesalers"
"FreshPoint Tomato LLC"                           "$0" 2012 "Nashville"       "export wholesalers"
"Geason Enterprises and affiliates"               "$0" 2015 ""                "export wholesalers"
"Gemaire Distributors, LLC"                       "$0" 2015 "Pensacola"       "export wholesalers"
"S.P Richards Company"                            "$0" 2003 "Mira Loma"       "export wholesalers"
"Genuine Parts Companay/NAPA Parts, In.c"         "$0" 2004 "Tampa"           "export wholesalers"
"Genuine Prts Company"                            "$0" 2013 "ATLANTA"         "export wholesalers"
"Genuine Prts Company"                            "$0" 2014 "ATLANTA"         "export wholesalers"
"Glazer Distribution"                             "$0" 2008 "Columbus"        "export wholesalers"
"Glazer's Distributors of Illinois, Inc."         "$0" 2008 "Chicago"         "export wholesalers"
"Global Companies"                                "$0" 2016 ""                "export wholesalers"
"Global Companies"                                "$0" 2014 ""                "export wholesalers"
"Global Companies LLC"                            "$0" 2015 ""                "export wholesalers"
"Global Equipment Company"                        "$0" 2004 "Suwanee"         "export wholesalers"
"Global Industrial Services"                      "$0" 2002 "Palo Alto"       "export wholesalers"
"Golden State Foods Corp."                        "$0" 2004 "Portland"        "export wholesalers"
"GRAYBAR ELECTRIC COMPANY"                        "$0" 2000 "Columbus"        "export wholesalers"
end

      Comment

      • #4
        Nick Baradar the reshape command did not like that 'company' was stored in 'strL' so I changed it to 'str99' in the following.

        Code:
        clear
input str99 company str14 subtractionfrompenalty int penaltyyear str60 city str42 domain
"ABC Supply Company,  Inc."                       "$0" 2007 "Columbia"        "export wholesalers"
"Advantage Logistics"                             "$0" 2001 "Oglesby"         "export wholesalers"
"Advantage Logistics"                             "$0" 2007 "Aurora"          "export wholesalers"
"Alliant Food Service, Inc."                      "$0" 2001 "Swedesboro"      "export wholesalers"
"Allied Building Products Corp."                  "$0" 2010 "East Rutherford" "export wholesalers"
"Allied Building Products Corp., Inc."            "$0" 2003 "West Allis"      "export wholesalers"
"ALLIED BUILDING PRODUCTS, CORP."                 "$0" 2000 "Cincinnat"       "export wholesalers"
"AMERICAN PROCUREMENT & LOGISTICS CO"             "$0" 2005 "EDEN PRAIRIE"    "export wholesalers"
"AmeriSourceBergen"                               "$0" 2003 "San Jose"        "export wholesalers"
"AmerisourceBergen"                               "$0" 2006 "Thorofare"       "export wholesalers"
"AmerisourceBergen Corporation"                   "$0" 2008 "Kent"            "export wholesalers"
"Amerisourcebergen Specialty Groups"              "$0" 2008 "Frisco"          "export wholesalers"
"Andersons, Inc."                                 "$0" 2015 ""                "export wholesalers"
"The Andersons Inc."                              "$0" 2007 ""                "export wholesalers"
"THE ANDERSONS, INC."                             "$0" 2016 ""                "export wholesalers"
"THE ANDERSONS, INC."                             "$0" 2011 ""                "export wholesalers"
"THE ANDERSONS, INC."                             "$0" 2010 ""                "export wholesalers"
"Anixter, Inc."                                   "$0" 2004 "Alsip"           "export wholesalers"
"Anixter International, Inc."                     "$0" 2011 "Erie"            "export wholesalers"
"Arrow Electronics Inc."                          "$0" 2006 ""                "export wholesalers"
"Associated Wholesale Grocers, Inc."              "$0" 2000 "Kansas City"     "export wholesalers"
"Avnet, Inc."                                     "$0" 2014 "Phoenix"         "export wholesalers"
"BASIC CHEMICAL SOLUTIONS"                        "$0" 2010 ""                "export wholesalers"
"Basic Chemical Solutions"                        "$0" 2005 ""                "export wholesalers"
"BASIC CHEMICAL SOLUTIONS"                        "$0" 2012 ""                "export wholesalers"
"Basin Transload"                                 "$0" 2013 ""                "export wholesalers"
"Basin Transload"                                 "$0" 2012 ""                "export wholesalers"
"Basin Transload, LLC"                            "$0" 2015 ""                "export wholesalers"
"Basin Transload, LLC"                            "$0" 2015 ""                "export wholesalers"
"Basin Transload, LLC"                            "$0" 2015 ""                "export wholesalers"
"Bozzuto's Inc."                                  "$0" 2000 "Cheshire"        "export wholesalers"
"Bozzuto's, Inc."                                 "$0" 2014 "Cheshire"        "export wholesalers"
"Bradco Supply Corp."                             "$0" 2005 "Melbourne"       "export wholesalers"
"Brenntag Mid-South Inc."                         "$0" 2004 ""                "export wholesalers"
"BRENNTAG MID-SOUTH, INC."                        "$0" 2014 ""                "export wholesalers"
"Brenntag Southwest, Inc."                        "$0" 2010 ""                "export wholesalers"
"Brenntag Canada Inc."                            "$0" 2004 ""                "export wholesalers"
"Brenntag Great Lakes, LLC"                       "$0" 2010 ""                "export wholesalers"
"Brenntag Northeast Inc."                         "$0" 2016 ""                "export wholesalers"
"Brenntag Southeast, Inc."                        "$0" 2010 ""                "export wholesalers"
"BRENNTAG SOUTHEAST, INC."                        "$0" 2014 ""                "export wholesalers"
"Builders First Source"                           "$0" 2007 "Arlington"       "export wholesalers"
"Builder's First Source-South Texas, L.P."        "$0" 2004 "Dallas"          "export wholesalers"
"Builders FirstSource"                            "$0" 2004 "North East"      "export wholesalers"
"C & S Wholesale Grocers, Inc."                   "$0" 2007 "Keene"           "export wholesalers"
"C & S Wholesale Grocers, Inc."                   "$0" 2008 "Keene"           "export wholesalers"
"C & S Wholesale Grocers, Inc."                   "$0" 2015 "Keene"           "export wholesalers"
"C & S Wholesale Grocers, Inc."                   "$0" 2005 "Middleburgh"     "export wholesalers"
"C& S Wholesale Grocers Inc."                     "$0" 2007 "Suffield"        "export wholesalers"
"C&S Wholesale Grocers, Inc."                     "$0" 2012 "Keene"           "export wholesalers"
"C&S Wholesale Grocers, Inc."                     "$0" 2015 ""                "export wholesalers"
"C&S Wholesale Grocers, Inc."                     "$0" 2013 "Du Bois"         "export wholesalers"
"Cardinal Health"                                 "$0" 2016 ""                "export wholesalers"
"Cardinal Health Inc."                            "$0" 2015 ""                "export wholesalers"
"Cardinal Health Inc."                            "$0" 2011 ""                "export wholesalers"
"Cardinal Health, Inc."                           "$0" 2007 ""                "export wholesalers"
"Charmer Industries Inc."                         "$0" 2007 "Astoria"         "export wholesalers"
"Chemicals & Solvents Inc"                        "$0" 2010 "ROANOKE"         "export wholesalers"
"CHEMICALS & SOLVENTS INC"                        "$0" 2007 "ROANOKE"         "export wholesalers"
"Chem-Solv Inc."                                  "$0" 2015 ""                "export wholesalers"
"Chemicals and Solvents, Inc. dba Chemsolv, Inc." "$0" 2011 "Roanoke"         "export wholesalers"
"Coastal Chem Inc."                               "$0" 2001 ""                "export wholesalers"
"Coastal Chem Inc."                               "$0" 2007 ""                "export wholesalers"
"Coastal Chem Inc."                               "$0" 2001 ""                "export wholesalers"
"Coastal Chem Inc."                               "$0" 2001 ""                "export wholesalers"
"Coastal Chem Inc."                               "$0" 2007 ""                "export wholesalers"
"Coastal Chemical Co., LLC"                       "$0" 2014 "Kilgore"         "export wholesalers"
"Coastal Lumber Company, Inc."                    "$0" 2002 "Bruceton Mills"  "export wholesalers"
"Computerlinks FZCO"                              "$0" 2013 ""                "export wholesalers"
"Convergys"                                       "$0" 2002 "Toledo"          "export wholesalers"
"Convergys Corporation"                           "$0" 2005 "Chattanooga"     "export wholesalers"
"Convergys Corporation"                           "$0" 2011 "Cincinnati"      "export wholesalers"
"Convergys Corporation"                           "$0" 2001 "Clarksville"     "export wholesalers"
"Convergys, Inc"                                  "$0" 2006 "Tucson"          "export wholesalers"
"Convergys Customer Management, Inc"              "$0" 2008 "Arnold"          "export wholesalers"
"Convergys Customer Management Group"             "$0" 2012 ""                "export wholesalers"
"Convergys Customer Managment Group Inc."         "$0" 2009 "Albuquerque"     "export wholesalers"
"Convergys Government Solutions"                  "$0" 2010 "Ogden"           "export wholesalers"
"DXP Enterprises, Inc."                           "$0" 2012 ""                "export wholesalers"
"Eby-Brown Co."                                   "$0" 2008 "Springfield"     "export wholesalers"
"Eby Brown Company"                               "$0" 2010 "Montgomery"      "export wholesalers"
"Eby-Brown, Co."                                  "$0" 2008 "Montgomery"      "export wholesalers"
"EMCO CHEMICAL DISTRIBUTORS, INC."                "$0" 2009 ""                "export wholesalers"
"FreshPoint Inc."                                 "$0" 2013 ""                "export wholesalers"
"FreshPoint Tomato LLC"                           "$0" 2012 "Nashville"       "export wholesalers"
"Geason Enterprises and affiliates"               "$0" 2015 ""                "export wholesalers"
"Gemaire Distributors, LLC"                       "$0" 2015 "Pensacola"       "export wholesalers"
"S.P Richards Company"                            "$0" 2003 "Mira Loma"       "export wholesalers"
"Genuine Parts Companay/NAPA Parts, In.c"         "$0" 2004 "Tampa"           "export wholesalers"
"Genuine Prts Company"                            "$0" 2013 "ATLANTA"         "export wholesalers"
"Genuine Prts Company"                            "$0" 2014 "ATLANTA"         "export wholesalers"
"Glazer Distribution"                             "$0" 2008 "Columbus"        "export wholesalers"
"Glazer's Distributors of Illinois, Inc."         "$0" 2008 "Chicago"         "export wholesalers"
"Global Companies"                                "$0" 2016 ""                "export wholesalers"
"Global Companies"                                "$0" 2014 ""                "export wholesalers"
"Global Companies LLC"                            "$0" 2015 ""                "export wholesalers"
"Global Equipment Company"                        "$0" 2004 "Suwanee"         "export wholesalers"
"Global Industrial Services"                      "$0" 2002 "Palo Alto"       "export wholesalers"
"Golden State Foods Corp."                        "$0" 2004 "Portland"        "export wholesalers"
"GRAYBAR ELECTRIC COMPANY"                        "$0" 2000 "Columbus"        "export wholesalers"
end

sort company penaltyyear
by company: gen cnt=sum(1)
reshape wide subtractionfrompenalty penaltyyear city domain, i(company) j(cnt)
        I assumed that you wanted rows to consist of companies. If it is another variable 'sort' and 'gen by' that variable instead.

        Comment

        • #5
          Thanks a lot for the code, Emil Alnor ! Can you please tell me how you coverted strL' to 'str99' ?

          Comment

          • #6
            Originally posted by Nick Baradar View Post
            Thanks a lot for the code, Emil Alnor ! Can you please tell me how you coverted strL' to 'str99' ?
            Its in the input part. Your code was:
            input strL company str14 subtractionfrompenalty int penaltyyear str60 city str42 domai
            Mine was:
            input str99 company str14 subtractionfrompenalty int penaltyyear str60 city str42 domain
            Differences emphasized with bold.

            Comment

            Previous Next
            Working...
            X