It is important to know how to reshape a dataframe for data analysis. Personally, I also value being able to perform the most important reshaping tasks (and other common tasks) with base R, without relying on external packages, according to a ‘learn Base-R first, and switch to the tidyverse next’ approach, which represents my current teaching ‘phylosophy’.

I am well aware that ‘dplyr’ and ‘tidyr’ are two excellent packages and that my teaching approach is open to debate (see this thread); I also realize this stance might make me look like a bit of a ‘dinosaur’…

Regardless, I decided to write this post about how we handle common data manipulation tasks by using base R, namely:

subsetting
sorting
casting
melting

We will use datasets from ‘staforbiology’, the accompanying package for this blog. Please ensure you have it installed if you intend to follow along with the code.

Subsetting the data

Subsetting means selecting the records (rows) or the variables (columns) that satisfy certain criteria. In base R, we can use the subset() function.

Let’s consider the students dataset, which is available in the statforbiology package. It represents a collection of exams taken by students at my university in different subjects. Let’s load it using the getAgroData() function from the statforbiology package.

library(statforbiology)
students <- getAgroData("students")
head(students)
##   Id  Subject       Date Mark Year  HighSchool
## 1  1 AGRONOMY 10/06/2002   30 2001  HUMANITIES
## 2  2 AGRONOMY 08/07/2002   24 2001 AGRICULTURE
## 3  3 AGRONOMY 24/06/2002   30 2001 AGRICULTURE
## 4  4 AGRONOMY 24/06/2002   26 2001  HUMANITIES
## 5  5 AGRONOMY 23/01/2003   30 2001  HUMANITIES
## 6  6 AGRONOMY 09/09/2002   28 2001 AGRICULTURE

Let’s say that we want a new dataset that contains only the records where the mark was equal to or above 28 (please note that, in Italy, an exam is passed with a minimum mark of 18, while the maximum mark is 30).

subData <- subset(students, Mark >= 28)
head(subData)
##    Id  Subject       Date Mark Year  HighSchool
## 1   1 AGRONOMY 10/06/2002   30 2001  HUMANITIES
## 3   3 AGRONOMY 24/06/2002   30 2001 AGRICULTURE
## 5   5 AGRONOMY 23/01/2003   30 2001  HUMANITIES
## 6   6 AGRONOMY 09/09/2002   28 2001 AGRICULTURE
## 11 11 AGRONOMY 09/09/2002   28 2001  SCIENTIFIC
## 17 17 AGRONOMY 10/06/2002   30 2001  HUMANITIES

Let’s make it more difficult and extract the records where the mark ranges from 26 to 28 (margins included). Look at the AND clause, which is expressed by using the & operator:

subData <- subset(students, Mark <= 28 & Mark >= 26)
head(subData)
##    Id  Subject       Date Mark Year  HighSchool
## 4   4 AGRONOMY 24/06/2002   26 2001  HUMANITIES
## 6   6 AGRONOMY 09/09/2002   28 2001 AGRICULTURE
## 7   7 AGRONOMY 24/02/2003   26 2001  HUMANITIES
## 8   8 AGRONOMY 09/09/2002   26 2001  SCIENTIFIC
## 10 10 AGRONOMY 08/07/2002   27 2001  HUMANITIES
## 11 11 AGRONOMY 09/09/2002   28 2001  SCIENTIFIC

Now we are interested in those students who got a mark ranging from 26 to 28 in MATHS (please note the equality operator, written as ==):

subData <- subset(students, Mark <= 28 & Mark >= 26 & 
                    Subject == "MATHS")
head(subData)
##      Id Subject       Date Mark Year  HighSchool
## 115 115   MATHS 15/07/2002   26 2001 AGRICULTURE
## 124 124   MATHS 16/09/2002   26 2001  SCIENTIFIC
## 138 138   MATHS 04/02/2002   27 2001  HUMANITIES
## 144 144   MATHS 10/02/2003   27 2001  HUMANITIES
## 145 145   MATHS 04/07/2003   27 2002  HUMANITIES
## 146 146   MATHS 28/02/2002   28 2001 AGRICULTURE

Let’s look for good students who got a mark ranging from 26 to 28 either in MATHS or in CHEMISTRY (OR clause; note the | operator):

