Transforming Dataframe in R...How to Categorize based on unique row values


#1

I have a data frame that looks like this:
Name Subject Marks
A Biology 55
B maths 70
A Physics 30

I want to transform it into
Name Subject1 Subject2 Marks1 Marks2
A Biology Physics 55 30
B maths NA 70 NA

Dcast function does not work as it has duplicate records. How to achieve this using R


#2

Hope this will help you :wink:

# Library -----------------------------------------------------------------

library(tidyverse)


# Script ------------------------------------------------------------------

# example data frame
df <- data.frame(name = c("A", "B", "A"), subject = c("Biology", "Maths", "Physics"), marks = c(50, 70, 30))

# Create a column reference (id) for the join
df <- df %>%
  arrange(name,subject) %>% 
  group_by(name) %>% 
  mutate(id = row_number())

# Create two dataframes for each id
df_1 <- df %>% 
  filter(id == 1) %>% 
  select(-id) %>% 
  rename(subject1 = subject,
         marks1 = marks)
  
df_2 <- df %>% 
  filter(id == 2) %>% 
  select(-id) %>% 
  rename(subject2 = subject,
         marks2 = marks)
  
# Create a dataframe with all the unique names
df_names <- df %>% 
  group_by(name) %>% 
  summarise()

# Join all
df_join <- df_names %>% 
  left_join(df_1, by = c("name")) %>% 
  left_join(df_2, by = c("name")) %>% 
  select(name, subject1, subject2, marks1, marks2)

# Final dataframe
df_join

#3

Thanks Antonio… :smiley:


#4

Hi Antonio,
This works fine, but i have 1000s of id…
Should we create that number of data frames to solve it?
Thanks in advance