1

I have raw, messy data for time series containing around 1400 observations. Here is a snippet of what it looks like:

[new Date('2021-08-24'),1.67,1.68,0.9,null],[new Date('2021-08-23'),1.65,1.68,0.9,null],[new Date('2021-08-22'),1.62,1.68,0.9,null] ... etc

I want to pull the date and its respective value to form a tsibble in R. So, from the above values, it would be like

Date y-variable
2021-08-24 1.67
2021-08-23 1.65
2021-08-22 1.62

Notice how only the first value is to be paired with its respective date - I don't need the other values. Right now, the raw data has been copied and pasted into a word document and I am unsure about how to approach data wrangling to import into R.

How could I achieve this?

zx8754
  • 52,746
  • 12
  • 114
  • 209
BrendonKoum
  • 25
  • 3
  • It would be easier to help if you create a small reproducible example along with expected output. Read about [how to give a reproducible example](http://stackoverflow.com/questions/5963269). – Ronak Shah Sep 01 '21 at 10:50
  • I assume the input does not contain linebreaks. Those were only added with the edit to the question. If they exist, the code from my answer can be adapted easily. – Roland Sep 01 '21 at 11:02

2 Answers2

1
#replace the text conncetion with a file connection if desired, the file should be a txt then
input <- readLines(textConnection("[new Date('2021-08-24'),1.67,1.68,0.9,null],[new Date('2021-08-23'),1.65,1.68,0.9,null],[new Date('2021-08-22'),1.62,1.68,0.9,null]"))

#insert line breaks
input <- gsub("],[", "\n", input, fixed = TRUE)

#remove "new Date"
input <- gsub("new Date", "", input, fixed = TRUE)

#remove parentheses and brackets
input <- gsub("[\\(\\)\\[\\]]", "", input, perl = TRUE)

#import cleaned data
DF <- read.csv(text = input, header = FALSE, quote = "'")
DF$V1 <- as.Date(DF$V1)
print(DF)
#          V1   V2   V3  V4   V5
#1 2021-08-24 1.67 1.68 0.9 null
#2 2021-08-23 1.65 1.68 0.9 null
#3 2021-08-22 1.62 1.68 0.9 null
Roland
  • 127,288
  • 10
  • 191
  • 288
0

How is this?

text <- "[new Date('2021-08-24'),1.67,1.68,0.9,null],[new Date('2021-08-23'),1.65,1.68,0.9,null],[new Date('2021-08-22'),1.62,1.68,0.9,null]"

df <- read.table(text = unlist(strsplit(gsub('new Date\\(|\\)', '', gsub('^.(.*).$', '\\1', text)), "].\\[")), sep = ",")

> df
          V1   V2   V3  V4   V5
1 2021-08-24 1.67 1.68 0.9 null
2 2021-08-23 1.65 1.68 0.9 null
3 2021-08-22 1.62 1.68 0.9 null

Changing column names and removing the last columns is trivial from this point

koolmees
  • 2,725
  • 9
  • 23