R how to pivot a table (with two columns) with categories of different length?

user_10214739 0
Viewed 94

I have a table, shown as below. I need to pivot it so that data is spread horizontally and store it in a txt file with sep = space. But since the number of items per category differs, I am not sure what data structure is the best to use.

I have been stuck with this for 3 days. Could anyone please kindly help me?

Many thanks

data.frame(
 name=c(9073050,9073050,9073050,9073050,9073050,9073050,9073050,9073050,9079078,9079078,9085376,9085376,9085376,9085376,9085376),
 sequence=c(1,15, 1,10,1, 4, 16,7,18,19,22,14,23,14,24)
)

    name sequence
1  9073050        1
2  9073050       15
3  9073050        1
4  9073050       10
5  9073050        1
6  9073050        4
7  9073050       16
8  9073050        7
9  9079078       18
10 9079078       19
11 9085376       22
12 9085376       14
13 9085376       23
14 9085376       14
15 9085376       24

9073050       1      15       1      10       1       4      16       7
9079078      18      19      
9085376      22      14      23      14      24
txt reshape pivot r list
3 Answers

You can directly store it in a .txt file like this

sink('Output.txt')
for (id in unique(df$name)){
  seq <- df[which(df$name == id), 'sequence']
  cat(id, seq, '\n')
}
sink()

Output.txt then looks like this:

9073050 1 15 1 10 1 4 16 7 
9079078 18 19 
9085376 22 14 23 14 24

And for completeness, if you also want to store it within R a list is probably the most convenient data structure:

lst <- list()
for (id in unique(df$name)){
  seq <- df[which(df$name == id), 'sequence']
  lst[[as.character(id)]] = seq
}

which yields

> lst
$`9073050`
[1]  1 15  1 10  1  4 16  7

$`9079078`
[1] 18 19

$`9085376`
[1] 22 14 23 14 24
user_179428710

Consider reshape to transform long to wide format after creating a counter variable by group:

long_df <- data.frame(
 name = c(rep(9073050, 8), rep(9079078,2), rep(9085376,5)),
 sequence = c(1,15,1,10,1,4,16,7,18,19,22,14,23,14,24)
)

wide_df <- transform(
    long_df,
    counter = ave(sequence, name, FUN=seq_along)
) |> reshape(
    idvar = "name",
    v.names = "sequence",
    timevar = "counter",
    sep = "_",
    direction = "wide"
)

# EXPORT TAB DELIMITED FILE WITH NO HEADERS
write.table(
    wide_df, file="output.txt", na="", sep="\t",
    row.names=FALSE, col.names=FALSE
)

# 9073050   1   15  1   10  1   4   16  7
# 9079078   18  19                      
# 9085376   22  14  23  14  24
user_14224510

Here is an alternative approach using pivot_wider:

library(dplyr)
library(tidyr)

df1 <- df %>% 
  group_by(name) %>% 
  mutate(id = row_number()) %>% 
  pivot_wider(
    names_from = id,
    values_from = sequence
  ) 

write.table(df1, file = "df1.txt", sep = "\t",
            row.names = FALSE, col.names = FALSE)


9073050 1   15  1   10  1   4   16  7
9079078 18  19  NA  NA  NA  NA  NA  NA
9085376 22  14  23  14  24  NA  NA  NA
user_133216470
Related