Convert of specific rows to a new column in R

Question

I have a data frame that looks like this:

            1058       <NA>                <NA>
218   ZD/57/2020 2020-08-07                  50
219   ZD/78/2020 2020-09-11                  50
225         1059       <NA>                <NA>
236   ZD/57/2020 2020-08-07                  50
237   ZD/79/2020 2020-09-18                  50
243         1060       <NA>                <NA>
254   ZD/79/2020 2020-09-18                  30

I would like the numbers in the first column with NA remaining columns to form a separate column.

218   ZD/57/2020 2020-08-07                  50  1058
219   ZD/78/2020 2020-09-11                  50  1058
236   ZD/57/2020 2020-08-07                  50  1059       
237   ZD/79/2020 2020-09-18                  50  1059         
254   ZD/79/2020 2020-09-18                  30  1060 

> dput(data)
structure(list(one = c("1003", "ZD/57/2020", "ZD/58/2020", "ZD/70/2020", 
"ZD/78/2020", "1004", "ZD/58/2020", "ZD/78/2020"), three = structure(c(NA, 
1596758400, 1597363200, 1599177600, 1599782400, NA, 1597363200, 
1599782400), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    `Ilość na mag Główny` = c(NA, "40", "50", "50", "50", NA, 
    "50", "50")), row.names = c(4L, 15L, 16L, 17L, 18L, 24L, 
29L, 30L), class = "data.frame")

Can you please use `dput()` to share your data in reproducible form? Thanks — Sotos, Jul 23 '20 at 11:00
This is basically a filling NA backwards problem, a lot of this on SO. Look for "na.locf R" in Google. Here is an example https://stackoverflow.com/questions/13616965/how-to-fill-nas-with-locf-by-factors-in-data-frame-split-by-country — David Arenburg, Jul 23 '20 at 11:07

score 2 · Accepted Answer · answered Jul 23 '20 at 11:06

You can use fill() in tidyr.

library(dplyr)
library(tidyr)

df %>%
  mutate(V4 = ifelse(is.na(V2), V1, NA)) %>%
  fill(V4) %>%
  drop_na()

#           V1         V2 V3   V4
# 1 ZD/57/2020 2020-08-07 50 1058
# 2 ZD/78/2020 2020-09-11 50 1058
# 3 ZD/57/2020 2020-08-07 50 1059
# 4 ZD/79/2020 2020-09-18 50 1059
# 5 ZD/79/2020 2020-09-18 30 1060

Data

df <- structure(list(V1 = c("1058", "ZD/57/2020", "ZD/78/2020", "1059", 
"ZD/57/2020", "ZD/79/2020", "1060", "ZD/79/2020"), V2 = structure(c(NA, 
18481, 18516, NA, 18481, 18523, NA, 18523), class = "Date"), 
V3 = c(NA, 50, 50, NA, 50, 50, NA, 30)), row.names = c(NA, 
-8L), class = "data.frame")

Convert of specific rows to a new column in R

1 Answers1