subData <- subset(students, Mark <= 28 & Mark >= 26 & 
                    (Subject == "MATHS" | 
                     Subject == "CHEMISTRY"))
head(subData)
##    Id   Subject       Date Mark Year   HighSchool
## 68 68 CHEMISTRY 04/06/2002   28 2001 OTHER SCHOOL
## 70 70 CHEMISTRY 04/06/2002   26 2001   ACCOUNTING
## 71 71 CHEMISTRY 04/06/2002   27 2001  AGRICULTURE
## 72 72 CHEMISTRY 23/01/2003   27 2001   SCIENTIFIC
## 75 75 CHEMISTRY 10/07/2002   27 2001  AGRICULTURE
## 81 81 CHEMISTRY 23/01/2003   28 2001  AGRICULTURE

We can also select columns; for example, we may want to display only the Subject, Mark, and HighSchool columns:

subData <- subset(students, Mark <= 28 & Mark >= 26 & 
                    (Subject == "MATHS" | 
                     Subject == "CHEMISTRY"),
                  select = c(Subject, Mark, HighSchool))
head(subData)
##      Subject Mark   HighSchool
## 68 CHEMISTRY   28 OTHER SCHOOL
## 70 CHEMISTRY   26   ACCOUNTING
## 71 CHEMISTRY   27  AGRICULTURE
## 72 CHEMISTRY   27   SCIENTIFIC
## 75 CHEMISTRY   27  AGRICULTURE
## 81 CHEMISTRY   28  AGRICULTURE

We can also drop unwanted columns:

subData <- subset(students, Mark <= 28 & Mark >= 26 & 
                    (Subject == "MATHS" | 
                     Subject == "CHEMISTRY"),
                  select = c(-Id, 
                             -Date,
                             -Year))
head(subData)
##      Subject Mark   HighSchool
## 68 CHEMISTRY   28 OTHER SCHOOL
## 70 CHEMISTRY   26   ACCOUNTING
## 71 CHEMISTRY   27  AGRICULTURE
## 72 CHEMISTRY   27   SCIENTIFIC
## 75 CHEMISTRY   27  AGRICULTURE
## 81 CHEMISTRY   28  AGRICULTURE

The subset() function is very easy to use. However, we might have greater flexibility by using indices for subsetting. We already know that the notation dataframe[i, j] returns the element in the i-th row and j-th column of a data frame. We can, of course, replace i and j with some subsetting rules. For example, selecting the exams where the mark is between 25 and 29 is done as follows:

subData <- students[(students$Mark <= 29 & students$Mark >= 25),]
head(subData)
##    Id  Subject       Date Mark Year  HighSchool
## 4   4 AGRONOMY 24/06/2002   26 2001  HUMANITIES
## 6   6 AGRONOMY 09/09/2002   28 2001 AGRICULTURE
## 7   7 AGRONOMY 24/02/2003   26 2001  HUMANITIES
## 8   8 AGRONOMY 09/09/2002   26 2001  SCIENTIFIC
## 10 10 AGRONOMY 08/07/2002   27 2001  HUMANITIES
## 11 11 AGRONOMY 09/09/2002   28 2001  SCIENTIFIC

This is useful for quickly editing the data. For example, if we want to replace all marks from 25 to 29 with NAs (missing values), we can simply do:

subData <- students
subData[(subData$Mark <= 29 & subData$Mark >= 25), "Mark"] <- NA
head(subData)
##   Id  Subject       Date Mark Year  HighSchool
## 1  1 AGRONOMY 10/06/2002   30 2001  HUMANITIES
## 2  2 AGRONOMY 08/07/2002   24 2001 AGRICULTURE
## 3  3 AGRONOMY 24/06/2002   30 2001 AGRICULTURE
## 4  4 AGRONOMY 24/06/2002   NA 2001  HUMANITIES
## 5  5 AGRONOMY 23/01/2003   30 2001  HUMANITIES
## 6  6 AGRONOMY 09/09/2002   NA 2001 AGRICULTURE

Please note that I created a new dataset to make the replacement, so as not to modify the original dataset. Of course, I can use the is.na() function to find missing values and edit them.

