2
\$\begingroup\$

I need to merge eve/odd rows on a data set.

Example:

      match team count points
    1     1  ARG    11     21
    2     1  ENG     9     19
    3     2  CAN     8     15
    4     2  USA     8     17
    5     3  FRA     7      7
    6     3  NZL     8     36

To:

      match team1 t1p t1s team2 t2p t2s
    1     1   ARG  11  21   ENG   9  19
    2     2   CAN   8  15   USA   8  17
    3     3   FRA   7   7   NZL   8  36

This is my current solution:

odd <- all_scores[seq(1, nrow(all_scores), 2),]
colnames(odd) <- c("match", "team1", "t1p", "t1s")
even <- all_scores[seq(2, nrow(all_scores), 2),]
colnames(even) <- c("match", "team2", "t2p", "t2s")
new_scores <- merge(odd, even)

Is there an easier, more space efficienct, or more R-like way to do this?

\$\endgroup\$
3
\$\begingroup\$

Have a look at the reshape function. To use the jargon, your data is in "long" format and you want to turn it to a "wide" format. Before using reshape, you will need to add a column of 1, 2, 1, 2, etc. so it can be used as the "time" column. You can do that on the fly using the transform function. In all:

new_scores <- reshape(transform(all_scores, i = 1:2),
                      idvar = "match", timevar = "i", direction = "wide")

#   match team.1 count.1 points.1 team.2 count.2 points.2
# 1     1    ARG      11       21    ENG       9       19
# 3     2    CAN       8       15    USA       8       17
# 5     3    FRA       7        7    NZL       8       36
\$\endgroup\$
  • 1
    \$\begingroup\$ The reshape is nice, but there is a way to do this without even adding another column, assuming that nrow is even and the data are in a data frame called dat1: merge(dat1[seq(1,nrow(dat1)-1,2),], dat1[seq(2,nrow(dat1),2),],by='match',all=TRUE) \$\endgroup\$ – Edward Carney Mar 5 '16 at 1:56
  • \$\begingroup\$ It's an interesting approach and thank you for sharing it with us. I'm just curious why you point at the fact that I add a column as if it was a bad thing? One a similar note, I could point that you wrote dat1 four times and used 8 function calls when I wrote all_scores only once and used only three function calls. \$\endgroup\$ – flodel Mar 5 '16 at 2:24
  • \$\begingroup\$ Good point w.r.t. repeated mention of dat1 and function calls. I didn't mean to imply that adding a column is a "bad thing," by any means. Mostly I did it for fun to see if it were possible to do it another way in just one line. Thanks for the reply. \$\endgroup\$ – Edward Carney Mar 5 '16 at 2:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.