Page tree


Contents:

   

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.

DailyStore_NbrPOS_SalesPOS_QtyPRODUCT_DESC
2/8/131707ACME LAWN GARDEN BAG CLEAR
2/7/13210.629ACME COOKIES CHOC CHIP
2/7/13200ACME SANDWICH BAG
2/7/1327.086ACME SODAS SALTED
2/7/1323.922ACME SCENTED OIL REFILL-CTRY SUN
2/7/13213.447ACME LARGE FUDGE GRAHAMS COOKIES
2/7/13200ACME SUGAR ICE WAFERS VANILLA
2/7/1333.162ACME ZOO ANIMAL FRUIT SNACKS 6'S
2/7/1333.162ACME WAFERS SUGER ICE
2/7/1333.162ACME SCENTED OIL REFILL-CTRY SUN
2/7/1336.324ACME RICE CRACKERS ONION
2/2/13915030ACME FROSTED OATMEAL COOKIE SQUA
2/2/1393.52ACME FRUIT SNACK CASTLE ADVENTRS
2/2/139909ACME COOKIES CHOC CHIP
2/2/139306ACME ASSORTED COOKIES DRP
2/2/139707ACME KITCHEN BAG
2/2/13917017ACME SNACK BAGS RESEALABLE
2/2/139204ACME CHEDDARY SN CRACKERS/PROCES
2/2/1396.52ACME RICE CRACKERS TERIYAKI
2/2/1391.53ACME COOKIE MAPLE LEAF CREME
2/2/139306ACME RICE CHIPS CHEDDAR
2/1/13719038ACME FROSTED OATMEAL COOKIE SQUA
2/1/137202ACME COOKIES CHOC CHIP
2/1/137101ACME DIGESTIVE RICH TEA BISCUITS
2/1/13712024ACME ASSORTED COOKIES DRP
2/1/13712012ACME KITCHEN BAG
2/1/137909ACME SNACK BAGS RESEALABLE
2/1/137101ACME FUDGE MINT COOKIES SQUARES
2/1/1379.519ACME CHEDDARY SN CRACKERS/PROCES
2/1/137101ACME COOKIES MAPLE CREAM
2/1/137408ACME COOKIE MAPLE LEAF CREME

Transformation:

In the following, the values in the Store_Nbr column have been converted to individual columns:

Transformation Name Convert values to columns
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 250 since 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:

DailyStore_NbrPOS_SalesPOS_QtyPRODUCT_DESCcolumn_1column_2column_3column_9column_7
2/8/131707ACME LAWN GARDEN BAG CLEARYes



2/7/13210.629ACME COOKIES CHOC CHIP
Yes


2/7/13200ACME SANDWICH BAG
Yes


2/7/1327.086ACME SODAS SALTED
Yes


2/7/1323.922ACME SCENTED OIL REFILL-CTRY SUN
Yes


2/7/13213.447ACME LARGE FUDGE GRAHAMS COOKIES
Yes


2/7/13200ACME SUGAR ICE WAFERS VANILLA
Yes


2/7/1333.162ACME ZOO ANIMAL FRUIT SNACKS 6'S

Yes

2/7/1333.162ACME WAFERS SUGER ICE

Yes

2/7/1333.162ACME SCENTED OIL REFILL-CTRY SUN

Yes

2/7/1336.324ACME RICE CRACKERS ONION

Yes

2/2/13915030ACME FROSTED OATMEAL COOKIE SQUA


Yes
2/2/1393.52ACME FRUIT SNACK CASTLE ADVENTRS


Yes
2/2/139909ACME COOKIES CHOC CHIP


Yes
2/2/139306ACME ASSORTED COOKIES DRP


Yes
2/2/139707ACME KITCHEN BAG


Yes
2/2/13917017ACME SNACK BAGS RESEALABLE


Yes
2/2/139204ACME CHEDDARY SN CRACKERS/PROCES


Yes
2/2/1396.52ACME RICE CRACKERS TERIYAKI


Yes
2/2/1391.53ACME COOKIE MAPLE LEAF CREME


Yes
2/2/139306ACME RICE CHIPS CHEDDAR


Yes
2/1/13719038ACME FROSTED OATMEAL COOKIE SQUA



Yes
2/1/137202ACME COOKIES CHOC CHIP



Yes
2/1/137101ACME DIGESTIVE RICH TEA BISCUITS



Yes
2/1/13712024ACME ASSORTED COOKIES DRP



Yes
2/1/13712012ACME KITCHEN BAG



Yes
2/1/137909ACME SNACK BAGS RESEALABLE



Yes
2/1/137101ACME FUDGE MINT COOKIES SQUARES



Yes
2/1/1379.519ACME CHEDDARY SN CRACKERS/PROCES



Yes
2/1/137101ACME COOKIES MAPLE CREAM



Yes
2/1/137408ACME COOKIE MAPLE LEAF CREME



Yes

See Also for Values to Columns:

This page has no comments.