Preprocessing of missing values. Data Mining and Business Intelligence GTU 2170715

Preprocessing of missing value :

Replace the missing values for given automobile dataset “imports-85.data” with mean value of each attribute class. (Consider no. of doors as the class attribute - 6th attribute)

Download Dataset From:

https://github.com/nyuvis/datasets/blob/master/auto/imports-85.data

Dataset Information:

https://archive.ics.uci.edu/ml/machine-learning-databases/autos/imports-85.names

Codes:

               import java.sql.*;

                    import java.util.TimeZone;

                   

                    public class DM3 {

                   

                                public static void main(String[] args) throws Exception{

                                            // TODO Auto-generated method stub

                                            Class.forName("oracle.jdbc.driver.OracleDriver");

                                           

                                            PreparedStatement qry;

                                            TimeZone t1;

                                            t1 = TimeZone.getTimeZone("yourtimezone");

                                            TimeZone.setDefault(t1);

                                           

                                            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "s16cos140", "student");

                   

                                    PreparedStatement ps = con.prepareStatement("select avg(NORMALIZED_LOSSES),avg(BORE),avg(STROKE),avg(PRICE) from dm4 where NUM_OF_DOORS='four' and NORMALIZED_LOSSES not LIKE '?%' and BORE not LIKE '?%' and STROKE not LIKE '?%' and PRICE not LIKE '?%'");

                                    ResultSet rs = ps.executeQuery();

                                    int norm=0,price=0;

                                    float bore=0,stroke=0;

                                    while (rs.next())

                                    {

                                            norm = rs.getInt(1);

                                            bore = rs.getFloat(2);

                                            stroke = rs.getFloat(3);

                                            price = rs.getInt(4);

                                    }

                                    System.out.println("Four Door\n Norm is:"+norm+"\nPrice: "+price+"\nBore: "+bore+"\nStroke: "+stroke);

                                    

                                    qry = con.prepareStatement("update DM4 set NORMALIZED_LOSSES='"+norm+"' where NUM_OF_DOORS='four' and NORMALIZED_LOSSES='?'");

                                    qry.executeUpdate();

                                    qry.close();

                                    

                                    qry = con.prepareStatement("update DM4 set BORE='"+bore+"' where NUM_OF_DOORS='four' and BORE='?'");

                                    qry.executeUpdate();

                                    qry.close();

                                    

                                    qry = con.prepareStatement("update DM4 set STROKE='"+stroke+"' where NUM_OF_DOORS='four' and STROKE='?'");

                                    qry.executeUpdate();

                                    qry.close();

                                    

                                    qry = con.prepareStatement("update DM4 set PRICE='"+price+"' where NUM_OF_DOORS='four' and PRICE='?'");

                                    qry.executeUpdate();

                                    qry.close();

                                    

                                    PreparedStatement ps2 = con.prepareStatement("select avg(NORMALIZED_LOSSES),avg(BORE),avg(STROKE),avg(PRICE) from dm4 where NUM_OF_DOORS='two' and NORMALIZED_LOSSES not LIKE '?%' and BORE not LIKE '?%' and STROKE not LIKE '?%' and PRICE not LIKE '?%'");

                                    ResultSet rs1 = ps2.executeQuery();

                                     norm=0;price=0;

                                     bore=0;stroke=0;

                                    while (rs1.next())

                                    {

                                            norm = rs1.getInt(1);

                                            bore = rs1.getFloat(2);

                                            stroke = rs1.getFloat(3);

                                            price = rs1.getInt(4);

                                    }

                                    System.out.println("Two Door \nNorm is:"+norm+"\nPrice: "+price+"\nBore: "+bore+"\nStroke: "+stroke);

                

                                    qry = con.prepareStatement("update DM4 set NORMALIZED_LOSSES='"+norm+"' where NUM_OF_DOORS='two' and NORMALIZED_LOSSES='?'");

                                    qry.executeUpdate();

                                    qry.close();

                                    

                                    qry = con.prepareStatement("update DM4 set BORE='"+bore+"' where NUM_OF_DOORS='two' and BORE='?'");

                                    qry.executeUpdate();

                                    qry.close();

                                    

                                    qry = con.prepareStatement("update DM4 set STROKE='"+stroke+"' where NUM_OF_DOORS='two' and STROKE='?'");

                                    qry.executeUpdate();

                                    qry.close();

                                    

                                    qry = con.prepareStatement("update DM4 set PRICE='"+price+"' where NUM_OF_DOORS='two' and PRICE='?'");

                                    qry.executeUpdate();

                                    qry.close();

                                }

                   

                    }