Merging Additional Columns and Rows to data.table in R (3 Examples)

This tutorial demonstrates how to combine a data.table with an additional row or column in R programming.

Preparing the Examples

Load the data.table package.

install.packages("data.table")                                                      # Install data.table package
library("data.table")                                                               # Load data.table package

For the examples, we use the iris dataset which is loaded by the following lines of code.

data(iris)                                                                          # Load iris data set
head(iris)
#   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
# 1          5.1         3.5          1.4         0.2  setosa
# 2          4.9         3.0          1.4         0.2  setosa
# 3          4.7         3.2          1.3         0.2  setosa
# 4          4.6         3.1          1.5         0.2  setosa
# 5          5.0         3.6          1.4         0.2  setosa
# 6          5.4         3.9          1.7         0.4  setosa

The next line of code converts the iris data to a data.table object.

iris_dt <- setDT(copy(iris))                                                        # Replicate iris and set it as data.table
iris_dt
#      Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
#   1:          5.1         3.5          1.4         0.2    setosa
#   2:          4.9         3.0          1.4         0.2    setosa
#   3:          4.7         3.2          1.3         0.2    setosa
#   4:          4.6         3.1          1.5         0.2    setosa
#   5:          5.0         3.6          1.4         0.2    setosa
#  ---                                                            
# 146:          6.7         3.0          5.2         2.3 virginica
# 147:          6.3         2.5          5.0         1.9 virginica
# 148:          6.5         3.0          5.2         2.0 virginica
# 149:          6.2         3.4          5.4         2.3 virginica
# 150:          5.9         3.0          5.1         1.8 virginica

Example 1: Adding a New Column

A new column can be added to a data.table object, simply by indexing the new column with square brackets and assigning values to the new column. In the example below, we set the values of the new column to 1. In contrast to other data types like a data.frame, the name of the new column does not have to be written as a string, i.e. we do not need quotation marks around New_column.

iris_dt_new_col <- data.table::copy(iris_dt)                                        # Copying iris_dt
iris_dt_new_col[, New_column := 1]
iris_dt_new_col
#      Sepal.Length Sepal.Width Petal.Length Petal.Width   Species New_column
#   1:          5.1         3.5          1.4         0.2    setosa          1
#   2:          4.9         3.0          1.4         0.2    setosa          1
#   3:          4.7         3.2          1.3         0.2    setosa          1
#   4:          4.6         3.1          1.5         0.2    setosa          1
#   5:          5.0         3.6          1.4         0.2    setosa          1
#  ---                                                                       
# 146:          6.7         3.0          5.2         2.3 virginica          1
# 147:          6.3         2.5          5.0         1.9 virginica          1
# 148:          6.5         3.0          5.2         2.0 virginica          1
# 149:          6.2         3.4          5.4         2.3 virginica          1
# 150:          5.9         3.0          5.1         1.8 virginica          1

Example 2: Adding a New Column with Column Name Stored In Object

When the name of the column which we want to add is stored as a string in another object, the following code can be used to combine the data.table with a column of that name.

iris_dt_new_col2 <- data.table::copy(iris_dt)                                       # Copying iris_dt
name_of_new_col <- "This column is new"
iris_dt_new_col2[, (name_of_new_col) := 5]
iris_dt_new_col2
#      Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
#   1:          5.1         3.5          1.4         0.2    setosa
#   2:          4.9         3.0          1.4         0.2    setosa
#   3:          4.7         3.2          1.3         0.2    setosa
#   4:          4.6         3.1          1.5         0.2    setosa
#   5:          5.0         3.6          1.4         0.2    setosa
#  ---                                                            
# 146:          6.7         3.0          5.2         2.3 virginica
# 147:          6.3         2.5          5.0         1.9 virginica
# 148:          6.5         3.0          5.2         2.0 virginica
# 149:          6.2         3.4          5.4         2.3 virginica
# 150:          5.9         3.0          5.1         1.8 virginica
#      This column is new
#   1:                  5
#   2:                  5
#   3:                  5
#   4:                  5
#   5:                  5
#  ---                   
# 146:                  5
# 147:                  5
# 148:                  5
# 149:                  5
# 150:                  5

In the above code, by using round brackets around name_of_new_col, we indicate that the new column should be named after the string stored in name_of_new_col.

Example 3: Adding a New Row

The iris data consists of 150 rows. We append an additional row by using function rbindlist from the data.table package. To do this, the following three things should be considered: The new row is defined as a data.table object itself. The data.table objects which we want to merge are put in a list. In addition, if in the new row the information on some columns is missing, we need the additional argument fill = TRUE. By that, we indicate that missing information is filled by NA.

iris_dt_new_row <- data.table::copy(iris_dt)                                        # Copying iris_dt
iris_dt_new_row <- rbindlist(list(iris_dt_new_row, data.table("Sepal.Width" = 5)),  # Row-Wisely combine dt_add_row and new_row   
                         fill = TRUE)                                               # Missing columns are filled with NA      
iris_dt_new_row
#      Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
#   1:          5.1         3.5          1.4         0.2    setosa
#   2:          4.9         3.0          1.4         0.2    setosa
#   3:          4.7         3.2          1.3         0.2    setosa
#   4:          4.6         3.1          1.5         0.2    setosa
#   5:          5.0         3.6          1.4         0.2    setosa
#  ---                                                            
# 147:          6.3         2.5          5.0         1.9 virginica
# 148:          6.5         3.0          5.2         2.0 virginica
# 149:          6.2         3.4          5.4         2.3 virginica
# 150:          5.9         3.0          5.1         1.8 virginica
# 151:           NA         5.0           NA          NA      <NA>

Row 151 is merged to the iris data. In the new row, only information on Sepal.Width was given. Therefore, all other column information is filled by NA.

Related Tutorials & Further Resources

Here, you may find some further resources on topics such as extracting data, matrices, and naming data.

 

Anna-Lena Wölwer R Programming & Survey Statistics

Note: This article was created in collaboration with Anna-Lena Wölwer. Anna-Lena is a researcher and programmer who creates tutorials on statistical methodology as well as on the R programming language. You may find more info about Anna-Lena and her other articles on her profile page.

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.
You need to agree with the terms to proceed

Menu
Top