Reshaping a dataframe with a huge character column into several numeric columns in R

Question

I have a dataset with a complex column results as (character class).

Data sample is here.

date_agenda id_question id_event results
2020-09-04  202009043   7426      "2:1:3|3:1:3|4:8:4|5:1:4|6:7:0|7:7:4|8:7:3|9:3:0|10:8:4|11:1:0|12:8:4|13:8:4|14:8:1|15:1:3|16:1:0|17:1:3|18:1:3|"
2020-09-04  202009044   7427      "2:1:3|3:1:3|4:8:4|5:1:4|6:7:0|7:7:4|8:7:3|9:3:0|10:8:4|11:1:0|12:8:4|13:8:4|14:8:3|15:1:3|16:1:0|17:1:3|18:1:3|"
2020-09-04  202009046   7428      "2:1:1|3:1:1|4:8:4|5:1:4|6:7:0|7:7:4|8:7:1|9:3:0|10:8:4|11:1:0|12:8:4|13:8:4|14:8:3|15:1:1|16:1:0|17:1:1|18:1:1|"

I would like to convert results and the whole dataframe from wide to long format, splitting the last column results into three numeric columns.

For example, 2:1:3 is one event in results and there hundreds of such events in this column.

My desired output:

date_agenda id_question id_event mp_id mp_fraction result
2020-09-04  202009043   7426       2         1        3
2020-09-04  202009043   7426       3         1        3
2020-09-04  202009043   7426       4         8        4
2020-09-04  202009043   7426       5         1        4
2020-09-04  202009043   7426       6         7        0

Basically, one row in the initial wide format (4 columns) will be converted into hundreds of rows in the long format (6 columns).

I understand that it should be done with pivot_longer, pivot_wider (or gather and spread) and maybe melt, but not sure that I have the logic now. Whether I should split the character first into hundreds of columns and then make it wide or something else.

Appreciate tips! Thanks!

Ehm, that's not exactly what is meant here by reproducible. https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — s_baldur, Sep 16 '20 at 08:36
@sindri_baldur, I got the answer, thank you so much for your time! — Anakin Skywalker, Sep 16 '20 at 08:41

score 1 · Accepted Answer · answered Sep 16 '20 at 08:28

We can use separate_rows to split results on "|" into different rows and separate to split on ":" in three different columns.

library(dplyr)
library(tidyr)

df %>%
  separate_rows(results, sep = '\\|') %>%
  #To remove empty values generated from last "|"
  filter(results != '') %>%
  separate(results,c("mp_id", "mp_fraction", "result"), sep = ":",convert = TRUE)

# A tibble: 51 x 6
#   date_agenda id_question id_event mp_id mp_fraction result
#   <chr>             <int>    <int> <int>       <int>  <int>
# 1 2020-09-04    202009043     7426     2           1      3
# 2 2020-09-04    202009043     7426     3           1      3
# 3 2020-09-04    202009043     7426     4           8      4
# 4 2020-09-04    202009043     7426     5           1      4
# 5 2020-09-04    202009043     7426     6           7      0
# 6 2020-09-04    202009043     7426     7           7      4
# 7 2020-09-04    202009043     7426     8           7      3
# 8 2020-09-04    202009043     7426     9           3      0
# 9 2020-09-04    202009043     7426    10           8      4
#10 2020-09-04    202009043     7426    11           1      0
# … with 41 more rows

Perfect solution, Ronak Shan! – Anakin Skywalker Sep 16 '20 at 08:38 — Anakin Skywalker, Sep 16 '20 at 08:38

score 1 · Answer 2 · answered Sep 16 '20 at 09:41

and the obligatory data.table solution ;-)

