Convert FIX message format ("Tag = Value") to CSV
I have a csv / log file of 35 = S (Quote messages; "Tag = Value") and I need to extract the bids to the correct CSV file for data mining. This is not strictly FIX-related, but rather an R-related question about how to clear a dataset.
The raw messages look something like this:
190=1.1204 ,191=-0.000029,193=20141008,537=0 ,631=1.12029575,642=0.000145,10=56
190=7.20425,191=0.000141 ,537=0 ,631=7.2034485,10=140 , ,
190=1.26237,191=0 ,537=1 ,10=068 , , ,
I need to first go to an intermediate dataset that looks like this when the same tags are aligned.
190=1.1204 ,191=-0.000029,193=20141008,537=0,631=1.12029575,642=0.000145,10=56
190=7.20425,191=0.000141 , ,537=0,631=7.2034485 , ,10=140
190=1.26237,191=0 , ,537=1, , ,10=068
which in turn would need to be converted to this:
190 ,191 ,193 ,537,631 ,642 ,10
1.1204 ,-0.000029,20141008,0 ,1.12029575,0.000145,56
7.20425,0.000141 , ,0 ,7.2034485 , ,140
1.26237,0 , ,1 , , ,068
I'm in the middle of developing a bash script with awk, but I'm wondering if I can do this in R. Currently, my biggest challenge is getting to the staging table. From the intermediate to the final table, I was thinking about using R with the tidyr package, specifically the "separate" function. If anyone can suggest a better logic I would greatly appreciate it!
source to share
Another possibility. Start with the same scan
as @Andrie, but also use arguments strip.white
and na.strings
:
x <- scan(text = "190=1.1204 ,191=-0.000029,193=20141008,537=0 ,631=1.12029575,642=0.000145,10=56
190=7.20425,191=0.000141 ,537=0 ,631=7.2034485,10=140 , ,
190=1.26237,191=0 ,537=1 ,10=068 , , ,",
sep = ",",
what = "character",
strip.white = TRUE,
na.strings = "")
# remove NA
x <- x[!is.na(x)]
Then use colsplit
and dcast
from the reshape2
package:
library(reshape2)
# split 'x' into two columns
d1 <- colsplit(string = x, pattern = "=", names = c("x", "y"))
# create an id variable, needed in dcast
d1$id <- ave(d1$x, d1$x, FUN = seq_along)
# reshape from long to wide
d2 <- dcast(data = d1, id ~ x, value.var = "y")
# id 10 190 191 193 537 631 642
# 1 1 56 1.12040 -0.000029 20141008 0 1.120296 0.000145
# 2 2 140 7.20425 0.000141 NA 0 7.203449 NA
# 3 3 68 1.26237 0.000000 NA 1 NA NA
Because you mentioned tidyr
:
library(tidyr)
d1 <- separate(data = data.frame(x), col = x, into = c("x", "y"), sep = "=")
d1$id <- ave(d1$x, d1$x, FUN = seq_along)
spread(data = d1, key = x, value = y)
# id 10 190 191 193 537 631 642
# 1 1 56 1.1204 -0.000029 20141008 0 1.12029575 0.000145
# 2 2 140 7.20425 0.000141 <NA> 0 7.2034485 <NA>
# 3 3 068 1.26237 0 <NA> 1 <NA> <NA>
This stores the values as character
. If you want numeric
, you can install convert = TRUE
in spread
.
source to share
By the editors. Complete solution using only basic R functions:
dat <- scan(sep=",", what="character", text="190=1.1204 ,191=-0.000029,193=20141008,537=0 ,631=1.12029575,642=0.000145,10=56
190=7.20425,191=0.000141 ,537=0 ,631=7.2034485,10=140 , ,
190=1.26237,191=0 ,537=1 ,10=068 , , ,")
dat <- gsub(" ", "", dat)
dat <- dat[dat != ""]
x <- as.data.frame(
matrix(
unlist(
sapply(dat, strsplit, split = "=", USE.NAMES=FALSE)
),
ncol=2, byrow=TRUE
)
)
z <- unstack(x, V2 ~ V1)
The resulting object is a named list that is close to what you wanted. You will need to do some extra work to convert this to a matrix, if necessary.
$`10`
[1] "56" "140" "068"
$`190`
[1] "1.1204" "7.20425" "1.26237"
$`191`
[1] "-0.000029" "0.000141" "0"
....
etc.
Here you just need to insert a list with the appropriate number of NA values:
maxLength <- max(sapply(z, length))
sapply(z, function(x)c(as.numeric(x), rep(NA, maxLength - length(x))))
gives:
10 190 191 193 537 631 642
[1,] 56 1.12040 -0.000029 20141008 0 1.120296 0.000145
[2,] 140 7.20425 0.000141 NA 0 7.203449 NA
[3,] 68 1.26237 0.000000 NA 1 NA NA
source to share