0

I have a tab delimited text file that I just can't seem to convert to a data frame.

Here's the txt file, tab-delimited, pretty standard:

Dispenser Zip\tPrescriber Zip\tDate Filled\tDate Written\tNDC Number\tDrug Name\tQuantity Dispensed\tNew/Refill Status\tDays Supply\tPayment Type\tPatient Age Group\tPatient Gender\tPatient Zip\tDrug Category\tDEA Schedule
535\t537\t4/1/2017 12:00:00 AM\t3/24/2017 12:00:00 AM\t59417010710\tLisdexamfetamine Dimesylate\t30.00\tNew\t30.00\tCommercial Insurance\t20-29\tFemale\t535\tStimulant\tSchedule II
535\t535\t4/1/2017 12:00:00 AM\t10/20/2016 12:00:00 AM\t16729013716\tClonazepam\t30.00\tRefill\t30.00\tCommercial Insurance\t60-69\tMale\t535\tBenzodiazepines\tSchedule IV
541\t543\t4/1/2017 12:00:00 AM\t2/13/2017 12:00:00 AM\t00228300350\tClonazepam\t30.00\tRefill\t30.00\tMedicaid\t60-69\tMale\t541\tBenzodiazepines\tSchedule IV
541\t541\t4/1/2017 12:00:00 AM\t11/15/2016 12:00:00 AM\t67253090111\tAlprazolam\t90.00\tRefill\t30.00\tMedicaid\t50-59\tFemale\t541\tBenzodiazepines\tSchedule IV
541\t530\t4/1/2017 12:00:00 AM\t2/28/2017 12:00:00 AM\t00071101768\tPregabalin\t60.00\tRefill\t30.00\tCommercial Insurance\t50-59\tFemale\t544\tOther\tSchedule V

I've tried:

my.data <- read.table(file = '//myfolder/my_text_file.txt',    # file is in a data folder in my working directory
                 sep = '\t',                  # file is tab--delimited
                 header = TRUE,               # the first row of the data is a header row
                 stringsAsFactors = FALSE)

As well as:

my.data <- read.delim( "//myfolder/my_text_file.txt", header = TRUE, sep = "\t")

And finally, I've tried:

my_text_file <- str_split_fixed(my_text_file$names, "\t", 2)

And, yet, no matter what I do, my code outputs this:

enter image description here

I can't seem to figure out why it's not recognizing the tab delimiter.

halfer
  • 19,824
  • 17
  • 99
  • 186
DiamondJoe12
  • 1,879
  • 7
  • 33
  • 81
  • 3
    how are you displaying the file? Are those actual tabs being displayed as `\t` or are they in fact the characters `\t`? Can you post the file somewhere? (If someone had mistakenly output the file with `\\t`rather than `\t` as the delimiter, this is what would happen ... – Ben Bolker Jul 09 '20 at 21:35
  • Ben, I think you are correct, it looks like the output file is displaying the characters \t. In this case, what do you recommend? The file is huge and not sure if I can post. Let me try .. – DiamondJoe12 Jul 10 '20 at 14:36
  • I've included a portion of the file here. – DiamondJoe12 Jul 10 '20 at 14:42
  • the answer in the duplicate linked by @akrun should work, I think. If it doesn't work, please edit your question to indicate that it doesn't (be specific about what's still not working ...) – Ben Bolker Jul 10 '20 at 15:40
  • Edit question above. – DiamondJoe12 Jul 10 '20 at 17:03
  • it's really counterintuitive, but try `str_split_fixed(my_text_file$names, "\\t", 2)` (double backslash) – Ben Bolker Jul 10 '20 at 17:55
  • Shoot.. still no luck. I also tried r"\t" and that didn't work either. – DiamondJoe12 Jul 10 '20 at 18:28
  • In fact, I can't even replace the \t using:my_text_file$names<-str_replace_all(my_text_file$names, '\t',' replacement') – DiamondJoe12 Jul 10 '20 at 19:07
  • 1
    it would be most useful if you could post a **link to your actual file** ... your `str_split_fixed` command doesn't make sense, you haven't shown us how to create a `my_text_file` variable ... if this is supposed to be `my.data`, then it won't work either because that variable doesn't have a `$names` component? – Ben Bolker Jul 10 '20 at 20:45

0 Answers0