Add a new row with a certain value into the first column and then "-" to the rest of the columns

user_17491547 0
Viewed 33

So I have a dataset which I would like to add a new row to (it has 70+ columns in it). If possible I would like the first column to have the name of the city and then every other column in that row to just have a dash.

Here is an example data:

mydata = data.frame (Column1 =c("1","1","1","1","1","1","1",'1',"1","1"),
                     Column2 = c("2000-01-01","2000-01-05","2000-02-02", "2000-02-12", 
                              "2000-02-14","2000-05-13", "2000-05-15", "2000-05-17", 
                              "2000-05-16", "2000-05-20"),
                     Column3 = c("A","A","B","B","B","A","A","A","C","C"))

Please see my code below. This code allows me to add a row which gives me Toronto into the first column, but im struggling to add the "-" to the other columns. I would rather NOT have to list out every single column as mine has more than 70 columns.

mydata <- mydata %>% bind_rows(list(`Column1` = "TORONTO"),.)

I tried the following but kept getting errors.


mydata <- mydata %>% bind_rows(list(`Column1` = "TORONTO"),col(2:ncol) = "-")

```

Any help would be appreciated!
data-manipulation r
2 Answers

We could use add_row

library(dplyr)
mydata %>%
  add_row(Column1 = "TORONTO", !!! setNames(rep('-', ncol(.)-1),
      names(.)[-1]))

-output

   Column1    Column2 Column3
1        1 2000-01-01       A
2        1 2000-01-05       A
3        1 2000-02-02       B
4        1 2000-02-12       B
5        1 2000-02-14       B
6        1 2000-05-13       A
7        1 2000-05-15       A
8        1 2000-05-17       A
9        1 2000-05-16       C
10       1 2000-05-20       C
11 TORONTO          -       -

By default, it adds the row at the bottom. If we want to add at a position, specify .before

mydata %>%
  add_row(Column1 = "TORONTO", !!! setNames(rep('-', ncol(.)-1),
      names(.)[-1]), .before = 1)

-output

  Column1    Column2 Column3
1  TORONTO          -       -
2        1 2000-01-01       A
3        1 2000-01-05       A
4        1 2000-02-02       B
5        1 2000-02-12       B
6        1 2000-02-14       B
7        1 2000-05-13       A
8        1 2000-05-15       A
9        1 2000-05-17       A
10       1 2000-05-16       C
11       1 2000-05-20       C

Or may use summarise with across to concatenate the - as the first element

mydata %>%
   summarise(Column1 = c("TORONTO", Column1), across(-Column1, ~ c("-", .x)))
   Column1    Column2 Column3
1  TORONTO          -       -
2        1 2000-01-01       A
3        1 2000-01-05       A
4        1 2000-02-02       B
5        1 2000-02-12       B
6        1 2000-02-14       B
7        1 2000-05-13       A
8        1 2000-05-15       A
9        1 2000-05-17       A
10       1 2000-05-16       C
11       1 2000-05-20       C
user_37322710

Here is an alternative approach that uses the.before argument and replace_na function:

library(dplyr)
library(tidyr)

mydata %>% 
  add_row(Column1 = "TORONTO", .before = 1) %>% 
  mutate(across(-Column1, ~replace_na(., "-")))

   Column1    Column2 Column3
1  TORONTO          -       -
2        1 2000-01-01       A
3        1 2000-01-05       A
4        1 2000-02-02       B
5        1 2000-02-12       B
6        1 2000-02-14       B
7        1 2000-05-13       A
8        1 2000-05-15       A
9        1 2000-05-17       A
10       1 2000-05-16       C
11       1 2000-05-20       C
user_133216470
Related