Pivot table from wide to long gives error in R-CodePudding

I want to change the table format from wide to long. I got two dataframes. The first dataframe has 15 variables of interest and the second dataframe has 23 variables of interest. I tried to convert it to a dataframe with two or three columns (per 7 long columns to 1 wide column) with a timeframe of 1 to 7. If I do this for two variables the code works perfectly fine but when I add a third variable it gives an extra row per ID with the value NA. And I dont want to simply na.omit the dataframe because it will also delete missing data that was actually already in the data set. I will first show the dataframes of 15 and 23 columns respectively.

structure(list(ID = c("ID1", "ID10", "ID11", "ID12", "ID13"), 
    AI_1 = c(0.393672183448241, 0.296109333767922, 0.31961673567578, 
    0.520204263001733, 0.361215896677733), AI_2 = c(0.4876954603533, 
    0.309960002123076, 0.227268892979164, 0.194175420670027, 
    0.256393554215028), AI_3 = c(0.411717908455957, 0.445886231347992, 
    0.433471105477564, 0.507417309543222, 0.429431082438377), 
    AI_4 = c(0.309769862660288, 0.370013553008003, 0.207184572401005, 
    0.1934679395598, NA), AI_5 = c(0.149826889496538, 0.393414429902431, 
    0.144257239122978, 0.0831932654836405, NA), AI_6 = c(0.2448558592586, 
    0.318940511323733, NA, 0.115391861884329, NA), AI_7 = c(0.123606753324621, 
    0.131112361225666, NA, 0.141420940969022, NA), AI_VAR_1 = c(0.154977788020905, 
    0.0876807375444826, 0.102154857724042, 0.270612475245176, 
    0.130476924012699), AI_VAR_2 = c(0.237846862049217, 0.0960752029161373, 
    0.0516511497159746, 0.0377040939923819, 0.0657376546430145
    ), AI_VAR_3 = c(0.169511636143347, 0.198814531305715, 0.187897199283942, 
    0.257472326024082, 0.184411054564196), AI_VAR_4 = c(0.0959573678125739, 
    0.136910029409606, 0.0429254470409874, 0.0374298436375145, 
    NA), AI_VAR_5 = c(0.0224480968162077, 0.154774913655455, 
    0.020810151039384, 0.00692111942183149, NA), AI_VAR_6 = c(0.0599543918132674, 
    0.101723049763444, NA, 0.0133152817891321, NA), AI_VAR_7 = c(0.0152786294674538, 
    0.0171904512661696, NA, 0.0199998825445637, NA)), row.names = c(NA, 
5L), class = "data.frame")

structure(list(ID = c("ID1", "ID10", "ID11", "ID12", "ID13"), 
    AI_1 = c(0.393672183448241, 0.296109333767922, 0.31961673567578, 
    0.520204263001733, 0.361215896677733), AI_2 = c(0.4876954603533, 
    0.309960002123076, 0.227268892979164, 0.194175420670027, 
    0.256393554215028), AI_3 = c(0.411717908455957, 0.445886231347992, 
    0.433471105477564, 0.507417309543222, 0.429431082438377), 
    AI_4 = c(0.309769862660288, 0.370013553008003, 0.207184572401005, 
    0.1934679395598, NA), AI_5 = c(0.149826889496538, 0.393414429902431, 
    0.144257239122978, 0.0831932654836405, NA), AI_6 = c(0.2448558592586, 
    0.318940511323733, NA, 0.115391861884329, NA), AI_7 = c(0.123606753324621, 
    0.131112361225666, NA, 0.141420940969022, NA), AI_VAR_1 = c(0.154977788020905, 
    0.0876807375444826, 0.102154857724042, 0.270612475245176, 
    0.130476924012699), AI_VAR_2 = c(0.237846862049217, 0.0960752029161373, 
    0.0516511497159746, 0.0377040939923819, 0.0657376546430145
    ), AI_VAR_3 = c(0.169511636143347, 0.198814531305715, 0.187897199283942, 
    0.257472326024082, 0.184411054564196), AI_VAR_4 = c(0.0959573678125739, 
    0.136910029409606, 0.0429254470409874, 0.0374298436375145, 
    NA), AI_VAR_5 = c(0.0224480968162077, 0.154774913655455, 
    0.020810151039384, 0.00692111942183149, NA), AI_VAR_6 = c(0.0599543918132674, 
    0.101723049763444, NA, 0.0133152817891321, NA), AI_VAR_7 = c(0.0152786294674538, 
    0.0171904512661696, NA, 0.0199998825445637, NA), x1.time = c(NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_), activity1 = c(0, 
    0.357638888888889, 0.448611111111111, 0.238888888888889, 
    0.45625), activity2 = c(0.303472222222222, 0.235416666666667, 
    0.213888888888889, 0.263888888888889, 0.239583333333333), 
    activity3 = c(0.232638888888889, 0.233333333333333, 0.248611111111111, 
    0.247916666666667, 0.335416666666667), activity4 = c(0.228472222222222, 
    0.2875, 0.288888888888889, 0.315277777777778, NA), activity5 = c(0.348611111111111, 
    0.353472222222222, 0.25625, 0.298611111111111, NA), activity6 = c(0.215972222222222, 
    0.356944444444444, NA, 0.173611111111111, NA), activity7 = c(0.123611111111111, 
    0.149305555555556, NA, 0.185416666666667, NA)), row.names = c(NA, 
5L), class = "data.frame")

