CODEWITHSUNDEEP
rtext
iGada
iGada

Reputation: 633

How to transform a given table in R?

I have a dataset of the form:

Var1 Freq
A     16
B     15
C     11
D     11
E     2
F     1

My goal is to get an OUTPUT of the following form:

cat1  cat2  cat3  cat4  cat5
 A     B     C,D    E     F
 16    15     11    2     1

where cat1, ..., cat5 are the name of variables. I appreciate for your help in advance!

Upvotes: 0

Views: 66

Answers (3)

Onyambu
Onyambu

Reputation: 79208

 with(aggregate(Var1 ~ Freq, df, paste, collapse = ","),
      setNames(rbind.data.frame(Var1, Freq)[, order(Var1)], paste0("cat", seq(Freq))))

  cat1 cat2 cat3 cat4 cat5
1    A    B  C,D    E    F
2   16   15   11    2    1

Upvotes: 2

Claudio Secco
Claudio Secco

Reputation: 41

# considering your data is in a data.frame called df
# let's create it
var1 <- LETTERS[1:6]
Freq <- c(16, 15, 11, 11, 2, 1)
df <- data.frame(var1, Freq, stringsAsFactors = FALSE)

# function to join var1
join <- function(x) {
  index <- which(df$Freq == x)
  paste(df$var1[index], collapse = ', ')
}

# get unique Freq and its length
unique_freq <- unique(df$Freq)
l <- length(unique_freq)

# create summarised var1
summarised_var <- rep("", l)
for (i in 1:l) {
  summarised_var[i] <- join(unique_freq[i])  
}

# create grouped data.frame
grouped_df <- data.frame(summarised_var, unique_freq, stringsAsFactors = FALSE)

# create a transposed data.frame to get rows into columns
transposed_df <- t(grouped_df)

# create columns names (variables names)
col_names <- paste0('cat', 1:nrow(grouped_df))

# rename columns
colnames(transposed_df) <- col_names

# transposed_df is your output

Upvotes: -1

NotThatKindODr
NotThatKindODr

Reputation: 719

Try this out

library(tidyverse)
  df <- tribble(~Var1, ~Freq,
        "A",     16,
        "B",     15,
        "C",     11,
        "D",     11,
        "E",     2,
        "F",     1) %>% 
  group_by(Freq) %>% 
  summarise(Var1 = paste(Var1, collapse = ",")) %>% 
  arrange(Var1) %>% 
  as.matrix() %>% 
  t() %>% as_tibble(.name_repair = "universal") %>% 
  mutate_all(~str_trim(.)) %>% 
  arrange(desc(...1))

colnames(df) <- paste0("cat", 1:length(df))

Upvotes: 0

Related Questions