Converting data to long format conditionally

[kuttan98]

I have a dataset where I track teachers week-wise. There are specific goals which have to be achieved. The goals are arranged week-wise. Hence, goal 1 is for week 1, goal 2 is for week 2 and so on. I want to convert the data so that a new variable "goal" is created and only the relevant score is shown (as can be seen in "data2" in the reprex).

library(tidyverse)


data<-tibble::tribble(
       ~master,           ~schoolid, ~teacher, ~week_no, ~goal1, ~goal2,
       "Nagma",        "GHPS UNKAL", "Gandhi",       1L,     1L,     NA,
        "Anil",    "GLPS REVADIHAL",  "Nehru",       1L,     1L,     NA,
        "Anil",    "GLPS CHIKKAMTH",  "Obama",       1L,     2L,     NA,
  "Madhumathi", "GHPS NO.3 KANNADA", "Gerald",       1L,     1L,     NA,
       "Nagma",         "GHPS PALE", "Gandhi",       2L,     NA,     1L,
        "Anil",         "GHPS INAM",  "Nehru",       2L,     NA,     2L,
        "Anil",     "GLPS BYAHATTI",  "Obama",       2L,     NA,     1L,
  "Madhumathi",      "GHPS KIRESUR", "Gerald",       2L,     NA,     1L
  )

data2<-tibble::tribble(
              ~master,           ~schoolid, ~teacher, ~week_no,   ~goal, ~goal1, ~goal2,
              "Nagma",        "GHPS UNKAL", "Gandhi",       1L, "goal1",     1L,     NA,
               "Anil",    "GLPS REVADIHAL",  "Nehru",       1L, "goal1",     1L,     NA,
               "Anil",    "GLPS CHIKKAMTH",  "Obama",       1L, "goal1",     2L,     NA,
         "Madhumathi", "GHPS NO.3 KANNADA", "Gerald",       1L, "goal1",     1L,     NA,
              "Nagma",         "GHPS PALE", "Gandhi",       2L, "goal2",     NA,     1L,
               "Anil",         "GHPS INAM",  "Nehru",       2L, "goal2",     NA,     2L,
               "Anil",     "GLPS BYAHATTI",  "Obama",       2L, "goal2",     NA,     1L,
         "Madhumathi",      "GHPS KIRESUR", "Gerald",       2L, "goal2",     NA,     1L
         )
Created on 2022-09-30 by the reprex package (v2.0.1)

[scottyd22]

Below is a way to do this using pivot_longer() and pivot_wider().

data3 = data %>%
  pivot_longer(cols = c('goal1', 'goal2')) %>%
  filter(!is.na(value)) %>%
  mutate(goal = name) %>%
  pivot_wider(names_from = name, values_from = value)

data3
#> # A tibble: 8 × 7
#>   master     schoolid          teacher week_no goal  goal1 goal2
#>   <chr>      <chr>             <chr>     <int> <chr> <int> <int>
#> 1 Nagma      GHPS UNKAL        Gandhi        1 goal1     1    NA
#> 2 Anil       GLPS REVADIHAL    Nehru         1 goal1     1    NA
#> 3 Anil       GLPS CHIKKAMTH    Obama         1 goal1     2    NA
#> 4 Madhumathi GHPS NO.3 KANNADA Gerald        1 goal1     1    NA
#> 5 Nagma      GHPS PALE         Gandhi        2 goal2    NA     1
#> 6 Anil       GHPS INAM         Nehru         2 goal2    NA     2
#> 7 Anil       GLPS BYAHATTI     Obama         2 goal2    NA     1
#> 8 Madhumathi GHPS KIRESUR      Gerald        2 goal2    NA     1

Created on 2022-09-30 with reprex v2.0.2.9000

[kuttan98]

Than you very much for this..