list - create new column in data.frames with letters assigned to specific numbers

Question

I have got a list with 10 data.frames, each one with 3 columns. I need to create a new column for each data.frame with letters. These letters have to be assigned conditionally on a specific data.frame column.

Here one of my data.frame within the list:

[[10]]
     x   y   z   
1   24  20  15    
2   25  15  25     
3   25  14  20

The column from which the new values have to be assigned is z. So lets say that 15 = S, 25 = A, 20 = T.

My data.frame with the new column should be:

[[10]]
     x   y   z  new_col
1   24  20  15   S  
2   25  15  25   A  
3   25  14  20   T

UPDATE

Here what I am trying with my real data (following @csgillespie suggestion):

lookup <- data.frame(num = c(-5, -1:8, 13:20, 25:32), let = c('P', 'A', 'E', 'AE', 'ASE', 'AS', 'ASW',
                                                                 'ZQ', 'AW', 'AN', 'OQ', 'AA', 'RT', 'SE', 
                                                                 'S', 'QP', 'W', 'NW', 'N', 'C', 'YE', 'PP',
                                                                 'ZQ', 'CS', 'L', 'CW', 'UW', 'CN'))

lst <- lapply(lst, function(i) 
    cbind(i, new_col=lookup[i$x, 2]))

But I got the following error, probably due to the negative numbers on the lookup data.frame:

Error in [.default(xj, i) : only 0's may be mixed with negative subscripts

2nd UPDATE

Thanks @m-dz!

Job done :)

csgillespie · Answer 1 · 2016-08-05T14:33:24.337

0

A simple lapply and cbind should work.

Example list

dd = data.frame(x = 1:3, y = 1:3, z = 1:3)
l = list(dd, dd)
look_up = data.frame(value = 1:3, letter = c("A", "B", "Z"))

Then loop through each element of l and add a new column using look_up as a look up table

lapply(l,function(i) 
  cbind(i, new_col=look_up[i$z, 2]))

edited Aug 05 '16 at 14:33

answered Aug 05 '16 at 14:24

csgillespie

59,189
14
150
185

hi, thanks. the letters, for my real list, are not ordered alphabetically. Is there any way that I can first assign a specific letter to each number and then combine them into a new col? – Aug 05 '16 at 14:29

score 0 · Accepted Answer · edited May 23 '17 at 12:22

I would go for a merge() for the readability:

dd <- data.frame(x = 1:3, y = 1:3, z = 3:1)
dd_list <- list(dd, dd)
lookup <- data.frame(what = 1:3,
                     new_col = c("Q", "W", "E"))
lapply(dd_list, function(dd) merge(dd, lookup, by.x = "z", by.y = "what", sort = FALSE))

Edit: Forgot about the "list" part.

Edit 2: Just realised that merge() reorders the columns and sorts them by the by columns, sort = FALSE deals with the latter, but to keep the original order of columns they need to be reordered back. I would use data.table here with its setorder() (or its merge() implementation actually) or anything from here: How does one reorder columns in a data frame?

list - create new column in data.frames with letters assigned to specific numbers

2 Answers2