Filling values in variables based on specific condition

ryo796

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

IngoRM

Hi,

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