This is the code that I used for the two variables

df4 <- df %>% 
  pivot_longer(-c(ID), 
               names_to = c(".value", "Time"),
               names_pattern = "(AI|AI_VAR)_*(\\d)")

Then I obtain the following dataset

structure(list(ID = c("ID1", "ID1", "ID1", "ID1", "ID1", "ID1", 
"ID1", "ID10", "ID10", "ID10", "ID10", "ID10", "ID10", "ID10", 
"ID11", "ID11", "ID11", "ID11", "ID11", "ID11"), Time = c("1", 
"2", "3", "4", "5", "6", "7", "1", "2", "3", "4", "5", "6", "7", 
"1", "2", "3", "4", "5", "6"), AI = c(0.393672183448241, 0.4876954603533, 
0.411717908455957, 0.309769862660288, 0.149826889496538, 0.2448558592586, 
0.123606753324621, 0.296109333767922, 0.309960002123076, 0.445886231347992, 
0.370013553008003, 0.393414429902431, 0.318940511323733, 0.131112361225666, 
0.31961673567578, 0.227268892979164, 0.433471105477564, 0.207184572401005, 
0.144257239122978, NA), AI_VAR = c(0.154977788020905, 0.237846862049217, 
0.169511636143347, 0.0959573678125739, 0.0224480968162077, 0.0599543918132674, 
0.0152786294674538, 0.0876807375444826, 0.0960752029161373, 0.198814531305715, 
0.136910029409606, 0.154774913655455, 0.101723049763444, 0.0171904512661696, 
0.102154857724042, 0.0516511497159746, 0.187897199283942, 0.0429254470409874, 
0.020810151039384, NA)), row.names = c(NA, -20L), class = c("tbl_df", 
"tbl", "data.frame"))

**And this is the code is use for the extra variable (activity)

df4 <- df3 %>% 
  pivot_longer(-c(ID), 
               names_to = c(".value", "Time"),
               names_pattern = "(AI|AI_VAR|activity)_*(\\d)")

Then I get the following dataframe:

structure(list(ID = c("ID1", "ID1", "ID1", "ID1", "ID1", "ID1", 
"ID1", "ID1", "ID10", "ID10", "ID10", "ID10", "ID10", "ID10", 
"ID10", "ID10", "ID11", "ID11", "ID11", "ID11"), Time = c("1", 
"2", "3", "4", "5", "6", "7", NA, "1", "2", "3", "4", "5", "6", 
"7", NA, "1", "2", "3", "4"), AI = c(0.393672183448241, 0.4876954603533, 
0.411717908455957, 0.309769862660288, 0.149826889496538, 0.2448558592586, 
0.123606753324621, NA, 0.296109333767922, 0.309960002123076, 
0.445886231347992, 0.370013553008003, 0.393414429902431, 0.318940511323733, 
0.131112361225666, NA, 0.31961673567578, 0.227268892979164, 0.433471105477564, 
0.207184572401005), AI_VAR = c(0.154977788020905, 0.237846862049217, 
0.169511636143347, 0.0959573678125739, 0.0224480968162077, 0.0599543918132674, 
0.0152786294674538, NA, 0.0876807375444826, 0.0960752029161373, 
0.198814531305715, 0.136910029409606, 0.154774913655455, 0.101723049763444, 
0.0171904512661696, NA, 0.102154857724042, 0.0516511497159746, 
0.187897199283942, 0.0429254470409874), activity = c(0, 0.303472222222222, 
0.232638888888889, 0.228472222222222, 0.348611111111111, 0.215972222222222, 
0.123611111111111, NA, 0.357638888888889, 0.235416666666667, 
0.233333333333333, 0.2875, 0.353472222222222, 0.356944444444444, 
0.149305555555556, NA, 0.448611111111111, 0.213888888888889, 
0.248611111111111, 0.288888888888889)), row.names = c(NA, -20L
), class = c("tbl_df", "tbl", "data.frame"))

My question is: how can I change the code of pivot_longer where I can get rid of the extra NA value per ID

CodePudding user response：

You could drop_na the rows based on the "Time" column like this:

library(dplyr)
library(tidyr)

df3 %>% 
  pivot_longer(-c(ID), 
               names_to = c(".value", "Time"),
               names_pattern = "(AI|AI_VAR|activity)_*(\\d)") %>%
  drop_na(Time)
#> # A tibble: 35 × 5
#>    ID    Time     AI AI_VAR activity
#>    <chr> <chr> <dbl>  <dbl>    <dbl>
#>  1 ID1   1     0.394 0.155     0    
#>  2 ID1   2     0.488 0.238     0.303
#>  3 ID1   3     0.412 0.170     0.233
#>  4 ID1   4     0.310 0.0960    0.228
#>  5 ID1   5     0.150 0.0224    0.349
#>  6 ID1   6     0.245 0.0600    0.216
#>  7 ID1   7     0.124 0.0153    0.124
#>  8 ID10  1     0.296 0.0877    0.358
#>  9 ID10  2     0.310 0.0961    0.235
#> 10 ID10  3     0.446 0.199     0.233
#> # … with 25 more rows

^{Created on 2022-10-12 with reprex v2.0.2}