Binning variables in a dataframe with input bin data from another dataframe
<p>Being a beginner-level user of <code>R</code>, despite having read (1) numerous posts about <code>binning&amp;grouping</code> here at SO, and (2) documentation on <code>data.table</code> and <code>dplyr</code> packages, I still can't figure out how to apply the power of those packages for binning continuous&amp;factor variables, for further use in credit scoring modelling. </p>

<p><strong>Problem: To build a code-efficient, easily-customisable, more or less automated solution for binning variables with minimal hard-coding.</strong> </p>

<p>These variables used to be binned with a stored procedure (<code>Oracle</code>), but I would like to switch entirely to <code>R</code> and operate the following dataframes: </p>

<p>to bin variables in <code>"df_Raw"</code> according to variable/bin ranges&amp;levels in <code>"binsDF"</code> and to store binned variables in <code>"df_Binned"</code>
. 
So far I have been able to produce simple straight-forward code that is lengthy,  error-prone (cut levels and labels are hard-coded), difficult to rollback and is just ugly; although it works. </p>

<p>The goal is to have this binning operation automated as much as possible with minimal hard-coding, so that to re-do binning it would take 
to update bin <code>ranges&amp;levels</code> in <code>"binsDF"</code> and to re-run the code rather than to manually edit all hard code. </p>

<p>I wonder how <code>**ply family</code> of functions and <code>dplyr</code> functions could be applied nicely to this problem. </p>

<p>Data description - the datasets have 100+ variables and 1-2 mln observations, with two types of variables to be binned: </p>

<ol>
<li><strong>Continuous variables.</strong> Example - <code>OVERDUEAMOUNT</code> - has values 0 (zero), "NA", and both negative&amp;positive numeric values. </li>
</ol>

<p><code>OVERDUEAMOUNT</code> needs to be split into 7 bins: <code>bin#1</code> contains only zeros, <code>bins#2-6</code> contain continuous values that need to be split into 5 custom-sized intervals, and <code>bin#7</code> contains only NAs. </p>

<ol start="2">
<li><strong>Factor variables</strong>, with both character and numeric values. Example - <code>PROFESSION</code> - has 4 levels: <code>"NA"</code> and 3 values/codes that stand for certain categories of professions/types of employment. </li>
</ol>

<p>It is important to place zeros and NAs in 2 separate bins, as they usually have very different interpretation from each other and from other values. </p>

<p>Datasets like iris or GermanCredit are not applicable due to not having NAs, strings or zeros, so I wrote some code below to replicate my data. 
Many thanks in advance! </p>

<p>Raw data to be binned. </p>

<pre><code>OVERDUEAMOUNT_numbers &lt;- rnorm(10000, mean = 9000, sd = 3000)
OVERDUEAMOUNT_zeros &lt;- rep(0, 3000)
OVERDUEAMOUNT_NAs &lt;- rep(NA, 4000)
OVERDUEAMOUNT &lt;- c(OVERDUEAMOUNT_numbers, OVERDUEAMOUNT_zeros, OVERDUEAMOUNT_NAs)

PROFESSION_f1 &lt;- rep("438", 3000) 
PROFESSION_f2 &lt;- rep("000", 4000)
PROFESSION_f3 &lt;- rep("selfemployed", 5000)
PROFESSION_f4 &lt;- rep(NA, 5000)
PROFESSION &lt;- c(PROFESSION_f1, PROFESSION_f2, PROFESSION_f3, PROFESSION_f4)

ID &lt;- sample(123456789:987654321, 17000, replace = TRUE); n_distinct(ID)

df_Raw &lt;- cbind.data.frame(ID, OVERDUEAMOUNT, PROFESSION) 
colnames(df_Raw) &lt;- c("ID", "OVERDUEAMOUNT", "PROFESSION")
</code></pre>

<p>Convert <code>PROFESSION</code> to factor to replicate this variable is processed &amp; prepared for further import into R. Reshuffle the dataframe row-wise to make it look like real data. </p>

<pre><code>df_Raw$PROFESSION &lt;- as.factor(df_Raw$PROFESSION) 
df_Raw &lt;- df_Raw[sample(nrow(df_Raw)), ]
</code></pre>

<p>Dataframe with bins. </p>

