I want to create a new csv from the existing csv consist of multiple same columns but not sorted data

Question

I have a CSV with these data:

 List  Rank.A  List   Rank.B  List  Rank.C
   a      4      a      8      b      3
   b      5      e      5      e      9
   c      7      f      5      r      1

I want to create a new csv in which there is only a one-column with a name List with a unique value and there is 3 more columns of "Rank.A", "Rank.B", "Rank.C" in same list. Suppose if Rank.A not listed with any row of List than it display blank. I want data in this format

List    Rank.A    Rank.B    Rank.C
  a       4         8      
  b       5                   3
  c       7      
  e                 5         9
  f                 5
  r                           1

Can you please help me in that?

Maurits Evers · Answer 1 · 2020-09-25T02:53:04.087

A base R option using split.default (to split your data.frame by columns) and Reduce + merge to combine data into a single data.frame.

Reduce(
    function(x, y) merge(x, y, all = TRUE),
    split.default(df, rep(1:(ncol(df) / 2), each = 2)))
#    List Rank.A Rank.B Rank.C
#  1    a      4      8     NA
#  2    b      5     NA      3
#  3    c      7     NA     NA
#  4    e     NA      5      9
#  5    f     NA      5     NA
#  6    r     NA     NA      1

Note that this assumes that you always have pairs of columns (List, Rank.x) in your original data.

Sample data

df <- read.table(text =
"List  Rank.A  List   Rank.B  List  Rank.C
   a      4      a      8      b      3
   b      5      e      5      e      9
   c      7      f      5      r      1", header = T, check.names = F)

I want to create a new csv from the existing csv consist of multiple same columns but not sorted data

1 Answers1

Sample data