library(data.table)
DT <- data.table::fread('date_agenda id_question id_event results
2020-09-04  202009043   7426      "2:1:3|3:1:3|4:8:4|5:1:4|6:7:0|7:7:4|8:7:3|9:3:0|10:8:4|11:1:0|12:8:4|13:8:4|14:8:1|15:1:3|16:1:0|17:1:3|18:1:3|"
2020-09-04  202009044   7427      "2:1:3|3:1:3|4:8:4|5:1:4|6:7:0|7:7:4|8:7:3|9:3:0|10:8:4|11:1:0|12:8:4|13:8:4|14:8:3|15:1:3|16:1:0|17:1:3|18:1:3|"
2020-09-04  202009046   7428      "2:1:1|3:1:1|4:8:4|5:1:4|6:7:0|7:7:4|8:7:1|9:3:0|10:8:4|11:1:0|12:8:4|13:8:4|14:8:3|15:1:1|16:1:0|17:1:1|18:1:1|"')

#first, split on pipe
ans <- DT[, list(V2 = unlist(strsplit(results, "[|]", perl = TRUE))), 
   by=.(date_agenda, id_question, id_event) ]
#the split on colon and drop the temp column
ans[, c("mp_id", "mp_fraction", "result") := tstrsplit( V2, ":") ][, -"V2" ]

output

#     date_agenda id_question id_event mp_id mp_fraction result
#  1:  2020-09-04   202009043     7426     2           1      3
#  2:  2020-09-04   202009043     7426     3           1      3
#  3:  2020-09-04   202009043     7426     4           8      4
#  4:  2020-09-04   202009043     7426     5           1      4
#  5:  2020-09-04   202009043     7426     6           7      0
#  6:  2020-09-04   202009043     7426     7           7      4
#  7:  2020-09-04   202009043     7426     8           7      3
#  8:  2020-09-04   202009043     7426     9           3      0
#  9:  2020-09-04   202009043     7426    10           8      4
# 10:  2020-09-04   202009043     7426    11           1      0
# 11:  2020-09-04   202009043     7426    12           8      4
# 12:  2020-09-04   202009043     7426    13           8      4
# 13:  2020-09-04   202009043     7426    14           8      1
# 14:  2020-09-04   202009043     7426    15           1      3
# 15:  2020-09-04   202009043     7426    16           1      0
# 16:  2020-09-04   202009043     7426    17           1      3
# 17:  2020-09-04   202009043     7426    18           1      3
# 18:  2020-09-04   202009044     7427     2           1      3
# 19:  2020-09-04   202009044     7427     3           1      3
# 20:  2020-09-04   202009044     7427     4           8      4
# 21:  2020-09-04   202009044     7427     5           1      4
# 22:  2020-09-04   202009044     7427     6           7      0
# 23:  2020-09-04   202009044     7427     7           7      4
# 24:  2020-09-04   202009044     7427     8           7      3
# 25:  2020-09-04   202009044     7427     9           3      0
# 26:  2020-09-04   202009044     7427    10           8      4
# 27:  2020-09-04   202009044     7427    11           1      0
# 28:  2020-09-04   202009044     7427    12           8      4
# 29:  2020-09-04   202009044     7427    13           8      4
# 30:  2020-09-04   202009044     7427    14           8      3
# 31:  2020-09-04   202009044     7427    15           1      3
# 32:  2020-09-04   202009044     7427    16           1      0
# 33:  2020-09-04   202009044     7427    17           1      3
# 34:  2020-09-04   202009044     7427    18           1      3
# 35:  2020-09-04   202009046     7428     2           1      1
# 36:  2020-09-04   202009046     7428     3           1      1
# 37:  2020-09-04   202009046     7428     4           8      4
# 38:  2020-09-04   202009046     7428     5           1      4
# 39:  2020-09-04   202009046     7428     6           7      0
# 40:  2020-09-04   202009046     7428     7           7      4
# 41:  2020-09-04   202009046     7428     8           7      1
# 42:  2020-09-04   202009046     7428     9           3      0
# 43:  2020-09-04   202009046     7428    10           8      4
# 44:  2020-09-04   202009046     7428    11           1      0
# 45:  2020-09-04   202009046     7428    12           8      4
# 46:  2020-09-04   202009046     7428    13           8      4
# 47:  2020-09-04   202009046     7428    14           8      3
# 48:  2020-09-04   202009046     7428    15           1      1
# 49:  2020-09-04   202009046     7428    16           1      0
# 50:  2020-09-04   202009046     7428    17           1      1
# 51:  2020-09-04   202009046     7428    18           1      1
#     date_agenda id_question id_event mp_id mp_fraction result

Thank you! Never use it, but will be helpful for the community! — Anakin Skywalker, Sep 16 '20 at 13:35

Reshaping a dataframe with a huge character column into several numeric columns in R

2 Answers2