2

could you please advise on restructuring the data so to get the data structure that can be used for time series modeling in SPSS Modeler?

E.g. (currently having the below structure)

ID  Period  Value
ABC  1      100
ABC  2      110
DEF  1      50
DEF  2      50
DEF  3      60
GHJ  5      200

(needed structure)

Period ABC DEF GHJ
1      100     50
2      110 50
3          60
5              200

I have been thinking to use Transpose node but it does not work for such a case, so I have used SetToFlag node and afterwards Transpose one but then need to replace somehow T / F values with the values for the respective period / ID.

I greatly appreciate any help / advice. Thanks a lot in advance.

eli-k
  • 10,898
  • 11
  • 40
  • 44
Patricia
  • 41
  • 1
  • 6

1 Answers1

3

I suggest following procedure with restructure+aggregate:

  1. In TYPE node set "ID" as nominal and "Read values"
  2. Use RESTRUCTURE node with "ID", move its categories to "Create restructured fields", set radio button to "use values from other fields" and choose variable "Value"
  3. Use AGGREGATE, set key as "Period" and select desired Fields to aggregate, e.g. "ABC", "DEF", "GHJ". Aggregate means (or max, min, it doesn't matter)
  4. Rename new variables with FILTER node to get rid off suffixes. (This could be annoying for lot of variables, it could be overcome with script or tricky use of RESTRUCTURE, MERGE, value_at(), @FIELDS_BETWEEN() but that's much harder to explain and probably not necessary right now.)

Note: 3rd row of your example should be:

GHJ 1 50

or else it doesn't correspond to desired structure.

Vojtěch Skubanič
  • 141
  • 2
  • Thanks a lot for your help. It is working, just in Restructure node there is no option for "ID" in the available fields list (could you please advise why it is not visible?) just "Period" so basically in the end I used the Transpose node and got the needed structure. In case there are more fields to be added in the Restructure node after updating the data please is there a way how to do it automatically so without any manual adjustments in order to avoid opening the node and updating the list of the Create restructured fields? – Patricia Oct 11 '16 at 10:16