Reshape data

[Muhammad Ramzan]

Hi

I have the following data having following variables lat level lon time air dup time2. The level variables takes only two values 1000 and 925. I want to convert the data to lat level_1000 level_925 lon time air dup time2.


how I can do this please.

Thanks

* Example generated by -dataex-. For more info, type help dataex
clear
input float lat int level float lon str19 time float(air dup) str8 time2
35 1000 0 "2021-01-01 00:00:00" 283.39996 1 "00:00:00"
35  925 0 "2021-01-01 00:00:00" 277.69998 2 "00:00:00"
35  925 0 "2021-01-02 00:00:00" 277.69998 1 "00:00:00"
35 1000 0 "2021-01-02 00:00:00" 282.69998 2 "00:00:00"
35 1000 0 "2021-01-03 00:00:00" 280.19998 1 "00:00:00"
35  925 0 "2021-01-03 00:00:00" 275.39996 2 "00:00:00"
35  925 0 "2021-01-04 00:00:00"     277.4 1 "00:00:00"
35 1000 0 "2021-01-04 00:00:00" 282.39996 2 "00:00:00"
35  925 0 "2021-01-05 00:00:00" 277.99997 1 "00:00:00"
35 1000 0 "2021-01-05 00:00:00" 283.89996 2 "00:00:00"
35  925 0 "2021-01-06 00:00:00" 279.89996 1 "00:00:00"
35 1000 0 "2021-01-06 00:00:00" 284.69998 2 "00:00:00"
35  925 0 "2021-01-07 00:00:00"     283.6 1 "00:00:00"
35 1000 0 "2021-01-07 00:00:00"     289.7 2 "00:00:00"
35 1000 0 "2021-01-08 00:00:00" 290.49997 1 "00:00:00"
35  925 0 "2021-01-08 00:00:00"       285 2 "00:00:00"
35  925 0 "2021-01-09 00:00:00"     287.2 1 "00:00:00"
35 1000 0 "2021-01-09 00:00:00" 292.19998 2 "00:00:00"
35  925 0 "2021-01-10 00:00:00" 279.99997 1 "00:00:00"
35 1000 0 "2021-01-10 00:00:00"     285.3 2 "00:00:00"
35  925 0 "2021-01-11 00:00:00" 277.69998 1 "00:00:00"
35 1000 0 "2021-01-11 00:00:00" 282.39996 2 "00:00:00"
35  925 0 "2021-01-12 00:00:00" 275.39996 1 "00:00:00"
35 1000 0 "2021-01-12 00:00:00"       280 2 "00:00:00"
35 1000 0 "2021-01-13 00:00:00" 280.99997 1 "00:00:00"
35  925 0 "2021-01-13 00:00:00"     277.7 2 "00:00:00"
35  925 0 "2021-01-14 00:00:00" 279.89996 1 "00:00:00"
35 1000 0 "2021-01-14 00:00:00" 283.19998 2 "00:00:00"
35  925 0 "2021-01-15 00:00:00"       278 1 "00:00:00"
35 1000 0 "2021-01-15 00:00:00" 282.39996 2 "00:00:00"
35  925 0 "2021-01-16 00:00:00"     280.1 1 "00:00:00"
35 1000 0 "2021-01-16 00:00:00" 283.89996 2 "00:00:00"
35 1000 0 "2021-01-17 00:00:00" 282.89996 1 "00:00:00"
35  925 0 "2021-01-17 00:00:00" 278.49997 2 "00:00:00"
35 1000 0 "2021-01-18 00:00:00" 287.69998 1 "00:00:00"
35  925 0 "2021-01-18 00:00:00"     285.8 2 "00:00:00"
35 1000 0 "2021-01-19 00:00:00"     288.7 1 "00:00:00"
35  925 0 "2021-01-19 00:00:00"       285 2 "00:00:00"
35 1000 0 "2021-01-20 00:00:00"     284.4 1 "00:00:00"
35  925 0 "2021-01-20 00:00:00"     282.6 2 "00:00:00"
35  925 0 "2021-01-21 00:00:00"     283.3 1 "00:00:00"
35 1000 0 "2021-01-21 00:00:00" 288.49997 2 "00:00:00"
35  925 0 "2021-01-22 00:00:00"     282.8 1 "00:00:00"
35 1000 0 "2021-01-22 00:00:00"     287.2 2 "00:00:00"
35  925 0 "2021-01-23 00:00:00" 282.49997 1 "00:00:00"
35 1000 0 "2021-01-23 00:00:00"     287.2 2 "00:00:00"
35  925 0 "2021-01-24 00:00:00"     282.8 1 "00:00:00"
35 1000 0 "2021-01-24 00:00:00"     287.9 2 "00:00:00"
35  925 0 "2021-01-25 00:00:00"     284.8 1 "00:00:00"
35 1000 0 "2021-01-25 00:00:00"       289 2 "00:00:00"
35 1000 0 "2021-01-26 00:00:00" 287.89996 1 "00:00:00"
35  925 0 "2021-01-26 00:00:00"     283.8 2 "00:00:00"
35 1000 0 "2021-01-27 00:00:00"     288.7 1 "00:00:00"
35  925 0 "2021-01-27 00:00:00" 284.89996 2 "00:00:00"
35  925 0 "2021-01-28 00:00:00"     287.8 1 "00:00:00"
35 1000 0 "2021-01-28 00:00:00" 291.99997 2 "00:00:00"
35  925 0 "2021-01-29 00:00:00" 289.89996 1 "00:00:00"
35 1000 0 "2021-01-29 00:00:00"     291.1 2 "00:00:00"
35  925 0 "2021-01-30 00:00:00" 288.69998 1 "00:00:00"
35 1000 0 "2021-01-30 00:00:00"       292 2 "00:00:00"
35  925 0 "2021-01-31 00:00:00" 285.59998 1 "00:00:00"
35 1000 0 "2021-01-31 00:00:00"     290.4 2 "00:00:00"
35 1000 0 "2021-02-01 00:00:00"       290 1 "00:00:00"
35  925 0 "2021-02-01 00:00:00"     284.9 2 "00:00:00"
35  925 0 "2021-02-02 00:00:00"     285.6 1 "00:00:00"
35 1000 0 "2021-02-02 00:00:00"     289.7 2 "00:00:00"
35  925 0 "2021-02-03 00:00:00" 288.19998 1 "00:00:00"
35 1000 0 "2021-02-03 00:00:00" 291.09998 2 "00:00:00"
35 1000 0 "2021-02-04 00:00:00"     291.7 1 "00:00:00"
35  925 0 "2021-02-04 00:00:00"     287.4 2 "00:00:00"
35 1000 0 "2021-02-05 00:00:00" 293.19998 1 "00:00:00"
35  925 0 "2021-02-05 00:00:00" 290.19998 2 "00:00:00"
35 1000 0 "2021-02-06 00:00:00" 293.89996 1 "00:00:00"
35  925 0 "2021-02-06 00:00:00"     289.2 2 "00:00:00"
35  925 0 "2021-02-07 00:00:00" 281.59998 1 "00:00:00"
35 1000 0 "2021-02-07 00:00:00"     286.4 2 "00:00:00"
35  925 0 "2021-02-08 00:00:00"     280.1 1 "00:00:00"
35 1000 0 "2021-02-08 00:00:00"     284.6 2 "00:00:00"
35  925 0 "2021-02-09 00:00:00"     283.2 1 "00:00:00"
35 1000 0 "2021-02-09 00:00:00" 289.09998 2 "00:00:00"
35 1000 0 "2021-02-10 00:00:00" 291.39996 1 "00:00:00"
35  925 0 "2021-02-10 00:00:00"     285.7 2 "00:00:00"
35  925 0 "2021-02-11 00:00:00" 283.69998 1 "00:00:00"
35 1000 0 "2021-02-11 00:00:00"     288.8 2 "00:00:00"
35  925 0 "2021-02-12 00:00:00"     287.9 1 "00:00:00"
35 1000 0 "2021-02-12 00:00:00"     292.8 2 "00:00:00"
35  925 0 "2021-02-13 00:00:00" 283.99997 1 "00:00:00"
35 1000 0 "2021-02-13 00:00:00" 289.59998 2 "00:00:00"
35 1000 0 "2021-02-14 00:00:00" 285.99997 1 "00:00:00"
35  925 0 "2021-02-14 00:00:00" 282.19998 2 "00:00:00"
35  925 0 "2021-02-15 00:00:00" 279.69998 1 "00:00:00"
35 1000 0 "2021-02-15 00:00:00"     284.8 2 "00:00:00"
35  925 0 "2021-02-16 00:00:00" 281.49997 1 "00:00:00"
35 1000 0 "2021-02-16 00:00:00"     284.7 2 "00:00:00"
35  925 0 "2021-02-17 00:00:00" 284.39996 1 "00:00:00"
35 1000 0 "2021-02-17 00:00:00"     285.3 2 "00:00:00"
35  925 0 "2021-02-18 00:00:00" 282.89996 1 "00:00:00"
35 1000 0 "2021-02-18 00:00:00" 284.99997 2 "00:00:00"
35 1000 0 "2021-02-19 00:00:00" 288.69998 1 "00:00:00"
35  925 0 "2021-02-19 00:00:00"     284.8 2 "00:00:00"
end

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

