Messed up order of variables in reshape

[Anne Ahrens]

Hi,

I am trying to reshape my data to wide, but then I reshape the order is messed up so tube"j" contains differnet tubes since there is lacking data for some ids (only 3 different tubes for some).

Is there a way to create new observations containing id and tube, but missing data in Assay to help the order?

Thank you for your inputs!

/Anne



Current output:

Desired output:

clear
input str2 id str4 tube str3 Assay
"1" zym Il2
"1" zym Il4
"1" zym IL6
"1" lps Il2
"1" lps Il4
"1" lps IL6
"2" null Il2
"2" null Il4
"2" null IL6
"2" zym Il2
"2" zym Il4
"2" zym IL6
"2" lps Il2
"2" lps Il4
"2" lps IL6
"2" null Il2
"2" null Il4
"2" null IL6
"2" cd3 Il2
"2" cd3 Il4
"2" cd3 IL6
"3" zym Il2
"3" zym Il4
"3" zym IL6
"3" lps Il2
"3" lps Il4
"3" lps IL6
"3" cd3 Il2
"3" cd3 Il4
"3" cd3 IL6
end

bysort id (tube): gen j=_n

reshape wide tube Assay, i(id) j(j)

[Nick Cox]

Don't blame reshape here.

The first problem you have is that you created variable j that doesn't do what you want.

But the next problem we have is what identifies an observation uniquely? In particular, you have some duplicate observations.

Also, I see (e.g.) three distinct assays for id 3 and tube CD3 but only one appears in your desired layout.

After wrestling with this for some time, I concluded that I don't understand why you want a wide layout at all, and certainly not this wide layout.

[Anne Ahrens]

Thank you for your quick reply.


My skills are the problem. Stata only does what I ask

Id tube and Assay uniquely identify my observations. I could create a variable for this, but I do not believe it would help me here.

The desired output do not contain all data, sorry.

I preferred the wide layout before merging withthe clinical data. I merged many to one with data in long, but as I had to export the data to excel (for a college) the dataset had to many observations (data example shown).

[Nick Cox]

Thanks for the feedback, but this doesn't let me help further. In particular, your data example shows duplicates. If that was a mistake in creating a data example, so be it. But your ideal layout still makes no sense to me, even given the explanation of wanting to give a copy of the data in different form to a colleague. .

. duplicates list 

Duplicates in terms of all variables

  +---------------------------------+
  | Group   Obs   id   tube   Assay |
  |---------------------------------|
  |     1     9    2   null     IL6 |
  |     1    18    2   null     IL6 |
  |     2     7    2   null     Il2 |
  |     2    16    2   null     Il2 |
  |     3     8    2   null     Il4 |
  |---------------------------------|
  |     3    17    2   null     Il4 |
  +---------------------------------+