Reshaping the Data

[CedSoft]

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

[nirgrahamuk]

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

[CedSoft]

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

[nirgrahamuk]

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:

Combining rows with the same value into one column

Hi @beginnersluke, It is strongly recommended to keep your data as "long" for most tasks. It makes it much easier to plot, clean, etc. However, since I don't know how you plan to use the data, here is a solution anyway: library(tidyverse) library(lubridate) data <- tribble( ~Name, ~Date, ~Systolic, ~Diastolic, 'John', '1/1/10', 132, 92, 'Amy', '1/2/10', 120, 80, 'John', '2/2/10', 135, 92, 'Amy', '2/2/10', 82, 118, 'Nick', '2/14/10', 108, 72, 'Amy', '3/1/10', 122, 80, 'John', '3/3/10', 128, 88 ) data %>% mutate(Date = mdy(Date)) %>% arrange(Name, Date) %>% group_by(Name) %>% mutate(Visit = 1:n()) %>% ungroup(…

[CedSoft]

Hi guys,

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

[CedSoft]

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)

[nirgrahamuk]

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?

[klingklang]

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_")

[CedSoft]

@klingklang

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

[CedSoft]

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.

[mfherman]

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:

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] …

[CedSoft]

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

[klingklang]

@CedSoft

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

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

[CedSoft]

Thank you Klingang for your help. I really appreciate