Reshape the data in long format

[andrew roder]

Dear All,

I have a data set in wide format but the unique key is not unique. Is there a key to reshape the date in the following format

Branch Bank Year Number
Chukha BOB 2002 65
Chukha BNB 2002 35
Chukha TBL 2002 40
Chukha BOB 2003 65
Chukha BNB 2003 35
Chukha TBL 2003 40

Bumthang BOB 2002 45
Bumthang BNB 2002 56
Bumthang TBL 2002 45

......So on

Thank you. Below is a sample of my data

copy starting from the next line -------------- --------

* Example generated by -dataex-. For more info, type help dataex
clear
input str70 cid str40 branch str255 yearofrecord str5 bank float year_f
""                         "chukha"          "10/15/2013"  "TBL"   2013
"(1042) 1042"              "paro"            "10/2/2018"   "DPNB"  2018
"(DCRC- 28)2010-2011/"     "sarpang"         "10/30/2013"  "TBL"   2013
"(DCRC-28)012300"          "wangdue"         "3/16/2021"   "BDBL"  2021
"(DCRC-28)20"              "samtse"          "4/10/2017"   "BDBL"  2017
"(DCRC-28)2009-10/293"     "wangdue"         "10/21/2013"  "TBL"   2013
"(DCRC-28)2012-2013/11859" "chukha"          "5/5/2020"    "BOBL"  2020
"(DCRC-28)2012-2013/2144 " "thimphu"         "9/28/2019"   "BOBL"  2019
"(DCRC-28)2012-2013/3289 " "tsirang"         "5/7/2011"    "BOBL"  2011
"(DCRC-28)2012-2013/6136"  "tsirang"         "4/10/2016"   "RENEW" 2016
"(DCRC-28)2014/3649"       "chukha"          "12/9/2014"   "TBL"   2014
"(DCRC-28)2015"            "paro"            "4/26/2017"   "TBL"   2017
"(DCRC-28)2015-0272"       "mongar"          "3/30/2018"   "TBL"   2018
"(DCRC-28)2017/1723"       "thimphu"         "3/27/2019"   "TBL"   2019
"(DCRC-28)2020/0023023   " "samtse"          "7/20/2020"   "BOBL"  2020
"(DCRC-28)2021/007345"     "sarpang"         "10/14/2021"  "TBL"   2021
"(DCRC-28)2021/022560"     "sarpang"         "11/30/2021"  "TBL"   2021
"(DCRC-28/2011-2012/2560)" "tsirang"         "11/20/2015"  "RENEW" 2015
"(DCRC28)2020/0018920"     "thimphu"         "8/7/2020"    "BNBL"  2020
"(DRC-28)/0004465"         "tashigang"       "4/3/2021"    "BDBL"  2021
"(DRC-28)2017/4029"        "chukha"          "6/20/2018"   "TBL"   2018
"(KHA 9)0272"              "thimphu"         "1/13/2016"   "DPNB"  2016
"(drc-28)348             " "samtse"          "1/8/2015"    "BOBL"  2015
"0"                        "thimphu"         "7/7/2016"    "DPNB"  2016
"0"                        "thimphu"         "7/30/2016"   "DPNB"  2016
"0"                        "thimphu"         "8/24/2016"   "DPNB"  2016
"0.120030026"              "sarpang"         "8/4/2016"    "TBL"   2016
"000000549/4"              "samtse"          "12/12/2013"  "BNBL"  2013
"000R0000360             " "thimphu"         "10/29/2009"  "BOBL"  2009
"00111443(Ol"              "wangdue"         "5/31/2007"   "BDBL"  2007
"002SB/11122             " "thimphu"         "9/23/1986"   "BOBL"  1986
"002SB/11419             " "thimphu"         "11/20/1986"  "BOBL"  1986
"002SB/30955             " "tashigang"       "5/1/2010"    "BOBL"  2010
"0088(KA-22)/0410/KA-"     "thimphu"         "4/13/2009"   "BNBL"  2009
"00915 PERMI"              "tsirang"         "5/31/2007"   "BDBL"  2007
"01-J-016463"              "bumthang"        "7/13/2012"   "BDBL"  2012
"01/ORD/2017"              "thimphu"         "10/24/2014"  "DPNB"  2014
"02/ORD/2014"              "thimphu"         "2/28/2014"   "DPNB"  2014
"0226(KA-78)"              "thimphu"         "7/25/2014"   "BNBL"  2014
"0367260-TRF"              "chukha"          "2/9/2004"    "BNBL"  2004
"04/05/2012"               "samdrupjongkhar" "7/10/2020"   "TBL"   2020
"0549282 (P)"              "paro"            "12/9/2006"   "BNBL"  2006
"0600062(Old"              "wangdue"         "3/25/2008"   "BDBL"  2008
"08/07/2003              " "tashigang"       "17-MAR-2021" "BOBL"  2021
"09/ORD/2019"              "thimphu"         "11/29/2017"  "DPNB"  2017
"1.00119E+13"              "wangdue"         "9/18/2019"   "TBL"   2019
"1.01001E+14"              "thimphu"         "4/7/2016"    "TBL"   2016
"1.01001E+14"              "thimphu"         "10/26/2015"  "TBL"   2015
"1.01001E+14"              "thimphu"         "10/8/2015"   "TBL"   2015
"1.01001E+14"              "thimphu"         "12/29/2016"  "TBL"   2016
"1.01001E+14"              "wangdue"         "3/3/2017"    "TBL"   2017
"1.01001E+14"              "wangdue"         "2/26/2019"   "TBL"   2019
"1.01001E+15"              "wangdue"         "10/9/2017"   "TBL"   2017
"1.01001E+15"              "wangdue"         "2/26/2019"   "TBL"   2019
"1.01001E+15"              "chukha"          "1/4/2019"    "TBL"   2019
"1.01001E+15"              "wangdue"         "9/18/2019"   "TBL"   2019
"1.01001E+15"              "wangdue"         "9/18/2019"   "TBL"   2019
"1.01001E+15"              "wangdue"         "10/1/2019"   "TBL"   2019
"1.01001E+15"              "wangdue"         "5/16/2019"   "TBL"   2019
"1.01001E+15"              "wangdue"         "1/6/2020"    "TBL"   2020
"1.01001E+15"              "wangdue"         "8/22/2019"   "TBL"   2019
"1.01001E+15"              "wangdue"         "5/22/2020"   "TBL"   2020
"1.01001E+15"              "wangdue"         "6/8/2020"    "TBL"   2020
"1.01001E+15"              "wangdue"         "7/30/2020"   "TBL"   2020
"1.01001E+15"              "wangdue"         "7/28/2020"   "TBL"   2020
"1.01001E+15"              "wangdue"         "4/13/2020"   "TBL"   2020
"1.01001E+15"              "wangdue"         "4/2/2020"    "TBL"   2020
"1.01001E+15"              "wangdue"         "2/28/2020"   "TBL"   2020
"1.01001E+15"              "wangdue"         "3/31/2020"   "TBL"   2020
"1.01001E+15"              "wangdue"         "7/29/2020"   "TBL"   2020
"1.01001E+15"              "wangdue"         "5/22/2020"   "TBL"   2020
"1.01001E+15"              "wangdue"         "5/22/2020"   "TBL"   2020
"1.01001E+15"              "wangdue"         "5/20/2020"   "TBL"   2020
"1.01001E+15"              "wangdue"         "3/30/2020"   "TBL"   2020
"1.01004E+15"              "chukha"          "3/25/2019"   "TBL"   2019
"1.0101E+15"               "wangdue"         "11/7/2019"   "TBL"   2019
"1.01504E+11"              "thimphu"         "8/2/2016"    "TBL"   2016
"1.02011E+11"              "chukha"          "10/28/2020"  "TBL"   2020
"1.0211E+11"               "chukha"          "12/20/2018"  "TBL"   2018
"1.02309E+14"              "sarpang"         "5/5/2016"    "TBL"   2016
"1.0504E+11"               "paro"            "2/15/2018"   "TBL"   2018
"1.06014E+11"              "thimphu"         "12/7/2020"   "TBL"   2020
"1.0606E+11"               "punakha"         "20/7/2021"   "RENEW" 2021
"1.0705E+11"               "wangdue"         "4/4/2018"    "TBL"   2018
"1.09003E+11"              "sarpang"         "7/2/2013"    "TBL"   2013
"1.10504E+11"              "chukha"          "7/24/2013"   "TBL"   2013
"1.11112E+15"              "samdrupjongkhar" "6/14/2017"   "TBL"   2017
"1.11112E+15"              "samdrupjongkhar" "2/9/2018"    "TBL"   2018
"1.11112E+15"              "samdrupjongkhar" "2/18/2020"   "TBL"   2020
"1.11112E+15"              "samdrupjongkhar" "2/10/2020"   "TBL"   2020
"1.11112E+15"              "samdrupjongkhar" "2/18/2020"   "TBL"   2020
"1.11114E+14"              "samdrupjongkhar" "6/27/2020"   "TBL"   2020
"1.11114E+15"              "samdrupjongkhar" "9/7/2017"    "TBL"   2017
"1.11114E+15"              "samdrupjongkhar" "8/12/2017"   "TBL"   2017
"1.11114E+15"              "samdrupjongkhar" "12/5/2018"   "TBL"   2018
"1.11114E+15"              "samdrupjongkhar" "12/24/2019"  "TBL"   2019
"1.11114E+15"              "samdrupjongkhar" "6/25/2020"   "TBL"   2020
"1.11909E+11"              "wangdue"         "3/16/2018"   "TBL"   2018
"1.12008E+11"              "sarpang"         "4/26/2014"   "TBL"   2014
"1.12052E+11"              "sarpang"         "3/16/2020"   "TBL"   2020
end

