CODEWITHSUNDEEP
rdplyr
beginnerRuser19
beginnerRuser19

Reputation: 33

Converting certain columns into new rows in a data frame and placing them in between rows

I have a data frame, df, that looks like the following:

Col1 Col2 Col3 Col4 Col5

Cat  JK  3.1   A   B
Dog  JK  4.0   C   D
Bir  JK  4.5   E   F

I would like the final data frame to look like:

Col1 Col2 Col3   

Cat  JK  3.1
A    NA  NA
B    NA  NA
Dog  JK  4.0
C    NA  NA
D    NA  NA
Bir  JK  4.5
E    NA  NA
F    NA  NA

So I want to place the contents of all columns after Col3 into new rows underneath their respective rows.

Important: Note that there's a variable number of rows after Col3 (I'm joining two data frames together to create df) so a for() loop might be necessary? The number of rows is also variable because it's a reactive table in Shiny so that's possibly another loop. If needed, all columns after Col3 can be renamed to anything.

The dplyr package's add_row() function seems like it would do the job, along with the .before/.after "option" but I'm not sure how I would go about doing that. Is this even possible?

Upvotes: 1

Views: 455

Answers (1)

Anoushiravan R
Anoushiravan R

Reputation: 21918

I don't know if this is what you are after but if there is anything that should be done, just let me know:

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(!c(Col2, Col3)) %>%
  relocate(value) %>%
  select(-name) %>%
  rename(Col1 = value)

# A tibble: 9 x 3
  Col1  Col2   Col3
  <chr> <chr> <dbl>
1 Cat   JK      3.1
2 A     JK      3.1
3 B     JK      3.1
4 Dog   JK      4  
5 C     JK      4  
6 D     JK      4  
7 Bir   JK      4.5
8 E     JK      4.5
9 F     JK      4.5

Data

df <- tribble(
  ~Col1, ~Col2, ~Col3, ~Col4, ~Col5,
  "Cat",  "JK",  3.1,   "A",   "B",
  "Dog",  "JK",  4.0,   "C",   "D",
  "Bir",  "JK",  4.5,   "E",   "F"
)

Upvotes: 2

Related Questions