CODEWITHSUNDEEP
spssspss-modeler
Patricia
Patricia

Reputation: 41

How to restructure data in SPSS Modeler?

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.

Upvotes: 2

Views: 2847

Answers (1)

Vojtěch Skubanič
Vojtěch Skubanič

Reputation: 141

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.

Upvotes: 3

Related Questions