Reshaping the Data

Hi,
I'm using R and I want to reshape the data structure for further analysis. I want to reshape the table so that could only one row for each ID. I read that the library tidy can do that but I don't know how.

every help will be really appreciated

Thanks in advance

Greetings, I recommend that your provide the code that will generate a representation of the first table in your graphical example.

Sorry, I've forgot to post it. It looks like

df = data.frame(id=c(123,123),Method=c("angular","angular"),Colour=c("red","blue"))
df

PS: I want to do it dynamically so that if there is a new value or column, the code can handle that

Thx a lot

library(tidyverse)
df = data.frame(id=c(123,123),Method=c("angular","angular"),Colour=c("red","blue"))
df


df %>% group_by(id) %>% 
  mutate(entry = 1:n()) %>% 
  ungroup() %>% 
  pivot_wider(
    names_from = entry, 
    values_from = c(Method,Colour),
    names_sep = ''
  ) -> df2

df2

I give credit to Matt for sharing this useful pattern:

1 Like

Hi guys,

thank you. I will try the code and let you know.

Hi,

I've run the code but as you can see in the picture attached in my first post, the column method should be appeared only one since its values are the same (so the column Method2 should be not existed)

What if for some IDs method changes and others it still the same, what column names should exist for that circumstance?

..what you have been given is a generic solution that includes all possibilities.

Is it because you know that there are impossible combinations of your data , and that's why you ask for a further adjustment?

I'll give this a go, it's my first message, so sorry in advance for any mistakes:

df2 <- df %>% 
  mutate(r = 1:n()) %>% 
  pivot_wider(names_from=r, values_from=Colour, names_prefix = "Colour_")

EDIT: maybe better this way, but it really depends on how you want to treat different combinations of ID-Method:

df %>% 
  group_by(id, Method) %>% 
  mutate(r = 1:n()) %>% 
  pivot_wider(names_from=r, values_from=Colour, names_prefix = "Colour_")

Hello Klinklang,

Thanks for your reply , but I'm worried about the fact that I have one column more. For example, Size with the values 10 and 20.

How the provided code should look like if I have more columns (ID, Method, Colour, Size)?

I tried to modify pivot_wider like this: pivot_wider(names_from=r, values_from=c(Colour,Size), names_prefix = c("Colour_","Size_")

But it doesn't gave me the following structure:

ID Method Colour_1 Colour_2 Size_1 Size_2

Every help would be appreciated

Thank you

Hi,

Thanks for your suggestions and provided code. You are right, I should give you all the details. It was my fault.

Thanks a lot for your help.

If your question's been answered (even by you!), would you mind choosing a solution? It helps other people see which questions still need help, or find solutions if they have similar problems. Here’s how to do it:

I had forget another aspect of my challenge. After resolving it, I would click on "Solution".

I think you're good with just
pivot_wider(names_from=r, values_from=c("Colour","Size"))

Thank you Klingang for your help. I really appreciate