How to convert this vertical dataset to horizontal dataset?

Question

I wanna change this data set:

        id PTMIINDT PTMIINTM DGOTDIAG DGOTDGGB
1: ys00000001 20160101      614     R060        1
2: ys00000002 20160101      640    S0090        1
3: ys00000002 20160101      640     A090        2
4: ys00000003 20160101      959      R42        1
5: ys00000007 20160101     1111    S0600        1
6: ys00000008 20160101     1253     R558        1

to this dataset:

         id     PTMIINDT PTMIINTM DGOTDIAG01 DGOTDGGB01 DGOTDIAG02  DGOTDGGB02
1 ys00000001      20160101     614      R060          1         NA          NA
2 ys00000002      20160101     640     S0090          1       A090           2
.     .               .         .
.     .               .         .
.     .               .         .

like this.

I tried to make this data set with mutate function. but it didn't work well. How can I change the dataset like this?

ba<-n6 %>% group_by(id,PTMIINDT,PTMIINTM) %>% 
  mutate(DGOTDIAG01=DGOTDIAG, DGOTDIAG02=DGOTDIAG, DGOTDGGB01=DGOTDGGB,DGOTDGGB02=DGOTDGGB)


ba<-n6 %>% group_by(id,PTMIINDT,PTMIINTM) %>% 
  mutate(DGOTDIAG01=DGOTDIAG, DGOTDIAG02=DGOTDIAG, DGOTDGGB01=DGOTDGGB,DGOTDGGB02=DGOTDGGB)

         id     PTMIINDT PTMIINTM DGOTDIAG01 DGOTDGGB01 DGOTDIAG02  DGOTDGGB02
1 ys00000001      20160101     614      R060          1         NA          NA
2 ys00000002      20160101     640     S0090          1       A090           2
.     .               .         .
.     .               .         .
.     .               .         .

score 1 · Answer 1 · answered Sep 10 '19 at 07:49

With data.table::dcast() this can be done using the following one-liner.

library(data.table)

sample data

