Filling in data in R (two columns of unequal length)

Question

Hello everyone and many thanks in advance for your help,

Kit of the question

As can be seen above, I have to assign numerical values from column A to column B, so that the number 4193 in column B matches the number 1 in column A each and every time, the number 15 in column B matches the number 2 in column A each and every time, and so on and so forth (this is just a random sample that I've presented as an example, as I'm working with an extremely large dataset). This should have been no big deal, but the thing is that these two columns are of different size (column A is much larger than column B).

I've spent hours and hours trying to do this by myself, as well as browsing forums, but I haven't found any similar question on how to get around this problem. Also, because the dataset I'm working with is extremely large, there is no way I could do this manually.

The main idea would be to have each single number in column B repeated standing side by side with its corresponding number from the column A (as explained previously). I don't know how to do this computationally, but logically, the idea is either to enlarge column B, or to put the numbers into another column, say, column C.

I'd be enormously grateful if anyone could help me out with this. A gist on how to do this in R would be much appreciated.

Many thanks once again!

ADDENDA:

DATA

df <- structure(list(A = c(rep(list(1048575))
), B = c(rep(list(10571)))), class = "data.frame", row.names = c(-10571L, -1048575L))

After introducing this data and trying to run the code, I get the following error: "Internal error in df_slice(): Columns must match the data frame size.".

CODE

The closest I have got so far to the desired 1048575 observations in Column B is by using the following code:

Result <- match(A, unique(B))

However, the given output is a matrix of 'NA's, while what I'm looking for is to have two numerical columns of 1048575 observations each.

Similarly, this other code does give a numerical output, but it is still in a matrix form and the order is incorrect:

Result <- match((A %in% unique(B)), B, table(A)[seq_along(B)])

The desired output should look like this:

Output

Thanks once again!

Answer 1

Here is one possible option if the data set is ordered using tidyverse. First, we keep only the rows with values in B, then we replace the values in A with just the unique values from A in the original df. Then, we can join that back to the original dataframe to fill all the rows with the corresponding values.

library(dplyr)

df %>%
  filter(complete.cases(B)) %>% 
  mutate(A = unique(df$A)) %>% 
  left_join(df %>% select(A), ., by = "A")

A base R option that is likely more efficient would be to use a combination of table and rep. Here, we take just the non-NA values from B, then repeat those values according to the frequency of each unique value in A, then we can just re-assign that to column B. Here, I also use factor with table to preserve the order of A.

df$B <- rep(df$B[!is.na(df$B)], table(factor(df$A, levels=unique(df$A))))

Output

   A    B
1  1 4193
2  1 4193
3  1 4193
4  1 4193
5  1 4193
6  1 4193
7  1 4193
8  2   15
9  2   15
10 2   15
11 3 9734
12 3 9734
13 3 9734
14 3 9734

Data

df <- structure(list(A = c(1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 3
), B = c(4193, 15, 9734, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA)), class = "data.frame", row.names = c(NA, -14L))