user6644063 user6644063 - 1 year ago 59
R Question

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

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:

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
. So lets say that 15 = S, 25 = A, 20 = T.

My data.frame with the new column should be:

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


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
(xj, i) : only 0's may be mixed with negative


Thanks @m-dz!

Job done :)

Answer Source

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?