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.
- Replicate Column with & Add to Data Frame with New Name
- Using apply() Function by Row & Column
- Select Value from Previous Row of data.table
- Extract Matrix Values by Row & Column Names
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.