subData[is.na(subData$Mark), "Mark"] <- 0 
head(subData)
##   Id  Subject       Date Mark Year  HighSchool
## 1  1 AGRONOMY 10/06/2002   30 2001  HUMANITIES
## 2  2 AGRONOMY 08/07/2002   24 2001 AGRICULTURE
## 3  3 AGRONOMY 24/06/2002   30 2001 AGRICULTURE
## 4  4 AGRONOMY 24/06/2002    0 2001  HUMANITIES
## 5  5 AGRONOMY 23/01/2003   30 2001  HUMANITIES
## 6  6 AGRONOMY 09/09/2002    0 2001 AGRICULTURE

Sorting the data

Sorting is very similar to subsetting by indexing. We simply need to use the order() function. For example, let’s sort the students dataset by mark:

sortedData <- students[order(students$Mark), ]
head(sortedData)
##    Id   Subject       Date Mark Year   HighSchool
## 51 51   BIOLOGY 01/03/2002   18 2001   HUMANITIES
## 67 67 CHEMISTRY 20/02/2003   18 2002  AGRICULTURE
## 76 76 CHEMISTRY 24/02/2003   18 2002 OTHER SCHOOL
## 79 79 CHEMISTRY 18/06/2003   18 2002  AGRICULTURE
## 82 82 CHEMISTRY 18/07/2002   18 2001  AGRICULTURE
## 83 83 CHEMISTRY 23/01/2003   18 2001   SCIENTIFIC

We can also sort in decreasing order:

sortedData <- students[order(-students$Mark), ]
head(sortedData)
##    Id  Subject       Date Mark Year  HighSchool
## 1   1 AGRONOMY 10/06/2002   30 2001  HUMANITIES
## 3   3 AGRONOMY 24/06/2002   30 2001 AGRICULTURE
## 5   5 AGRONOMY 23/01/2003   30 2001  HUMANITIES
## 17 17 AGRONOMY 10/06/2002   30 2001  HUMANITIES
## 18 18 AGRONOMY 10/06/2002   30 2001 AGRICULTURE
## 19 19 AGRONOMY 09/09/2002   30 2001 AGRICULTURE

We can obviously use multiple keys. For example, let’s sort by mark and, within each mark, by subject:

sortedData <- students[order(-students$Mark, students$Subject), ]
head(sortedData)
##    Id  Subject       Date Mark Year  HighSchool
## 1   1 AGRONOMY 10/06/2002   30 2001  HUMANITIES
## 3   3 AGRONOMY 24/06/2002   30 2001 AGRICULTURE
## 5   5 AGRONOMY 23/01/2003   30 2001  HUMANITIES
## 17 17 AGRONOMY 10/06/2002   30 2001  HUMANITIES
## 18 18 AGRONOMY 10/06/2002   30 2001 AGRICULTURE
## 19 19 AGRONOMY 09/09/2002   30 2001 AGRICULTURE

If I want to sort a character variable (such as Subject) in decreasing order, I need to use the helper function xtfrm():

sortedData <- students[order(-students$Mark, -xtfrm(students$Subject)), ]
head(sortedData)
##      Id Subject       Date Mark Year   HighSchool
## 116 116   MATHS 01/07/2002   30 2001 OTHER SCHOOL
## 117 117   MATHS 18/06/2002   30 2001   ACCOUNTING
## 118 118   MATHS 09/07/2002   30 2001  AGRICULTURE
## 121 121   MATHS 18/06/2002   30 2001   ACCOUNTING
## 123 123   MATHS 09/07/2002   30 2001   HUMANITIES
## 130 130   MATHS 07/02/2002   30 2001   SCIENTIFIC

Casting and melting

These are two operations that we can perform on the entire dataframe, to reshape it from:

LONG to WIDE format (casting)
WIDE to LONG format (melting)

In base R, we use the same function, namely reshape(), which was originally tailored to the needs of longitudinal data management. Although the terminology comes from this original conceptual framework (which is quite confusing at the beginning), you can use this function for any type of data that needs to be reshaped from LONG to WIDE or vice versa.

Casting the data

