The Altair Community is migrating to a new platform to provide a better experience for you. In preparation for the migration, the Altair Community is on read-only mode from October 28 - November 6, 2024. Technical support via cases will continue to work as is. For any urgent requests from Students/Faculty members, please submit the form linked here
Filling values in variables based on specific condition
Learner III
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