vertical to horizontal dataframe

General
1

Hallo R Studio Community,

I am trying to prepare properly my data to be analyzed and I am stucked in this issue.

I have dataframe similar to this reprex

df = data.frame(pt_name=c("mario","mario","mario","luigi","luigi","luigi","toad","toad","toad"),
                pod=rep(c(1,2,3),3),
                crea=c(0.4,0.5,0.4,1,2,2.5,4,4.5,6),
                other_value=rep(c(NA),9))

When I call df I obtain

  pt_name pod crea other_value
1   mario   1  0.4          NA
2   mario   2  0.5          NA
3   mario   3  0.4          NA
4   luigi   1  1.0          NA
5   luigi   2  2.0          NA
6   luigi   3  2.5          NA
7    toad   1  4.0          NA
8    toad   2  4.5          NA
9    toad   3  6.0          NA

I would like to convert in this way

df_wish = data.frame(pt_name =c("mario","luigi","toad"),
                     crea_pod_1 = c(0.4, 1, 4),
                     crea_pod_2 = c(0.5, 2, 4.5),
                     crea_pod_3 = c(0.4, 2.5, 6),
                     other_value_pod_1 = rep(c(NA),3),
                     other_value_pod_2 = rep(c(NA),3),
                     other_value_pod_3 = rep(c(NA),3))

So that I can obtain

 pt_name crea_pod_1 crea_pod_2 crea_pod_3 other_value_pod_1 other_value_pod_2 other_value_pod_3
1   mario        0.4        0.5        0.4                NA                NA                NA
2   luigi        1.0        2.0        2.5                NA                NA                NA
3    toad        4.0        4.5        6.0                NA                NA                NA

is there any simple function to do this? ( I am beginner in R)
Thanks in advance!

2

I've always been bad at reshaping. But I think this will make you start:

> df = data.frame(pt_name=c("mario","mario","mario","luigi","luigi","luigi","toad","toad","toad"),
+                 pod=rep(c(1,2,3),3),
+                 crea=c(0.4,0.5,0.4,1,2,2.5,4,4.5,6),
+                 other_value=rep(c(NA),9))
> 
> reshape(data = df,
+         timevar = "pod",
+         idvar = "pt_name",
+         direction = "wide",
+         sep = "_")
  pt_name crea_1 other_value_1 crea_2 other_value_2 crea_3 other_value_3
1   mario    0.4            NA    0.5            NA    0.4            NA
4   luigi    1.0            NA    2.0            NA    2.5            NA
7    toad    4.0            NA    4.5            NA    6.0            NA

Hope this helps.

1 Like
3

It works perfectly on the reprex, but not in the actual dataframe ( it returns all NA).
I investigate on it! Thanks a lot!

4

On the actual dataframe the code gives an unexpected result:

pt_name                 `crea_c( 1, 2, 3)` `other_value_c( 1, 2, 3)`
   <chr>                    <dbl>                 <dbl>
mario                          NA                      NA
 luigi                         NA                      NA                                           
toad                           NA                      NA

It seems that it did not split the variables by pod .
Do you know what could be the issue?

Thanks again!!

5

Here's how to do it with tidyr::pivot_wider(). Maybe you can test whether this works on your actual data frame?

df <- data.frame(pt_name = c("mario","mario","mario","luigi","luigi","luigi","toad","toad","toad"),
                 pod = rep(c(1, 2, 3), 3),
                 crea = c(0.4, 0.5, 0.4, 1, 2, 2.5, 4, 4.5, 6),
                 other_value = rep(c(NA), 9))

tidyr::pivot_wider(df, names_from = pod, names_sep = "_pod_", values_from = c(crea, other_value))
#> # A tibble: 3 x 7
#>   pt_name crea_pod_1 crea_pod_2 crea_pod_3 other_value_pod~ other_value_pod~
#>   <chr>        <dbl>      <dbl>      <dbl> <lgl>            <lgl>           
#> 1 mario          0.4        0.5        0.4 NA               NA              
#> 2 luigi          1          2          2.5 NA               NA              
#> 3 toad           4          4.5        6   NA               NA              
#> # ... with 1 more variable: other_value_pod_3 <lgl>

Created on 2020-06-15 by the reprex package (v0.3.0)

6

great, thanks! it worked out.

7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.