Replace rows in df1 with rows in df2 using rownames (or something similar)

RStudio IDE
1

Hi!

I have two data frames , df1 and df2.
df1 = 5 rows, 3 columns ex.

     , [col1]   [col2]   [col3]
[row1]   1       2       2
[row2]   2       3       3
[row3]   3       3       1
[row4]   4       4       4
[row5]   4       3       4

df2 = 2 rows, 3 columns ex.

     , [col2]   [col3]   [col1]
[row4]   9       0       9
[row5]   8       0       8

df1 and df2 have the same number of columns and the same column names. However, the column names in df2 are not in the same order.
The rownames of df2 match those in df1.

How can I generate a new df where I replace row4 and row5 of df1 with those from df2, and have the numbers replaced so that they retain the column order set in df1.
Something like this:

     , [col1]   [col2]   [col3]
[row1]   1       2       2
[row2]   2       3       3
[row3]   3       3       1
[row4]   9       9       0
[row5]   8       0       8

I've been attempting some merge() and match() functions trying to match the rows by row.names(df) with no luck. I would end up with additional columns, i.e. 6 columns after merging instead of 3, or get the addition of more rows.

Thanks!!

Angelica

2

Hi @amiraple,

If you add a column containing your row names (tibble::rownames_to_column), you can match by values in that column and by column names via dplyr::rows_upsert.

library("dplyr")
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library("tibble")

df1 <- data.frame("col1" = c(1,2,3,4,4), "col2" = c(2,3,3,4,3),
  "col3" = c(2,3,1,4,4), row.names = paste0("row", 1:5))


df2 <- data.frame("col2" = c(9,8), "col3" = c(0, 0),
  "col1" = c(9, 8), row.names = paste0("row", 4:5))

df1 <- df1 |>
  rownames_to_column()

df2 <- df2 |>
  rownames_to_column()

df_patched <- rows_upsert(df1, df2, by = "rowname") |>
  select(-rowname)

df_patched
#>   col1 col2 col3
#> 1    1    2    2
#> 2    2    3    3
#> 3    3    3    1
#> 4    9    9    0
#> 5    8    8    0

Created on 2024-10-25 with reprex v2.1.1

3

A base R alternative for reference using @amiraple first approach

df1 <- data.frame("col1" = c(1,2,3,4,4), 
                  "col2" = c(2,3,3,4,3),
                  "col3" = c(2,3,1,4,4), 
                  row.names = paste0("row", 1:5))

df2 <- data.frame("col2" = c(9,8), 
                  "col3" = c(0, 0),
                  "col1" = c(9, 8), 
                  row.names = paste0("row", 4:5))

replaceRow <- match(rownames(df1),
                    rownames(df2))

df3 <- rbind(df1[which(is.na(replaceRow)), ], 
             df2[replaceRow[!is.na(replaceRow)], colnames(df1)] )

Ouput

> df3
     col1 col2 col3
row1    1    2    2
row2    2    3    3
row3    3    3    1
row4    9    9    0
row5    8    8    0