Formatting a table in R

Question

I have data like

            147 147 231 231
2011_15_1   99  106 152 156
2011_15_2   99  102 150 156
2011_15_3   99  99  152 156
2011_15_7   99  106 152 156

and I would like to reformat it to become:

            147     231
2011_15_1   99      152
            106     156
2011_15_2   99      150
            102     156
2011_15_3   99      152
            99      156
2011_15_7   99      152
            106     156

I have tried reading the table into R and using the 'reshape2' package and the melt() function, but I am unsure how to make it collapse columns with the same name into narrow-form.

Can anyone help?

Answer 1

You can use dplyr + tidyr.

library(tidyr)
library(dplyr)

df %>%
  mutate(date = row.names(.)) %>%
  gather(key, value, -date) %>%
  arrange(date) %>%
  mutate(key = gsub("[.]1$", "", key)) %>%
  group_by(date, key) %>%
  mutate(id = 1:n()) %>%
  spread(key, value) %>%
  select(-id)

Result:

# A tibble: 8 x 3
# Groups:   date [4]
       date  X147  X231
*     <chr> <int> <int>
1 2011_15_1    99   152
2 2011_15_1   106   156
3 2011_15_2    99   150
4 2011_15_2   102   156
5 2011_15_3    99   152
6 2011_15_3    99   156
7 2011_15_7    99   152
8 2011_15_7   106   156

Note:

1. read.table does two conversions to the column names when reading in the data: attaching X in front and .1 as a suffix for repeated column names. This is because neither pure numbers nor identical column names are valid.
2. What I basically did was first convert rownames to the first column date,
3. transform the data from wide to long form (gather),
4. remove all .1 suffixes in the key column,
5. added an id that is group_by date and key to make each row unique,
6. then finally transform the data back to wide form (spread) using the new key and value columns.

Data:

df = read.table(text="         147 147 231 231
                2011_15_1   99  106 152 156
                2011_15_2   99  102 150 156
                2011_15_3   99  99  152 156
                2011_15_7   99  106 152 156", header = TRUE)