Read data stored as character

Question

I have a column within a dataframe with numbers that looks like this below

      City                Temperature
      Edmonton, Alberta   4.1,13.6,15.2,15.7,14.2,15.2,16,14.2,17,13.1
      Edmonton, Alberta   15,18.2,14.8,16.5,14.6,16.9,14.3,17.5,13,15.8
      Edmonton, Alberta   15.8,17.9,16.9,15.1,13.2,13.1,16.8,12.4,14.7,15.6
      Edmonton, Alberta   14.3,17.3,14.6,17.3,14.8,14,15.4,14.1,16,15.4

My objective is to read the data in Temperature column and create two additional columns that stores the Minimum and Maximum temperatures like this.

      City                Temperature                                         Min      Max
      Edmonton, Alberta   4.1,13.6,15.2,15.7,14.2,15.2,16,14.2,17,13.1        4.1      16
      Edmonton, Alberta   15,18.2,14.8,16.5,14.6,16.9,14.3,17.5,13,15.8       13       18.2
      Edmonton, Alberta   15.8,17.9,16.9,15.1,13.2,13.1,16.8,12.4,14.7,15.6   12.4     17.9
      Edmonton, Alberta   14.3,17.3,14.6,17.3,14.8,14,15.4,14.1,16,15.4       14.1     17.3

I tried the simple min(df$Temperature[1]) function but it didnt work. So not sure how to deal with this data, any advise or suggestion is much appreciated.

Possible duplicate of [Convert comma separated entry to columns](http://stackoverflow.com/questions/8464312). — zx8754, Oct 20 '16 at 06:13

score 3 · Answer 1 · answered Oct 20 '16 at 05:57

3

We need to split the 'Temperature' column by ',', convert to numeric, get the range, rbind it and create two columns

df1[c("Min", "Max")] <- do.call(rbind, lapply(strsplit(as.character(df1$Temperature), ','), 
                        function(x) range(as.numeric(x))))

The as.character is only needed if the column 'Temperature' is factor class.

answered Oct 20 '16 at 05:57

akrun

874,273
37
540
662

1

this worked but I am going with the scan function suggested by 42 above just because i can add other summary statistics easily using scan function if needed, for example I am not sure how to include average if needed in the future using this approach, but I relate well with scan function. – Diggy Detroit Oct 20 '16 at 06:19
@DiggyDetroit For the average, you can do `function(x) {x1 <- as.numeric(x); c(range(x), mean(x))}))` also change the `df1[c("Min", "Max", "Mean")] <- ` – akrun Oct 20 '16 at 06:33

score 3 · Accepted Answer · answered Oct 20 '16 at 06:06

The scan function can read across text fields and parse out values divided by the "sep" argument:

> dat$min_temp <- sapply( as.character(dat$Temperature), 
                    function(x) min( as.numeric( scan( text=x, sep=",", what=""))))
Read 10 items
Read 10 items
Read 10 items
Read 10 items
> dat
              City                                       Temperature
1 Edmonton,Alberta      4.1,13.6,15.2,15.7,14.2,15.2,16,14.2,17,13.1
2 Edmonton,Alberta     15,18.2,14.8,16.5,14.6,16.9,14.3,17.5,13,15.8
3 Edmonton,Alberta 15.8,17.9,16.9,15.1,13.2,13.1,16.8,12.4,14.7,15.6
4 Edmonton,Alberta     14.3,17.3,14.6,17.3,14.8,14,15.4,14.1,16,15.4
  min_temp
1      4.1
2     13.0
3     12.4
4     14.0

Ah the scan function, I knew that for some reason I couldn't connect. Thanks 42, — Diggy Detroit, Oct 20 '16 at 06:15

Read data stored as character

2 Answers2