Setting up Data

[Moeen Butt]

Dear Members, I need your help in setting up data in Stata. I have the variable customer_home_city that includes cities and numbers of customers in each city. I want to bring the list of cities in this variable in a column. I would appreciate your help.

The sample data is as follows.

* Example generated by -dataex-. For more info, type help dataex
clear
input float storeid strL customer_home_city
1 `"{"Wayne, NJ":19,"Lincoln Park, NJ":4,"Clifton, NJ":4,"Bloomington, IN":2,"Hibernia, NJ":2,"Harrison, NY":2,"Paterson, NJ":2,"Singac, NJ":2,"Fairfield, NJ":2,"Hawthorne, NJ":2,"Ridgewood, NJ":2}"'
end

Thank you.

Moeen

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input float storeid strL customer_home_city
1 `"{"Wayne, NJ":19,"Lincoln Park, NJ":4,"Clifton", NJ":4,"Bloomington, IN":2,"Hibernia, NJ":2,"Harrison, NY":2,"Paterson, NJ":2,"Singac, NJ":2,"Fairfield, NJ":2,"Hawthorne, NJ":2,"Ridgewood, NJ":2}"'
end

gen count= length(customer_home_city)- length(subinstr(customer_home_city, ":", "", .)) 
expand count
bys storeid: gen location= word(subinstr(ustrregexra(customer_home_city, "[^a-zA-Z\:]", ""), ":", " ", .), _n)
replace location= trim(itrim(ustrregexra(ustrregexra(location, "(.*)([a-z])([A-Z])(.*)", "$1$2, $3$4"), "([a-z])([A-Z])", "$1 $2")))
bys storeid: gen customers= real(word(subinstr(ustrregexra(customer_home_city, "[^0-9\:]", ""), ":", " ", .), _n))

Res.:

. l storeid location customers, sepby(storeid)

     +---------------------------------------+
     | storeid           location   custom~s |
     |---------------------------------------|
  1. |       1          Wayne, NJ         19 |
  2. |       1   Lincoln Park, NJ          4 |
  3. |       1        Clifton, NJ          4 |
  4. |       1    Bloomington, IN          2 |
  5. |       1       Hibernia, NJ          2 |
  6. |       1       Harrison, NY          2 |
  7. |       1       Paterson, NJ          2 |
  8. |       1         Singac, NJ          2 |
  9. |       1      Fairfield, NJ          2 |
 10. |       1      Hawthorne, NJ          2 |
 11. |       1      Ridgewood, NJ          2 |
     +---------------------------------------+

.

[Mike Lacy]

I approached this as a split and reshape problem. My skill with regular expressions is minimal, so here's an approach without that:

* Example generated by -dataex-. For more info, type help dataex
clear
input float storeid strL customer_home_city
1 `"{"Wayne, NJ":19,"Lincoln Park, NJ":4,"Clifton, NJ":4,"Bloomington, IN":2,"Hibernia, NJ":2,"Harrison, NY":2,"Paterson, NJ":2,"Singac, NJ":2,"Fairfield, NJ":2,"Hawthorne, NJ":2,"Ridgewood, NJ":2}"'
end
// I assume that the string "comma quote" reliably terminates each city string, so I
// use that to create  "/" as a parsing character between city string.
gen working = subinstr(customer_home_city, `",""',"/", .)
//
// Quotes and curly braces are a nuisance
replace working = subinstr(working, `"""', "", .)
replace working = subinstr(working, "{", "", .)
replace working = subinstr(working, "}", "", .)
//
// Here's the heart of the solution
split working, gen(cityname) parse("/")
reshape long cityname, i(storeid) j(cityseq)
//
// Clean up the city name and extract the number of customers.
gen numstring = substr(cityname, strpos(cityname, ":"), .)
destring numstring, gen(ncustomers) ignore(":")
replace cityname = subinstr(cityname, numstring, "", .)
//
list storeid cityname ncustomers
// clean up as desired
// drop numstring working cityseq
     +---------------------------------------+
     | storeid           cityname   ncusto~s |
     |---------------------------------------|
  1. |       1          Wayne, NJ         19 |
  2. |       1   Lincoln Park, NJ          4 |
  3. |       1        Clifton, NJ          4 |
  4. |       1    Bloomington, IN          2 |
  5. |       1       Hibernia, NJ          2 |
     |---------------------------------------|
  6. |       1       Harrison, NY          2 |
  7. |       1       Paterson, NJ          2 |
  8. |       1         Singac, NJ          2 |
  9. |       1      Fairfield, NJ          2 |
 10. |       1      Hawthorne, NJ          2 |
     |---------------------------------------|
 11. |       1      Ridgewood, NJ          2 |
     +---------------------------------------+

// clean up as desired
// drop customer_home_city numstring working cityseq

[Moeen Butt]

Thanks so much, Andrew and Mike. I appreciate your help.