copy up to and including the previous line --------- --------

[Mike Lacy]

*Perhaps* (?) you want bank and branch to identify groups of observations, with year_f identifying subobservations. (I'm not sure this is true.) I note that you have not given in your example any variable that would seem to be the "number" variable of your desired result: Saying that you want to go from wide to long would mean that there would be several number* variables on each wide observations, but those don't appear in your example. Without seeing how your number* variables are named/structured, and without knowing for sure what variables you *want* to identify groups of observations, I don't see how we can help you. All that I might suggest is that your reshape command *might* be something like this:

reshape long number, i(bank branch) j(year_f)

I'd note, by the way, that besides omitting variables that are important to your question, you've included ones that may or may not be relevant (i.e., cid and yearofrecord -- which by the way might better be named something transaction_date), but we don't have a way to know about that.

While someone may be able to guess what your situation is and give a useful answer, I think you'll have better luck if you can post an example that includes the number* variables, and tell us what combination of variables you want to define a group of related observations.

[andrew roder]

The command you have shared will not work since year_f is a varibale already defined. It needs to be a new variable.

Sorry I think I think i would need to collapse the data set instead of reshaping it. Can you help me with the command as to how can I can collapse to get in the above format.

[andrew roder]

To collapse the data at the branch level is ok i can generate a new variable and write the command
gen indi =1
collapse (sum) indi, by(branch) But what i need it as follows :
Indi would be the number of individual in each year in each bank

Branch Bank Year Indi
Chhukha BOB 2002 344
Chukha BNB 2002 3445
Thimphu BOB 2003 3434
Thimphu BNB 2003 344

Thank you

[Mike Lacy]

You're certainly right about my -reshape- command being off base. To get some help here, I'd encourage you to focus on explaining what you want, not the means (e.g. -collapse-) by which you want to get there. I'd suggest presenting a good example of what you have and what you want, using the ideas in the StataList FAQ.

[Nick Cox]

#5 Excellent advice from Mike Lacy


#4 sounds like a approach to

contract branch year

or

contract bank branch year