C4.5 Decision Tree Algorithm completion with Excel in Mixed Data Case

Basically _ split decision tree algorithm Becomes a number of part , that is there is CART, ID3, C4.5, C5.0, Random Forest, and Gradient Boosting algorithms . of several algorithm the base from the calculation permanent same that is look for Entropy value as Step early , however for step next will different stages in accordance of each criterion in the algorithm that .

In example ID3 calculation will finished at stage calculate information gain. Different with C4.5 algorithm that can count until stage gain ratio even the C4.5 algorithm also works count only with information gain. And also can count with Here 's a different index with the gain ratio.

On some case algorithm C4.5 only solved with calculating information gain, especially in discrete data settlement or categorical and for mixed data , solution will more details _ that is with look for Gain Ratio value .

Well, on the article this I will write a number of method complete c4.5 algorithm in mixed data case using Ms. Excel with calculate the Gain Ratio.

In case this , I will complete with a defining data set appropriateness somebody get help from government with class yes and no . There are 6 data attributes consisting of of 3 categorical data and 3 numerical data . Following sample data ; 

Determining Attribute Entropy Value Class

As already _ is known that formula for look for score entropy is as following 

Well, for complete using excel, we need separate in 3 use models for make it easy for look for score entropy . Then you put in excel formulas each with COUNTA and COUNTIF for look for amount from each category

Next for look for score entropy is as following

Explanation from each column is

=((-L4/K4)*IMLOG2(L4/K4)+(-M4/K4)*IMLOG2(M4/K4))

K4 = Total Number of Data Sets

L4 = Total category "Yes"

M4 = Total category "No"

IMLOG2 = Log2 ( Description from formula entropy )

Count Entropy , Gain Info, and Gain Ratio on categorical data attributes

Stage next is determine score entropy , Information Gain, and Gain Ratio in the example valuable attribute _ discrete or categorical , in one example our will define on attribute type gender . For determine same entropy value case with determine the entropy attribute class above .

1. Define the amount of data in each category with determine Number of “ Male ” and “Female”.

• =COUNTIF($A$ 3:$ A$52,J13)
• $A$ 3:$ A$52 = Column “Gender”
• J13 = location column "Male"

Using the $ symbol in the formula is for lock column so that at copied in another cell location the column no change . You can push F4 key at the time choose column to be executed . 

2. Then define number of  "Male" in condition "Yes" and "No" classes .

You can use COUNTIFS formula . Example determine number of  "Male" in condition "Yes" class .

=COUNTIFS($A$2:$ A$28,$J13,$G$2:$G$28,$L$11)

• $A$2:$ A$28 = Column “Gender”
• $J13 = location column "Male"
• $G$2:$G$28 = “Can Government Assistance” Column
• $L$11 = location column "Yes".

You should too pay attention to the $ symbol , p this done so that we enough write the formula very then with same condition _ our only need for copy-paste course .

3. Next is determine score entropy 

in Thing this you enough copy the formula that has you do at the time count entropy class . If still confused you enough write down like this

4. Next is calculate Information Gain, 

formula for calculate the information gain is as following

For apply in Excel, we enough for follow plot the formula already written above , example .

=($N$4)-( (($K13/$K$4)*($N13)+($K14/$K$4)*($N14)))

• $N$4 = Total entropy value
• $K13 = Amount of “Male”
• $K$4 = Amount Of Data
• $N13 = Entropy Value of "Male"

• $K14 = Amount of " FeMale "
• $N14 = Entropy Value of "Female"

Read : Calculating Naive Bayes with Excel Numeric Data Attributes

5. Then define Split Information value. 

For determine the split info formula is like this.

And for determine using excel we also live follow the flow course . Example can you see under this

= -(K13/$K$4)*IMLOG2(K13/$K$4)-(K14/$K$4)*IMLOG2(K14/$K$4)

• K13 = Number of "Male"
• $K$4 = Amount Of Data
• K14 = Number of "Female"

6. Stage next is determine Gain Ratio value. 

On completion this can you do with easy because formula for look for the gain ratio value is 

For writing in excel enough like this course.

Count Entropy , Gain Info, and Gain Ratio on numeric data attributes .

In stages this , before you determine from all score that , stage beginning is make attribute score numeric finger attribute categorical . The method is you can determine with look for mean , median and mode. But if I personal usually what i do define is only with look for average 😊value .

Example : on the attribute "age"

I found the average value of attribute “ age ” is 65. Then next Separate use arithmetic becomes “>65” and “<=65”.

Well, for determine score of entropy, information gain, split information and gain ratio. You stay copy all table part from attribute "Gender ". 

Then you paste appropriate is at below , then change score attributes Becomes like this .

Next you block part total number of data – "no", such as this

Then you press on the keyboard Ctrl + H, and you change $A to $B. Because $A is location from column “gender” then our change it to column "age" to $B. Then click "Replace All".

Final completion

For solution to calculations this C4.5 algorithm , is search the highest Gain Ratio value , then specify the root . And for next you stay follow flow and stages from calculation algorithm C4.5, Because in the article this only discuss method count using Microsoft excel.=

Okay thank you , hope useful and you can find what are you search for "Don't forget to breathe and stay happy in gratitude” . 

See You 

Share :