Converting spaced text data into dataframe
up vote
0
down vote
favorite
There's text data like this
※ 19:20 AAAA (BBB:CCC) --DDDD aaaa 33.1 bbbb 23.8 cccc 20.9 ddddd 14.9 eeeee 8.3 ffffff 6.8 gggggg 4.5 hhhhhh 4.2 --EEEE aaaaa 11.8
or there's HTML File like this
<div style='AAAA'>
※ 19:20 AAAA (BB:CC)<br /><br /><br /><br />--DDDD<br />aaaa 33.1 bbbb 23.8 cccc 20.9<br /><br /><br />--EEEE<br />aaaaa 11.8
What I want to make in Dataframe is
Time Type1 Type2 Data1 Data2
19:20 AAAAA DDDD aaaa 33.1
19:20 AAAAA DDDD bbbb 23.8
19:20 AAAAA EEEE aaaaa 11.8
How can I make it?
r
asked Nov 11 at 9:00
Kihyen Kim
34
add a comment |
up vote
0
down vote
favorite
There's text data like this
※ 19:20 AAAA (BBB:CCC) --DDDD aaaa 33.1 bbbb 23.8 cccc 20.9 ddddd 14.9 eeeee 8.3 ffffff 6.8 gggggg 4.5 hhhhhh 4.2 --EEEE aaaaa 11.8
or there's HTML File like this
<div style='AAAA'>
※ 19:20 AAAA (BB:CC)<br /><br /><br /><br />--DDDD<br />aaaa 33.1 bbbb 23.8 cccc 20.9<br /><br /><br />--EEEE<br />aaaaa 11.8
What I want to make in Dataframe is
Time Type1 Type2 Data1 Data2
19:20 AAAAA DDDD aaaa 33.1
19:20 AAAAA DDDD bbbb 23.8
19:20 AAAAA EEEE aaaaa 11.8
How can I make it?
r
asked Nov 11 at 9:00
Kihyen Kim
34
Is all the data on one line? Will there be any spaces in the real data where you put AAAA DDDD EEEE aaaa bbbb placeholders?
– hrbrmstr
Nov 11 at 11:24
@hrbrmstr Yes. I usedtext<-gsub(pattern = '<.*?>', replacement = ' ', html_nodes(read_html(file_name, encoding='EUC-KR'), "div"))to read this html. For your help, I added the real html files to read.
– Kihyen Kim
Nov 11 at 12:17
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
There's text data like this
※ 19:20 AAAA (BBB:CCC) --DDDD aaaa 33.1 bbbb 23.8 cccc 20.9 ddddd 14.9 eeeee 8.3 ffffff 6.8 gggggg 4.5 hhhhhh 4.2 --EEEE aaaaa 11.8
or there's HTML File like this
<div style='AAAA'>
※ 19:20 AAAA (BB:CC)<br /><br /><br /><br />--DDDD<br />aaaa 33.1 bbbb 23.8 cccc 20.9<br /><br /><br />--EEEE<br />aaaaa 11.8
What I want to make in Dataframe is
Time Type1 Type2 Data1 Data2
19:20 AAAAA DDDD aaaa 33.1
19:20 AAAAA DDDD bbbb 23.8
19:20 AAAAA EEEE aaaaa 11.8
How can I make it?
r
asked Nov 11 at 9:00
Kihyen Kim
34
There's text data like this
※ 19:20 AAAA (BBB:CCC) --DDDD aaaa 33.1 bbbb 23.8 cccc 20.9 ddddd 14.9 eeeee 8.3 ffffff 6.8 gggggg 4.5 hhhhhh 4.2 --EEEE aaaaa 11.8
or there's HTML File like this
<div style='AAAA'>
※ 19:20 AAAA (BB:CC)<br /><br /><br /><br />--DDDD<br />aaaa 33.1 bbbb 23.8 cccc 20.9<br /><br /><br />--EEEE<br />aaaaa 11.8
What I want to make in Dataframe is
Time Type1 Type2 Data1 Data2
19:20 AAAAA DDDD aaaa 33.1
19:20 AAAAA DDDD bbbb 23.8
19:20 AAAAA EEEE aaaaa 11.8
How can I make it?
r
r
asked Nov 11 at 9:00
Kihyen Kim
34
asked Nov 11 at 9:00
Kihyen Kim
34
edited Nov 11 at 12:16
asked Nov 11 at 9:00
Kihyen Kim
34
asked Nov 11 at 9:00
Kihyen Kim
34
asked Nov 11 at 9:00
Kihyen Kim
34
34
Is all the data on one line? Will there be any spaces in the real data where you put AAAA DDDD EEEE aaaa bbbb placeholders?
– hrbrmstr
Nov 11 at 11:24
@hrbrmstr Yes. I usedtext<-gsub(pattern = '<.*?>', replacement = ' ', html_nodes(read_html(file_name, encoding='EUC-KR'), "div"))to read this html. For your help, I added the real html files to read.
– Kihyen Kim
Nov 11 at 12:17
add a comment |
Is all the data on one line? Will there be any spaces in the real data where you put AAAA DDDD EEEE aaaa bbbb placeholders?
– hrbrmstr
Nov 11 at 11:24
@hrbrmstr Yes. I usedtext<-gsub(pattern = '<.*?>', replacement = ' ', html_nodes(read_html(file_name, encoding='EUC-KR'), "div"))to read this html. For your help, I added the real html files to read.
– Kihyen Kim
Nov 11 at 12:17
Is all the data on one line? Will there be any spaces in the real data where you put AAAA DDDD EEEE aaaa bbbb placeholders?
– hrbrmstr
Nov 11 at 11:24
Is all the data on one line? Will there be any spaces in the real data where you put AAAA DDDD EEEE aaaa bbbb placeholders?
– hrbrmstr
Nov 11 at 11:24
@hrbrmstr Yes. I used
text<-gsub(pattern = '<.*?>', replacement = ' ', html_nodes(read_html(file_name, encoding='EUC-KR'), "div")) to read this html. For your help, I added the real html files to read.– Kihyen Kim
Nov 11 at 12:17
@hrbrmstr Yes. I used
text<-gsub(pattern = '<.*?>', replacement = ' ', html_nodes(read_html(file_name, encoding='EUC-KR'), "div")) to read this html. For your help, I added the real html files to read.– Kihyen Kim
Nov 11 at 12:17
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
1) First remove everything within parens including the parens giving txt2. Then split what is left at -- separators and flatten it giving txt3. Then scan in the words separately for each element giving s and convert that to matrix m and data frame DF. Finally convert the types of the columns. No packages are used.
txt2 <- gsub("[(].*?[)]", "", txt)
txt3 <- unlist(strsplit(txt2, "--"))
s <- lapply(txt3, function(x) scan(text = x, what = "", quiet = TRUE))
m <- cbind(s[[1]][1], s[[1]][2], do.call("rbind", lapply(s[-1],
function(x) cbind(x[1], matrix(x[-1],,2, byrow = TRUE)))))
DF <- as.data.frame(m, stringsAsFactors = FALSE)
DF <- lapply(DF, type.convert, stringsAsFactors = FALSE)
giving:
> DF
V1 V2 V3 V4 V5
1 19:20 AAAA DDDD aaaa 33.1
2 19:20 AAAA DDDD bbbb 23.8
3 19:20 AAAA DDDD cccc 20.9
4 19:20 AAAA DDDD ddddd 14.9
5 19:20 AAAA DDDD eeeee 8.3
6 19:20 AAAA DDDD ffffff 6.8
7 19:20 AAAA DDDD gggggg 4.5
8 19:20 AAAA DDDD hhhhhh 4.2
9 19:20 AAAA EEEE aaaaa 11.8
2) If we can assume that only the fifth column is numeric then we can use this simpler alternate approach. First remove the parens and everything inside them as above and then scan in the words into s. Locate the positions ix of the words containing only digits and dot and then pick out each field assembling them into a data.frame.
txt2 <- gsub("[(].*?[)]", "", txt)
s <- scan(text = txt2, what = "", quiet = TRUE)
ix <- grep("^[0-9.]+$", s)
data.frame(
V1 = s[1],
V2 = s[2],
V3 = sub("--", "", s[sapply(ix-2, function(i) tail(grep("--", s[seq(i)]), 1))]),
V4 = s[ix-1],
V5 = as.numeric(s[ix]),
stringsAsFactors = FALSE
)
Note
The input is assumed to be:
txt <- "19:20 AAAA (BBB:CCC) --DDDD aaaa 33.1 bbbb 23.8 cccc 20.9 ddddd 14.9 eeeee 8.3 ffffff 6.8 gggggg 4.5 hhhhhh 4.2 --EEEE aaaaa 11.8"
answered Nov 11 at 12:49
G. Grothendieck
142k9124229
Thanks for your wonderful answer! There's some problems in data, so I need to clean my data to fit this things. But your help teaches me what to do and how I can do.
– Kihyen Kim
Nov 11 at 13:08
Have added second approach.
– G. Grothendieck
Nov 11 at 14:56
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
1) First remove everything within parens including the parens giving txt2. Then split what is left at -- separators and flatten it giving txt3. Then scan in the words separately for each element giving s and convert that to matrix m and data frame DF. Finally convert the types of the columns. No packages are used.
txt2 <- gsub("[(].*?[)]", "", txt)
txt3 <- unlist(strsplit(txt2, "--"))
s <- lapply(txt3, function(x) scan(text = x, what = "", quiet = TRUE))
m <- cbind(s[[1]][1], s[[1]][2], do.call("rbind", lapply(s[-1],
function(x) cbind(x[1], matrix(x[-1],,2, byrow = TRUE)))))
DF <- as.data.frame(m, stringsAsFactors = FALSE)
DF <- lapply(DF, type.convert, stringsAsFactors = FALSE)
giving:
> DF
V1 V2 V3 V4 V5
1 19:20 AAAA DDDD aaaa 33.1
2 19:20 AAAA DDDD bbbb 23.8
3 19:20 AAAA DDDD cccc 20.9
4 19:20 AAAA DDDD ddddd 14.9
5 19:20 AAAA DDDD eeeee 8.3
6 19:20 AAAA DDDD ffffff 6.8
7 19:20 AAAA DDDD gggggg 4.5
8 19:20 AAAA DDDD hhhhhh 4.2
9 19:20 AAAA EEEE aaaaa 11.8
2) If we can assume that only the fifth column is numeric then we can use this simpler alternate approach. First remove the parens and everything inside them as above and then scan in the words into s. Locate the positions ix of the words containing only digits and dot and then pick out each field assembling them into a data.frame.
txt2 <- gsub("[(].*?[)]", "", txt)
s <- scan(text = txt2, what = "", quiet = TRUE)
ix <- grep("^[0-9.]+$", s)
data.frame(
V1 = s[1],
V2 = s[2],
V3 = sub("--", "", s[sapply(ix-2, function(i) tail(grep("--", s[seq(i)]), 1))]),
V4 = s[ix-1],
V5 = as.numeric(s[ix]),
stringsAsFactors = FALSE
)
Note
The input is assumed to be:
txt <- "19:20 AAAA (BBB:CCC) --DDDD aaaa 33.1 bbbb 23.8 cccc 20.9 ddddd 14.9 eeeee 8.3 ffffff 6.8 gggggg 4.5 hhhhhh 4.2 --EEEE aaaaa 11.8"
answered Nov 11 at 12:49
G. Grothendieck
142k9124229
Thanks for your wonderful answer! There's some problems in data, so I need to clean my data to fit this things. But your help teaches me what to do and how I can do.
– Kihyen Kim
Nov 11 at 13:08
Have added second approach.
– G. Grothendieck
Nov 11 at 14:56
add a comment |
up vote
0
down vote
accepted
1) First remove everything within parens including the parens giving txt2. Then split what is left at -- separators and flatten it giving txt3. Then scan in the words separately for each element giving s and convert that to matrix m and data frame DF. Finally convert the types of the columns. No packages are used.
txt2 <- gsub("[(].*?[)]", "", txt)
txt3 <- unlist(strsplit(txt2, "--"))
s <- lapply(txt3, function(x) scan(text = x, what = "", quiet = TRUE))
m <- cbind(s[[1]][1], s[[1]][2], do.call("rbind", lapply(s[-1],
function(x) cbind(x[1], matrix(x[-1],,2, byrow = TRUE)))))
DF <- as.data.frame(m, stringsAsFactors = FALSE)
DF <- lapply(DF, type.convert, stringsAsFactors = FALSE)
giving:
> DF
V1 V2 V3 V4 V5
1 19:20 AAAA DDDD aaaa 33.1
2 19:20 AAAA DDDD bbbb 23.8
3 19:20 AAAA DDDD cccc 20.9
4 19:20 AAAA DDDD ddddd 14.9
5 19:20 AAAA DDDD eeeee 8.3
6 19:20 AAAA DDDD ffffff 6.8
7 19:20 AAAA DDDD gggggg 4.5
8 19:20 AAAA DDDD hhhhhh 4.2
9 19:20 AAAA EEEE aaaaa 11.8
2) If we can assume that only the fifth column is numeric then we can use this simpler alternate approach. First remove the parens and everything inside them as above and then scan in the words into s. Locate the positions ix of the words containing only digits and dot and then pick out each field assembling them into a data.frame.
txt2 <- gsub("[(].*?[)]", "", txt)
s <- scan(text = txt2, what = "", quiet = TRUE)
ix <- grep("^[0-9.]+$", s)
data.frame(
V1 = s[1],
V2 = s[2],
V3 = sub("--", "", s[sapply(ix-2, function(i) tail(grep("--", s[seq(i)]), 1))]),
V4 = s[ix-1],
V5 = as.numeric(s[ix]),
stringsAsFactors = FALSE
)
Note
The input is assumed to be:
txt <- "19:20 AAAA (BBB:CCC) --DDDD aaaa 33.1 bbbb 23.8 cccc 20.9 ddddd 14.9 eeeee 8.3 ffffff 6.8 gggggg 4.5 hhhhhh 4.2 --EEEE aaaaa 11.8"
answered Nov 11 at 12:49
G. Grothendieck
142k9124229
Thanks for your wonderful answer! There's some problems in data, so I need to clean my data to fit this things. But your help teaches me what to do and how I can do.
– Kihyen Kim
Nov 11 at 13:08
Have added second approach.
– G. Grothendieck
Nov 11 at 14:56
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
1) First remove everything within parens including the parens giving txt2. Then split what is left at -- separators and flatten it giving txt3. Then scan in the words separately for each element giving s and convert that to matrix m and data frame DF. Finally convert the types of the columns. No packages are used.
txt2 <- gsub("[(].*?[)]", "", txt)
txt3 <- unlist(strsplit(txt2, "--"))
s <- lapply(txt3, function(x) scan(text = x, what = "", quiet = TRUE))
m <- cbind(s[[1]][1], s[[1]][2], do.call("rbind", lapply(s[-1],
function(x) cbind(x[1], matrix(x[-1],,2, byrow = TRUE)))))
DF <- as.data.frame(m, stringsAsFactors = FALSE)
DF <- lapply(DF, type.convert, stringsAsFactors = FALSE)
giving:
> DF
V1 V2 V3 V4 V5
1 19:20 AAAA DDDD aaaa 33.1
2 19:20 AAAA DDDD bbbb 23.8
3 19:20 AAAA DDDD cccc 20.9
4 19:20 AAAA DDDD ddddd 14.9
5 19:20 AAAA DDDD eeeee 8.3
6 19:20 AAAA DDDD ffffff 6.8
7 19:20 AAAA DDDD gggggg 4.5
8 19:20 AAAA DDDD hhhhhh 4.2
9 19:20 AAAA EEEE aaaaa 11.8
2) If we can assume that only the fifth column is numeric then we can use this simpler alternate approach. First remove the parens and everything inside them as above and then scan in the words into s. Locate the positions ix of the words containing only digits and dot and then pick out each field assembling them into a data.frame.
txt2 <- gsub("[(].*?[)]", "", txt)
s <- scan(text = txt2, what = "", quiet = TRUE)
ix <- grep("^[0-9.]+$", s)
data.frame(
V1 = s[1],
V2 = s[2],
V3 = sub("--", "", s[sapply(ix-2, function(i) tail(grep("--", s[seq(i)]), 1))]),
V4 = s[ix-1],
V5 = as.numeric(s[ix]),
stringsAsFactors = FALSE
)
Note
The input is assumed to be:
txt <- "19:20 AAAA (BBB:CCC) --DDDD aaaa 33.1 bbbb 23.8 cccc 20.9 ddddd 14.9 eeeee 8.3 ffffff 6.8 gggggg 4.5 hhhhhh 4.2 --EEEE aaaaa 11.8"
answered Nov 11 at 12:49
G. Grothendieck
142k9124229
1) First remove everything within parens including the parens giving txt2. Then split what is left at -- separators and flatten it giving txt3. Then scan in the words separately for each element giving s and convert that to matrix m and data frame DF. Finally convert the types of the columns. No packages are used.
txt2 <- gsub("[(].*?[)]", "", txt)
txt3 <- unlist(strsplit(txt2, "--"))
s <- lapply(txt3, function(x) scan(text = x, what = "", quiet = TRUE))
m <- cbind(s[[1]][1], s[[1]][2], do.call("rbind", lapply(s[-1],
function(x) cbind(x[1], matrix(x[-1],,2, byrow = TRUE)))))
DF <- as.data.frame(m, stringsAsFactors = FALSE)
DF <- lapply(DF, type.convert, stringsAsFactors = FALSE)
giving:
> DF
V1 V2 V3 V4 V5
1 19:20 AAAA DDDD aaaa 33.1
2 19:20 AAAA DDDD bbbb 23.8
3 19:20 AAAA DDDD cccc 20.9
4 19:20 AAAA DDDD ddddd 14.9
5 19:20 AAAA DDDD eeeee 8.3
6 19:20 AAAA DDDD ffffff 6.8
7 19:20 AAAA DDDD gggggg 4.5
8 19:20 AAAA DDDD hhhhhh 4.2
9 19:20 AAAA EEEE aaaaa 11.8
2) If we can assume that only the fifth column is numeric then we can use this simpler alternate approach. First remove the parens and everything inside them as above and then scan in the words into s. Locate the positions ix of the words containing only digits and dot and then pick out each field assembling them into a data.frame.
txt2 <- gsub("[(].*?[)]", "", txt)
s <- scan(text = txt2, what = "", quiet = TRUE)
ix <- grep("^[0-9.]+$", s)
data.frame(
V1 = s[1],
V2 = s[2],
V3 = sub("--", "", s[sapply(ix-2, function(i) tail(grep("--", s[seq(i)]), 1))]),
V4 = s[ix-1],
V5 = as.numeric(s[ix]),
stringsAsFactors = FALSE
)
Note
The input is assumed to be:
txt <- "19:20 AAAA (BBB:CCC) --DDDD aaaa 33.1 bbbb 23.8 cccc 20.9 ddddd 14.9 eeeee 8.3 ffffff 6.8 gggggg 4.5 hhhhhh 4.2 --EEEE aaaaa 11.8"
answered Nov 11 at 12:49
G. Grothendieck
142k9124229
edited Nov 11 at 15:36
answered Nov 11 at 12:49
G. Grothendieck
142k9124229
answered Nov 11 at 12:49
G. Grothendieck
142k9124229
answered Nov 11 at 12:49
G. Grothendieck
142k9124229
142k9124229
Thanks for your wonderful answer! There's some problems in data, so I need to clean my data to fit this things. But your help teaches me what to do and how I can do.
– Kihyen Kim
Nov 11 at 13:08
Have added second approach.
– G. Grothendieck
Nov 11 at 14:56
add a comment |
Thanks for your wonderful answer! There's some problems in data, so I need to clean my data to fit this things. But your help teaches me what to do and how I can do.
– Kihyen Kim
Nov 11 at 13:08
Have added second approach.
– G. Grothendieck
Nov 11 at 14:56
Thanks for your wonderful answer! There's some problems in data, so I need to clean my data to fit this things. But your help teaches me what to do and how I can do.
– Kihyen Kim
Nov 11 at 13:08
Thanks for your wonderful answer! There's some problems in data, so I need to clean my data to fit this things. But your help teaches me what to do and how I can do.
– Kihyen Kim
Nov 11 at 13:08
Have added second approach.
– G. Grothendieck
Nov 11 at 14:56
Have added second approach.
– G. Grothendieck
Nov 11 at 14:56
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53247219%2fconverting-spaced-text-data-into-dataframe%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Is all the data on one line? Will there be any spaces in the real data where you put AAAA DDDD EEEE aaaa bbbb placeholders?
– hrbrmstr
Nov 11 at 11:24
@hrbrmstr Yes. I used
text<-gsub(pattern = '<.*?>', replacement = ' ', html_nodes(read_html(file_name, encoding='EUC-KR'), "div"))to read this html. For your help, I added the real html files to read.– Kihyen Kim
Nov 11 at 12:17