dt <- data.table::fread("id PTMIINDT PTMIINTM DGOTDIAG DGOTDGGB
ys00000001 20160101      614     R060        1
ys00000002 20160101      640    S0090        1
ys00000002 20160101      640     A090        2
ys00000003 20160101      959      R42        1
ys00000007 20160101     1111    S0600        1
ys00000008 20160101     1253     R558        1")

code

data.table::dcast( dt, id + PTMIINDT + PTMIINTM ~ DGOTDGGB, value.var = c("DGOTDIAG", "DGOTDGGB") )

output

#            id PTMIINDT PTMIINTM DGOTDIAG_1 DGOTDIAG_2 DGOTDGGB.1_1 DGOTDGGB.1_2
# 1: ys00000001 20160101      614       R060       <NA>            1           NA
# 2: ys00000002 20160101      640      S0090       A090            1            2
# 3: ys00000003 20160101      959        R42       <NA>            1           NA
# 4: ys00000007 20160101     1111      S0600       <NA>            1           NA
# 5: ys00000008 20160101     1253       R558       <NA>            1           NA

Jon Spring · Answer 2 · 2019-09-10T14:28:34.430

0

The development version of tidyr features a new verb, pivot_wider which is better suited to this task.

https://tidyr.tidyverse.org/dev/articles/pivot.html

In the meantime, you can gather, convert, and spread:

n6 %>%
  gather(column, value, -c(id, PTMIINDT, PTMIINTM)) %>%
  group_by(id, PTMIINDT, PTMIINTM) %>%
  mutate(column = paste0(column, 
                         stringr::str_pad(row_number(), width = 2, pad = 0))) %>%
  spread(column, value)

# A tibble: 5 x 8
# Groups:   id, PTMIINDT, PTMIINTM [5]
  id         PTMIINDT PTMIINTM DGOTDGGB02 DGOTDGGB03 DGOTDGGB04 DGOTDIAG01 DGOTDIAG02
  <chr>         <int>    <int> <chr>      <chr>      <chr>      <chr>      <chr>     
1 ys00000001 20160101      614 1          NA         NA         R060       NA        
2 ys00000002 20160101      640 NA         1          2          S0090      A090      
3 ys00000003 20160101      959 1          NA         NA         R42        NA        
4 ys00000007 20160101     1111 1          NA         NA         S0600      NA        
5 ys00000008 20160101     1253 1          NA         NA         R558       NA

edited Sep 10 '19 at 14:28

answered Sep 10 '19 at 06:44

Jon Spring

55,165
4
35
53

thanks for comment. but i got an error like this. what should i do on this error? > aa<-n6 %>% + gather(column, value, -c(id, PTMIINDT, PTMIINTM)) %>% + group_by(id, PTMIINDT, PTMIINTM) %>% + mutate(column = paste0(column, + str_pad(row_number(), width = 2, pad = 0))) %>% + spread(column, value) Error in str_pad(row_number(), width = 2, pad = 0) : could not find function "str_pad" – johnson kim Sep 10 '19 at 13:44
sorry about that, should have written `stringr::str_pad` in it's place. edited above. – Jon Spring Sep 10 '19 at 14:28
thanks for reply. i tried. but i got this messege again.. > aa<-n6 %>% + gather(column, value, -c(id, PTMIINDT, PTMIINTM)) %>% + group_by(id, PTMIINDT, PTMIINTM) %>% + mutate(column = paste0(column, + stringr::str_pad(row_number(), width = 2, pad = 0))) %>% + spread(column, value) Error: row_number() should only be called in a data context Call `rlang::last_error()` to see a backtrace – johnson kim Sep 10 '19 at 14:42

iago · Answer 3 · 2019-09-17T14:21:39.257

0

With the last version of tidyr (1.0.0, already on CRAN):

library(tidyr)
library(dplyr)
n6 %>% 
    group_by(id) %>% 
    dplyr::mutate(sbs = row_number()) %>% 
    pivot_wider(names_from = sbs, values_from = c(DGOTDIAG,DGOTDGGB))

# A tibble: 5 x 7
# Groups:   id [5]
  id         PTMIINDT PTMIINTM DGOTDIAG_1 DGOTDIAG_2 DGOTDGGB_1 DGOTDGGB_2
  <fct>         <dbl>    <dbl> <fct>      <fct>           <dbl>      <dbl>
1 ys00000001 20160101      614 R060       NA                  1         NA
2 ys00000002 20160101      640 S0090      A090                1          2
3 ys00000003 20160101      959 R42        NA                  1         NA
4 ys00000007 20160101     1111 S0600      NA                  1         NA
5 ys00000008 20160101     1253 R558       NA                  1         NA

edited Sep 17 '19 at 14:21

answered Sep 10 '19 at 09:11

iago

2,990
4
21
27

thanks for the comment. but i got an error like this. Error in get(nm, envir = fn, mode = "function") : object 'pivot_wider' of mode 'function' was not found what should i do? – johnson kim Sep 10 '19 at 13:46
Have you installed development version of `tidyr`? It seems that you do not have the function loaded. – iago Sep 10 '19 at 14:17
yapp! i installed the latest version of tidyr. Is it 0.8.3 version right?! – johnson kim Sep 10 '19 at 14:28
No, you should use development version in github. Instruction `devtools::install_github("tidyverse/tidyr")` should install it. – iago Sep 10 '19 at 14:33
I tried ! but still it has some problems. ac<-n6 %>% + group_by(id) %>% + mutate(sbs = row_number()) %>% + tidyr::pivot_wider(names_from = sbs, values_from = c(DGOTDIAG,DGOTDGGB)) Error: row_number() should only be called in a data context what should i do? thanks – johnson kim Sep 11 '19 at 02:12
I don't know. I didn't have that problem. What object is n6? Is it a data frame? Could you specify in the question how did you define/build n6? Maybe is a problem of an old version of `dplyr` (I have installed `0.8.3`) – iago Sep 12 '19 at 08:00
Looking at the comments of the answer of [this question](https://stackoverflow.com/questions/55506865/adding-a-new-column-to-a-data-frame-using-numbers-to-show-order-of-date-oldest), it could be an issue due to have the package `plyr` loaded. If this is the case, you may unload `plyr` or write `dplyr::mutate` instead of `mutate`. – iago Sep 12 '19 at 08:06

How to convert this vertical dataset to horizontal dataset?

3 Answers3