CODEWITHSUNDEEP
rdataframedelimiterdata-import
Uther Pendragon
Uther Pendragon

Reputation: 302

Data importing Delimiter issue in R

I am trying to import a text file into R, and put it into a data frame, along with other data.

My delimiter is "|" and a sample of my data is here :

|Painless check-in. Two legs of 3 on AC: AC105, YYZ-YVR. Roomy and clean A321 with fantastic crew. AC33: YVR-SYD, very light load and had 3 seats to myself. A very enthusiastic and friendly crew as usual on this transpacific route that I take several times a year. Arrived 20 min ahead of schedule. The expected high level of service from our flag carrier, Air Canada. Altitude Elite member. |We recently returned from Dublin to Toronto, then on to Winnipeg. Other than cutting it close due to limited staffing in Toronto our flight was excellent. Due to the rush in Toronto one of our carry ones was placed to go in the cargo hold. When we arrived in Winnipeg it stayed in Toronto, they were most helpful and kind at the Winnipeg airport, and we received 3 phone calls the following day in regards to the misplaced bag and it was delivered to our home. We are very thankful and more than appreciative of the service we received what a great end to a wonderful holiday. |Flew Toronto to Heathrow. Much worse flight than on the way out. We paid a hefty extra fee for exit seats which had no storage whatsoever, and not even any room under the seats. Ridiculous. Crew were poor, not friendly. One older male member of staff was quite attitudinal, acting as though he was doing everyone a huge favour by serving them. A reasonable dinner but breakfast was a measly piece of banana loaf. That's it! The worst airline breakfast I have had.enter image description here

As you can see, there are many "|" , but as this screenshot below shows, when I imported the data in R, it only separated it once, instead of about 152 times.

How do I get each individual piece of text in a different column inside the data frame? I would like a data frame of length 152, not 2.

EDIT: The code lines are:

  myData <- read.table("C:/Users/Norbert/Desktop/research/Important files/Airline Reviews/Reviews/air_can_Review.txt", sep="|",quote=NULL, comment='',fill = TRUE, header=FALSE)

length(myData)
[1] 2
class(myData)
[1] "data.frame"
str(myData)
'data.frame':   1244 obs. of  2 variables:
 $ V1: Factor w/ 1093 levels "","'delayed' on departure (I reference flights between March 2014 and January 2015 in this regard: Denver, SFO,",..: 210 367    698 853 1 344 483 87 757 52 ...
 $ V2: Factor w/ 154 levels ""," hotel","5/9/2014, LHR to Vancouver, AC855. 23/9/2014, Vancouver to LHR, AC854. For Economy the leg room was OK compared to",..: 1 1 1 1 78 1 1 1 1 1 ...

 myDataFrame <- data.frame(text = myData, otherVar2 = 1, otherVar2 = "blue", stringsAsFactors = FALSE)
 str(myDataFrame)
 'data.frame':   531 obs. of  3 variables:
  $ text       : chr  "BRU-YUL, May 26th, A330-300. Departed on-time, landed 30 minutes late due to strong winds, nice flight, food" "excellent, cabin-crew smiling and attentive except for one old lady throwing meal trays like boomerangs. Seat-" "pitch was very generous, comfortable seat,  IFE a bit outdated but selection was Okay. Air Canadas problem is\nthat the new pro"| __truncated__ "" ...
$ otherVar2  : num  1 1 1 1 1 1 1 1 1 1 ...
$ otherVar2.1: chr  "blue" "blue" "blue" "blue" ...

length(myDataFrame)
[1] 3

Upvotes: 0

Views: 266

Answers (1)

Ken Benoit
Ken Benoit

Reputation: 14902

A better way to read in the text is using scan(), and then put it into a data frame with your other variables (here I just made some up). Note that I took your text above, and pasted it into a file called sample.txt, after removing the starting "|".

myData <- scan("sample.txt", what = "character", sep = "|")
myDataFrame <- data.frame(text = myData, otherVar2 = 1, otherVar2 = "blue",
                          stringsAsFactors = FALSE)
str(myDataFrame)
## 'data.frame':    3 obs. of  3 variables:
##  $ text       : chr  "Painless check-in. Two legs of 3 on AC: AC105, YYZ-YVR. Roomy and clean A321 with fantastic crew. AC33: YVR-SYD, very light loa"| __truncated__ "We recently returned from Dublin to Toronto, then on to Winnipeg. Other than cutting it close due to limited staffing in Toront"| __truncated__ "Flew Toronto to Heathrow. Much worse flight than on the way out. We paid a hefty extra fee for exit seats which had no storage "| __truncated__
##  $ otherVar2  : num  1 1 1
##  $ otherVar2.1: Factor w/ 1 level "blue": 1 1 1

The otherVar1, otherVar2 are just placeholders for your own variables, as you said you wanted a data.frame with other variables. I chose an integer variable and a text variable, and by specifying a single value, it gets recycled for all observations in the dataset (in the example, 3).

I realize that your question asks how to get each text in a different column, but that is not a good way to use a data.frame, since data.frames are designed to hold variables in columns. (With one text per column, you cannot add other variables.)

If you really want to do that, you have to coerce the data after transposing it, as follows:

myDataFrame <- as.data.frame(t(data.frame(text = myData, stringsAsFactors = FALSE)), stringsAsFactors = FALSE)
str(myDataFrame)
## 'data.frame':    1 obs. of  3 variables:
##  $ V1: chr "Painless check-in. Two legs of 3 on AC: AC105, YYZ-YVR. Roomy and clean A321 with fantastic crew. AC33: YVR-SYD, very light loa"| __truncated__
##  $ V2: chr "We recently returned from Dublin to Toronto, then on to Winnipeg. Other than cutting it close due to limited staffing in Toront"| __truncated__
##  $ V3: chr "Flew Toronto to Heathrow. Much worse flight than on the way out. We paid a hefty extra fee for exit seats which had no storage "| __truncated__
length(myDataFrame)
## [1] 3

"Measly banana loaf"? Definitely economy class.

Upvotes: 1

Related Questions