We might be familiar with the ‘pivot table’ function in Excel, which reshapes a dataset from the LONG format to the WIDE format. For example, let’s take the rimsulfuron dataset in the statforbiology package, which contains the results of an experiment in blocks designed to compare 16 herbicides for weed control in maize. The dataset is in the LONG format, with one row for each plot and the observations made in each plot listed in different columns.

rimsulfuron <- getAgroData("rimsulfuron")
head(rimsulfuron)
##                      Herbicide Plot Code Block Column WeedCover Yield
## 1             Rimsulfuron (40)    1    1     1      1      27.8 85.91
## 2             Rimsulfuron (45)    2    2     1      1      27.8 93.03
## 3             Rimsulfuron (50)    3    3     1      1      23.0 86.93
## 4             Rimsulfuron (60)    4    4     1      1      42.8 52.99
## 5    Rimsulfuron (50+30 split)    5    5     1      1      15.1 71.36
## 6 Rimsulfuron + thyfensulfuron    6    6     1      1      22.9 75.28

Let’s put this data frame in the WIDE format, so that we have the 16 herbicides in different rows, and the observations for each herbicide are listed in different columns, with a separate column per each block (provided that we have only one observation per herbicide in each block, which is the case here). In base R, we can use the reshape() function with direction = "wide". Basically, we need to specify which variable should identify the rows (in our case, idvar = "Herbicide") and which variable identifies the different sets of observations (in our case, timevar = "Block"). The name timevar shows that this function was initially tailored to the needs of longitudinal data. Initially, we have to subset the data frame to retain only the columns we need to display in the final table, although we can also use the drop argument to exclude the variables we do not intend to use.

castData <- reshape(
  rimsulfuron[, c("Herbicide", "Block", "Yield")], 
  direction = "wide",
  idvar = "Herbicide",
  timevar = "Block"
)
castData
##                                     Herbicide Yield.1 Yield.2 Yield.3 Yield.4
## 1                            Rimsulfuron (40)   85.91   91.09  111.42   93.15
## 2                            Rimsulfuron (45)   93.03  105.00   89.19   79.04
## 3                            Rimsulfuron (50)   86.93  105.82  110.02   89.10
## 4                            Rimsulfuron (60)   52.99  102.86  100.62   97.04
## 5                   Rimsulfuron (50+30 split)   71.36   77.57  115.91   92.16
## 6                Rimsulfuron + thyfensulfuron   75.28   82.59   94.96   85.85
## 7                        Rimsulfuron + hoeing   73.22   86.06  118.01   98.32
## 8    Pendimethalin (pre) + rimsulfuron (post)   65.51   88.72   95.52   82.39
## 9  Pendimethalin (post) + rimsuulfuron (post)   94.82   87.72  102.05  101.94
## 10                        Rimsulfuron + Atred   94.11   89.86  104.34   99.63
## 11                             Thifensulfuron   78.47   42.32   62.52   24.34
## 12         Metolachlor + terbuthylazine (pre)   51.77   52.10   49.46   34.67
## 13                  Alachlor + terbuthylazine   12.06   49.58   41.34   16.37
## 14                                Hand-Weeded   77.58   92.08   86.59   99.63
## 15                                 Unweeded 1   10.88   31.77   23.92   20.85
## 16                                 Unweeded 2   27.58   51.55   25.13   38.61

Melting the data

The reshape() function can also be used to transform a dataset from WIDE to LONG format by setting the direction = "long" argument. For this task, let’s use the WeedPop dataset in the statforbiology package, which reports the results of a weed survey involving six species in nine conditions (the letters from A to I in the Code variable). The experimental unit is the condition and, for each condition, the ground cover of the six species is reported in different columns.

Now, we want to reshape this data frame so that we have one row for each combination of species and condition, with the ground cover listed in a single column. In order to use the reshape() function, we need to think of this table as if it represented longitudinal data, with repeated measurements taken at different time points on the same subject (id). Thus, the values that change with ‘time’ (the varying variables) are those contained in the original dataset in columns 2 to 7. These variables will be combined into a single column in the newly created dataset, which we will name WeedCover (v.names = "WeedCover").

Now, we have to add at least two other variables to this newly created dataset. The first one represents the subjects (idvar) and must contain the codes contained in the WeedPop$Code column (ids = WeedPop$Code). We will name this variable Code (idvar = "Code").

