Create new dataframe (long to wide)

[Boris]

Hi all,

I want to create a new dataframe where every row is 1 version (0-100 versions) , and all the attribute information (Att1 Att2 Att3) of this version are extra columns of that specific row. So create a new wide dataframe from a long dataframe.

Could you help me out? I hope I was clear enough.

Best,
Boris

[woodward]

Check out the pivot_wider function in the tidyr package, which is part of tidyverse.

[Boris]

Thank you. Unfortunately i'm not able to get the code working.

df_orderd <- designcbc_now %>%
pivot_wider(names_from = version,
values_from = c(att1, att2, att3),
values_fn = list)

One of the problems is, is that I lose the column "version" and I need that column to match it with another dataframe.

Is it also possible to create matrices or vectors of the attribute columns with the version number as condition?

[siddharthprabhu]

Your syntax isn't quite right. Please read the documentation for pivot_longer() to learn how to use it correctly.

Here is a working example using the first 6 rows of your data as a sample.

library(tidyverse)

data <- tribble(~ version, ~ task, ~ concept, ~ attr1, ~ attr2, ~ attr3,
                0, 1, 1, 1, 1, 2,
                0, 1, 2, 3, 2, 1,
                0, 1, 3, 2, 3, 3,
                1, 1, 1, 1, 1, 2,
                1, 1, 2, 3, 2, 3,
                1, 1, 3, 2, 3, 2)

pivot_longer(data, cols = c(attr1, attr2, attr3))
#> # A tibble: 18 x 5
#>    version  task concept name  value
#>      <dbl> <dbl>   <dbl> <chr> <dbl>
#>  1       0     1       1 attr1     1
#>  2       0     1       1 attr2     1
#>  3       0     1       1 attr3     2
#>  4       0     1       2 attr1     3
#>  5       0     1       2 attr2     2
#>  6       0     1       2 attr3     1
#>  7       0     1       3 attr1     2
#>  8       0     1       3 attr2     3
#>  9       0     1       3 attr3     3
#> 10       1     1       1 attr1     1
#> 11       1     1       1 attr2     1
#> 12       1     1       1 attr3     2
#> 13       1     1       2 attr1     3
#> 14       1     1       2 attr2     2
#> 15       1     1       2 attr3     3
#> 16       1     1       3 attr1     2
#> 17       1     1       3 attr2     3
#> 18       1     1       3 attr3     2

^{Created on 2020-05-05 by the reprex package (v0.3.0)}

[Boris]

Many thanks for the clear example. However, I read the file multiple times but still not able to create a wide dataframe.

Could you help me a little bit further please?

[siddharthprabhu]

Sorry, I misread your question. You want to convert a long data frame to a wide data frame (the solution I gave was for the inverse transformation).

Can you please post a sample of what your long data frame looks like?

[Boris]

No worries, it is like the picture above. Is that enough for you?

I'm now trying this syntax:
pivot_wider(data, names_from = concept, values_from = c(attr1, attr2, attr3))

[siddharthprabhu]

If the picture is your original data, then it is already in wide form. May I ask you to post a sample of what your expected output should look like?

You could take the first 3 records from your data frame that have version 0 and illustrate what your table should look like after the transformation.

[Boris]

I need all the attributes information as columns, so I only have 1 version number for each row.
Then I can combine my two datasets based on version number.

I hope this is a good explanation.

Maybe a looped matrix can work?

[siddharthprabhu]

OK, that helped. I think you want something like this. Here I've prefixed the resulting columns with t and c (for task and concept respectively) and their values from the original data frame.

library(tidyverse)

data <- tribble(~ version, ~ task, ~ concept, ~ attr1, ~ attr2, ~ attr3,
                0, 1, 1, 1, 1, 2,
                0, 1, 2, 3, 2, 1,
                0, 1, 3, 2, 3, 3,
                1, 1, 1, 1, 1, 2,
                1, 1, 2, 3, 2, 3,
                1, 1, 3, 2, 3, 2)

data %>% 
  pivot_longer(cols = c(attr1, attr2, attr3)) %>% 
  mutate(new_name = str_c("t", task, "_c", concept, "_", name)) %>% 
  pivot_wider(id_cols = version, names_from = new_name, values_from = value)
#> # A tibble: 2 x 10
#>   version t1_c1_attr1 t1_c1_attr2 t1_c1_attr3 t1_c2_attr1 t1_c2_attr2
#>     <dbl>       <dbl>       <dbl>       <dbl>       <dbl>       <dbl>
#> 1       0           1           1           2           3           2
#> 2       1           1           1           2           3           2
#> # ... with 4 more variables: t1_c2_attr3 <dbl>, t1_c3_attr1 <dbl>,
#> #   t1_c3_attr2 <dbl>, t1_c3_attr3 <dbl>

^{Created on 2020-05-05 by the reprex package (v0.3.0)}

[Boris]

Outstanding! Thank you very much, it works perfect.

[siddharthprabhu]

Great! If I solved your problem, would you mind marking my post as a solution?

FAQ: How do I mark a solution?

If your question has been answered, don't forget to mark the solution! How do I mark a solution? Find the reply you want to mark as the solution and look for the row of small gray icons at the bottom of that reply. Click the one that looks like a box with a checkmark in it: [image] Hovering over the mark solution button shows the label, "Select if this reply solves the problem". If you don't see the mark solution button, try clicking the three dots button ( ••• ) to expand the full set of options. When a solution is chosen, the icon turns green and the hover label changes to: "Unselect if this reply no longer solves the problem". Success! [solution_reply_author] …