rregexdataframe

R split 1 column into several columns based on regex value

I have dataframe like this

col_names
Aceh..........................................................66.29..........................56.49..........................64.01..................................49.72.....................................39.58...............................52.15
Sumatera.Utara.........................................88.20..........................82.19..........................79.45..................................66.94.....................................53.35...............................73.16
Sumatera.Barat..........................................84.48..........................74.76..........................79.30..................................55.64.....................................44.68...............................78.55

I want to convert into like this

Province Value_1 Value_2 Value_3 Value_4 Value_5 Value_6
Aceh 66.29 56.49 64.01 49.72 39.58 52.15
Sumatera.Utara 88.20 82.19 79.45 66.94 53.35 73.16
Sumatera.Barat 84.48 74.76 79.30 55.64 44.68 78.55

my code

df$province <- str_extract(df$col_names, '\\w*\\.\\w*')

df$values <- str_extract_all(df$col_names, '[0-9]+\\.[0-9]+')

df <- subset(df, select = c(province, values))

but the values are still in 1 column. I want to separate each of those 6 values into 1 column each.

Solution

  • For example

    > read.table(text=sapply(dat,function(x){gsub("\\.{2,}",",",x)}),sep=",")
              V1    V2    V3    V4    V5    V6    V7
1           Aceh 66.29 56.49 64.01 49.72 39.58 52.15
2 Sumatera.Utara 88.20 82.19 79.45 66.94 53.35 73.16
3 Sumatera.Barat 84.48 74.76 79.30 55.64 44.68 78.55