PivotFromColumns

✖
`PivotFromColumns`

New in 14.2[Experimental]

✖

PivotFromColumns[tab,cols{vars, vals}]

pivots to make a longer table with the columns cols replaced by two columns, vars containing the column names and vals containing the values from cols.

✖

PivotFromColumns[tab,{prule₁,prule₂,…}]

pivots on multiple sets of columns specified by the pivot rules prule_i.

Details

PivotFromColumns is also known as gather and pivot longer.
PivotFromColumns is typically used as a structural data-cleaning operation, where some columns are not variables but values of variables. PivotFromColumns pivots the role of variable and value by gathering the values from several columns, making the resulting tabular object longer.
PivotToColumns is effectively the inverse of PivotFromColumns.

Examples

open allclose all

Basic Examples (1)Summary of the most common use cases

Pivot the keys of several columns into a column called "day" and their values into a column called "temp":

In[1]:=1

✖

https://wolfram.com/xid/0cf2c7rbtteublk-e14cod

Out[1]=1

In[2]:=2

✖

https://wolfram.com/xid/0cf2c7rbtteublk-lh8ctc

Out[2]=2

PivotToColumns can then be used to separate the columns for high and low temperatures:

In[3]:=3

✖

https://wolfram.com/xid/0cf2c7rbtteublk-77ynol

Out[3]=3

Scope (2)Survey of the scope of standard use cases

Take a Tabular object of populations of the G7 countries:

In[1]:=1

✖

https://wolfram.com/xid/0cf2c7rbtteublk-h935jg

Pivot the country column names and population values into new columns "Country" and "Population":

In[2]:=2

✖

https://wolfram.com/xid/0cf2c7rbtteublk-8ic9vn

Out[2]=2

Take tabular data of weekend temperature and sun information:

In[1]:=1

✖

https://wolfram.com/xid/0cf2c7rbtteublk-i3i7rr

Pivot the "high" and "low" columns of temperature values:

In[2]:=2

✖

https://wolfram.com/xid/0cf2c7rbtteublk-od3u0i

Out[2]=2

Pivot "sunrise" with "high" and "sunset" with "low":

In[3]:=3

✖

https://wolfram.com/xid/0cf2c7rbtteublk-h51dn7

Out[3]=3

Alternatively, pivot "sunrise" with "low" and "sunset" with "high":

In[4]:=4

✖

https://wolfram.com/xid/0cf2c7rbtteublk-zvv1tw

Out[4]=4

When the sets of different value columns have different lengths, both names and values are filled in with Missing:

In[5]:=5

✖

https://wolfram.com/xid/0cf2c7rbtteublk-e5s0dz

Out[5]=5

Applications (2)Sample problems that can be solved with this function

Create a Tabular object with measurements of soil pH at three depth levels of 0, 30 and 80 meters:

In[1]:=1

✖

https://wolfram.com/xid/0cf2c7rbtteublk-srpc9p

Out[1]=1

Pivot the depth values to create "depth" and "pH" columns and then sort by depth:

In[2]:=2

✖

https://wolfram.com/xid/0cf2c7rbtteublk-usi1rm

Out[2]=2

Find mean pH for each depth:

In[3]:=3

✖

https://wolfram.com/xid/0cf2c7rbtteublk-m4fdfw

Out[3]=3

Take rectangular data organized by years and months:

In[1]:=1

✖

https://wolfram.com/xid/0cf2c7rbtteublk-bfsxuv

In[2]:=2

✖

https://wolfram.com/xid/0cf2c7rbtteublk-k2879o

Out[2]=2

Pivot the month columns into a "month" column and "data" column:

In[3]:=3

✖

https://wolfram.com/xid/0cf2c7rbtteublk-ykyqet

Out[3]=3

It is now easy to compute yearly means:

In[4]:=4

✖

https://wolfram.com/xid/0cf2c7rbtteublk-0qow46

Out[4]=4

Properties & Relations (3)Properties of the function, and connections to other functions

Take tabular data of weekend temperature and sun information:

In[1]:=1

✖

https://wolfram.com/xid/0cf2c7rbtteublk-s0a05b

Pivot two sets of columns at the same time:

In[2]:=2

✖

https://wolfram.com/xid/0cf2c7rbtteublk-hfn551

Out[2]=2

Changing the order of the rules will change the order of columns:

In[3]:=3

✖

https://wolfram.com/xid/0cf2c7rbtteublk-uhm91z

Out[3]=3

In[4]:=4

✖

https://wolfram.com/xid/0cf2c7rbtteublk-gsb2dr

Out[4]=4

Take tabular data of weekend temperature and sun information:

In[1]:=1

✖

https://wolfram.com/xid/0cf2c7rbtteublk-bhxjek

Simultaneous pivoting is not equivalent to using the same rules in consecutive separate steps:

In[2]:=2

✖

https://wolfram.com/xid/0cf2c7rbtteublk-0iksxh

Out[2]=2

In[3]:=3

✖

https://wolfram.com/xid/0cf2c7rbtteublk-t8nbuo

Out[3]=3

Use PivotFromColumns on a Tabular object:

In[1]:=1

✖

https://wolfram.com/xid/0cf2c7rbtteublk-ddg72y

Out[1]=1

In[2]:=2

✖

https://wolfram.com/xid/0cf2c7rbtteublk-w8o166

Out[2]=2

PivotToColumns is effectively the inverse of PivotFromColumns:

In[3]:=3

✖

https://wolfram.com/xid/0cf2c7rbtteublk-xfvgpp

Out[3]=3

Remove the common part in ExtendedKey to recover the original object:

In[4]:=4

✖

https://wolfram.com/xid/0cf2c7rbtteublk-227l39

Out[4]=4

In[5]:=5

✖

https://wolfram.com/xid/0cf2c7rbtteublk-e9ndrv

Out[5]=5

Top