-1

I'm a newbee to R-programming and would like to thank this forum for the invaluable suggestions this forum provides.

I'm working on a Dataframe DF1 in below format

I'm trying to create another dataframe DF2 where the values of column "Description" of DF1 would have be the Column names in DF2 and the Credit and Debit values corresponding to the Decription would have to be captured accordingly (Credits as positive numbers and debits as negatives)

DF1 DF2

I would be very grateful if you could advise the logic of attaining this in R. Thanks in advance. 

dput(DF1) is as below
structure(list(Account_number = c(1234, 1234, 1234, 3456, 3456, 
4567, 4567), Credit = c(5.1, NA, 10, NA, 10, 5, NA), Debit = c(NA, 
7.2, NA, 20, NA, NA, 30), Description = c("abc", "pqr", "xyz", 
"xyz", "abc", "pqr", "abc")), .Names = c("Account_number", "Credit", 
"Debit", "Description"), row.names = c(NA, -7L), class = c("tbl_df", 
"tbl", "data.frame"))

Vish

Vish
  • 25
  • 7
  • 1
    Can you provide `DF1` in a copy&paste-able form? Post the output of `dput(DF1)` at the end of your question. – markus Sep 03 '18 at 13:34
  • Hi markus - I have added the dput result to my question. Thanks again for the help. Cheers – Vish Sep 03 '18 at 13:53
  • After changing the sign of $Debit the rest of the solution is covered by https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format – jogo Sep 03 '18 at 14:13

1 Answers1

1

With tidyverse :

DF1 %>% mutate(Debit=-Debit) %>% 
  gather(k,v,-Account_number,-Description) %>%
  select(-k) %>% filter(!is.na(v)) %>% 
  spread(Description,v)

# A tibble: 3 x 4 
#  Account_number    abc    pqr   xyz
#*          <dbl>  <dbl>  <dbl> <dbl>
#1          1234.   5.10  -7.20   10.
#2          3456.  10.0   NA     -20.
#3          4567. -30.0    5.00   NA
Nicolas2
  • 2,170
  • 1
  • 6
  • 15
  • Hi Nicolas. Thank you for the solution. Tried running it, but it failed because the object k and v used haven't been defined. Can you please enlighten me if I need to define the objects k and v prior to running the above solution? – Vish Sep 03 '18 at 14:34
  • Hi Nicolas, please ignore my earlier comment. It was a mistake on my part. The solution suggested by you works great. Thanks a lot – Vish Sep 03 '18 at 14:44