<pre><code>variable &lt;- c(rep("OVERDUEAMOUNT", 7), rep("PROFESSION", 4))
min &lt;- c(0, c(-Inf, 1500, 4000, 8000, 12000), "", c("438", "000", "selfemployed", ""))
max &lt;- c(0, c(1500, 4000, 8000, 12000, Inf), "", c("438", "000", "selfemployed", ""))
bin &lt;- c(c(1, 2, 3, 4, 5, 6, 7), c(1, 2, 3, 4))

binsDF &lt;- cbind.data.frame(variable, min, max, bin)
colnames(binsDF) &lt;- c("variable", "min", "max", "bin")
</code></pre>

<p>How I bin the variables: copy the list of IDs "as is" in a separate dataframe for further use in semi-joins as a "reference/standard" for the original list of IDs.</p>

<pre><code>dfID &lt;- as.data.frame(df_Raw$ID); colnames(dfID) &lt;- c("ID")
</code></pre>

<p><strong>Continuous variable</strong> - <code>OVERDUEAMOUNT</code>. Split the variable into 3 temporary dataframes: zeros, NAs and numeric observations to cut. </p>

<pre><code>df_tmp_zeros &lt;- subset(x=df_Raw, subset=(OVERDUEAMOUNT == 0), select=c(ID, OVERDUEAMOUNT)); nrow(df_tmp_zeros)
df_tmp_NAs &lt;- subset(x=df_Raw, subset=(is.na(OVERDUEAMOUNT)), select=c(ID, OVERDUEAMOUNT)); nrow(df_tmp_NAs)
df_tmp_numbers &lt;- subset(x=df_Raw, subset=(OVERDUEAMOUNT != 0 &amp; OVERDUEAMOUNT != is.na(OVERDUEAMOUNT)), select=c(ID, OVERDUEAMOUNT)); nrow(df_tmp_numbers)
(nrow(df_tmp_zeros) + nrow(df_tmp_NAs) + nrow(df_tmp_numbers)) == nrow(df_Raw) # double-check that all observations are split into 3 parts.
</code></pre>

<p>Replace zeros and NAs with an appropriate bin numbers. 
Specify number of intervals, interval ranges and partition numeric values into bins. 
Cut the variable into intervals. 
Merge 3 parts together. 
Append the binned variable to the final dataframe. </p>

<pre><code>df_tmp_zeros$OVERDUEAMOUNT &lt;- as.factor(1)
df_tmp_NAs$OVERDUEAMOUNT &lt;- as.factor(7)
cuts.OVERDUEAMOUNT &lt;- c(-Inf, 1500, 4000, 8000, 12000, Inf) 
labels.OVERDUEAMOUNT &lt;- c(2:6) 
df_tmp_numbers$OVERDUEAMOUNT &lt;- cut(df_tmp_numbers$OVERDUEAMOUNT, breaks = cuts.OVERDUEAMOUNT, labels = labels.OVERDUEAMOUNT, right = FALSE)

df_tmp_allback &lt;- rbind(df_tmp_zeros, df_tmp_NAs, df_tmp_numbers)
nrow(df_tmp_allback) == nrow(df_Raw) # double-check that all observations are added back. 

df_semijoin &lt;- semi_join(x=df_tmp_allback, y=dfID, by=c("ID")) # return all rows from x where there are matching values in y, keeping just columns from x.
glimpse(df_semijoin); summary(df_semijoin)

df_Binned &lt;- df_semijoin
str(df_Binned)
</code></pre>

<p>Factor variable - PROFESSION. Split the variable into several temporary dataframes: NAs and as many parts as there are other factor levels.</p>

<pre><code>df_tmp_f1 &lt;- subset(x=df_Raw, subset=(df_Raw$PROFESSION == "438"), select=c(ID, PROFESSION)); nrow(df_tmp_f1)
df_tmp_f2 &lt;- subset(x=df_Raw, subset=(df_Raw$PROFESSION == "000"), select=c(ID, PROFESSION)); nrow(df_tmp_f2)
df_tmp_f3 &lt;- subset(x=df_Raw, subset=(df_Raw$PROFESSION == "selfemployed"), select=c(ID, PROFESSION)); nrow(df_tmp_f3)
df_tmp_NAs &lt;- subset(x=df_Raw, subset=(is.na(PROFESSION)), select=c(ID, PROFESSION)); nrow(df_tmp_NAs)

