CODEWITHSUNDEEP
rdataframedplyrtidyversetidyr
Johnny Thomas
Johnny Thomas

Reputation: 623

Need to separate out variable names from a column in r

So I have a pretty bad dataset I am not allowed to change. I would like to take the column "Draw_CashFlow" and make only certain values into their own columns. Additionally I need to make the variables all one column (period) (wide to Tidy if you will).

In the dataset below we have a column (Draw_CashFlow) which begins with the variable in question followed by a list of IDs, then repeats for the next variable. Some variables may have NA entries.

structure(list(Draw_CashFlow = c("Principal", "R01", 
"R02", "R03", "Workout Recovery Principal", 
"Prepaid Principal", "R01", "R02", "R03", 
"Interest", "R01", "R02"), `PERIOD 1` = c(NA, 
834659.51, 85800.18, 27540.31, NA, NA, 366627.74, 0, 0, NA, 317521.73, 
29175.1), `PERIOD 2` = c(NA, 834659.51, 85800.18, 27540.31, NA, 
NA, 306125.98, 0, 0, NA, 302810.49, 28067.8), `PERIOD 3` = c(NA, 
834659.51, 85800.18, 27540.31, NA, NA, 269970.12, 0, 0, NA, 298529.92, 
27901.36), `PERIOD 4` = c(NA, 834659.51, 85800.18, 27540.31, 
NA, NA, 307049.06, 0, 0, NA, 293821.89, 27724.4)), row.names = c(NA, 
-12L), class = c("tbl_df", "tbl", "data.frame"))

Now it is a finite list of variables needed (Principal, Workout Recovery Principal, Prepaid Principal, and Interest) so I tried to make a loop where it would see if it existed then gather but that was not correct.

After the variables are set apart from Draw_CashFlow I hope it looks something like this (First four rows, ignore variable abbreviations).

ID  Period   Principal  Wrk_Reco_Principal   Prepaid_Principal    Interest
R01      1   834659.51                  NA           366627.74   317521.73
R02      1    85800.18                  NA                0.00    29175.10
R03      1    27540.31                  NA                0.00          NA
R01      2   834659.51                  NA           306125.98   302810.49

Notes: Wrl_Reco_Principal is NA because there are no ID's within this Draw_CashFlow for this variable. Keep in mind this is supposed to be built to combat any number of IDs, but the variable names in the Draw_CashFlow column will always be the same.

Upvotes: 0

Views: 209

Answers (1)

Jon Spring
Jon Spring

Reputation: 66935

Here's an approach which assumes the Draw_CashFlow values that start with an R are ID numbers. You might need a different method (e.g. !Draw_CashFlow %in% LIST_OF_VARIABLES) if that doesn't hold up.

df %>%
  # create separate columns for ID and Variable
  mutate(ID = if_else(Draw_CashFlow %>% str_starts("R"),
                      Draw_CashFlow, NA_character_),
         Variable = if_else(!Draw_CashFlow %>% str_starts("R"),
                        Draw_CashFlow, NA_character_)) %>%
  fill(Variable) %>%  # Fill down Variable in NA rows from above
  select(-Draw_CashFlow) %>%
  gather(Period, value, -c(ID, Variable)) %>%  # Gather into long form
  drop_na() %>%
  spread(Variable, value, fill = 0) %>% # Spread based on Variable
  mutate(Period = parse_number(Period))


# A tibble: 12 x 5
   ID    Period Interest `Prepaid Principal` Principal
   <chr>  <dbl>    <dbl>               <dbl>     <dbl>
 1 R01        1  317522.             366628.   834660.
 2 R01        2  302810.             306126.   834660.
 3 R01        3  298530.             269970.   834660.
 4 R01        4  293822.             307049.   834660.
 5 R02        1   29175.                  0     85800.
 6 R02        2   28068.                  0     85800.
 7 R02        3   27901.                  0     85800.
 8 R02        4   27724.                  0     85800.
 9 R03        1       0                   0     27540.
10 R03        2       0                   0     27540.
11 R03        3       0                   0     27540.
12 R03        4       0                   0     27540.

Upvotes: 1

Related Questions