Using Reshape to Combine Columns

Question

I have this dataset that I'm trying to melt and combine "Debit" and "Credit" into the same column.

random

    Address ID Debit Credit
1     tower1 A1    33     NA
2     happy1 A2    NA     24
3     today2 A3   145     NA
4 yesterday3 A4   122     NA
5    random3 A5    NA  14143

random <- melt(random, id = c("Address", "ID"))

      Address ID variable value
1      tower1 A1    Debit    33
2      happy1 A2    Debit    NA
3      today2 A3    Debit   145
4  yesterday3 A4    Debit   122
5     random3 A5    Debit    NA
6      tower1 A1   Credit    NA
7      happy1 A2   Credit    24
8      today2 A3   Credit    NA
9  yesterday3 A4   Credit    NA
10    random3 A5   Credit 14143

random[!(is.na(random$value)| random$value == ""),] #to remove NA and join them together

I'm wondering if it is possible to achieve my final dataset directly via reshape package?

This is the final dataset I hope to obtain

     Address ID variable value
1      tower1 A1    Debit    33
3      today2 A3    Debit   145
4  yesterday3 A4    Debit   122
7      happy1 A2   Credit    24
10    random3 A5   Credit 14143

Answer 1

We can use gather to convert the dataframe into long format and then use na.omit to remove NA rows.

library(tidyverse)

df %>%
  gather(key, value, -c(Address, ID)) %>%
  na.omit()


#   Address ID    key value
#1      tower1 A1  Debit    33
#3      today2 A3  Debit   145
#4  yesterday3 A4  Debit   122
#7      happy1 A2 Credit    24
#10    random3 A5 Credit 14143

gather also has na.rm parameter to remove NA rows

df %>% gather(key, value, -c(Address, ID), na.rm = TRUE) 

---

With reshape2 you can add na.rm = TRUE to remove NA rows

library(reshape2)
melt(df, id = c("Address", "ID"), na.rm = TRUE)

#      Address ID variable value
#1      tower1 A1    Debit    33
#3      today2 A3    Debit   145
#4  yesterday3 A4    Debit   122
#7      happy1 A2   Credit    24
#10    random3 A5   Credit 14143