df_tmp_f1$PROFESSION &lt;- as.factor(1)
df_tmp_f2$PROFESSION &lt;- as.factor(2)
df_tmp_f3$PROFESSION &lt;- as.factor(3)
df_tmp_NAs$PROFESSION &lt;- as.factor(4)

df_tmp_allback &lt;- rbind(df_tmp_f1, df_tmp_f2, df_tmp_f3, df_tmp_NAs)
nrow(df_tmp_allback) == nrow(df_Raw) # double-check that all observations are added back. 

df_semijoin &lt;- semi_join(x=df_tmp_allback, y=dfID, by=c("ID")) # return all rows from x where there are matching values in y, keeping just columns from x.
str(df_semijoin); summary(df_semijoin)

df_Binned &lt;- cbind(df_Binned, df_semijoin$PROFESSION)
str(df_Binned)
</code></pre>

<p>And so on...</p>

<p>P.S. UPDATE: 
The best solution to this problem is given in this post. 
<a href="https://stackoverflow.com/questions/24480031/roll-join-with-start-end-window">roll join with start/end window</a></p>

<p>These posts are also helpful: 
<a href="https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right">How to join (merge) data frames (inner, outer, left, right)?</a> 
<a href="https://stackoverflow.com/questions/12773822/why-does-xy-join-of-data-tables-not-allow-a-full-outer-join-or-a-left-join">Why does X[Y] join of data.tables not allow a full outer join, or a left join?</a></p>

<p>The idea is as follows: make a subset from the dataframe with raw data (1 column with unique IDs, 1 column with raw data (values of the variable) and 1 column with the name of variable (use rep() to repeat the variable name as many times are there are observations of the variable; then make a subset from the dataframe with bins with just one variable (as many rows as many bins of that particular variable), and in my case 4 columns - Variable, Min, Max, Bin. 
See sample code below:  </p>

<p>Also I tried foverlaps() from data.table package, but it can't handle NAs; processing of NAs has to be done separately AFAIU; another solution is to use rolling joins but I haven't cracked that yet. Will appreciate advice with the rolling joins. </p>

<pre><code># Subset numeric variables by variable name. 

rawDF_num_X &lt;- cbind(rawDF2bin$ID, 
                 rep(var_num, times = nrow(rawDF2bin[, vars_num_raw][var_num])), 
                 rawDF2bin[, vars_num_raw][var_num])
colnames(rawDF_num_X) &lt;- c("ID", "Variable", "Value")
rawDF_num_X &lt;- as.data.table(rawDF_num_X)

# Subset table with bins for numeric variables by variable name. 

bins_num_X &lt;- bins_num[bins_num$Variable == var_num, ] 
bins_num_X &lt;- arrange(bins_num_X, Bin) # sort by bin values, in ascending order. 
bins_num_X &lt;- as.data.table(bins_num_X)

# Select and join numeric variables with their corresponding bins using sqldf package. 

vars_num_join &lt;- sqldf("SELECT a.ID, a.Variable, a.Value, b.Min, b.Max, b.Bin 
                        FROM rawDF_num_X AS a, bins_num_X AS b 
                        WHERE a.Variable = b.Variable AND a.Value between b.Min and b.Max
                        OR a.Value IS NULL AND b.Min IS NULL AND b.Max IS NULL") 
View(vars_num_join); dim(vars_num_join)

# Create a TRUE/FALSE flag/check according to the binning conditions. 

vars_num_join$check &lt;- ifelse((is.na(vars_num_join$Value)= TRUE &amp; is.na(vars_num_join$Min) == TRUE &amp; is.na(vars_num_join$Max) == TRUE), "TRUE", 
                               ifelse((vars_num_join$Value == 0 &amp; vars_num_join$Min == 0 &amp; vars_num_join$Max == 0), "TRUE", 
                                      ifelse((vars_num_join$Value != 0 &amp; vars_num_join$Value &gt;= vars_num_join$Min &amp; vars_num_join$Value &lt; vars_num_join$Max), "TRUE", "FALSE")))

# Remove (duplicate) rows that have FALSE flag due to not matching the binning conditions. 

vars_num_join &lt;- vars_num_join[vars_num_join$check == TRUE, ]
identical(rawDF2bin$ID, vars_num_join$ID) # should be TRUE 
</code></pre>