[Muhammad Ramzan]

I am using the following command but getting the error message

reshape wide level, i(time2) j(dup)

[William Lisowski]

Perhaps this will start you in a useful direction.

list in 1/10, sep(2)
rename (air dup) (air_ dup_)
reshape wide air_ dup_, i(lat lon time time2) j(level)
list in 1/5

. list in 1/10, sep(2)

     +------------------------------------------------------------------+
     | lat   level   lon                  time     air   dup      time2 |
     |------------------------------------------------------------------|
  1. |  35    1000     0   2021-01-01 00:00:00   283.4     1   00:00:00 |
  2. |  35     925     0   2021-01-01 00:00:00   277.7     2   00:00:00 |
     |------------------------------------------------------------------|
  3. |  35     925     0   2021-01-02 00:00:00   277.7     1   00:00:00 |
  4. |  35    1000     0   2021-01-02 00:00:00   282.7     2   00:00:00 |
     |------------------------------------------------------------------|
  5. |  35    1000     0   2021-01-03 00:00:00   280.2     1   00:00:00 |
  6. |  35     925     0   2021-01-03 00:00:00   275.4     2   00:00:00 |
     |------------------------------------------------------------------|
  7. |  35     925     0   2021-01-04 00:00:00   277.4     1   00:00:00 |
  8. |  35    1000     0   2021-01-04 00:00:00   282.4     2   00:00:00 |
     |------------------------------------------------------------------|
  9. |  35     925     0   2021-01-05 00:00:00     278     1   00:00:00 |
 10. |  35    1000     0   2021-01-05 00:00:00   283.9     2   00:00:00 |
     +------------------------------------------------------------------+

