Extracting values and units from character column to multiple new columns (using extract in R:dplyr?)

user_19766496 0
Viewed 39

I have looked through many different previous questions but not come up with one that is solving my problem

I have a very messy and irregular character column in a data frame that looks like this: cost$k1:

 "33p/kWh”
 "40p/kWh on bp pulse50 units. 50p/kWh on bp pulse150 units."                                                                                                                    
 "42p/kWh”
 "Free"                                                                                                                                                                          
 "30p/kWh ( min £1.50 )”
 "42p/kWh”
 "Polar members: 12p/kWh”
 "Polar Subscription - 27p/kWh Polar Free Membership - 42p/kWh Contactless - 42p/kWh"                                                                                            
 "47p/kWh”
 "47p/kWh”
 "25p/kWh”
 "25p/kWh”
 "50kW: 43p/kWh”

I need to try and take up to 4 of the prices (including the units) and put them into new columns for example:

 cost$k2[2] would be "40p/kWh"
 cost$k3[2] would be "50p/kWh" 
 cost$k2[8] would be "27p/kWh"
 cost$k3[8] would be "42p/kWh" 
 cost$k4[8] would be "42p/kWh"

Looking around has suggested that dplyr::extract() should be ideal, but I am having problems getting it to work.

Three examples which I have just spent too long reordering and moving around are….

cost %>% extract(k1,c("k2","k3","k4"),"([0-9]+p/kWh)-([0-9]+p/kWh)-([0-9]+p/kWh)",remove=FALSE)

Gives no results

cost %>%  extract(k1,"([[0-9]+]p/kWh)",remove=FALSE)

just gets me the first set of numbers and not the units, or even the correct units

cost %>%  extract(k1,into = c("k2","k3","k4"),regex="([0-9]+p/kWh)*([0-9]+p/kWh)*([[0-9]+p/kWh)",remove=FALSE)

Gets me the numbers and units where there is one cost but only writes this this to column k4 (in row 4 it gets me the 40p/kWh but not the 50p/kWh

Any ideas?

Data here

structure(list(ID = c(194597L, 194510L, 193430L, 191632L, 190347L, 
190056L, 189724L, 189630L, 189350L, 189349L, 188842L, 188841L, 
188046L, 176130L, 175867L, 175683L, 175682L, 175526L, 175354L, 
175323L, 175034L, 174985L, 173800L, 173795L, 173794L, 173713L, 
173668L, 173518L, 173027L, 173026L, 173025L, 173018L, 172194L, 
172008L, 171158L, 171137L, 171136L, 170768L, 170767L, 170764L, 
170763L, 170701L, 170372L, 170368L, 170366L, 170365L, 170364L, 
170362L, 170359L, 170356L), k1 = c("20p/kWh", "25p/kWhContactless Card ", 
"33p/kWh; other tariffs available", "40p/kWh on bp pulse50 units. 50p/kWh on bp pulse150 units.", 
"42p/kWh; other tariffs available", "33p/kWh; other tariffs available", 
"35p/kWh; other tariffs available", "16p/kWh; other tariffs available", 
"Free", "42p/kWh; other tariffs available", "26p/kWh; other tariffs available", 
"26p/kWh; other tariffs available", "35p/kWh. Overstay £10.00/hour after 90 mins; other tariffs available", 
"30p/kWh ( min £1.50 ); other tariffs available. Parking fees apply", 
"33p/kWh; other tariffs available", "33p/kWh; other tariffs available", 
"33p/kWh; other tariffs available", "42p/kWh; other tariffs available", 
"42p/kWh; other tariffs available", "47p/kWh; other tariffs available", 
"42p/kWh; other tariffs available", "Polar members: 12p/kWh; instant: 18p/kWh (£1.20 min payment)", 
"Polar Subscription - 27p/kWh Polar Free Membership - 42p/kWh Contactless - 42p/kWh", 
"47p/kWh; other tariffs available", "47p/kWh; other tariffs available", 
"25p/kWh; other tariffs available", "25p/kWh; other tariffs available", 
"50kW: 43p/kWh; 150kW: 42p/kWh; other tariffs available", "Rapids Polar Card 15p/kWh, Contactless 20p/kWh", 
"Polar members: 12p/kWh; instant: 18p/kWh (£1.20 min payment)", 
"Rapids Polar Card 15p/kWh, Contactless 20p/kWh", "25p/kWh; other tariffs available", 
"Polar members: 12p/kWh; instant: 18p/kWh (£1.20 min payment)", 
"Polar members: 12p/kWh; instant: 18p/kWh (£1.20 min payment)", 
"30p/kWh Contactless; Overstay £10/hour after 90 minutes; Other tariffs available", 
"Rapids Polar Card 15p/kWh, Contactless 20p/kWh", "Rapids Polar Card 15p/kWh, Contactless 20p/kWh", 
"Rapids Polar Card 15p/kWh, Contactless 20p/kWh", "Rapids Polar Card 15p/kWh, Contactless 20p/kWh", 
"Rapids Polar Card 15p/kWh, Contactless 20p/kWh", "Rapids Polar Card 15p/kWh, Contactless 20p/kWh", 
"40p - 20p/kWh", "Rapids Polar Card 15p/kWh, Contactless 20p/kWh", 
"Rapids Polar Card 15p/kWh, Contactless 20p/kWh", "Rapids Polar Card 15p/kWh, Contactless 20p/kWh", 
"Rapids Polar Card 15p/kWh, Contactless 20p/kWh", "40p - 20p/kWh", 
"40p - 20p/kWh", "40p - 20p/kWh", "Polar Plus 20p/kWh Polar Instant 35p/kWh Contactless 40p/kWh £1.50 connection charge"
)), row.names = c(NA, -50L), class = c("data.table", "data.frame"
), .internal.selfref = <pointer: 0x000001e772333ba0>)
dplyr extract r regex
1 Answers

With str_extract_all:

str_extract_all(df$k1, '\\b\\d+p/kWh')
# [[1]]
# [1] "33p/kWh"
# 
# [[2]]
# [1] "40p/kWh" "50p/kWh"

Or in a dataframe:

df %>% 
  mutate(col = str_extract_all(k1, '\\b\\d+p/kWh')) %>% 
  unnest_wider(col, names_sep = "")

# A tibble: 2 × 3
  k1                                                         col1    col2   
  <chr>                                                      <chr>   <chr>  
1 33p/kWh                                                    33p/kWh NA     
2 40p/kWh on bp pulse50 units. 50p/kWh on bp pulse150 units. 40p/kWh 50p/kWh

data

df = data.frame(k1 = c("33p/kWh",
 "40p/kWh on bp pulse50 units. 50p/kWh on bp pulse150 units."))
user_134606020
Related