Set Variables at the right Spot.

[Pierre Diatta]

Hello,
I have some problems with my data
My data contains varialbes on Harvesting from 20 plots (field1 to field 20)
on the other side I have those fields measured "Measurment1 to 20
But the values of size of the fields (Area) are not in the correct column.
Do you have a code that I can use to get the "Area" at the right spot, to match the "Harvest" fields so I may be able to compute the yield. Thank you

Harvest1	harvest2	harvest3	harvest4	harvest5	Area1	area2	area3	area4	area5
1.2		2.1		2.0	1.3	1.5	1.4
	1.2		1		1.2	2.1
	2	1.2		1.3	1.1	1.5	1.3
1.2		1.3	1.4		2.1	1.4	1.5
	1.4				1.3
		2		2.5	2.3	1.7
		1.3	1.3	1.1	1.4	3	1.3
1	1.1			1.2	1.1	1.3	1.4
	1.2	1.3		1.1	1.2	1.3	1.4

[Andrew Musau]

ADDED IN EDIT: In future, please present a data example using the dataex command as recommended in FAQ Advice #12. Trying to recreate your dataset results in uneven frequencies between area and harvest for some observations, so the dataset below is slightly modified from the original. Once your dataset is in long layout (first presentation in the results), you can stop as it is usually easier to work with data in this layout. reshape wide only if you have to revert to the wide layout.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(harvest1 harvest2 harvest3 harvest4 harvest5 area1 area2 area3 area4) byte area5
1.2   . 2.1   .   2 1.3 1.5 1.4   . .
1.2   .   1   . 1.2 2.1   .   .   . .
  2 1.2   . 1.3   . 1.1 1.5 1.3   . .
1.2   . 1.3 1.4   . 2.1 1.4 1.5   . .
1.4   .   .   .   .   . 1.3   .   . .
  2   . 2.5   .   .   .   . 2.3 1.7 .
1.3 1.3 1.1   .   . 1.3 1.4   3   . .
  1 1.1   .   . 1.2 1.1 1.3 1.4   . .
1.2 1.3   . 1.1   .   . 1.2 1.3 1.4 .
end


gen plot=_n
reshape long harvest area, i(plot) j(which)
preserve
keep plot area
drop if missing(area)
gen which=_n
bys plot (which): replace which=_n
tempfile area
save `area'
restore
drop which area
drop if missing(harvest)
gen which=_n
bys plot (which): replace which=_n
merge 1:1 plot which using `area', nogen
l, sepby(plot)
reshape wide harvest area, i(plot) j(which)

Res.:

. l, sepby(plot)

     +-------------------------------+
     | plot   harvest   which   area |
     |-------------------------------|
  1. |    1       1.2       1    1.3 |
  2. |    1       2.1       2    1.5 |
  3. |    1         2       3    1.4 |
     |-------------------------------|
  4. |    2       1.2       1    2.1 |
  5. |    2         1       2      . |
  6. |    2       1.2       3      . |
     |-------------------------------|
  7. |    3         2       1    1.1 |
  8. |    3       1.2       2    1.5 |
  9. |    3       1.3       3    1.3 |
     |-------------------------------|
 10. |    4       1.2       1    2.1 |
 11. |    4       1.3       2    1.4 |
 12. |    4       1.4       3    1.5 |
     |-------------------------------|
 13. |    5       1.4       1    1.3 |
     |-------------------------------|
 14. |    6         2       1    2.3 |
 15. |    6       2.5       2    1.7 |
     |-------------------------------|
 16. |    7       1.3       1    1.3 |
 17. |    7       1.3       2    1.4 |
 18. |    7       1.1       3      3 |
     |-------------------------------|
 19. |    8         1       1    1.1 |
 20. |    8       1.1       2    1.3 |
 21. |    8       1.2       3    1.4 |
     |-------------------------------|
 22. |    9       1.2       1    1.2 |
 23. |    9       1.3       2    1.3 |
 24. |    9       1.1       3    1.4 |
     +-------------------------------+

.
. reshape wide harvest area, i(plot) j(which)
(note: j = 1 2 3)

Data                               long   ->   wide
-----------------------------------------------------------------------------
Number of obs.                       24   ->       9
Number of variables                   4   ->       7
j variable (3 values)             which   ->   (dropped)
xij variables:
                                harvest   ->   harvest1 harvest2 harvest3
                                   area   ->   area1 area2 area3
-----------------------------------------------------------------------------

. l, sep(0)

     +---------------------------------------------------------------+
     | plot   harvest1   area1   harvest2   area2   harvest3   area3 |
     |---------------------------------------------------------------|
  1. |    1        1.2     1.3        2.1     1.5          2     1.4 |
  2. |    2        1.2     2.1          1       .        1.2       . |
  3. |    3          2     1.1        1.2     1.5        1.3     1.3 |
  4. |    4        1.2     2.1        1.3     1.4        1.4     1.5 |
  5. |    5        1.4     1.3          .       .          .       . |
  6. |    6          2     2.3        2.5     1.7          .       . |
  7. |    7        1.3     1.3        1.3     1.4        1.1       3 |
  8. |    8          1     1.1        1.1     1.3        1.2     1.4 |
  9. |    9        1.2     1.2        1.3     1.3        1.1     1.4 |
     +---------------------------------------------------------------+

[Pierre Diatta]

Thank you so much Andrew for your response and your advise. I will definitely use dataex next time. Best