CODEWITHSUNDEEP
rexcelimport
Soren Christensen
Soren Christensen

Reputation: 376

Import of an excel file

I try to download a file from NORDPOOL.COM into R, but somehow the xls format is changeing into a strange format

I have tried different file formats, but read.table is the only one that gives a meaningful table

 df <- read.table("https://www.nordpoolgroup.com/globalassets/marketdata-excel-files/regulating-prices_2019_hourly_dkk.xls")

I am looking for a dataframe that looks like the excel file....

Upvotes: 1

Views: 137

Answers (1)

maydin
maydin

Reputation: 3755

rvest can be helpful as, 

library(rvest)

page <- read_html("https://www.nordpoolgroup.com/globalassets/marketdata-excel-files/regulating-prices_2019_hourly_dkk.xls")

out <- page %>%  html_node("table")   %>%    html_table()
out <- out[-c(1,2,3,4),c(1,2,23,24,25,26)]
colnames(out) <- c("Date","Hour","DK1_Up","DK1_Down","DK2_Up","DK2_Down")

head(out)

         Date    Hour DK1_Up DK1_Down DK2_Up DK2_Down
5  01-01-2019 00 - 01 211,45   211,45 211,45   211,45
6  01-01-2019 01 - 02  75,19    75,19  75,19    75,19
7  01-01-2019 02 - 03 -30,46   -30,46 -30,46   -30,46
8  01-01-2019 03 - 04 -73,99   -73,99 -73,99   -73,99
9  01-01-2019 04 - 05 -55,33   -55,33 -55,33   -55,33
10 01-01-2019 05 - 06 -93,71   -93,71 -93,71   -93,71

However, it is an irregular data, to arrange it, you have to have a look at the inside of the .xls file. That was what I did actually. So I think, after downloading the file and making the necessary arrangements, reading the file in R still a good option comparing to rvest.

Upvotes: 4

Related Questions