2
$\begingroup$

I have received a dataset in text file with the following format

col1=datac1r1,col2=datac2r1,col3=datac3r1
col1=datac1r2,col2=datac2r2,col3=datac3r2
col1=datac1r3,col2=datac2r3,col3=datac3r3
col1=datac1r4,col2=datac2r4,col3=datac3r4

Each row is a unique entry, with columns separated by comma, just that the column name is repeated in each element.

I need to parse this in R and analyze it. I have worked with csv files extensively, but I have never seen this format before.

Is it a std. format I can import it in? Or do I need to write a script to convert it into a csv format?

$\endgroup$
4
$\begingroup$

Using Miller (https://github.com/johnkerl/miller)

mlr --ocsv unsparsify input.txt

you will have this CSV

col1,col2,col3
datac1r1,datac2r1,datac3r1
datac1r2,datac2r2,datac3r2
datac1r3,datac2r3,datac3r3
datac1r4,datac2r4,datac3r4
$\endgroup$
5
  • 1
    $\begingroup$ Sorry for the confusion. I didn't intend to have () there. $\endgroup$ – tallharish Jul 19 '19 at 15:56
  • $\begingroup$ Ok, I have edited my script $\endgroup$ – aborruso Jul 19 '19 at 19:41
  • $\begingroup$ @tallharish please try and make me know if now it works $\endgroup$ – aborruso Jul 20 '19 at 7:29
  • $\begingroup$ @tallharish please let me know if it works $\endgroup$ – aborruso Jul 24 '19 at 6:36
  • $\begingroup$ @tallharish did you try? $\endgroup$ – aborruso Aug 20 '19 at 8:44
0
$\begingroup$

In a perfect-world, miller package (Aborruso's answer) would do it. However, I needed to clean the dataset as well. I wrote the following function that does it all. The key pieces are the extract_header() and remove_header() functions.

clean_dataset <- function(dataset) {
  #Convert all elements into character (for now)
  dataset[] = lapply(dataset, as.character)

  #Come up with header
  extract_header = function(x) {
    strsplit(x,"=")[[1]][1]
  }

  names(dataset) = lapply(dataset[1,], extract_header)

  #Remove headers from all elements
  remove_header = function(x) {
    strsplit(x,"=")[[1]][2]
  }

  dataset = as.data.frame(apply(dataset, MARGIN=c(1,2), remove_header))
  dataset[!is.na(names(dataset))]
}

dataset <- clean_dataset(dataset)
$\endgroup$
1
  • $\begingroup$ If you could share your "real" input file I'd like to try to process it $\endgroup$ – aborruso Aug 21 '19 at 7:48

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.