martin_m
martin_m

Reputation: 137

Get messy scraped data into data.frame in R

I am working on scraping a certain part of a perticular website, which looks like a table but isn't (unfortunately).

I use this code...

htmldoc <- read_html("http://www.wettportal.com/quotenvergleich/valuebets/")

data <- htmldoc %>% 
  html_node(xpath='//*[(@id = "datagrid_content")]') %>%
  html_text()

# alternative css selector: "#datagrid_content"

.. and get this kind of output:

Fussball | Schweden | Cup\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n08.06.2016\r\nTipp\r\nVQ\r\nBuchmacher\r\n100%\r\nProfit\r\n\r\n\r\n\r\n\r\n19:00\r\nHuddinge IF - Enskede IK\r\n1 (DNB)\r\n1.73\r\nCoral\r\n1.50\r\n45.17%\r\n\r\n\r\n19:00\r\nHuddinge IF - Enskede IK\r\n1\r\n2.25\r\nCoral\r\n1.93\r\n35.00%\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n

As you can see, it is really messy and so far I have not been able to get it neatly into a data.frame.

Anyone got an idea of how to either

Thank you.

Upvotes: 0

Views: 441

Answers (2)

Tomas H
Tomas H

Reputation: 713

Well there are some things which make this a bit complicated. I use different approach for webscraping but the code down there could help you out a bit

library(RCurl)
library(XML)
library(stringr)
library(tidyr)
url<-"http://www.wettportal.com/quotenvergleich/valuebets/"
url2<-getURL(url)
parsed<-htmlParse(url2,encoding = "UTF-8")

info1<-xpathSApply(parsed,"//div[@id='datagrid_content']//h2/span[1]",xmlValue)
date<-xpathSApply(parsed,"//th/time",xmlValue)
df<-data.frame(matrix(unlist(str_split(info1," . ",n = 3)),nrow=length(info1),byrow=T))
colnames(df)<-c("Sport","Country","Competition")
df<-cbind(df,date)
time<-xpathSApply(parsed,"//div[@id='datagrid_content']//tbody/tr/td[1]",xmlValue)
teams<-xpathSApply(parsed,"//div[@id='datagrid_content']//a/span",xmlValue)
ID<-1
for (i in 2:length(teams)){
    if (teams[i]==teams[i-1]){
        x<-max(ID,na.rm=TRUE)
    } else {
        x=max(ID,na.rm=TRUE)+1
    }
    ID<-c(ID,x)

}
df2<-cbind(teams,ID,time)
df$ID<-1:nrow(df)

final<-merge(df2,df)
final<-separate(final,col = teams,into=c("team1","team2"),sep =" - ")
final<-final[ ,c(5:8,4,2,3,1)]

enter image description here

Upvotes: 1

John Smith
John Smith

Reputation: 1698

I didn't you what you expected to get when executing your code, but it did exactly what you told it to do: it returns all the xmlvalue in the div with id="datagrid_content".

  • Now if you want a data.frame, you have to write a code so that it will give you a data.frame.
  • The data is messy, you have to clean it. There is no automatic way. For example, you can get rid of some special characters, then split the data with |: strsplit(gsub("\r|\n","",data)," | ")
  • Since data are in tables, you can also do:

    data <- htmldoc %>% html_nodes(xpath='//*[@class="table-type-liga-1"]')%>% html_table()

You can get a list of data.frame.

Upvotes: 0

Related Questions