The second variable must contain the names of the original variables (times = colnames(WeedPop[2:7])) corresponding to the observations taken for each subject. We can assign a name to this newly created variable by using timevar = "Weed name".

WeedPop <- getAgroData("WeedPop")
WeedPop
##   Code POLLA CHEPO ECHCG AMARE XANST POLAV
## 1    A   0.1    33    11     0   0.1   0.1
## 2    B   0.1     3     3     0   0.1   0.0
## 3    C   7.0    19    19     4   7.0   1.0
## 4    D  18.0     3    28    19  12.0   6.0
## 5    E   5.0     7    28     3  10.0   1.0
## 6    F  11.0     9    33     7  10.0   6.0
## 7    G   8.0    13    33     6  15.0  15.0
## 8    H  18.0     5    33     4  19.0  12.0
## 9    I   6.0     6    38     3  10.0   6.0
mdati <- reshape(
  WeedPop,
  direction = "long",
  varying = colnames(WeedPop[2:7]),
  v.names = "WeedCover",
  times = colnames(WeedPop[2:7]),
  timevar = "Weed name",
  ids = WeedPop$Code,
  idvar = "Code",
)
head(mdati)
##         Code Weed name WeedCover
## A.POLLA    A     POLLA       0.1
## B.POLLA    B     POLLA       0.1
## C.POLLA    C     POLLA       7.0
## D.POLLA    D     POLLA      18.0
## E.POLLA    E     POLLA       5.0
## F.POLLA    F     POLLA      11.0

How about the ‘tidyverse first’ approach?

I must admit that base R functions are not always intuitive, but as mentioned, I prefer my students to gain a solid command of base R before adopting other dialects. However, many instructors prefer a “tidyverse first” teaching philosophy. In that framework, the toolset changes significantly.

For subsetting, we can use the filter() function from the ‘dplyr’ package using very similar logical conditions. Note that the ‘stats’ package (loaded by default) also contains a function named filter(), so explicitly using dplyr::filter() prevents namespace conflicts.

subData <- dplyr::filter(students, Mark >= 28)
head(subData)
##   Id  Subject       Date Mark Year  HighSchool
## 1  1 AGRONOMY 10/06/2002   30 2001  HUMANITIES
## 2  3 AGRONOMY 24/06/2002   30 2001 AGRICULTURE
## 3  5 AGRONOMY 23/01/2003   30 2001  HUMANITIES
## 4  6 AGRONOMY 09/09/2002   28 2001 AGRICULTURE
## 5 11 AGRONOMY 09/09/2002   28 2001  SCIENTIFIC
## 6 17 AGRONOMY 10/06/2002   30 2001  HUMANITIES

A key difference is that dplyr::filter() only selects rows, not columns. For column selection, ‘dplyr’ provides select(). Therefore, if we want to filter students with marks between 26 and 28 in Maths or Chemistry and keep only the columns Subject, Mark, and HighSchool, we combine both operations using the pipe operator (|>).

# subData <- subset(students, Mark <= 28 & Mark >=26 &
#                     (Subject == "MATHS" |
#                     Subject == "CHEMISTRY"),
#                  select = c(Subject, Mark, HighSchool))
subData1 <- students |>
  dplyr::filter(Mark <= 28 & Mark >=26 & 
                    (Subject == "MATHS" | 
                    Subject == "CHEMISTRY")) |>
  dplyr::select(Subject, Mark, HighSchool)
head(subData1)
##     Subject Mark   HighSchool
## 1 CHEMISTRY   28 OTHER SCHOOL
## 2 CHEMISTRY   26   ACCOUNTING
## 3 CHEMISTRY   27  AGRICULTURE
## 4 CHEMISTRY   27   SCIENTIFIC
## 5 CHEMISTRY   27  AGRICULTURE
## 6 CHEMISTRY   28  AGRICULTURE

This two-step process highlights the intuitive nature of the ‘pipe’ operator: it effectively replaces the word “then” between operations (i.e., ‘filter’ then ‘select’ translates to filter() |> select()).

To sort a dataframe, order() is replaced by dplyr::arrange():

