How to merge multiple columns-CodePudding

I want to merge 4 columns together, (Standing, Stepping, Cycling, New_Sitting). In this case, I want to create a new column (called "Posture"). This new column (as per the example below) should be like:

Posture
<dbl>
2
3
2
1
1
1
3
4
4
4

Here is an example of my data:

> head(graph_pre,30)
# A tibble: 30 × 11
# Groups:   Date [1]
   Date       Time                Axis1 Axis2 Axis3    VM Standing Stepping Cycling New_Sitting
   <date>     <dttm>              <dbl> <dbl> <dbl> <dbl>    <dbl>    <dbl>   <dbl>       <dbl>
 1 2022-03-14 2022-03-14 09:51:00    89    41    39 105.         0        0       2           0
 2 2022-03-14 2022-03-14 09:51:01    88   135   117 199.         0        3       0           0
 3 2022-03-14 2022-03-14 09:51:02     0    61     8  61.5        0        0       2           0
 4 2022-03-14 2022-03-14 09:51:03     0    25     0  25          0        0       0           1
 5 2022-03-14 2022-03-14 09:51:04     0     0     0   0          0        0       0           1
 6 2022-03-14 2022-03-14 09:51:05     0     0     0   0          0        0       0           1
 7 2022-03-14 2022-03-14 09:51:06     0    24    35  42.4        0        3       0           0
 8 2022-03-14 2022-03-14 09:51:07     0    28     0  28          4        0       0           0
 9 2022-03-14 2022-03-14 09:51:08     4    96    20  98.1        4        0       0           0
10 2022-03-14 2022-03-14 09:51:09     0    11     0  11          4        0       0           0
# … with 20 more rows, and 1 more variable: Counter <int>

Please let me know if you need more information as I'm new to this.

EDIT

> dput(head(graph_pre,30))
structure(list(Date = structure(c(19065, 19065, 19065, 19065, 
19065, 19065, 19065, 19065, 19065, 19065, 19065, 19065, 19065, 
19065, 19065, 19065, 19065, 19065, 19065, 19065, 19065, 19065, 
19065, 19065, 19065, 19065, 19065, 19065, 19065, 19065), class = "Date"), 
    Time = structure(c(1647265860, 1647265861, 1647265862, 1647265863, 
    1647265864, 1647265865, 1647265866, 1647265867, 1647265868, 
    1647265869, 1647265870, 1647265871, 1647265872, 1647265873, 
    1647265874, 1647265875, 1647265876, 1647265877, 1647265878, 
    1647265879, 1647265880, 1647265881, 1647265882, 1647265883, 
    1647265884, 1647265885, 1647265886, 1647265887, 1647265888, 
    1647265889), tzone = "", class = c("POSIXct", "POSIXt")), 
    Axis1 = c(89, 88, 0, 0, 0, 0, 0, 0, 4, 0, 3, 9, 5, 0, 1, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 13, 11, 3, 0), Axis2 = c(41, 
    135, 61, 25, 0, 0, 24, 28, 96, 11, 91, 44, 8, 8, 29, 1, 17, 
    0, 0, 0, 15, 0, 0, 0, 0, 28, 47, 28, 48, 0), Axis3 = c(39, 
    117, 8, 0, 0, 0, 35, 0, 20, 0, 22, 2, 16, 21, 48, 3, 35, 
    0, 5, 29, 32, 0, 0, 0, 0, 4, 26, 68, 5, 0), VM = c(105.47, 
    199.14, 61.52, 25, 0, 0, 42.44, 28, 98.14, 11, 93.67, 44.96, 
    18.57, 22.47, 56.09, 3.16, 38.91, 0, 5, 29, 35.34, 0, 0, 
    0, 0, 28.28, 55.26, 74.36, 48.35, 0), Standing = c(0, 0, 
    0, 0, 0, 0, 0, 4, 4, 4, 4, 4, 4, 4, 0, 4, 0, 4, 4, 0, 0, 
    4, 4, 4, 4, 4, 0, 0, 4, 4), Stepping = c(0, 3, 0, 0, 0, 0, 
    3, 0, 0, 0, 0, 0, 0, 0, 3, 0, 3, 0, 0, 3, 3, 0, 0, 0, 0, 
    0, 3, 3, 0, 0), Cycling = c(2, 0, 2, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0), New_Sitting = c(0, 0, 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), Counter = c(0L, 
    1L, 1L, 1L, 0L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 
    1L, 1L, 0L, 1L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 0L)), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -30L), groups = structure(list(
    Date = structure(19065, class = "Date"), .rows = structure(list(
        1:30), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -1L), .drop = TRUE))

CodePudding user response：

What you can do is first replace the zeros with NA and after that unite the columns together. You can use the following code:

library(dplyr)
library(tidyr)
graph_pre %>%
   mutate(across(Standing:New_Sitting, na_if, 0)) %>%
   unite(Posture, Standing:New_Sitting, na.rm = TRUE, sep = '', remove = T) %>%
   mutate(Posture = as.numeric(Posture))

Output:

# A tibble: 30 × 8
# Groups:   Date [1]
   Date       Time                Axis1 Axis2 Axis3    VM Posture Counter
   <date>     <dttm>              <dbl> <dbl> <dbl> <dbl>   <dbl>   <int>
 1 2022-03-14 2022-03-14 14:51:00    89    41    39 105.        2       0
 2 2022-03-14 2022-03-14 14:51:01    88   135   117 199.        3       1
 3 2022-03-14 2022-03-14 14:51:02     0    61     8  61.5       2       1
 4 2022-03-14 2022-03-14 14:51:03     0    25     0  25         1       1
 5 2022-03-14 2022-03-14 14:51:04     0     0     0   0         1       0
 6 2022-03-14 2022-03-14 14:51:05     0     0     0   0         1       0
 7 2022-03-14 2022-03-14 14:51:06     0    24    35  42.4       3       1
 8 2022-03-14 2022-03-14 14:51:07     0    28     0  28         4       1
 9 2022-03-14 2022-03-14 14:51:08     4    96    20  98.1       4       0
10 2022-03-14 2022-03-14 14:51:09     0    11     0  11         4       0
# … with 20 more rows

CodePudding user response：

If you just want to merge them by summing the values for each row, you can do this:

library(tidyverse)

your_dataframe %>% 
mutate(Posture = sum(Standing, Stepping, Cycling, New_Sitting))

Which will add an extra column called Posture at the end of your dataframe