. rename (air dup) (air_ dup_)

. reshape wide air_ dup_, i(lat lon time time2) j(level)
(j = 925 1000)

Data                               Long   ->   Wide
-----------------------------------------------------------------------------
Number of observations              100   ->   50          
Number of variables                   7   ->   8           
j variable (2 values)             level   ->   (dropped)
xij variables:
                                   air_   ->   air_925 air_1000
                                   dup_   ->   dup_925 dup_1000
-----------------------------------------------------------------------------

. list in 1/5

     +--------------------------------------------------------------------------------------+
     | lat   lon                  time      time2   air_925   dup_925   air_1000   dup_1000 |
     |--------------------------------------------------------------------------------------|
  1. |  35     0   2021-01-01 00:00:00   00:00:00     277.7         2      283.4          1 |
  2. |  35     0   2021-01-02 00:00:00   00:00:00     277.7         1      282.7          2 |
  3. |  35     0   2021-01-03 00:00:00   00:00:00     275.4         2      280.2          1 |
  4. |  35     0   2021-01-04 00:00:00   00:00:00     277.4         1      282.4          2 |
  5. |  35     0   2021-01-05 00:00:00   00:00:00       278         1      283.9          2 |
     +--------------------------------------------------------------------------------------+

.

[Muhammad Ramzan]

