Transposing data of column X in condition of data in column Y

user_9011929 0
Viewed 159

I have an data organizing issue. I have data which looks like this:

ROW   date      names
1     1.1.2000  A
2     NA        B
3     NA        C
4     1.1.2000  X
5     NA        Y
6     2.1.2000  Z

I want it to look like this:

ROW   date      name1 name2 name3 name4
1     1.1.2000  A     B     C     NA
2     1.1.2000  X     Y     NA    NA
3     2.1.2000  Z     NA    NA    NA

So the code should check the column "date" and when it finds a date, it stores the row number(ROW1). Then, it will check the next rows of "date"-column and in case of "NA" value, the program stores their rownumbers(ROW y:x) until it finally finds the next date (next date row is not stored). The code then goes to the rows y:x in column "names" and moves their data into new columns in the ROW1. After this, the code does the same process for the next date it finds after rows y:x.

Whether the ROW1 is included in y:x doesn't matter to me since I have that data already within the right row. There is multiple indentical dates, as you can see it in my example and I need to keep them separated.

If you can help me even by mentioning some useful functions, it would help a lot since I don't know how to begin this.

tidyr dplyr transpose r
1 Answers
library(dplyr)
library(tidyr)

df = read.table(text = "
ROW   date      names
1     1.1.2000  A
2     NA        B
3     NA        C
4     1.1.2000  X
5     NA        Y
6     2.1.2000  Z
", header=T, stringsAsFactors=F)

df %>%
  group_by(ROW = cumsum(!is.na(date))) %>%       # create the rows of updated dataset based on rows without NAs; for each new row:
  mutate(counter = row_number(),                 # count how many columns you need for each new row
         date = unique(date[!is.na(date)])) %>%  # keep unique date after excluding NAs
  ungroup() %>%                                  # forget the grouping
  mutate(counter = paste0("name",counter)) %>%   # update variable to use as column names
  spread(counter, names)                         # reshape dataset

# # A tibble: 3 x 5
#     ROW     date name1 name2 name3
# * <int>    <chr> <chr> <chr> <chr>
# 1     1 1.1.2000     A     B     C
# 2     2 1.1.2000     X     Y  <NA>
# 3     3 2.1.2000     Z  <NA>  <NA>
user_51934850
Related