Hi, this should work
suppressMessages(library(tidyverse))
df1 <- read_tsv("sample.txt", col_names = c("date", "time", paste0("p", 1:32)))
#>
#> -- Column specification --------------------------------------------------------
#> cols(
#> .default = col_double(),
#> date = col_character(),
#> time = col_time(format = "")
#> )
#> i Use `spec()` for the full column specifications.
df_tidy <- df1 %>%
#Convert df in tidy format
pivot_longer(cols = p1:p32,
names_to = "individ",
values_to = "activity") %>%
# Group by date and person
group_by(date, individ) %>%
mutate(total_day_act = sum(activity), #find total activity in each date for each person
activity_pct = activity/total_day_act*100) #find % of activities in each timepoint
head(df_tidy)
#> # A tibble: 6 x 6
#> # Groups: date, individ [6]
#> date time individ activity total_day_act activity_pct
#> <chr> <time> <chr> <dbl> <dbl> <dbl>
#> 1 26 Jul 20 01'00" p1 0 1127 0
#> 2 26 Jul 20 01'00" p2 0 974 0
#> 3 26 Jul 20 01'00" p3 0 421 0
#> 4 26 Jul 20 01'00" p4 0 775 0
#> 5 26 Jul 20 01'00" p5 0 1283 0
#> 6 26 Jul 20 01'00" p6 0 1248 0
#Convert back to "dirty" format if needed
df_dirty <- df_tidy %>%
ungroup() %>%
select(!c(activity, total_day_act)) %>%
pivot_wider(names_from = individ,
values_from = activity_pct)
#Last date has only one timepoint, hence it either 100% or NA because of division by 0
tail(df_dirty)
#> # A tibble: 6 x 34
#> date time p1 p2 p3 p4 p5 p6 p7 p8 p9 p10
#> <chr> <tim> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 2 Au~ 23:55 0 0 0 0 0. 0 0 0 0 0
#> 2 2 Au~ 23:56 0 0.139 0 0 0. 0 0 0 0 0
#> 3 2 Au~ 23:57 0 0.555 0 0 0. 0 0 0 0 0
#> 4 2 Au~ 23:58 0 0 0 0 9.43e-2 0 0 0 0 0
#> 5 2 Au~ 23:59 0 0 0 0 3.77e-1 0 0 0 0 0
#> 6 3 Au~ 00:00 NaN NaN NaN NaN 1.00e+2 NaN NaN NaN NaN NaN
#> # ... with 22 more variables: p11 <dbl>, p12 <dbl>, p13 <dbl>, p14 <dbl>,
#> # p15 <dbl>, p16 <dbl>, p17 <dbl>, p18 <dbl>, p19 <dbl>, p20 <dbl>,
#> # p21 <dbl>, p22 <dbl>, p23 <dbl>, p24 <dbl>, p25 <dbl>, p26 <dbl>,
#> # p27 <dbl>, p28 <dbl>, p29 <dbl>, p30 <dbl>, p31 <dbl>, p32 <dbl>