Skip to main content

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

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:

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