How to restructure aggregated count data as individual observation?

[Faysal Shuvo]

Hi altruist,

I am just wondering whether STATA has any function to convert aggregated count data into individual row.
For example, I have audited different parks and counted how many people are doing three different types of physical activity.
I did that multiple times in same park.
So in my raw data each row is a park and each column include 'female walking' and 'male walking' etc.
Now I want a new data where row number will be equivalent to the count of each of the activities.
I have attached a screenshot of my raw data (first) and also how the restructure may look like (2nd).
Can anyone tell me whether it is possible to restructure in STATA or not?

Thank you very much in anticipation.

best regards,

Faysal

[Carlo Lazzaro]

Faysal:
the trivial advice is to take a look at -help reshape-.
Moreover, please note that spreadsheet jargon does not match with Stata's one (rows are observations; columns are variable).
I would also add that snapshots are not particularly welocomed on this forum because it is impossible to work on them positively. It's better to share an excerpt of your data via -dataex-.
Eventually, please note that many on this list would not download spreadsheets coming from unknown sources, due to the rosk of active contents (hence, please do not attach them to your next messages).

[Phil Bromiley]

I'm sure this is not efficient, but this is the way I handled this problem:
local oborig=_N
local cntob=`oborig' + 1
set obs 10000
set trace off
g y=.
g iii=.
quietly {
forvalues i=1/`oborig' {
local parmN=n_phack_iv[`i']
local hacked=iv004[`i']

forvalues j= 1/`parmN' {
*local newob= `=_N+1'


replace y=1 if `j'<=`hacked' in `cntob'/`cntob'
replace y=0 if `j'>`hacked' in `cntob'/`cntob'
replace iii=`i' in `cntob'/`cntob'
foreach var in aff1_ft_rank aff1_rank_ever aff1_rank_never phd1_top asst1 asso1 full1 tenured1 gender1 au1_pubsperyr ///
asp_perf1 report_p1 iv_1tail asq ln_obs pct_evidence time n_au n_phack_iv aff1_ft_rank2 au1_id article_id {
replace `var'=`var'[`i'] in `cntob'/`cntob'
}
local cntob=`cntob' + 1
}
}
}