Perhaps this will start you in a useful direction.

list in 1/10, sep(2)
rename (air dup) (air_ dup_)
reshape wide air_ dup_, i(lat lon time time2) j(level)
list in 1/5

. list in 1/10, sep(2)

+------------------------------------------------------------------+
| lat level lon time air dup time2 |
|------------------------------------------------------------------|
1. | 35 1000 0 2021-01-01 00:00:00 283.4 1 00:00:00 |
2. | 35 925 0 2021-01-01 00:00:00 277.7 2 00:00:00 |
|------------------------------------------------------------------|
3. | 35 925 0 2021-01-02 00:00:00 277.7 1 00:00:00 |
4. | 35 1000 0 2021-01-02 00:00:00 282.7 2 00:00:00 |
|------------------------------------------------------------------|
5. | 35 1000 0 2021-01-03 00:00:00 280.2 1 00:00:00 |
6. | 35 925 0 2021-01-03 00:00:00 275.4 2 00:00:00 |
|------------------------------------------------------------------|
7. | 35 925 0 2021-01-04 00:00:00 277.4 1 00:00:00 |
8. | 35 1000 0 2021-01-04 00:00:00 282.4 2 00:00:00 |
|------------------------------------------------------------------|
9. | 35 925 0 2021-01-05 00:00:00 278 1 00:00:00 |
10. | 35 1000 0 2021-01-05 00:00:00 283.9 2 00:00:00 |
+------------------------------------------------------------------+

. rename (air dup) (air_ dup_)

. reshape wide air_ dup_, i(lat lon time time2) j(level)
(j = 925 1000)

Data Long -> Wide
-----------------------------------------------------------------------------
Number of observations 100 -> 50
Number of variables 7 -> 8
j variable (2 values) level -> (dropped)
xij variables:
air_ -> air_925 air_1000
dup_ -> dup_925 dup_1000
-----------------------------------------------------------------------------

. list in 1/5

+--------------------------------------------------------------------------------------+
| lat lon time time2 air_925 dup_925 air_1000 dup_1000 |
|--------------------------------------------------------------------------------------|
1. | 35 0 2021-01-01 00:00:00 00:00:00 277.7 2 283.4 1 |
2. | 35 0 2021-01-02 00:00:00 00:00:00 277.7 1 282.7 2 |
3. | 35 0 2021-01-03 00:00:00 00:00:00 275.4 2 280.2 1 |
4. | 35 0 2021-01-04 00:00:00 00:00:00 277.4 1 282.4 2 |
5. | 35 0 2021-01-05 00:00:00 00:00:00 278 1 283.9 2 |
+--------------------------------------------------------------------------------------+

.

Thanks William Lisowski

[Muhammad Ramzan]

Hi

I am getting the following error message please for the reshape command.

. reshape wide air_ dup_ , i(lat lon time time2) j(level)
(j = 925 1000)
variable dup not constant within lat lon time time2
Your data are currently long. You are performing a reshape wide. You typed something like

. reshape wide a b, i(lat lon time time2) j(level)

There are variables other than a, b, lat lon time time2, level in your data. They must be constant within lat lon time time2 because that is the only way
they can fit into wide data without loss of information.

The variable or variables listed above are not constant within lat lon time time2. Perhaps the values are in error. Type reshape error for a list of the
problem observations.

Either that, or the values vary because they should vary, in which case you must either add the variables to the list of xij variables to be reshaped, or
drop them.
r(9);

[William Lisowski]

You neglected

rename (air dup) (air_ dup_)

Either include that code or change your reshape command to

reshape wide air dup , i(lat lon time time2) j(level)