{"id":76291,"date":"2020-04-01T13:42:28","date_gmt":"2020-04-01T11:42:28","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/r-paket-tidyr-i-ego-novye-funkczii-pivot_longer-i-pivot_wider"},"modified":"2020-04-01T13:42:28","modified_gmt":"2020-04-01T11:42:28","slug":"r-paket-tidyr-i-ego-novye-funkczii-pivot_longer-i-pivot_wider","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/r-paket-tidyr-i-ego-novye-funkczii-pivot_longer-i-pivot_wider","title":{"rendered":"R package tidyr and its new pivot_longer and pivot_wider functions","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>The package <strong>tidyr<\/strong> is part of one of the most popular libraries in R language - <strong>tidyverse<\/strong>.<br \/>\nThe main purpose of the package is to tidy data.<\/p>\n<p><\/p>\n<p>There is already <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/248741\/\">publication<\/a><\/noindex> dedicated to this package, but it dates back to 2015. I want to talk about the most relevant changes that its author Hadley Wickham announced just a few days ago.<\/p>\n<p>\n<img decoding=\"async\" alt=\"R package tidyr and its new pivot_longer and pivot_wider functions\" src=\"\/wp-content\/uploads\/2020\/04\/a290263b63aaea3db70e83a1ec411415.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<blockquote><p><b>SJK<\/b>: Will the functions gather() and spread() be deprecated?<\/p>\n<p><b>Hadley Wickham<\/b>: To some extent. We will stop recommending the use of these functions and fixing bugs in them, but they will still remain in the package in their current state.<\/p><\/blockquote>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h2 id=\"soderzhanie\">Content<\/h2>\n<p><\/p>\n<p><em>If you're interested in data analysis, you might find my <noindex><a rel=\"nofollow\" href=\"https:\/\/t.me\/R4marketing\">telegram<\/a><\/noindex> and <noindex><a rel=\"nofollow\" href=\"https:\/\/www.youtube.com\/R4marketing\/?sub_confirmation=1\">youtube<\/a><\/noindex> channels interesting. Most of the content is dedicated to the R language.<\/em><\/p>\n<p><\/p>\n<ul>\n<li><noindex><a rel=\"nofollow\" href=\"#koncepciya-tidydata\">The TidyData Concept<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#osnovnye-funkcii-vhodyaschie-v-paket-tydir\">The main functions included in the tidyr package<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#novaya-koncepciya-preobrazovaniya-dannyh-iz-shirokogo-formata-v-dlinnyy-i-naoborot\">A new concept for transforming data from wide format to long and vice versa<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"http:\/\/ustnovka-naibolee-aktualnoy-versii-tidyr-0839000\">Installing the latest version of tidyr 0.8.3.9000<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#perehod-na-novye-funkcii\">Transition to new functions<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#prostoy-primer-preobrazovaniya-dannyh-iz-shirokogo-formata-v-dlinnyy\">A simple example of transforming data from wide format to long<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#specifikacii\">Specifications<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#specifikaciya-s-ispolzovaniem-neskolkih-znacheniyvalue\">Specification using multiple values (.value)<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#preobrazovanie-data-freymov-iz-dlinnogo-formata-k-shirokomu\">Transforming data frames from long format to wide<\/a><\/noindex>\n<ul>\n<li><noindex><a rel=\"nofollow\" href=\"#prosteyshiy-primer-privedeniya-tablicy-k-shirokomu-formatu\">A basic example of tidying a table to a wide format<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#generaciya-imeni-stolbca-iz-neskolkih-ishodnyh-peremennyh\">Generating a column name from several source variables<\/a><\/noindex><\/li>\n<\/ul>\n<\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#neskolko-prodvinutyh-primerov-raboty-s-novoy-koncepciey-tidyr\">Several advanced examples of working with the new tidyr concept<\/a><\/noindex>\n<ul>\n<li><noindex><a rel=\"nofollow\" href=\"#privedenie-dannyh-k-akkuratnomu-vidu-na-primere-nabora-dannyh-o-perepisi-dohoda-i-arendnoy-platy-v-ssha\">Tidying data using a dataset on income and rent census in the USA<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#vsemirnyy-bank\">World Bank<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#spisok-kontaktov\">Contact list<\/a><\/noindex><\/li>\n<\/ul>\n<\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#zaklyuchenie\">Conclusion<\/a><\/noindex><\/li>\n<\/ul>\n<p><\/p>\n<h2 id=\"koncepciya-tidydata\">The TidyData Concept<\/h2>\n<p><\/p>\n<p>The goal <strong>tidyr<\/strong> \u2014 to help you tidy your data. Tidy data means data where: <\/p>\n<p><\/p>\n<ul>\n<li>Every variable is in a column. <\/li>\n<li>Every observation is a row. <\/li>\n<li>Every value is a cell.<\/li>\n<\/ul>\n<p><\/p>\n<p>Data that is tidied to tidy data is significantly easier and more convenient to work with during analysis.<\/p>\n<p><\/p>\n<h2 id=\"osnovnye-funkcii-vhodyaschie-v-paket-tidyr\">The main functions included in the tidyr package<\/h2>\n<p><\/p>\n<p>tidyr contains a set of functions designed for table transformations:<\/p>\n<p><\/p>\n<ul>\n<li><code>fill()<\/code> \u2014 fills in missing values in a column with the previous values;<\/li>\n<li><code>separate()<\/code> \u2014 splits one field into several using a separator;<\/li>\n<li><code>unite()<\/code> \u2014 performs the operation of combining several fields into one, the reverse action of the function <code>separate()<\/code>;<\/li>\n<li><code>pivot_longer()<\/code> \u2014 a function that transforms data from wide format to long;<\/li>\n<li><code>pivot_wider()<\/code> \u2014 a function that transforms data from long format to wide. The reverse operation of what is performed by the function <code>pivot_longer()<\/code>.<\/li>\n<li><code>gather()<\/code><strong>deprecated<\/strong> \u2014 a function that transforms data from wide format to long;<\/li>\n<li><code>spread()<\/code><strong>deprecated<\/strong> \u2014 a function that transforms data from long format to wide. The reverse operation of what is performed by the function <code>gather()<\/code>.<\/li>\n<\/ul>\n<p><\/p>\n<h2 id=\"novaya-koncepciya-preobrazovaniya-dannyh-iz-shirokogo-formata-v-dlinnyy-i-naoborot\">A new concept for transforming data from wide format to long and vice versa<\/h2>\n<p><\/p>\n<p>Previously, functions were used for such transformations <code>gather()<\/code> and <code>spread()<\/code>Over the years these functions have existed, it has become clear that for most users, including the package author, the names of these functions and their arguments were not very obvious, causing difficulties in finding and understanding which of these functions converts a data frame from wide to long format, and vice versa.<\/p>\n<p><\/p>\n<p>In this regard, <strong>tidyr<\/strong> two new, important functions have been added, designed for transforming data frames.<\/p>\n<p><\/p>\n<p>New features <code>pivot_longer()<\/code> and <code>pivot_wider()<\/code> were created under the influence of some functions from the package <strong>cdata<\/strong>, created by John Mount and Nina Zumel.<\/p>\n<p><\/p>\n<h3 id=\"ustanovka-naibolee-aktualnoy-versii-tidyr-0839000\">Installing the latest version of tidyr 0.8.3.9000<\/h3>\n<p><\/p>\n<p>To install the new, most up-to-date version of the package, <strong>tidyr<\/strong> <em>0.8.3.9000<\/em>, which includes the new functions, use the following code.<\/p>\n<p><\/p>\n<p><code>devtools::install_github(\"tidyverse\/tidyr\")<\/code><\/p>\n<p><\/p>\n<p>At the time of writing this article, these functions are only available in the dev version of the package on GitHub.<\/p>\n<p><\/p>\n<h3 id=\"perehod-na-novye-funkcii\">Transition to new functions<\/h3>\n<p><\/p>\n<p>In fact, translating old scripts to work with new functions is not difficult; for better understanding, I will take an example from the documentation of the old functions and show how these same operations are performed using the new <code>pivot_*()<\/code> functions.<\/p>\n<p><\/p>\n<p>Converting wide format to long.<\/p>\n<p>\n<b class=\"spoiler_title\">Example code from the gather function documentation<\/b><\/p>\n<pre><code class=\"plaintext\"># example\nlibrary(dplyr)\nstocks &lt;- data.frame(\n  time = as.Date('2009-01-01') + 0:9,\n  X = rnorm(10, 0, 1),\n  Y = rnorm(10, 0, 2),\n  Z = rnorm(10, 0, 4)\n)\n\n# old\nstocks_gather &lt;- stocks %&gt;% gather(key   = stock, \n                                   value = price, \n                                   -time)\n\n# new\nstocks_long   &lt;- stocks %&gt;% pivot_longer(cols      = -time, \n                                       names_to  = &quot;stock&quot;, \n                                       values_to = &quot;price&quot;)\n<\/code><\/pre>\n<p><\/p>\n<p>Converting long format to wide.<\/p>\n<p>\n<b class=\"spoiler_title\">Example code from the spread function documentation<\/b><\/p>\n<pre><code class=\"plaintext\"># old\nstocks_spread &lt;- stocks_gather %&gt;% spread(key = stock, \n                                          value = price) \n\n# new \nstock_wide    &lt;- stocks_long %&gt;% pivot_wider(names_from  = &quot;stock&quot;,\n                                            values_from = &quot;price&quot;)\n<\/code><\/pre>\n<p><\/p>\n<p>Since the examples above work with <code>pivot_longer()<\/code> and <code>pivot_wider()<\/code>, in the original table <em>stocks<\/em> there are no columns listed in the arguments <em>names_to<\/em> and <em>values_to<\/em> their names must be specified in quotes.<\/p>\n<p><\/p>\n<p>A table that will help you easily understand how to transition to working with the new concept <strong>tidyr<\/strong>.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"R package tidyr and its new pivot_longer and pivot_wider functions\" src=\"\/wp-content\/uploads\/2020\/04\/fe598b5cd152088a2f1b23b846d212e9.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<h2 id=\"primechanie-ot-avtora\">Note from the Author<\/h2>\n<p><\/p>\n<blockquote><p>Everything presented in the following text is adaptive; I would even say a free translation of the <noindex><a rel=\"nofollow\" href=\"https:\/\/tidyr.tidyverse.org\/dev\/articles\/pivot.html\">vignettes<\/a><\/noindex> from the official tidyverse library website.<\/p><\/blockquote>\n<p><\/p>\n<h2 id=\"prostoy-primer-preobrazovaniya-dannyh-iz-shirokogo-formata-v-dlinnyy\">A simple example of transforming data from wide format to long<\/h2>\n<p><\/p>\n<p><code>pivot_longer ()<\/code> \u2014 makes data sets longer by reducing the number of columns and increasing the number of rows.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"R package tidyr and its new pivot_longer and pivot_wider functions\" src=\"\/wp-content\/uploads\/2020\/04\/fc88b99f2fecdcdaa6179d2e7d29e59d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>To run the examples presented in the article, you need to first load the necessary packages:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">library(tidyr)\nlibrary(dplyr)\nlibrary(readr)<\/code><\/pre>\n<p><\/p>\n<p>Suppose we have a table with survey results, which (among other things) asked people about their religion and annual income:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 18 x 11\n#&gt;    religion `&lt;$10k` `$10-20k` `$20-30k` `$30-40k` `$40-50k` `$50-75k`\n#&gt;    &lt;chr&gt;      &lt;dbl&gt;     &lt;dbl&gt;     &lt;dbl&gt;     &lt;dbl&gt;     &lt;dbl&gt;     &lt;dbl&gt;\n#&gt;  1 Agnostic      27        34        60        81        76       137\n#&gt;  2 Atheist       12        27        37        52        35        70\n#&gt;  3 Buddhist      27        21        30        34        33        58\n#&gt;  4 Catholic     418       617       732       670       638      1116\n#&gt;  5 Don\u2019t k\u2026      15        14        15        11        10        35\n#&gt;  6 Evangel\u2026     575       869      1064       982       881      1486\n#&gt;  7 Hindu          1         9         7         9        11        34\n#&gt;  8 Histori\u2026     228       244       236       238       197       223\n#&gt;  9 Jehovah\u2026      20        27        24        24        21        30\n#&gt; 10 Jewish        19        19        25        25        30        95\n#&gt; # \u2026 with 8 more rows, and 4 more variables: `$75-100k` &lt;dbl&gt;,\n#&gt; #   `$100-150k` &lt;dbl&gt;, `&gt;150k` &lt;dbl&gt;, `Don't know\/refused` &lt;dbl&gt;<\/code><\/pre>\n<p><\/p>\n<p>This table contains data on the respondents' religion in rows, while income levels are spread across the column names. The number of respondents from each category is stored in the cell values at the intersection of religion and income level. To tidy the table into a neat, correct format, simply use <code>pivot_longer()<\/code>:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">pew %&gt;% \n  pivot_longer(cols = -religion, names_to = \"income\", values_to = \"count\")<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">pew %&gt;% \n  pivot_longer(cols = -religion, names_to = \"income\", values_to = \"count\")\n#&gt; # A tibble: 180 x 3\n#&gt;    religion income             count\n#&gt;                      \n#&gt;  1 Agnostic   2 Agnostic $10-20k               34\n#&gt;  3 Agnostic $20-30k               60\n#&gt;  4 Agnostic $30-40k               81\n#&gt;  5 Agnostic $40-50k               76\n#&gt;  6 Agnostic $50-75k              137\n#&gt;  7 Agnostic $75-100k             122\n#&gt;  8 Agnostic $100-150k            109\n#&gt;  9 Agnostic &gt;150k                 84\n#&gt; 10 Agnostic Don't know\/refused    96\n#&gt; # \u2026 with 170 more rows<\/code><\/pre>\n<p><\/p>\n<p>Function arguments <code>pivot_longer()<\/code><\/p>\n<p><\/p>\n<ul>\n<li>The first argument <em>cols<\/em>, describes which columns need to be combined. In this case, all columns except <em>time<\/em>.<\/li>\n<li>Argument <em>names_to<\/em> gives the name of the variable that will be created from the names of the columns we combined.<\/li>\n<li><em>values_to<\/em> gives the name of the variable that will be created from the data stored in the cell values of the combined columns.<\/li>\n<\/ul>\n<p><\/p>\n<h2 id=\"specifikacii\">Specifications<\/h2>\n<p><\/p>\n<p>This is a new feature of the package <strong>tidyr<\/strong>, which was previously unavailable when working with deprecated functions.<\/p>\n<p><\/p>\n<p>The specification is a data frame where each row corresponds to one column in the new output data frame, along with two special columns that start with: <\/p>\n<p><\/p>\n<ul>\n<li><em>.name<\/em> contains the original column name. <\/li>\n<li><em>.value<\/em> contains the name of the column that will hold the cell values. <\/li>\n<\/ul>\n<p><\/p>\n<p>The remaining specification columns reflect how the names of the columns being compressed will appear in the new column from <em>.name<\/em>.<\/p>\n<p><\/p>\n<p>The specification describes metadata stored in the column name, with one row for each column and one column for each variable combined with the column name. This definition might seem confusing right now, but after looking at a few examples, it will become much clearer.<\/p>\n<p><\/p>\n<p>The essence of the specification is that you can extract, modify, and assign new metadata to the transformed data frame.<\/p>\n<p><\/p>\n<p>To work with specifications when transforming a table from wide format to long format, the function used is <code>pivot_longer_spec()<\/code>.<\/p>\n<p><\/p>\n<p>How this function works is that it takes any data frame and forms its metadata in the way described above. <\/p>\n<p><\/p>\n<p>For example, let's take the who dataset that comes with the package. <strong>tidyr<\/strong>This dataset contains information provided by the World Health Organization about tuberculosis incidence.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">who\n#&gt; # A tibble: 7,240 x 60\n#&gt;    country iso2  iso3   year new_sp_m014 new_sp_m1524 new_sp_m2534\n#&gt;        &amp; &amp;                       \n#&gt;  1 Afghan\u2026 AF    AFG    1980          NA           NA           NA\n#&gt;  2 Afghan\u2026 AF    AFG    1981          NA           NA           NA\n#&gt;  3 Afghan\u2026 AF    AFG    1982          NA           NA           NA\n#&gt;  4 Afghan\u2026 AF    AFG    1983          NA           NA           NA\n#&gt;  5 Afghan\u2026 AF    AFG    1984          NA           NA           NA\n#&gt;  6 Afghan\u2026 AF    AFG    1985          NA           NA           NA\n#&gt;  7 Afghan\u2026 AF    AFG    1986          NA           NA           NA\n#&gt;  8 Afghan\u2026 AF    AFG    1987          NA           NA           NA\n#&gt;  9 Afghan\u2026 AF    AFG    1988          NA           NA           NA\n#&gt; 10 Afghan\u2026 AF    AFG    1989          NA           NA           NA\n#&gt; # \u2026 with 7,230 more rows, and 53 more variables<\/code><\/pre>\n<p><\/p>\n<p>We will build its specification.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">spec %\n  pivot_longer_spec(new_sp_m014:newrel_f65, values_to = \"count\")<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 56 x 3\n#&gt;    .name        .value name        \n#&gt;    &lt;chr&gt;        &lt;chr&gt;  &lt;chr&gt;       \n#&gt;  1 new_sp_m014  count  new_sp_m014 \n#&gt;  2 new_sp_m1524 count  new_sp_m1524\n#&gt;  3 new_sp_m2534 count  new_sp_m2534\n#&gt;  4 new_sp_m3544 count  new_sp_m3544\n#&gt;  5 new_sp_m4554 count  new_sp_m4554\n#&gt;  6 new_sp_m5564 count  new_sp_m5564\n#&gt;  7 new_sp_m65   count  new_sp_m65  \n#&gt;  8 new_sp_f014  count  new_sp_f014 \n#&gt;  9 new_sp_f1524 count  new_sp_f1524\n#&gt; 10 new_sp_f2534 count  new_sp_f2534\n#&gt; # \u2026 with 46 more rows<\/code><\/pre>\n<p><\/p>\n<p>Fields <em>country<\/em>, <em>iso2<\/em>, <em>iso3<\/em> are already variables. Our task is to pivot the columns with <em>new_sp_m014<\/em> by <em>newrel_f65<\/em>.<\/p>\n<p><\/p>\n<p>The names of these columns contain the following information:<\/p>\n<p><\/p>\n<ul>\n<li>The prefix <code>new_<\/code> indicates that the column contains data on new tuberculosis cases; the current data frame contains information only on new cases, so this prefix has no semantic meaning in the current context.<\/li>\n<li><code>sp<\/code>\/<code>rel<\/code>\/<code>sp<\/code>\/<code>ep<\/code> describes the method of disease diagnosis.<\/li>\n<li><code>m<\/code>\/<code>f<\/code> the patient's gender.<\/li>\n<li><code>014<\/code>\/<code>1524<\/code>\/<code>2535<\/code>\/<code>3544<\/code>\/<code>4554<\/code>\/<code>65<\/code> the patient's age range.<\/li>\n<\/ul>\n<p><\/p>\n<p>We can separate these columns using the function <code>extract()<\/code>, using a regular expression.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">spec %\n        extract(name, c(\"diagnosis\", \"gender\", \"age\"), \"new_?(.*)_(.)(.*)\")<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 56 x 5\n#&gt;    .name        .value diagnosis gender age  \n#&gt;    &lt;chr&gt;        &lt;chr&gt;  &lt;chr&gt;     &lt;chr&gt;  &lt;chr&gt;\n#&gt;  1 new_sp_m014  count  sp        m      014  \n#&gt;  2 new_sp_m1524 count  sp        m      1524 \n#&gt;  3 new_sp_m2534 count  sp        m      2534 \n#&gt;  4 new_sp_m3544 count  sp        m      3544 \n#&gt;  5 new_sp_m4554 count  sp        m      4554 \n#&gt;  6 new_sp_m5564 count  sp        m      5564 \n#&gt;  7 new_sp_m65   count  sp        m      65   \n#&gt;  8 new_sp_f014  count  sp        f      014  \n#&gt;  9 new_sp_f1524 count  sp        f      1524 \n#&gt; 10 new_sp_f2534 count  sp        f      2534 \n#&gt; # \u2026 with 46 more rows<\/code><\/pre>\n<p><\/p>\n<p>Note, the column <em>.name<\/em> should remain unchanged, as it is our index in the names of the original dataset's columns. <\/p>\n<p><\/p>\n<p>Gender and age (the columns <em>gender<\/em> and <em>age<\/em>) have fixed and known values, so it is advisable to convert these columns to factors:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">spec %\n            mutate(\n              gender = factor(gender, levels = c(\"f\", \"m\")),\n              age = factor(age, levels = unique(age), ordered = TRUE)\n            ) <\/code><\/pre>\n<p><\/p>\n<p>Finally, to apply the specification we created to the original data frame <em>who<\/em> we need to use the argument <em>spec<\/em> in the function <code>pivot_longer()<\/code>.<\/p>\n<p><\/p>\n<p><code>who %&gt;% pivot_longer(spec = spec)<\/code><\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 405,440 x 8\n#&gt;    country     iso2  iso3   year diagnosis gender age   count\n#&gt;    &lt;chr&gt;       &lt;chr&gt; &lt;chr&gt; &lt;int&gt; &lt;chr&gt;     &lt;fct&gt;  &lt;ord&gt; &lt;int&gt;\n#&gt;  1 Afghanistan AF    AFG    1980 sp        m      014      NA\n#&gt;  2 Afghanistan AF    AFG    1980 sp        m      1524     NA\n#&gt;  3 Afghanistan AF    AFG    1980 sp        m      2534     NA\n#&gt;  4 Afghanistan AF    AFG    1980 sp        m      3544     NA\n#&gt;  5 Afghanistan AF    AFG    1980 sp        m      4554     NA\n#&gt;  6 Afghanistan AF    AFG    1980 sp        m      5564     NA\n#&gt;  7 Afghanistan AF    AFG    1980 sp        m      65       NA\n#&gt;  8 Afghanistan AF    AFG    1980 sp        f      014      NA\n#&gt;  9 Afghanistan AF    AFG    1980 sp        f      1524     NA\n#&gt; 10 Afghanistan AF    AFG    1980 sp        f      2534     NA\n#&gt; # \u2026 with 405,430 more rows<\/code><\/pre>\n<p><\/p>\n<p>Everything we've just done can be illustrated as follows:<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"R package tidyr and its new pivot_longer and pivot_wider functions\" src=\"\/wp-content\/uploads\/2020\/04\/77c8c266c205e1b087738d31de8b8523.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<h2 id=\"specifikaciya-s-ispolzovaniem-neskolkih-znacheniyvalue\">Specification using multiple values (.value)<\/h2>\n<p><\/p>\n<p>In the example above, the specification column <em>.value<\/em> contained only one value, which is usually the case. <\/p>\n<p><\/p>\n<p>But occasionally a situation may arise where you need to combine data from columns of different data types. With the outdated function, <code>spread()<\/code> it would be quite difficult to do this.<\/p>\n<p><\/p>\n<p>The example given below is taken from <noindex><a rel=\"nofollow\" href=\"https:\/\/cran.r-project.org\/web\/packages\/data.table\/vignettes\/datatable-reshape.html\">vignettes<\/a><\/noindex> the package <strong>data.table<\/strong>.<\/p>\n<p><\/p>\n<p>Let's create a practice dataframe.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">family &lt;- tibble::tribble(\n  ~family,  ~dob_child1,  ~dob_child2, ~gender_child1, ~gender_child2,\n       1L, &quot;1998-11-26&quot;, &quot;2000-01-29&quot;,             1L,             2L,\n       2L, &quot;1996-06-22&quot;,           NA,             2L,             NA,\n       3L, &quot;2002-07-11&quot;, &quot;2004-04-05&quot;,             2L,             2L,\n       4L, &quot;2004-10-10&quot;, &quot;2009-08-27&quot;,             1L,             1L,\n       5L, &quot;2000-12-05&quot;, &quot;2005-02-28&quot;,             2L,             1L,\n)\nfamily % mutate_at(vars(starts_with(\"dob\")), parse_date)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 5 x 5\n#&gt;   family dob_child1 dob_child2 gender_child1 gender_child2\n#&gt;    &lt;int&gt; &lt;date&gt;     &lt;date&gt;             &lt;int&gt;         &lt;int&gt;\n#&gt; 1      1 1998-11-26 2000-01-29             1             2\n#&gt; 2      2 1996-06-22 NA                     2            NA\n#&gt; 3      3 2002-07-11 2004-04-05             2             2\n#&gt; 4      4 2004-10-10 2009-08-27             1             1\n#&gt; 5      5 2000-12-05 2005-02-28             2             1<\/code><\/pre>\n<p><\/p>\n<p>The created dataframe contains data about the children of one family in each row. Families may have one or two children. For each child, information about their date of birth and gender is provided, with the data for each child in separate columns. Our task is to bring this data to a format suitable for analysis.<\/p>\n<p><\/p>\n<p>Note that we have two variables with information about each child: their gender and date of birth (the columns with the prefix <em>dob<\/em> contain the date of birth, while the columns with the prefix <em>gender<\/em> contain the child's gender). In the expected result, they should be in separate columns. We can achieve this by generating a specification in which the column <code>.value<\/code> will have two different values.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">spec %\n  pivot_longer_spec(-family) %&gt;%\n  separate(col = name, into = c(\".value\", \"child\"))%&gt;%\n  mutate(child = parse_number(child))\n<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 4 x 3\n#&gt;   .name         .value child\n#&gt;   &lt;chr&gt;         &lt;chr&gt;  &lt;dbl&gt;\n#&gt; 1 dob_child1    dob        1\n#&gt; 2 dob_child2    dob        2\n#&gt; 3 gender_child1 gender     1\n#&gt; 4 gender_child2 gender     2<\/code><\/pre>\n<p><\/p>\n<p>So, let's break down the steps that the above code executes.<\/p>\n<p><\/p>\n<ul>\n<li><code>pivot_longer_spec(-family)<\/code> \u2014 we create a specification that compresses all existing columns except the family column.<\/li>\n<li><code>separate(col = name, into = c(\".value\", \"child\"))<\/code> \u2014 we split the column <em>.name<\/em>, which contains the names of the original fields, by underscore and place the resulting values into the columns <em>.value<\/em> and <em>child<\/em>.<\/li>\n<li><code>mutate(child = parse_number(child))<\/code> \u2014 we convert the field values <em>child<\/em> from text to numeric data type.<\/li>\n<\/ul>\n<p><\/p>\n<p>Now we can apply the obtained specification to the original dataframe and bring the table to the desired form.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">family %&gt;% \n    pivot_longer(spec = spec, na.rm = T)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 9 x 4\n#&gt;   family child dob        gender\n#&gt;    &lt;int&gt; &lt;dbl&gt; &lt;date&gt;      &lt;int&gt;\n#&gt; 1      1     1 1998-11-26      1\n#&gt; 2      1     2 2000-01-29      2\n#&gt; 3      2     1 1996-06-22      2\n#&gt; 4      3     1 2002-07-11      2\n#&gt; 5      3     2 2004-04-05      2\n#&gt; 6      4     1 2004-10-10      1\n#&gt; 7      4     2 2009-08-27      1\n#&gt; 8      5     1 2000-12-05      2\n#&gt; 9      5     2 2005-02-28      1<\/code><\/pre>\n<p><\/p>\n<p>We use the argument <code>na.rm = TRUE<\/code>, because the current form of the data forces us to create extra rows for non-existent observations. Since family 2 has only one child, <code>na.rm = TRUE<\/code> ensures that family 2 will have one row in the output.<\/p>\n<p><\/p>\n<h2 id=\"preobrazovanie-data-freymov-iz-dlinnogo-formata-k-shirokomu\">Transforming data frames from long format to wide<\/h2>\n<p><\/p>\n<p><code>pivot_wider()<\/code> is the inverse transformation, and conversely increases the number of columns in the data frame by reducing the number of rows.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"R package tidyr and its new pivot_longer and pivot_wider functions\" src=\"\/wp-content\/uploads\/2020\/04\/b470664e06f362d6f0ea6df9c1ed426d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Such transformations are rarely used to clean data, yet this technique can be useful for creating summary tables for presentations or for integration with other tools.<\/p>\n<p><\/p>\n<p>In fact, the functions <code>pivot_longer()<\/code> and <code>pivot_wider()<\/code> are symmetrical and perform inverse actions on each other, i.e.: <code>df %&gt;% pivot_longer(spec = spec) %&gt;% pivot_wider(spec = spec)<\/code> and <code>df %&gt;% pivot_wider(spec = spec) %&gt;% pivot_longer(spec = spec)<\/code> will return the original df.<\/p>\n<p><\/p>\n<h3 id=\"prosteyshiy-primer-privedeniya-tablicy-k-shirokomu-formatu\">A basic example of tidying a table to a wide format<\/h3>\n<p><\/p>\n<p>To demonstrate how the function works, <code>pivot_wider()<\/code> we will use the dataset <em>fish_encounters<\/em>, which contains information on how various stations track the movement of fish in the river.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 114 x 3\n#&gt;    fish  station  seen\n#&gt;    &lt;fct&gt; &lt;fct&gt;   &lt;int&gt;\n#&gt;  1 4842  Release     1\n#&gt;  2 4842  I80_1       1\n#&gt;  3 4842  Lisbon      1\n#&gt;  4 4842  Rstr        1\n#&gt;  5 4842  Base_TD     1\n#&gt;  6 4842  BCE         1\n#&gt;  7 4842  BCW         1\n#&gt;  8 4842  BCE2        1\n#&gt;  9 4842  BCW2        1\n#&gt; 10 4842  MAE         1\n#&gt; # \u2026 with 104 more rows<\/code><\/pre>\n<p><\/p>\n<p>In most cases, this table will be more informative and convenient to use if the information for each station is presented in a separate column.<\/p>\n<p><\/p>\n<p><code>fish_encounters %&gt;% pivot_wider(names_from = station, values_from = seen)<\/code><\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">fish_encounters %&gt;% pivot_wider(names_from = station, values_from = seen)\n#&gt; # A tibble: 19 x 12\n#&gt;    fish  Release I80_1 Lisbon  Rstr Base_TD   BCE   BCW  BCE2  BCW2   MAE\n#&gt;        &amp;   &amp;    &amp; &amp; &amp; &amp; &amp;\n#&gt;  1 4842        1     1      1     1       1     1     1     1     1     1\n#&gt;  2 4843        1     1      1     1       1     1     1     1     1     1\n#&gt;  3 4844        1     1      1     1       1     1     1     1     1     1\n#&gt;  4 4845        1     1      1     1       1    NA    NA    NA    NA    NA\n#&gt;  5 4847        1     1      1    NA      NA    NA    NA    NA    NA    NA\n#&gt;  6 4848        1     1      1     1      NA    NA    NA    NA    NA    NA\n#&gt;  7 4849        1     1     NA    NA      NA    NA    NA    NA    NA    NA\n#&gt;  8 4850        1     1     NA     1       1     1     1    NA    NA    NA\n#&gt;  9 4851        1     1     NA    NA      NA    NA    NA    NA    NA    NA\n#&gt; 10 4854        1     1     NA    NA      NA    NA    NA    NA    NA    NA\n#&gt; # \u2026 with 9 more rows, and 1 more variable: MAW<\/code><\/pre>\n<p><\/p>\n<p>This dataset records information only in cases where a fish was detected by the station, meaning that if any fish were not recorded by some station, that data will not be in the table. This implies that the output will be filled with NA. <\/p>\n<p><\/p>\n<p>However, in this case, we know that the absence of a record means that the fish was not observed, so we can use the argument <em>values_fill<\/em> in the function <code>pivot_wider()<\/code> and fill these missing values with zeros:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">fish_encounters %&gt;% pivot_wider(\n  names_from = station, \n  values_from = seen,\n  values_fill = list(seen = 0)\n)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 19 x 12\n#&gt;    fish  Release I80_1 Lisbon  Rstr Base_TD   BCE   BCW  BCE2  BCW2   MAE\n#&gt;    &lt;fct&gt;   &lt;int&gt; &lt;int&gt;  &lt;int&gt; &lt;int&gt;   &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt;\n#&gt;  1 4842        1     1      1     1       1     1     1     1     1     1\n#&gt;  2 4843        1     1      1     1       1     1     1     1     1     1\n#&gt;  3 4844        1     1      1     1       1     1     1     1     1     1\n#&gt;  4 4845        1     1      1     1       1     0     0     0     0     0\n#&gt;  5 4847        1     1      1     0       0     0     0     0     0     0\n#&gt;  6 4848        1     1      1     1       0     0     0     0     0     0\n#&gt;  7 4849        1     1      0     0       0     0     0     0     0     0\n#&gt;  8 4850        1     1      0     1       1     1     1     0     0     0\n#&gt;  9 4851        1     1      0     0       0     0     0     0     0     0\n#&gt; 10 4854        1     1      0     0       0     0     0     0     0     0\n#&gt; # \u2026 with 9 more rows, and 1 more variable: MAW &lt;int&gt;<\/code><\/pre>\n<p><\/p>\n<h3 id=\"generaciya-imeni-stolbca-iz-neskolkih-ishodnyh-peremennyh\">Generating a column name from several source variables<\/h3>\n<p><\/p>\n<p>Imagine that we have a table containing combinations of product, country, and year. You can generate a test data frame using the following code:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">df %\n  filter((product == \"A\" &amp; country == \"AI\") | product == \"B\") %&gt;% \n  mutate(value = rnorm(nrow(.)))<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 45 x 4\n#&gt;    product country  year    value\n#&gt;    &lt;chr&gt;   &lt;chr&gt;   &lt;int&gt;    &lt;dbl&gt;\n#&gt;  1 A       AI       2000 -2.05   \n#&gt;  2 A       AI       2001 -0.676  \n#&gt;  3 A       AI       2002  1.60   \n#&gt;  4 A       AI       2003 -0.353  \n#&gt;  5 A       AI       2004 -0.00530\n#&gt;  6 A       AI       2005  0.442  \n#&gt;  7 A       AI       2006 -0.610  \n#&gt;  8 A       AI       2007 -2.77   \n#&gt;  9 A       AI       2008  0.899  \n#&gt; 10 A       AI       2009 -0.106  \n#&gt; # \u2026 with 35 more rows<\/code><\/pre>\n<p><\/p>\n<p>Our task is to expand the data frame so that one column contains data for each combination of product and country. To do this, it's sufficient to pass to the argument <em>names_from<\/em> a vector containing the names of the fields to be combined.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">df %&gt;% pivot_wider(names_from = c(product, country),\n                 values_from = \"value\")<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 15 x 4\n#&gt;     year     A_AI    B_AI    B_EI\n#&gt;    &lt;int&gt;    &lt;dbl&gt;   &lt;dbl&gt;   &lt;dbl&gt;\n#&gt;  1  2000 -2.05     0.607   1.20  \n#&gt;  2  2001 -0.676    1.65   -0.114 \n#&gt;  3  2002  1.60    -0.0245  0.501 \n#&gt;  4  2003 -0.353    1.30   -0.459 \n#&gt;  5  2004 -0.00530  0.921  -0.0589\n#&gt;  6  2005  0.442   -1.55    0.594 \n#&gt;  7  2006 -0.610    0.380  -1.28  \n#&gt;  8  2007 -2.77     0.830   0.637 \n#&gt;  9  2008  0.899    0.0175 -1.30  \n#&gt; 10  2009 -0.106   -0.195   1.03  \n#&gt; # \u2026 with 5 more rows<\/code><\/pre>\n<p><\/p>\n<p>You can also apply specifications to the function <code>pivot_wider()<\/code>. But when passed to <code>pivot_wider()<\/code> the specification performs the opposite transformation <code>pivot_longer()<\/code>: it creates columns as specified in <em>.name<\/em>, using values from <em>.value<\/em> and other columns.<\/p>\n<p><\/p>\n<p>For this dataset, you can generate a custom specification if you want every possible combination of country and product to have its own column, not just those present in the data:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">spec % \n  expand(product, country, .value = \"value\") %&gt;% \n  unite(\".name\", product, country, remove = FALSE)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 4 x 4\n#&gt;   .name product country .value\n#&gt;   &lt;chr&gt; &lt;chr&gt;   &lt;chr&gt;   &lt;chr&gt; \n#&gt; 1 A_AI  A       AI      value \n#&gt; 2 A_EI  A       EI      value \n#&gt; 3 B_AI  B       AI      value \n#&gt; 4 B_EI  B       EI      value<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">df %&gt;% pivot_wider(spec = spec) %&gt;% head()<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 6 x 5\n#&gt;    year     A_AI  A_EI    B_AI    B_EI\n#&gt;   &lt;int&gt;    &lt;dbl&gt; &lt;dbl&gt;   &lt;dbl&gt;   &lt;dbl&gt;\n#&gt; 1  2000 -2.05       NA  0.607   1.20  \n#&gt; 2  2001 -0.676      NA  1.65   -0.114 \n#&gt; 3  2002  1.60       NA -0.0245  0.501 \n#&gt; 4  2003 -0.353      NA  1.30   -0.459 \n#&gt; 5  2004 -0.00530    NA  0.921  -0.0589\n#&gt; 6  2005  0.442      NA -1.55    0.594<\/code><\/pre>\n<p><\/p>\n<h2 id=\"neskolko-prodvinutyh-primerov-raboty-s-novoy-koncepciey-tidyr\">Several advanced examples of working with the new tidyr concept<\/h2>\n<p><\/p>\n<h3 id=\"privedenie-dannyh-k-akkuratnomu-vidu-na-primere-nabora-dannyh-o-perepisi-dohoda-i-arendnoy-platy-v-ssha\">Tidying data using a dataset on income and rent census in the USA<\/h3>\n<p><\/p>\n<p>Dataset <em>us_rent_income<\/em> contains information about the average income and rent for each state in the USA for 2017 (the dataset is available in the package <strong>tidycensus<\/strong>).<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">us_rent_income\n#&gt; # A tibble: 104 x 5\n#&gt;    GEOID NAME       variable estimate   moe\n#&gt;                   \n#&gt;  1 01    Alabama    income      24476   136\n#&gt;  2 01    Alabama    rent          747     3\n#&gt;  3 02    Alaska     income      32940   508\n#&gt;  4 02    Alaska     rent         1200    13\n#&gt;  5 04    Arizona    income      27517   148\n#&gt;  6 04    Arizona    rent          972     4\n#&gt;  7 05    Arkansas   income      23789   165\n#&gt;  8 05    Arkansas   rent          709     5\n#&gt;  9 06    California income      29454   109\n#&gt; 10 06    California rent         1358     3\n#&gt; # \u2026 with 94 more rows<\/code><\/pre>\n<p><\/p>\n<p>In the form in which the data is stored in the dataset, <em>us_rent_income<\/em> working with it is extremely inconvenient, so we would like to create a dataset with the columns: <em>rent<\/em>, <em>rent_moe<\/em>, <em>come<\/em>, <em>income_moe<\/em>. There are many ways to create this specification, but the main point is that we need to generate each combination of variable values and <em>estimate\/moe<\/em>, and then generate the column name.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">  spec % \n    expand(variable, .value = c(\"estimate\", \"moe\")) %&gt;% \n    mutate(\n      .name = paste0(variable, ifelse(.value == \"moe\", \"_moe\", \"\"))\n    )<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 4 x 3\n#&gt;   variable .value   .name     \n#&gt;   &lt;chr&gt;    &lt;chr&gt;    &lt;chr&gt;     \n#&gt; 1 income   estimate income    \n#&gt; 2 income   moe      income_moe\n#&gt; 3 rent     estimate rent      \n#&gt; 4 rent     moe      rent_moe<\/code><\/pre>\n<p><\/p>\n<p>Providing this specification <code>pivot_wider()<\/code> gives us the result we are looking for:<\/p>\n<p><\/p>\n<p><code>us_rent_income %&gt;% pivot_wider(spec = spec)<\/code><\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 52 x 6\n#&gt;    GEOID NAME                 income income_moe  rent rent_moe\n#&gt;    &lt;chr&gt; &lt;chr&gt;                 &lt;dbl&gt;      &lt;dbl&gt; &lt;dbl&gt;    &lt;dbl&gt;\n#&gt;  1 01    Alabama               24476        136   747        3\n#&gt;  2 02    Alaska                32940        508  1200       13\n#&gt;  3 04    Arizona               27517        148   972        4\n#&gt;  4 05    Arkansas              23789        165   709        5\n#&gt;  5 06    California            29454        109  1358        3\n#&gt;  6 08    Colorado              32401        109  1125        5\n#&gt;  7 09    Connecticut           35326        195  1123        5\n#&gt;  8 10    Delaware              31560        247  1076       10\n#&gt;  9 11    District of Columbia  43198        681  1424       17\n#&gt; 10 12    Florida               25952         70  1077        3\n#&gt; # \u2026 with 42 more rows<\/code><\/pre>\n<p><\/p>\n<h3 id=\"vsemirnyy-bank\">World Bank<\/h3>\n<p><\/p>\n<p>Sometimes transforming a dataset into the required format involves several steps.<br \/>\nDataset <em>world_bank_pop<\/em> contains data from the World Bank on the population of each country from 2000 to 2018.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 1,056 x 20\n#&gt;    country indicator `2000` `2001` `2002` `2003`  `2004`  `2005`   `2006`\n#&gt;    &lt;chr&gt;   &lt;chr&gt;      &lt;dbl&gt;  &lt;dbl&gt;  &lt;dbl&gt;  &lt;dbl&gt;   &lt;dbl&gt;   &lt;dbl&gt;    &lt;dbl&gt;\n#&gt;  1 ABW     SP.URB.T\u2026 4.24e4 4.30e4 4.37e4 4.42e4 4.47e+4 4.49e+4  4.49e+4\n#&gt;  2 ABW     SP.URB.G\u2026 1.18e0 1.41e0 1.43e0 1.31e0 9.51e-1 4.91e-1 -1.78e-2\n#&gt;  3 ABW     SP.POP.T\u2026 9.09e4 9.29e4 9.50e4 9.70e4 9.87e+4 1.00e+5  1.01e+5\n#&gt;  4 ABW     SP.POP.G\u2026 2.06e0 2.23e0 2.23e0 2.11e0 1.76e+0 1.30e+0  7.98e-1\n#&gt;  5 AFG     SP.URB.T\u2026 4.44e6 4.65e6 4.89e6 5.16e6 5.43e+6 5.69e+6  5.93e+6\n#&gt;  6 AFG     SP.URB.G\u2026 3.91e0 4.66e0 5.13e0 5.23e0 5.12e+0 4.77e+0  4.12e+0\n#&gt;  7 AFG     SP.POP.T\u2026 2.01e7 2.10e7 2.20e7 2.31e7 2.41e+7 2.51e+7  2.59e+7\n#&gt;  8 AFG     SP.POP.G\u2026 3.49e0 4.25e0 4.72e0 4.82e0 4.47e+0 3.87e+0  3.23e+0\n#&gt;  9 AGO     SP.URB.T\u2026 8.23e6 8.71e6 9.22e6 9.77e6 1.03e+7 1.09e+7  1.15e+7\n#&gt; 10 AGO     SP.URB.G\u2026 5.44e0 5.59e0 5.70e0 5.76e0 5.75e+0 5.69e+0  4.92e+0\n#&gt; # \u2026 with 1,046 more rows, and 11 more variables: `2007` &lt;dbl&gt;,\n#&gt; #   `2008` &lt;dbl&gt;, `2009` &lt;dbl&gt;, `2010` &lt;dbl&gt;, `2011` &lt;dbl&gt;, `2012` &lt;dbl&gt;,\n#&gt; #   `2013` &lt;dbl&gt;, `2014` &lt;dbl&gt;, `2015` &lt;dbl&gt;, `2016` &lt;dbl&gt;, `2017` &lt;dbl&gt;<\/code><\/pre>\n<p><\/p>\n<p>Our goal is to create a tidy dataset where each variable is in its own column. It's currently unclear what specific steps are necessary, but we will start with the most obvious issue: the year is spread across multiple columns.<\/p>\n<p><\/p>\n<p>To fix this, we need to use the function <code>pivot_longer()<\/code>.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">pop2 % \n  pivot_longer(`2000`:`2017`, names_to = \"year\")<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 19,008 x 4\n#&gt;    country indicator   year  value\n#&gt;    &lt;chr&gt;   &lt;chr&gt;       &lt;chr&gt; &lt;dbl&gt;\n#&gt;  1 ABW     SP.URB.TOTL 2000  42444\n#&gt;  2 ABW     SP.URB.TOTL 2001  43048\n#&gt;  3 ABW     SP.URB.TOTL 2002  43670\n#&gt;  4 ABW     SP.URB.TOTL 2003  44246\n#&gt;  5 ABW     SP.URB.TOTL 2004  44669\n#&gt;  6 ABW     SP.URB.TOTL 2005  44889\n#&gt;  7 ABW     SP.URB.TOTL 2006  44881\n#&gt;  8 ABW     SP.URB.TOTL 2007  44686\n#&gt;  9 ABW     SP.URB.TOTL 2008  44375\n#&gt; 10 ABW     SP.URB.TOTL 2009  44052\n#&gt; # \u2026 with 18,998 more rows<\/code><\/pre>\n<p><\/p>\n<p>The next step is to consider the variable indicator.<br \/>\n<code>pop2 %&gt;% count(indicator)<\/code><\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 4 x 2\n#&gt;   indicator       n\n#&gt;   &lt;chr&gt;       &lt;int&gt;\n#&gt; 1 SP.POP.GROW  4752\n#&gt; 2 SP.POP.TOTL  4752\n#&gt; 3 SP.URB.GROW  4752\n#&gt; 4 SP.URB.TOTL  4752<\/code><\/pre>\n<p><\/p>\n<p>Where SP.POP.GROW is population growth, SP.POP.TOTL is total population, and SP.URB.* is the same, but only for urban areas. Let's separate these values into two variables: area (total or urban) and a variable containing the actual data (population or growth):<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">pop3 % \n  separate(indicator, c(NA, \"area\", \"variable\"))<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 19,008 x 5\n#&gt;    country area  variable year  value\n#&gt;    &lt;chr&gt;   &lt;chr&gt; &lt;chr&gt;    &lt;chr&gt; &lt;dbl&gt;\n#&gt;  1 ABW     URB   TOTL     2000  42444\n#&gt;  2 ABW     URB   TOTL     2001  43048\n#&gt;  3 ABW     URB   TOTL     2002  43670\n#&gt;  4 ABW     URB   TOTL     2003  44246\n#&gt;  5 ABW     URB   TOTL     2004  44669\n#&gt;  6 ABW     URB   TOTL     2005  44889\n#&gt;  7 ABW     URB   TOTL     2006  44881\n#&gt;  8 ABW     URB   TOTL     2007  44686\n#&gt;  9 ABW     URB   TOTL     2008  44375\n#&gt; 10 ABW     URB   TOTL     2009  44052\n#&gt; # \u2026 with 18,998 more rows<\/code><\/pre>\n<p><\/p>\n<p>Now we just need to split the variable into two columns:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">pop3 %&gt;% \n  pivot_wider(names_from = variable, values_from = value)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 9,504 x 5\n#&gt;    country area  year   TOTL    GROW\n#&gt;    &lt;chr&gt;   &lt;chr&gt; &lt;chr&gt; &lt;dbl&gt;   &lt;dbl&gt;\n#&gt;  1 ABW     URB   2000  42444  1.18  \n#&gt;  2 ABW     URB   2001  43048  1.41  \n#&gt;  3 ABW     URB   2002  43670  1.43  \n#&gt;  4 ABW     URB   2003  44246  1.31  \n#&gt;  5 ABW     URB   2004  44669  0.951 \n#&gt;  6 ABW     URB   2005  44889  0.491 \n#&gt;  7 ABW     URB   2006  44881 -0.0178\n#&gt;  8 ABW     URB   2007  44686 -0.435 \n#&gt;  9 ABW     URB   2008  44375 -0.698 \n#&gt; 10 ABW     URB   2009  44052 -0.731 \n#&gt; # \u2026 with 9,494 more rows<\/code><\/pre>\n<p><\/p>\n<h3 id=\"spisok-kontaktov\">Contact list<\/h3>\n<p><\/p>\n<p>In the last example, imagine you have a list of contacts that you copied and pasted from a website:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">contacts &lt;- tribble(\n  ~field, ~value,\n  &quot;name&quot;, &quot;Jiena McLellan&quot;,\n  &quot;company&quot;, &quot;Toyota&quot;, \n  &quot;name&quot;, &quot;John Smith&quot;, \n  &quot;company&quot;, &quot;google&quot;, \n  &quot;email&quot;, &quot;john@google.com&quot;,\n  &quot;name&quot;, &quot;Huxley Ratcliffe&quot;\n)<\/code><\/pre>\n<p><\/p>\n<p>Transforming this list into a table format is quite challenging because there is no variable that identifies which data belongs to which contact. We can fix this by noting that the data for each new contact begins with the name (\"name\"), so we can create a unique identifier and increment it by one each time the value \"name\" appears in the field column:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">contacts % \n  mutate(\n    person_id = cumsum(field == \"name\")\n  )\ncontacts<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 6 x 3\n#&gt;   field   value            person_id\n#&gt;   &lt;chr&gt;   &lt;chr&gt;                &lt;int&gt;\n#&gt; 1 name    Jiena McLellan           1\n#&gt; 2 company Toyota                   1\n#&gt; 3 name    John Smith               2\n#&gt; 4 company google                   2\n#&gt; 5 email   john@google.com          2\n#&gt; 6 name    Huxley Ratcliffe         3<\/code><\/pre>\n<p><\/p>\n<p>Now that we have a unique identifier for each contact, we can pivot the field and value into columns:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">contacts %&gt;% \n  pivot_wider(names_from = field, values_from = value)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">#&gt; # A tibble: 3 x 4\n#&gt;   person_id name             company email          \n#&gt;       &lt;int&gt; &lt;chr&gt;            &lt;chr&gt;   &lt;chr&gt;          \n#&gt; 1         1 Jiena McLellan   Toyota  &lt;NA&gt;           \n#&gt; 2         2 John Smith       google  john@google.com\n#&gt; 3         3 Huxley Ratcliffe &lt;NA&gt;    &lt;NA&gt;<\/code><\/pre>\n<p><\/p>\n<h2 id=\"zaklyuchenie\">Conclusion<\/h2>\n<p><\/p>\n<p>In my personal opinion, the new concept <strong>tidyr<\/strong> is indeed intuitively clearer and significantly outperforms outdated functions. <code>spread()<\/code> and <code>gather()<\/code>. I hope this article helped you understand <code>pivot_longer()<\/code> and <code>pivot_wider()<\/code>.<\/p>\n<p>Source: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/444622\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041f\u0430\u043a\u0435\u0442 tidyr \u0432\u0445\u043e\u0434\u0438\u0442 \u0432 \u044f\u0434\u0440\u043e \u043e\u0434\u043d\u043e\u0439 \u0438\u0437 \u043d\u0430\u0438\u0431\u043e\u043b\u0435\u0435 \u043f\u043e\u043f\u0443\u043b\u044f\u0440\u043d\u044b\u0445 \u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a \u043d\u0430 \u044f\u0437\u044b\u043a\u0435 R \u2014 tidyverse. \u041e\u0441\u043d\u043e\u0432\u043d\u043e\u0435 \u043d\u0430\u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u043f\u0430\u043a\u0435\u0442\u0430 \u2014 \u043f\u0440\u0438\u0432\u0435\u0434\u0435\u043d\u0438\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 \u043a \u0430\u043a\u043a\u0443\u0440\u0430\u0442\u043d\u043e\u043c\u0443 \u0432\u0438\u0434\u0443. \u041d\u0430 \u0425\u0430\u0431\u0440\u0435 \u0443\u0436\u0435 \u0435\u0441\u0442\u044c \u043f\u0443\u0431\u043b\u0438\u043a\u0430\u0446\u0438\u044f \u043f\u043e\u0441\u0432\u044f\u0449\u0451\u043d\u043d\u0430\u044f \u0434\u0430\u043d\u043d\u043e\u043c\u0443 \u043f\u0430\u043a\u0435\u0442\u0443, \u043d\u043e \u0434\u0430\u0442\u0438\u0440\u0443\u044e\u0435\u0442\u0441\u044f \u043e\u043d\u0430 2015 \u0433\u043e\u0434\u043e\u043c. \u0410 \u044f \u0445\u043e\u0447\u0443 \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u0430\u0442\u044c, \u043e \u043d\u0430\u0438\u0431\u043e\u043b\u0435\u0435 \u0430\u043a\u0442\u0443\u0430\u043b\u044c\u043d\u044b\u0445 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f\u0445, \u043e \u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0434\u043d\u0435\u0439 \u043d\u0430\u0437\u0430\u0434 \u0441\u043e\u043e\u0431\u0449\u0438\u043b \u0435\u0433\u043e \u0430\u0432\u0442\u043e\u0440 \u0425\u0435\u0434\u043b\u0438 \u0412\u0438\u043a\u0445\u0435\u043c. [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":76292,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-76291","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041f\u0430\u043a\u0435\u0442 tidyr \u0432\u0445\u043e\u0434\u0438\u0442 \u0432 \u044f\u0434\u0440\u043e \u043e\u0434\u043d\u043e\u0439 \u0438\u0437 \u043d\u0430\u0438\u0431\u043e\u043b\u0435\u0435 \u043f\u043e\u043f\u0443\u043b\u044f\u0440\u043d\u044b\u0445 \u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a \u043d\u0430 \u044f\u0437\u044b\u043a\u0435 R \u2014 tidyverse. \u041e\u0441\u043d\u043e\u0432\u043d\u043e\u0435 \u043d\u0430\u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u043f\u0430\u043a\u0435\u0442\u0430 \u2014 \u043f\u0440\u0438\u0432\u0435\u0434\u0435\u043d\u0438\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 \u043a \u0430\u043a\u043a\u0443\u0440\u0430\u0442\u043d\u043e\u043c\u0443 \u0432\u0438\u0434\u0443. \u041d\u0430 \u0425\u0430\u0431\u0440\u0435.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/r-paket-tidyr-i-ego-novye-funkczii-pivot_longer-i-pivot_wider\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2\" \/>\n\t\t<meta property=\"og:locale\" content=\"en_US\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47R \u043f\u0430\u043a\u0435\u0442 tidyr \u0438 \u0435\u0433\u043e \u043d\u043e\u0432\u044b\u0435 \u0444\u0443\u043d\u043a\u0446\u0438\u0438 pivot_longer \u0438 pivot_wider | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041f\u0430\u043a\u0435\u0442 tidyr \u0432\u0445\u043e\u0434\u0438\u0442 \u0432 \u044f\u0434\u0440\u043e \u043e\u0434\u043d\u043e\u0439 \u0438\u0437 \u043d\u0430\u0438\u0431\u043e\u043b\u0435\u0435 \u043f\u043e\u043f\u0443\u043b\u044f\u0440\u043d\u044b\u0445 \u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a \u043d\u0430 \u044f\u0437\u044b\u043a\u0435 R \u2014 tidyverse. \u041e\u0441\u043d\u043e\u0432\u043d\u043e\u0435 \u043d\u0430\u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u043f\u0430\u043a\u0435\u0442\u0430 \u2014 \u043f\u0440\u0438\u0432\u0435\u0434\u0435\u043d\u0438\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 \u043a \u0430\u043a\u043a\u0443\u0440\u0430\u0442\u043d\u043e\u043c\u0443 \u0432\u0438\u0434\u0443. \u041d\u0430 \u0425\u0430\u0431\u0440\u0435.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/r-paket-tidyr-i-ego-novye-funkczii-pivot_longer-i-pivot_wider\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-04-01T11:42:28+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-04-01T11:42:28+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47The R package tidyr and its new features pivot_longer and pivot_wider | ProHoster","description":"The tidyr package is part of one of the most popular libraries in R \u2014 tidyverse. The main purpose of the package is to tidy data. On Habr.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/r-paket-tidyr-i-ego-novye-funkczii-pivot_longer-i-pivot_wider","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47R \u043f\u0430\u043a\u0435\u0442 tidyr \u0438 \u0435\u0433\u043e \u043d\u043e\u0432\u044b\u0435 \u0444\u0443\u043d\u043a\u0446\u0438\u0438 pivot_longer \u0438 pivot_wider | ProHoster","og:description":"\u041f\u0430\u043a\u0435\u0442 tidyr \u0432\u0445\u043e\u0434\u0438\u0442 \u0432 \u044f\u0434\u0440\u043e \u043e\u0434\u043d\u043e\u0439 \u0438\u0437 \u043d\u0430\u0438\u0431\u043e\u043b\u0435\u0435 \u043f\u043e\u043f\u0443\u043b\u044f\u0440\u043d\u044b\u0445 \u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a \u043d\u0430 \u044f\u0437\u044b\u043a\u0435 R \u2014 tidyverse. \u041e\u0441\u043d\u043e\u0432\u043d\u043e\u0435 \u043d\u0430\u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u043f\u0430\u043a\u0435\u0442\u0430 \u2014 \u043f\u0440\u0438\u0432\u0435\u0434\u0435\u043d\u0438\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 \u043a \u0430\u043a\u043a\u0443\u0440\u0430\u0442\u043d\u043e\u043c\u0443 \u0432\u0438\u0434\u0443. \u041d\u0430 \u0425\u0430\u0431\u0440\u0435.","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/r-paket-tidyr-i-ego-novye-funkczii-pivot_longer-i-pivot_wider","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-04-01T11:42:28+00:00","article:modified_time":"2020-04-01T11:42:28+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"76291","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 17:39:23","updated":"2022-09-28 03:21:22","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/76291","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=76291"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/76291\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/76292"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=76291"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=76291"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=76291"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}