Replicating Excel Pivot Table features
batstache611
New Altair Community Member
Hello,
Often I use excel to make a deeper inspection of my process results such as inspecting cluster composition based on the attributes, or inspecting GLM predictions on a scoring set. E.g. What is the distribution in each cluster for different cities of City attribute, or how are the predicted leads distributed across the various business Categories and Region attributes, etc. And I need to do this back and forth quite a few number of times until I'm satisfied that the scored data looks very similar to training data, or that my clusters are distinct enough. As a habit, I do not blindly trust an algorithm or a computer program.
I have used the pivot operator in RM but unlike excel, first of all it does not give me totals for rows or columns of the pivot table. Secondly, I'm not sure how to go about changing the numbers to percentage of row/column total or percentage of parent row/column total, etc. on the fly like in excel. I have been able to adjust the significant digits after a decimal point, but that's about it. Also the index attribute defined in the Pivot operator does not get passed to the next set of operators. E.g. If the index attribute was cluster, and the pivot table contained attributes cluster_0, cluster_1, 2, 3, etc... they do not get passed to the next operator - say, generate aggregation, or select attributes, etc. I have to manually type them in.
The reason I am looking for a solution inside rapidminer is to avoid having to do so much back and forth and keep recreating the same pivots over and over again with just different data values. If I can build the whole thing inside RM, I just need to hit play everytime and stretch my arms and relax until it finishes and dumps all pivot tables and other outputs onto the results window. Thank you very much.
Tagged:
0
Answers
-
Hi,
"If I can build the whole thing inside RM, I just need to hit play everytime and stretch my arms and relax until it finishes and dumps all..."
That's the spirit :smileyvery-happy:
In order to replicate the Excel Pivot functionality you actually need to combined 2 to 3 operators in RapidMiner. They are "Pivot", "Aggregate", and sometimes even "Generate Attributes".
Why those three? Because Excel is actually lying to you. The Pivot operation itself is actually only the rotation, but they built in the total / grouping / aggregation functionality into their Pivot tables as well. While this is certainly useful, it actually is more than just pivoting...
Anyway, maybe the hint above is already helpful. If not, and if you could share some data with us (in case it is not senstitive), we could try to help you building such a process delivering the desired output. Best would be an Excel file with the original data (like it is in RapidMiner) and then another tab showing the Pivot table like you want it to be. We can then work together on building the process for this and I am sure you can take it from there...
Cheers,
Ingo
1 -
Thank you @IngoRM, I guess I am lazy like that sometimes lol. You know what they say though, lazy people tend to find clever solutions :smileytongue: or atleast I hope to! Anyways thank you very much for your help once again. I will go ahead and try the aggregate and generate attributes to see if I can figure it out. But should you wish to take a look also, I am also sharing the data (anonymized) and the rapidminer process xml to give you an idea of what I'm trying to achieve. The excel file contains two sample pivots on one sheet and output of clustering on another. You can use the output data as input to the process by excluding the cluster attribute if you wish.
<?xml version="1.0" encoding="UTF-8"?><process version="7.5.001">
<context>
<input/>
<output/>
<macros/>
</context>
<operator activated="true" class="process" compatibility="7.5.001" expanded="true" name="Process">
<process expanded="true">
<operator activated="true" class="read_csv" compatibility="7.5.001" expanded="true" height="68" name="Read CSV (2)" width="90" x="45" y="34">
<parameter key="csv_file" value="C:\Users\Pari\Documents\clustering_input_final.csv"/>
<parameter key="column_separators" value=","/>
<parameter key="first_row_as_names" value="false"/>
<list key="annotations">
<parameter key="0" value="Name"/>
</list>
<parameter key="encoding" value="UTF-8"/>
<list key="data_set_meta_data_information">
<parameter key="0" value="Redeemer.true.polynominal.attribute"/>
<parameter key="1" value="user_age.true.integer.attribute"/>
<parameter key="2" value="user_gender.true.polynominal.attribute"/>
<parameter key="3" value="user_postal_code.true.polynominal.attribute"/>
<parameter key="4" value="user_province_code.true.integer.attribute"/>
<parameter key="5" value="numCreditCards.true.polynominal.attribute"/>
<parameter key="6" value="pct_income_pers_20-40.true.real.attribute"/>
<parameter key="7" value="pct_income_pers_40-80.true.real.attribute"/>
<parameter key="8" value="pct_income_pers_80-125.true.real.attribute"/>
<parameter key="9" value="pct_income_pers_125up.true.real.attribute"/>
<parameter key="10" value="val_income_pers_avg.true.integer.attribute"/>
<parameter key="11" value="pct_education_nodegree.true.real.attribute"/>
<parameter key="12" value="pct_education_highschool.true.real.attribute"/>
<parameter key="13" value="pct_education_certificate.true.real.attribute"/>
<parameter key="14" value="pct_education_bachelor.true.real.attribute"/>
<parameter key="15" value="pct_education_advanced.true.real.attribute"/>
<parameter key="16" value="pct_education_collegestudent.true.real.attribute"/>
<parameter key="17" value="pct_residence_own.true.real.attribute"/>
<parameter key="18" value="pct_residence_rent.true.real.attribute"/>
<parameter key="19" value="val_resident_medianhousingvalue.true.integer.attribute"/>
<parameter key="20" value="val_resident_medianrent.true.integer.attribute"/>
<parameter key="21" value="pct_income_lowincome.true.real.attribute"/>
<parameter key="22" value="Membership Duration (Days).true.real.attribute"/>
<parameter key="23" value="user_account_balance.true.real.attribute"/>
<parameter key="24" value="Admin Amount.true.real.attribute"/>
<parameter key="25" value="Number of Admin.true.integer.attribute"/>
<parameter key="26" value="Coupon Amount.true.real.attribute"/>
<parameter key="27" value="Survey Amount.true.real.attribute"/>
<parameter key="28" value="Research Survey Amount.true.integer.attribute"/>
<parameter key="29" value="Video Amount.true.real.attribute"/>
<parameter key="30" value="Total Amount.true.real.attribute"/>
<parameter key="31" value="Profile Survey Amount.true.integer.attribute"/>
<parameter key="32" value="Number of Coupon.true.integer.attribute"/>
<parameter key="33" value="Number of Profile Survey.true.integer.attribute"/>
<parameter key="34" value="Number of Research Survey.true.integer.attribute"/>
<parameter key="35" value="Number of Survey.true.integer.attribute"/>
<parameter key="36" value="Number of Video.true.integer.attribute"/>
<parameter key="37" value="total Transactions.true.integer.attribute"/>
<parameter key="38" value="no_of_redemptions.true.integer.attribute"/>
<parameter key="39" value="total_of_redemptions.true.real.attribute"/>
<parameter key="40" value="languageAtHome.true.polynominal.attribute"/>
<parameter key="41" value="income.true.polynominal.attribute"/>
<parameter key="42" value="ethnicity.true.polynominal.attribute"/>
<parameter key="43" value="minorityStatus.true.polynominal.attribute"/>
<parameter key="44" value="workMode.true.polynominal.attribute"/>
<parameter key="45" value="educationLevel.true.polynominal.attribute"/>
<parameter key="46" value="relationship.true.polynominal.attribute"/>
<parameter key="47" value="jobLevel.true.polynominal.attribute"/>
<parameter key="48" value="homeOwner.true.polynominal.attribute"/>
<parameter key="49" value="numChildren.true.polynominal.attribute"/>
</list>
</operator>
<operator activated="true" class="generate_id" compatibility="7.5.001" expanded="true" height="82" name="Generate ID" width="90" x="179" y="34">
<parameter key="offset" value="100"/>
</operator>
<operator activated="true" class="generate_attributes" compatibility="7.5.001" expanded="true" height="82" name="Generate census (3)" width="90" x="313" y="34">
<list key="function_descriptions">
<parameter key="Low Income Group" value="pct_income_lowincome+[pct_income_pers_20-40]"/>
<parameter key="Mid Income Group" value="[pct_income_pers_40-80]"/>
<parameter key="High Income Group" value="[pct_income_pers_80-125]+pct_income_pers_125up"/>
<parameter key="High School" value="pct_education_nodegree+pct_education_highschool"/>
<parameter key="Trade School" value="pct_education_certificate"/>
<parameter key="College" value="pct_education_collegestudent"/>
<parameter key="University" value="pct_education_advanced+pct_education_bachelor"/>
<parameter key="Owns a House" value="pct_residence_own"/>
<parameter key="Median House Value" value="val_resident_medianhousingvalue"/>
<parameter key="Rents a House" value="pct_residence_rent"/>
<parameter key="Median Rent Value" value="val_resident_medianrent"/>
<parameter key="Average Income Per Person" value="val_income_pers_avg"/>
</list>
</operator>
<operator activated="true" class="generate_attributes" compatibility="7.5.001" expanded="true" height="82" name="Generate Frequencies & Bins" width="90" x="447" y="34">
<list key="function_descriptions">
<parameter key="Frequency of Redemption" value="[Membership Duration (Days)]/no_of_redemptions"/>
<parameter key="Frequency of Transaction" value="[Membership Duration (Days)]/[total Transactions]"/>
<parameter key="Bucket Survey Amount" value="if([Survey Amount]<=4, "Low", if([Survey Amount]<=10, "Mid", if([Survey Amount]>10, "High", "Error")))"/>
<parameter key="Bucket Coupon Amount" value="if([Coupon Amount]<=10, "Low", if([Coupon Amount]<=25, "Lower Mid", if([Coupon Amount]<=45, "Upper Mid", if([Coupon Amount]>45, "High", "Error"))))"/>
<parameter key="Bucket Total Amount" value="if([Total Amount]<=20, "Low", if([Total Amount]<=50, "Mid", if([Total Amount]>50, "High", "Error")))"/>
<parameter key="Bucket User Age" value="if(user_age<20, "Teens", if(user_age<30, "20s", if(user_age<40, "30s", if(user_age<50, "40s", if(user_age>=50, "Senior Citizen", "Error")))))"/>
<parameter key="Bucket User Account Balance" value="if(user_account_balance<=10, "Low", if(user_account_balance<=20, "Lower Mid", if(user_account_balance<=40, "Upper Mid", if(user_account_balance>40, "High", "Error"))))"/>
<parameter key="Frequency of Coupons" value="[Membership Duration (Days)]/[Number of Coupon]"/>
<parameter key="Frequency of Surveys" value="[Membership Duration (Days)]/[Number of Survey]"/>
</list>
</operator>
<operator activated="true" class="select_attributes" compatibility="7.5.001" expanded="true" height="82" name="Select Attributes (4)" width="90" x="581" y="34">
<parameter key="attribute_filter_type" value="subset"/>
<parameter key="attributes" value="id|Average Income Per Person|Bucket Coupon Amount|Bucket Survey Amount|Bucket Total Amount|Bucket User Account Balance|Bucket User Age|College|High Income Group|High School|Low Income Group|Median House Value|Median Rent Value|Mid Income Group|Owns a House|Rents a House|Trade School|University|educationLevel|ethnicity|homeOwner|income|jobLevel|languageAtHome|minorityStatus|numChildren|numCreditCards|relationship|workMode|user_id|handyRating|nextCarType"/>
<parameter key="include_special_attributes" value="true"/>
</operator>
<operator activated="true" class="impute_missing_values" compatibility="7.3.001" expanded="true" height="68" name="Impute Missing Values (2)" width="90" x="715" y="34">
<parameter key="include_special_attributes" value="true"/>
<process expanded="true">
<operator activated="true" class="k_nn" compatibility="7.5.001" expanded="true" height="82" name="k-NN (2)" width="90" x="447" y="187">
<parameter key="k" value="5"/>
</operator>
<connect from_port="example set source" to_op="k-NN (2)" to_port="training set"/>
<connect from_op="k-NN (2)" from_port="model" to_port="model sink"/>
<portSpacing port="source_example set source" spacing="0"/>
<portSpacing port="sink_model sink" spacing="0"/>
</process>
</operator>
<operator activated="true" class="nominal_to_numerical" compatibility="7.5.001" expanded="true" height="103" name="Nominal to Numerical" width="90" x="849" y="34">
<parameter key="include_special_attributes" value="true"/>
<parameter key="coding_type" value="unique integers"/>
<list key="comparison_groups"/>
</operator>
<operator activated="true" class="normalize" compatibility="7.5.001" expanded="true" height="103" name="Normalize" width="90" x="983" y="34">
<parameter key="attributes" value="|University|Trade School|Rents a House|Owns a House|Mid Income Group|Median Rent Value|Low Income Group|High School|High Income Group|Frequency of Transaction|Frequency of Surveys|Frequency of Redemption|Frequency of Coupons|College"/>
<parameter key="max" value="10.0"/>
</operator>
<operator activated="true" class="k_means" compatibility="7.5.001" expanded="true" height="82" name="K-Means" width="90" x="1117" y="34">
<parameter key="k" value="4"/>
<parameter key="max_runs" value="100"/>
<parameter key="measure_types" value="MixedMeasures"/>
<parameter key="max_optimization_steps" value="500"/>
</operator>
<operator activated="true" class="select_attributes" compatibility="7.5.001" expanded="true" height="82" name="Select Attributes (3)" width="90" x="849" y="187">
<parameter key="attribute_filter_type" value="subset"/>
<parameter key="attributes" value="cluster|id"/>
<parameter key="include_special_attributes" value="true"/>
</operator>
<operator activated="true" class="join" compatibility="7.5.001" expanded="true" height="82" name="Join" width="90" x="849" y="289">
<parameter key="join_type" value="left"/>
<list key="key_attributes">
<parameter key="user_id" value="user_id"/>
</list>
</operator>
<operator activated="true" class="multiply" compatibility="7.5.001" expanded="true" height="103" name="Multiply" width="90" x="983" y="289"/>
<operator activated="true" class="subprocess" compatibility="7.5.001" expanded="true" height="82" name="Create Pivots" width="90" x="983" y="187">
<process expanded="true">
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate" width="90" x="246" y="34">
<list key="aggregation_attributes">
<parameter key="Average Income Per Person" value="average"/>
<parameter key="Frequency of Coupons" value="average"/>
<parameter key="Frequency of Redemption" value="average"/>
<parameter key="Frequency of Surveys" value="average"/>
<parameter key="Frequency of Transaction" value="average"/>
<parameter key="Owns a House" value="average"/>
<parameter key="Median House Value" value="average"/>
<parameter key="Rents a House" value="average"/>
<parameter key="Median Rent Value" value="average"/>
<parameter key="High School" value="average"/>
<parameter key="Trade School" value="average"/>
<parameter key="College" value="average"/>
<parameter key="University" value="average"/>
</list>
<parameter key="group_by_attributes" value="cluster"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (2)" width="90" x="246" y="136">
<list key="aggregation_attributes">
<parameter key="Redeemer" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="cluster|Redeemer"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="Redeemer" width="90" x="380" y="136">
<parameter key="group_attribute" value="Redeemer"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename Redeemer" width="90" x="514" y="136">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (5)" width="90" x="246" y="238">
<list key="aggregation_attributes">
<parameter key="Bucket Total Amount" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="cluster|Bucket Total Amount"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="Bucket Total Amount" width="90" x="380" y="238">
<parameter key="group_attribute" value="Bucket Total Amount"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (6)" width="90" x="246" y="340">
<list key="aggregation_attributes">
<parameter key="Bucket Coupon Amount" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="cluster|Bucket Coupon Amount"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="Bucket Coupon Amount" width="90" x="380" y="340">
<parameter key="group_attribute" value="Bucket Coupon Amount"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (7)" width="90" x="246" y="442">
<list key="aggregation_attributes">
<parameter key="Bucket Survey Amount" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="Bucket Survey Amount|cluster"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="Bucket Survey Amount" width="90" x="380" y="442">
<parameter key="group_attribute" value="Bucket Survey Amount"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (8)" width="90" x="246" y="544">
<list key="aggregation_attributes">
<parameter key="Bucket User Age" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="cluster|Bucket User Age"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="Bucket User Age" width="90" x="380" y="544">
<parameter key="group_attribute" value="Bucket User Age"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (9)" width="90" x="246" y="646">
<list key="aggregation_attributes">
<parameter key="user_gender" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="cluster|user_gender"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="Gender" width="90" x="380" y="646">
<parameter key="group_attribute" value="user_gender"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (10)" width="90" x="246" y="748">
<list key="aggregation_attributes">
<parameter key="ethnicity" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="cluster|ethnicity"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="Ethnicity" width="90" x="380" y="748">
<parameter key="group_attribute" value="ethnicity"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (11)" width="90" x="246" y="850">
<list key="aggregation_attributes">
<parameter key="languageAtHome" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="cluster|languageAtHome"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="languageAtHome" width="90" x="380" y="850">
<parameter key="group_attribute" value="languageAtHome"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (12)" width="90" x="246" y="952">
<list key="aggregation_attributes">
<parameter key="minorityStatus" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="cluster|minorityStatus"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="Minority Status" width="90" x="380" y="952">
<parameter key="group_attribute" value="minorityStatus"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (13)" width="90" x="246" y="1054">
<list key="aggregation_attributes">
<parameter key="educationLevel" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="cluster|educationLevel"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="Education Level" width="90" x="380" y="1054">
<parameter key="group_attribute" value="educationLevel"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (14)" width="90" x="246" y="1156">
<list key="aggregation_attributes">
<parameter key="relationship" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="cluster|relationship"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="relationship" width="90" x="380" y="1156">
<parameter key="group_attribute" value="relationship"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (15)" width="90" x="246" y="1258">
<list key="aggregation_attributes">
<parameter key="homeOwner" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="cluster|homeOwner"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="Home Owner" width="90" x="380" y="1258">
<parameter key="group_attribute" value="homeOwner"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate (16)" width="90" x="246" y="1360">
<list key="aggregation_attributes">
<parameter key="numChildren" value="count (percentage)"/>
</list>
<parameter key="group_by_attributes" value="cluster|numChildren"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="NumChildren" width="90" x="380" y="1360">
<parameter key="group_attribute" value="numChildren"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename Tot. Amt" width="90" x="514" y="238">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename Coupon Amt" width="90" x="514" y="340">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename Survey Amt" width="90" x="514" y="442">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename User Age" width="90" x="514" y="544">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename Gender" width="90" x="514" y="646">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename Ethnicity" width="90" x="514" y="748">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="langAtHome" width="90" x="514" y="850">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename Minority" width="90" x="514" y="952">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename Education Lvl" width="90" x="514" y="1054">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename Relationship" width="90" x="514" y="1156">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename HomwOwner" width="90" x="514" y="1258">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename Children" width="90" x="514" y="1360">
<parameter key="attributes" value="countWithMissings(Redeemer)"/>
<parameter key="regular_expression" value="(percentage\_count\(Redeemer\)\_)"/>
<parameter key="replace_what" value="(percentage\_count\(.*\)\_)(.*)"/>
<parameter key="replace_by" value="$2"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename by Replacing" width="90" x="380" y="34">
<parameter key="replace_what" value="average"/>
</operator>
<operator activated="true" class="transpose" compatibility="7.5.001" expanded="true" height="82" name="Transpose" width="90" x="514" y="34"/>
<operator activated="true" class="rename_by_example_values" compatibility="7.5.001" expanded="true" height="82" name="Numericals Averages by Cluster" width="90" x="648" y="34"/>
<operator activated="true" class="collect" compatibility="7.5.001" expanded="true" height="355" name="Collect" width="90" x="849" y="340"/>
<connect from_port="in 1" to_op="Aggregate" to_port="example set input"/>
<connect from_op="Aggregate" from_port="example set output" to_op="Rename by Replacing" to_port="example set input"/>
<connect from_op="Aggregate" from_port="original" to_op="Aggregate (2)" to_port="example set input"/>
<connect from_op="Aggregate (2)" from_port="example set output" to_op="Redeemer" to_port="example set input"/>
<connect from_op="Aggregate (2)" from_port="original" to_op="Aggregate (5)" to_port="example set input"/>
<connect from_op="Redeemer" from_port="example set output" to_op="Rename Redeemer" to_port="example set input"/>
<connect from_op="Rename Redeemer" from_port="example set output" to_op="Collect" to_port="input 2"/>
<connect from_op="Aggregate (5)" from_port="example set output" to_op="Bucket Total Amount" to_port="example set input"/>
<connect from_op="Aggregate (5)" from_port="original" to_op="Aggregate (6)" to_port="example set input"/>
<connect from_op="Bucket Total Amount" from_port="example set output" to_op="Rename Tot. Amt" to_port="example set input"/>
<connect from_op="Aggregate (6)" from_port="example set output" to_op="Bucket Coupon Amount" to_port="example set input"/>
<connect from_op="Aggregate (6)" from_port="original" to_op="Aggregate (7)" to_port="example set input"/>
<connect from_op="Bucket Coupon Amount" from_port="example set output" to_op="Rename Coupon Amt" to_port="example set input"/>
<connect from_op="Aggregate (7)" from_port="example set output" to_op="Bucket Survey Amount" to_port="example set input"/>
<connect from_op="Aggregate (7)" from_port="original" to_op="Aggregate (8)" to_port="example set input"/>
<connect from_op="Bucket Survey Amount" from_port="example set output" to_op="Rename Survey Amt" to_port="example set input"/>
<connect from_op="Aggregate (8)" from_port="example set output" to_op="Bucket User Age" to_port="example set input"/>
<connect from_op="Aggregate (8)" from_port="original" to_op="Aggregate (9)" to_port="example set input"/>
<connect from_op="Bucket User Age" from_port="example set output" to_op="Rename User Age" to_port="example set input"/>
<connect from_op="Aggregate (9)" from_port="example set output" to_op="Gender" to_port="example set input"/>
<connect from_op="Aggregate (9)" from_port="original" to_op="Aggregate (10)" to_port="example set input"/>
<connect from_op="Gender" from_port="example set output" to_op="Rename Gender" to_port="example set input"/>
<connect from_op="Aggregate (10)" from_port="example set output" to_op="Ethnicity" to_port="example set input"/>
<connect from_op="Aggregate (10)" from_port="original" to_op="Aggregate (11)" to_port="example set input"/>
<connect from_op="Ethnicity" from_port="example set output" to_op="Rename Ethnicity" to_port="example set input"/>
<connect from_op="Aggregate (11)" from_port="example set output" to_op="languageAtHome" to_port="example set input"/>
<connect from_op="Aggregate (11)" from_port="original" to_op="Aggregate (12)" to_port="example set input"/>
<connect from_op="languageAtHome" from_port="example set output" to_op="langAtHome" to_port="example set input"/>
<connect from_op="Aggregate (12)" from_port="example set output" to_op="Minority Status" to_port="example set input"/>
<connect from_op="Aggregate (12)" from_port="original" to_op="Aggregate (13)" to_port="example set input"/>
<connect from_op="Minority Status" from_port="example set output" to_op="Rename Minority" to_port="example set input"/>
<connect from_op="Aggregate (13)" from_port="example set output" to_op="Education Level" to_port="example set input"/>
<connect from_op="Aggregate (13)" from_port="original" to_op="Aggregate (14)" to_port="example set input"/>
<connect from_op="Education Level" from_port="example set output" to_op="Rename Education Lvl" to_port="example set input"/>
<connect from_op="Aggregate (14)" from_port="example set output" to_op="relationship" to_port="example set input"/>
<connect from_op="Aggregate (14)" from_port="original" to_op="Aggregate (15)" to_port="example set input"/>
<connect from_op="relationship" from_port="example set output" to_op="Rename Relationship" to_port="example set input"/>
<connect from_op="Aggregate (15)" from_port="example set output" to_op="Home Owner" to_port="example set input"/>
<connect from_op="Aggregate (15)" from_port="original" to_op="Aggregate (16)" to_port="example set input"/>
<connect from_op="Home Owner" from_port="example set output" to_op="Rename HomwOwner" to_port="example set input"/>
<connect from_op="Aggregate (16)" from_port="example set output" to_op="NumChildren" to_port="example set input"/>
<connect from_op="NumChildren" from_port="example set output" to_op="Rename Children" to_port="example set input"/>
<connect from_op="Rename Tot. Amt" from_port="example set output" to_op="Collect" to_port="input 3"/>
<connect from_op="Rename Coupon Amt" from_port="example set output" to_op="Collect" to_port="input 4"/>
<connect from_op="Rename Survey Amt" from_port="example set output" to_op="Collect" to_port="input 5"/>
<connect from_op="Rename User Age" from_port="example set output" to_op="Collect" to_port="input 6"/>
<connect from_op="Rename Gender" from_port="example set output" to_op="Collect" to_port="input 7"/>
<connect from_op="Rename Ethnicity" from_port="example set output" to_op="Collect" to_port="input 8"/>
<connect from_op="langAtHome" from_port="example set output" to_op="Collect" to_port="input 9"/>
<connect from_op="Rename Minority" from_port="example set output" to_op="Collect" to_port="input 10"/>
<connect from_op="Rename Education Lvl" from_port="example set output" to_op="Collect" to_port="input 11"/>
<connect from_op="Rename Relationship" from_port="example set output" to_op="Collect" to_port="input 12"/>
<connect from_op="Rename HomwOwner" from_port="example set output" to_op="Collect" to_port="input 13"/>
<connect from_op="Rename Children" from_port="example set output" to_op="Collect" to_port="input 14"/>
<connect from_op="Rename by Replacing" from_port="example set output" to_op="Transpose" to_port="example set input"/>
<connect from_op="Transpose" from_port="example set output" to_op="Numericals Averages by Cluster" to_port="example set input"/>
<connect from_op="Numericals Averages by Cluster" from_port="example set output" to_op="Collect" to_port="input 1"/>
<connect from_op="Collect" from_port="collection" to_port="out 1"/>
<portSpacing port="source_in 1" spacing="0"/>
<portSpacing port="source_in 2" spacing="0"/>
<portSpacing port="sink_out 1" spacing="0"/>
<portSpacing port="sink_out 2" spacing="0"/>
</process>
</operator>
<operator activated="true" class="write_excel" compatibility="7.5.001" expanded="true" height="82" name="Write Excel" width="90" x="1117" y="289">
<parameter key="excel_file" value="C:\Users\Pari\Documents\clustering_demo.xlsx"/>
</operator>
<connect from_op="Read CSV (2)" from_port="output" to_op="Generate ID" to_port="example set input"/>
<connect from_op="Generate ID" from_port="example set output" to_op="Generate census (3)" to_port="example set input"/>
<connect from_op="Generate census (3)" from_port="example set output" to_op="Generate Frequencies & Bins" to_port="example set input"/>
<connect from_op="Generate Frequencies & Bins" from_port="example set output" to_op="Select Attributes (4)" to_port="example set input"/>
<connect from_op="Select Attributes (4)" from_port="example set output" to_op="Impute Missing Values (2)" to_port="example set in"/>
<connect from_op="Select Attributes (4)" from_port="original" to_op="Join" to_port="left"/>
<connect from_op="Impute Missing Values (2)" from_port="example set out" to_op="Nominal to Numerical" to_port="example set input"/>
<connect from_op="Nominal to Numerical" from_port="example set output" to_op="Normalize" to_port="example set input"/>
<connect from_op="Normalize" from_port="example set output" to_op="K-Means" to_port="example set"/>
<connect from_op="K-Means" from_port="cluster model" to_port="result 1"/>
<connect from_op="K-Means" from_port="clustered set" to_op="Select Attributes (3)" to_port="example set input"/>
<connect from_op="Select Attributes (3)" from_port="example set output" to_op="Join" to_port="right"/>
<connect from_op="Join" from_port="join" to_op="Multiply" to_port="input"/>
<connect from_op="Multiply" from_port="output 1" to_op="Create Pivots" to_port="in 1"/>
<connect from_op="Multiply" from_port="output 2" to_op="Write Excel" to_port="input"/>
<connect from_op="Create Pivots" from_port="out 1" to_port="result 2"/>
<connect from_op="Write Excel" from_port="through" to_port="result 3"/>
<portSpacing port="source_input 1" spacing="0"/>
<portSpacing port="sink_result 1" spacing="0"/>
<portSpacing port="sink_result 2" spacing="0"/>
<portSpacing port="sink_result 3" spacing="0"/>
<portSpacing port="sink_result 4" spacing="0"/>
</process>
</operator>
</process>Best regards and a good afternoon!
0 -
@IngoRM attached with this post is the sample pivots. I found that I could not upload an xlsx with multiple sheets on the forum so I broke up the file into two csv files. The sample pivots no longer behave as excel pivots unfortunately. Sorry about the inconvenience.
0 -
You may also be interested in the Crosstab operator, which is available in the Statistics extension from the Marketplace. It essentially combines some of the steps into a single operator for convenience. But as Ingo already said, with Aggregate and Generate Aggregation you can already do everything you need inside the basic RapidMiner Studio.
0 -
Ok, here we go:
<?xml version="1.0" encoding="UTF-8"?><process version="7.5.001">
<context>
<input/>
<output/>
<macros/>
</context>
<operator activated="true" class="process" compatibility="7.5.001" expanded="true" name="Process">
<process expanded="true">
<operator activated="true" class="read_csv" compatibility="7.5.001" expanded="true" height="68" name="Read CSV" width="90" x="45" y="34">
<parameter key="csv_file" value="C:\Users\IngoMierswa\Desktop\clustering_demo.csv"/>
<parameter key="column_separators" value=","/>
<parameter key="first_row_as_names" value="false"/>
<list key="annotations">
<parameter key="0" value="Name"/>
</list>
<parameter key="encoding" value="windows-1252"/>
<list key="data_set_meta_data_information">
<parameter key="0" value="Redeemer.true.polynominal.attribute"/>
<parameter key="1" value="user_age.true.real.attribute"/>
<parameter key="2" value="user_gender.true.polynominal.attribute"/>
<parameter key="3" value="user_postal_code.true.polynominal.attribute"/>
<parameter key="4" value="user_province_code.true.real.attribute"/>
<parameter key="5" value="numCreditCards.true.polynominal.attribute"/>
<parameter key="6" value="pct_income_pers_20-40.true.real.attribute"/>
<parameter key="7" value="pct_income_pers_40-80.true.real.attribute"/>
<parameter key="8" value="pct_income_pers_80-125.true.real.attribute"/>
<parameter key="9" value="pct_income_pers_125up.true.real.attribute"/>
<parameter key="10" value="val_income_pers_avg.true.real.attribute"/>
<parameter key="11" value="pct_education_nodegree.true.real.attribute"/>
<parameter key="12" value="pct_education_highschool.true.real.attribute"/>
<parameter key="13" value="pct_education_certificate.true.real.attribute"/>
<parameter key="14" value="pct_education_bachelor.true.real.attribute"/>
<parameter key="15" value="pct_education_advanced.true.real.attribute"/>
<parameter key="16" value="pct_education_collegestudent.true.real.attribute"/>
<parameter key="17" value="pct_residence_own.true.real.attribute"/>
<parameter key="18" value="pct_residence_rent.true.real.attribute"/>
<parameter key="19" value="val_resident_medianhousingvalue.true.real.attribute"/>
<parameter key="20" value="val_resident_medianrent.true.real.attribute"/>
<parameter key="21" value="pct_income_lowincome.true.real.attribute"/>
<parameter key="22" value="Membership Duration (Days).true.real.attribute"/>
<parameter key="23" value="user_account_balance.true.real.attribute"/>
<parameter key="24" value="Admin Amount.true.real.attribute"/>
<parameter key="25" value="Number of Admin.true.real.attribute"/>
<parameter key="26" value="Coupon Amount.true.real.attribute"/>
<parameter key="27" value="Survey Amount.true.real.attribute"/>
<parameter key="28" value="Research Survey Amount.true.real.attribute"/>
<parameter key="29" value="Video Amount.true.real.attribute"/>
<parameter key="30" value="Total Amount.true.real.attribute"/>
<parameter key="31" value="Profile Survey Amount.true.real.attribute"/>
<parameter key="32" value="Number of Coupon.true.real.attribute"/>
<parameter key="33" value="Number of Profile Survey.true.real.attribute"/>
<parameter key="34" value="Number of Research Survey.true.real.attribute"/>
<parameter key="35" value="Number of Survey.true.real.attribute"/>
<parameter key="36" value="Number of Video.true.real.attribute"/>
<parameter key="37" value="total Transactions.true.real.attribute"/>
<parameter key="38" value="no_of_redemptions.true.real.attribute"/>
<parameter key="39" value="total_of_redemptions.true.real.attribute"/>
<parameter key="40" value="languageAtHome.true.polynominal.attribute"/>
<parameter key="41" value="income.true.polynominal.attribute"/>
<parameter key="42" value="ethnicity.true.polynominal.attribute"/>
<parameter key="43" value="minorityStatus.true.polynominal.attribute"/>
<parameter key="44" value="workMode.true.polynominal.attribute"/>
<parameter key="45" value="educationLevel.true.polynominal.attribute"/>
<parameter key="46" value="relationship.true.polynominal.attribute"/>
<parameter key="47" value="jobLevel.true.polynominal.attribute"/>
<parameter key="48" value="homeOwner.true.polynominal.attribute"/>
<parameter key="49" value="numChildren.true.polynominal.attribute"/>
<parameter key="50" value="Low Income Group.true.real.attribute"/>
<parameter key="51" value="Mid Income Group.true.real.attribute"/>
<parameter key="52" value="High Income Group.true.real.attribute"/>
<parameter key="53" value="High School.true.real.attribute"/>
<parameter key="54" value="Trade School.true.real.attribute"/>
<parameter key="55" value="College.true.real.attribute"/>
<parameter key="56" value="University.true.real.attribute"/>
<parameter key="57" value="Owns a House.true.real.attribute"/>
<parameter key="58" value="Median House Value.true.real.attribute"/>
<parameter key="59" value="Rents a House.true.real.attribute"/>
<parameter key="60" value="Median Rent Value.true.real.attribute"/>
<parameter key="61" value="Average Income Per Person.true.real.attribute"/>
<parameter key="62" value="Frequency of Redemption.true.polynominal.attribute"/>
<parameter key="63" value="Frequency of Transaction.true.real.attribute"/>
<parameter key="64" value="Bucket Survey Amount.true.polynominal.attribute"/>
<parameter key="65" value="Bucket Coupon Amount.true.polynominal.attribute"/>
<parameter key="66" value="Bucket Total Amount.true.polynominal.attribute"/>
<parameter key="67" value="Bucket User Age.true.polynominal.attribute"/>
<parameter key="68" value="Bucket User Account Balance.true.polynominal.attribute"/>
<parameter key="69" value="Frequency of Coupons.true.polynominal.attribute"/>
<parameter key="70" value="Frequency of Surveys.true.real.attribute"/>
<parameter key="71" value="id.true.real.attribute"/>
<parameter key="72" value="cluster.true.polynominal.attribute"/>
</list>
</operator>
<operator activated="true" class="filter_examples" compatibility="7.5.001" expanded="true" height="103" name="Filter Examples" width="90" x="179" y="34">
<list key="filters_list">
<parameter key="filters_entry_key" value="ethnicity.does_not_equal.0"/>
</list>
</operator>
<operator activated="true" class="aggregate" compatibility="7.5.001" expanded="true" height="82" name="Aggregate" width="90" x="313" y="34">
<list key="aggregation_attributes">
<parameter key="ethnicity" value="count (fractional)"/>
</list>
<parameter key="group_by_attributes" value="ethnicity|cluster"/>
</operator>
<operator activated="true" class="generate_attributes" compatibility="7.5.001" expanded="true" height="82" name="Generate Attributes" width="90" x="447" y="34">
<list key="function_descriptions">
<parameter key="Percentage" value="round([fractional_count(ethnicity)]*100,1)+"%""/>
</list>
</operator>
<operator activated="true" class="select_attributes" compatibility="7.5.001" expanded="true" height="82" name="Select Attributes" width="90" x="581" y="34">
<parameter key="attribute_filter_type" value="single"/>
<parameter key="attribute" value="fractional_count(ethnicity)"/>
<parameter key="invert_selection" value="true"/>
</operator>
<operator activated="true" class="pivot" compatibility="7.5.001" expanded="true" height="82" name="Pivot" width="90" x="715" y="34">
<parameter key="group_attribute" value="ethnicity"/>
<parameter key="index_attribute" value="cluster"/>
</operator>
<operator activated="true" class="rename" compatibility="7.5.001" expanded="true" height="82" name="Rename" width="90" x="849" y="34">
<parameter key="old_name" value="ethnicity"/>
<parameter key="new_name" value="Ethnicity"/>
<list key="rename_additional_attributes"/>
</operator>
<operator activated="true" class="rename_by_replacing" compatibility="7.5.001" expanded="true" height="82" name="Rename by Replacing" width="90" x="983" y="34">
<parameter key="replace_what" value="Percentage_(.*)"/>
<parameter key="replace_by" value="$1"/>
</operator>
<connect from_op="Read CSV" from_port="output" to_op="Filter Examples" to_port="example set input"/>
<connect from_op="Filter Examples" from_port="example set output" to_op="Aggregate" to_port="example set input"/>
<connect from_op="Aggregate" from_port="example set output" to_op="Generate Attributes" to_port="example set input"/>
<connect from_op="Generate Attributes" from_port="example set output" to_op="Select Attributes" to_port="example set input"/>
<connect from_op="Select Attributes" from_port="example set output" to_op="Pivot" to_port="example set input"/>
<connect from_op="Pivot" from_port="example set output" to_op="Rename" to_port="example set input"/>
<connect from_op="Rename" from_port="example set output" to_op="Rename by Replacing" to_port="example set input"/>
<connect from_op="Rename by Replacing" from_port="example set output" to_port="result 1"/>
<portSpacing port="source_input 1" spacing="0"/>
<portSpacing port="sink_result 1" spacing="0"/>
<portSpacing port="sink_result 2" spacing="0"/>
</process>
</operator>
</process>I only did this for the Ethinicty and also added a couple of operators to format things a bit nicer (you know, like better names, percentage symbols and stuff like this). I hope you can take it from here...
Have a nice weekend,
Ingo
0 -
Thank you @IngoRM, sorry I couldn't reply sooner. Hope you had a nice weekend yourself. Greatly appreciate you helping me here! My sincere thanks :smileyhappy:
0
