Values to Columns
You can convert individual values from a column to separate columns in your dataset.
Individual row values become the headers of the new columns that are created. If the value represented by the column is present in the original data, a consistent value is inserted (For example, Yes) in the corresponding row in the newly created column. If it's missing, another value is inserted (For example, No).
Tip
This type of conversion can be useful for preparing data for machine learning systems. You can convert the presence or absence of specific values in a row to 1 or 0, respectively. These values can also be used for filtering out rows as needed.
Source:
The following example contains transactional information about products sold in individual stores. In the transformation, the values in the Store_Nbr column are converted to columns.
Daily | Store_Nbr | POS_Sales | POS_Qty | PRODUCT_DESC |
|---|---|---|---|---|
2/8/13 | 1 | 70 | 7 | ACME LAWN GARDEN BAG CLEAR |
2/7/13 | 2 | 10.62 | 9 | ACME COOKIES CHOC CHIP |
2/7/13 | 2 | 0 | 0 | ACME SANDWICH BAG |
2/7/13 | 2 | 7.08 | 6 | ACME SODAS SALTED |
2/7/13 | 2 | 3.92 | 2 | ACME SCENTED OIL REFILL-CTRY SUN |
2/7/13 | 2 | 13.44 | 7 | ACME LARGE FUDGE GRAHAMS COOKIES |
2/7/13 | 2 | 0 | 0 | ACME SUGAR ICE WAFERS VANILLA |
2/7/13 | 3 | 3.16 | 2 | ACME ZOO ANIMAL FRUIT SNACKS 6'S |
2/7/13 | 3 | 3.16 | 2 | ACME WAFERS SUGER ICE |
2/7/13 | 3 | 3.16 | 2 | ACME SCENTED OIL REFILL-CTRY SUN |
2/7/13 | 3 | 6.32 | 4 | ACME RICE CRACKERS ONION |
2/2/13 | 9 | 150 | 30 | ACME FROSTED OATMEAL COOKIE SQUA |
2/2/13 | 9 | 3.5 | 2 | ACME FRUIT SNACK CASTLE ADVENTRS |
2/2/13 | 9 | 90 | 9 | ACME COOKIES CHOC CHIP |
2/2/13 | 9 | 30 | 6 | ACME ASSORTED COOKIES DRP |
2/2/13 | 9 | 70 | 7 | ACME KITCHEN BAG |
2/2/13 | 9 | 170 | 17 | ACME SNACK BAGS RESEALABLE |
2/2/13 | 9 | 20 | 4 | ACME CHEDDARY SN CRACKERS/PROCES |
2/2/13 | 9 | 6.5 | 2 | ACME RICE CRACKERS TERIYAKI |
2/2/13 | 9 | 1.5 | 3 | ACME COOKIE MAPLE LEAF CREME |
2/2/13 | 9 | 30 | 6 | ACME RICE CHIPS CHEDDAR |
2/1/13 | 7 | 190 | 38 | ACME FROSTED OATMEAL COOKIE SQUA |
2/1/13 | 7 | 20 | 2 | ACME COOKIES CHOC CHIP |
2/1/13 | 7 | 10 | 1 | ACME DIGESTIVE RICH TEA BISCUITS |
2/1/13 | 7 | 120 | 24 | ACME ASSORTED COOKIES DRP |
2/1/13 | 7 | 120 | 12 | ACME KITCHEN BAG |
2/1/13 | 7 | 90 | 9 | ACME SNACK BAGS RESEALABLE |
2/1/13 | 7 | 10 | 1 | ACME FUDGE MINT COOKIES SQUARES |
2/1/13 | 7 | 9.5 | 19 | ACME CHEDDARY SN CRACKERS/PROCES |
2/1/13 | 7 | 10 | 1 | ACME COOKIES MAPLE CREAM |
2/1/13 | 7 | 40 | 8 | ACME COOKIE MAPLE LEAF CREME |
Transformation:
In the following, the values in the Store_Nbr column have been converted to individual columns:
Transformation Name |
|
|---|---|
Parameter: Column | Store_Nbr |
Parameter: Fill when present | Yes |
Parameter: Max number of columns to create | 250 |
In the above:
Fill when present identifies the string literal value to insert if the row contains the column's value (
Yes).Fill when missing identifies the string literal value to insert if the row does not contain the column's value (empty).
Max number of columns to create places a limit on the total number of columns that the application is permitted to create. In this case, the limit is set to
250since the known number of stores is 250.Tip
It's a good habit to set limits on the maximum number of columns to create. Data can become sparse or unwieldy if limits are not considered.
Results:
Daily | Store_Nbr | POS_Sales | POS_Qty | PRODUCT_DESC | column_1 | column_2 | column_3 | column_9 | column_7 |
|---|---|---|---|---|---|---|---|---|---|
2/8/13 | 1 | 70 | 7 | ACME LAWN GARDEN BAG CLEAR | Yes | ||||
2/7/13 | 2 | 10.62 | 9 | ACME COOKIES CHOC CHIP | Yes | ||||
2/7/13 | 2 | 0 | 0 | ACME SANDWICH BAG | Yes | ||||
2/7/13 | 2 | 7.08 | 6 | ACME SODAS SALTED | Yes | ||||
2/7/13 | 2 | 3.92 | 2 | ACME SCENTED OIL REFILL-CTRY SUN | Yes | ||||
2/7/13 | 2 | 13.44 | 7 | ACME LARGE FUDGE GRAHAMS COOKIES | Yes | ||||
2/7/13 | 2 | 0 | 0 | ACME SUGAR ICE WAFERS VANILLA | Yes | ||||
2/7/13 | 3 | 3.16 | 2 | ACME ZOO ANIMAL FRUIT SNACKS 6'S | Yes | ||||
2/7/13 | 3 | 3.16 | 2 | ACME WAFERS SUGER ICE | Yes | ||||
2/7/13 | 3 | 3.16 | 2 | ACME SCENTED OIL REFILL-CTRY SUN | Yes | ||||
2/7/13 | 3 | 6.32 | 4 | ACME RICE CRACKERS ONION | Yes | ||||
2/2/13 | 9 | 150 | 30 | ACME FROSTED OATMEAL COOKIE SQUA | Yes | ||||
2/2/13 | 9 | 3.5 | 2 | ACME FRUIT SNACK CASTLE ADVENTRS | Yes | ||||
2/2/13 | 9 | 90 | 9 | ACME COOKIES CHOC CHIP | Yes | ||||
2/2/13 | 9 | 30 | 6 | ACME ASSORTED COOKIES DRP | Yes | ||||
2/2/13 | 9 | 70 | 7 | ACME KITCHEN BAG | Yes | ||||
2/2/13 | 9 | 170 | 17 | ACME SNACK BAGS RESEALABLE | Yes | ||||
2/2/13 | 9 | 20 | 4 | ACME CHEDDARY SN CRACKERS/PROCES | Yes | ||||
2/2/13 | 9 | 6.5 | 2 | ACME RICE CRACKERS TERIYAKI | Yes | ||||
2/2/13 | 9 | 1.5 | 3 | ACME COOKIE MAPLE LEAF CREME | Yes | ||||
2/2/13 | 9 | 30 | 6 | ACME RICE CHIPS CHEDDAR | Yes | ||||
2/1/13 | 7 | 190 | 38 | ACME FROSTED OATMEAL COOKIE SQUA | Yes | ||||
2/1/13 | 7 | 20 | 2 | ACME COOKIES CHOC CHIP | Yes | ||||
2/1/13 | 7 | 10 | 1 | ACME DIGESTIVE RICH TEA BISCUITS | Yes | ||||
2/1/13 | 7 | 120 | 24 | ACME ASSORTED COOKIES DRP | Yes | ||||
2/1/13 | 7 | 120 | 12 | ACME KITCHEN BAG | Yes | ||||
2/1/13 | 7 | 90 | 9 | ACME SNACK BAGS RESEALABLE | Yes | ||||
2/1/13 | 7 | 10 | 1 | ACME FUDGE MINT COOKIES SQUARES | Yes | ||||
2/1/13 | 7 | 9.5 | 19 | ACME CHEDDARY SN CRACKERS/PROCES | Yes | ||||
2/1/13 | 7 | 10 | 1 | ACME COOKIES MAPLE CREAM | Yes | ||||
2/1/13 | 7 | 40 | 8 | ACME COOKIE MAPLE LEAF CREME | Yes |