Options
Filling values in variables based on specific condition
Contributor I
Hi!
I have a standard data mining data preparation problem:
In the database i have customers identified by the acct_id attribute.
Each of them have statuses (like 'O', 'N', 'T').
Each of them have a numeric weight (real).
I want to create new attributes for each status, in which I want to store the sum of weights for that specific customer and status.
I created flags for each of the statuses with values 0/1 (nominal to binominal, nominal to numeric).
Now I want to replace the '1' valuse by the weight value in that same row.
After this, I could aggregate on acct_id, and take the sum values of the attributes weight_O, weight_N etc.
How can I do this replacement?
Thank you!
Starting database:
acct_id acct_status weight
1234 'O' 1.2345
4566 'N' -2.345
1234 'N' 3.456
Desired result:
acct_id (unique) weight_O weight_N
1234 1.2345 3.456
4566 0 -2.345
I have a standard data mining data preparation problem:
In the database i have customers identified by the acct_id attribute.
Each of them have statuses (like 'O', 'N', 'T').
Each of them have a numeric weight (real).
I want to create new attributes for each status, in which I want to store the sum of weights for that specific customer and status.
I created flags for each of the statuses with values 0/1 (nominal to binominal, nominal to numeric).
Now I want to replace the '1' valuse by the weight value in that same row.
After this, I could aggregate on acct_id, and take the sum values of the attributes weight_O, weight_N etc.
How can I do this replacement?
Thank you!
Starting database:
acct_id acct_status weight
1234 'O' 1.2345
4566 'N' -2.345
1234 'N' 3.456
Desired result:
acct_id (unique) weight_O weight_N
1234 1.2345 3.456
4566 0 -2.345
0
Answers
you can use the operator "Pivot" for this. I have created and uploaded a process showing this with our new Community Extension which connects RapidMiner to the myExperiment portal. Just install the new Community Extension from your help menu and you can download and execute the process with a few clicks. The process is called "Pivoting", the process website is http://www.myexperiment.org/workflows/1274
This process shows the basics of Pivoting. A data set with three columns is loaded and partially generated. Afterwards, the data is rotated and missings are replaced by zero just as you want it to be.
Cheers,
Ingo