# sortedData <- students[order(-students$Mark, students$Subject), ]
# head(sortedData)
sortedData <- dplyr::arrange(students, dplyr::desc(Mark), Subject)
head(sortedData)
##   Id  Subject       Date Mark Year  HighSchool
## 1  1 AGRONOMY 10/06/2002   30 2001  HUMANITIES
## 2  3 AGRONOMY 24/06/2002   30 2001 AGRICULTURE
## 3  5 AGRONOMY 23/01/2003   30 2001  HUMANITIES
## 4 17 AGRONOMY 10/06/2002   30 2001  HUMANITIES
## 5 18 AGRONOMY 10/06/2002   30 2001 AGRICULTURE
## 6 19 AGRONOMY 09/09/2002   30 2001 AGRICULTURE

# sortedData <- students[order(-students$Mark, -xtfrm(students$Subject)), ]
# head(sortedData)
sortedData <- dplyr::arrange(students, dplyr::desc(Mark), desc(Subject))
head(sortedData)
##    Id Subject       Date Mark Year   HighSchool
## 1 116   MATHS 01/07/2002   30 2001 OTHER SCHOOL
## 2 117   MATHS 18/06/2002   30 2001   ACCOUNTING
## 3 118   MATHS 09/07/2002   30 2001  AGRICULTURE
## 4 121   MATHS 18/06/2002   30 2001   ACCOUNTING
## 5 123   MATHS 09/07/2002   30 2001   HUMANITIES
## 6 130   MATHS 07/02/2002   30 2001   SCIENTIFIC

For sorting, there is no contest! The arrange() function, combined with desc() for descending order, is far cleaner and more readable than order() and xtfrm().

For casting and melting, ‘tidyr’ offers pivot_wider() and pivot_longer(). The former converts data from LONG to WIDE format; it is generally more flexible than reshape(), notably because preliminary column selection is unnecessary.

# castData <- reshape(
#   rimsulfuron[, c("Herbicide", "Block", "Yield")], 
#   direction = "wide",
#   idvar = "Herbicide",
#   timevar = "Block"
# )
# castData

castData <- rimsulfuron |>
  tidyr::pivot_wider(id_cols = Herbicide,
              names_from = Block, 
              values_from = Yield)
head(castData)
## # A tibble: 6 × 5
##   Herbicide                      `1`   `2`   `3`   `4`
##   <chr>                        <dbl> <dbl> <dbl> <dbl>
## 1 Rimsulfuron (40)              85.9  91.1 111.   93.2
## 2 Rimsulfuron (45)              93.0 105    89.2  79.0
## 3 Rimsulfuron (50)              86.9 106.  110.   89.1
## 4 Rimsulfuron (60)              53.0 103.  101.   97.0
## 5 Rimsulfuron (50+30 split)     71.4  77.6 116.   92.2
## 6 Rimsulfuron + thyfensulfuron  75.3  82.6  95.0  85.8

To melt data (rescaling from WIDE to LONG format), we use pivot_longer():

# mdati <- reshape(
#   WeedPop,
#   direction = "long",
#   varying = colnames(WeedPop[2:7]),
#   v.names = "WeedCover",
#   times = colnames(WeedPop[2:7]),
#   timevar = "Weed name",
#   ids = WeedPop$Code,
#   idvar = "Code",
# )
# head(mdati)
mdati <- WeedPop |>
  tidyr::pivot_longer(names_to = "Weed name", 
                      values_to = "Weed Cover",
             cols = c(2:7))
head(mdati)
## # A tibble: 6 × 3
##   Code  `Weed name` `Weed Cover`
##   <chr> <chr>              <dbl>
## 1 A     POLLA                0.1
## 2 A     CHEPO               33  
## 3 A     ECHCG               11  
## 4 A     AMARE                0  
## 5 A     XANST                0.1
## 6 A     POLAV                0.1

Have fun working with these functions! Should you have comments, please, drop me a note at the address below.

And … don’t forget to check out my new book!

Prof. Andrea Onofri
Department of Agricultural, Food and Environmental Sciences
University of Perugia (Italy)
Send comments to: andrea.onofri@unipg.it

This post was originally published on 2019-03-27