Dataframe extracted from downloaded html file contains floats separated by spaces; how to clean?-CodePudding

This is a follow-up to this question, in which I downloaded a file from CDS and extracted with rvest using the following script:

library(rvest)

download.file("https://cdsarc.cds.unistra.fr/viz-bin/nph-Cat/html?J/MNRAS/495/1706/subaru.dat.gz", "subaru.dat.gz")
x <- rvest::read_html("subaru.dat.gz")
y <- rvest::html_table(x)

write.csv(y, file = 'subaru_fixed.csv')

The resulting csv file contains several character-type columns which contain two floats (representing a measurement and its error) separated by a space. Ideally, I'd like to separate those two floats and put the errors in their own column, but I could get away with ignoring the second float altogether. For example,

Bmag (e)       | Vmag (e)  | rmag (e)
21.6219 0.0015 |24.0 0.012 | 23.3316 0.0089

becomes

Bmag       | Vmag | rmag
21.6219    | 24.0 | 23.3316

I imagine there's some way to do it using Python. Can anyone help?

CodePudding user response：

You can use tidyr::separate before you write the CSV. There's probably a clever function to apply separate to several columns at once, but here's a way using 3 separates for the 3 columns of interest.

library(tidyr)

# example data
df1 <- data.frame(`Bmag (e)` = "21.6219 0.0015",
                  `Vmag (e)` = "24.0 0.012",
                  `rmag (e)` = "23.3316 0.0089",
                  check.names = FALSE)

df1 %>% 
  separate(`Bmag (e)`, 
           into = c("Bmag", "Bmag_e"), 
           sep = " ") %>% 
  separate(`Vmag (e)`, 
           into = c("Vmag", "Vmag_e"), 
           sep = " ") %>% 
  separate(`rmag (e)`, 
           into = c("rmag", "rmag_e"), 
           sep = " ")

Result:

     Bmag Bmag_e Vmag Vmag_e    rmag rmag_e
1 21.6219 0.0015 24.0  0.012 23.3316 0.0089