CODEWITHSUNDEEP
r
Jstation
Jstation

Reputation: 407

How to open this text file properly in R?

So I have this line of code in a file:

{"id":53680,"title":"daytona1-usa"}

But when I try to open it in R using this:

df <- read.csv("file1.txt", strip.white = TRUE, sep = ":")

It produces columns like this:

Col1: X53680.title
Col2: daytona1.usa.url

What I want to do is open the file so that the columns are like this:

Col1: 53680
Col2: daytona1-usa

How can I do this in R?

Edit: The actual file I'm reading in is this:

{"id":53203,"title":"bbc-moment","url":"https:\/\/wow.bbc.com\/bbc-ids\/live\/enus\/211\/53203","type":"audio\/mpeg"},{"id":53204,"title":"shg-moment","url":"https:\/\/wow.shg.com\/shg-ids\/live\/enus\/212\/53204","type":"audio\/mpeg"},{"id":53205,"title":"was-zone","url":"https:\/\/wow.was.com\/was-ids\/live\/enus\/213\/53205","type":"audio\/mpeg"},{"id":53206,"title":"xx1-zone","url":"https:\/\/wow.xx1.com\/xx1-ids\/live\/enus\/214\/53206","type":"audio\/mpeg"},], WH.ge('zonemusicdiv-zonemusic'), {loop: true});

After reading it in, I remove the first column and then every 3rd and 4th column with this:

# Delete the first column
df <- df[-1]

# Delete every 3rd and 4th columns
i1 <- rep(seq(3, ncol(df), 4) , each = 2) + 0:1
df <- df[,-i1]

Thank you.

Edit 2:

Adding this fixed it:

df[] <- lapply(df, gsub, pattern = ".title", replacement = "", fixed = TRUE)
df[] <- lapply(df, gsub, pattern = ",url", replacement = "", fixed = TRUE)

Upvotes: 0

Views: 80

Answers (2)

Jstation
Jstation

Reputation: 407

Although the answers above would have been correct if the data had been formatted properly, it seems they don't work for the data I have so what I ended up going with was this:

df <- read.csv("file1.txt", header = FALSE, sep = ":", dec = "-")

# Delete the first column
df <- df[-1]

# Delete every 3rd and 4th columns
i1 <- rep(seq(3, ncol(df), 4) , each = 2) + 0:1
df <- df[,-i1]

df[] <- lapply(df, gsub, pattern = ".title", replacement = "", fixed = TRUE)
df[] <- lapply(df, gsub, pattern = ",url", replacement = "", fixed = TRUE)

Upvotes: 1

r2evans
r2evans

Reputation: 160407

If it is a single JSON in the file, then

jsonlite::read_json("file1.txt")
# $id
# [1] 53680
# $title
# [1] "daytona1-usa"

If it is instead NDJSON (Newline-Delimited json), then

jsonlite::stream_in(file("file1.txt"), verbose = FALSE)
#      id        title
# 1 53680 daytona1-usa

Upvotes: 1

Related Questions