Like many topics in machine learning, explaining what a problem is, is nearly as difficult as solving it. The best way to describe the topic of this blog post is by way of a concrete example. Suppose you have mixed numeric and categorical data that looks like:

F,short,24,arkansas,29500,liberal
M,tall,39,delaware,51200,moderate
F,short,63,colorado,75800,conservative
M,medium,36,illinois,44500,moderate
F,short,27,colorado,28600,liberal
. . .

The fields are sex, height, age, State (one of four), annual income, and political leaning.

If you want to use this data for any machine learning algorithm that computes Euclidean distance between data items, the categorical data must be converted to numeric, and all the numeric data values must be normalized to roughly the same magnitude so that columns with large values (like income) don’t overwhelm columns with smaller values (like age). Example algorithms that use Euclidean distance are k-means clustering, k-nearest neighbors classification, and many others.

The normalized and encoded demo data could look like this:

[  0]    0.5  0.2500  0.1136  0.2500  0.0000  0.0000  0.0000  0.0374   2
[  1]    0.0  0.7500  0.4545  0.0000  0.0000  0.2500  0.0000  0.4886   1
[  2]    0.5  0.2500  1.0000  0.0000  0.2500  0.0000  0.0000  1.0000   0
[  3]    0.0  0.5000  0.3864  0.0000  0.0000  0.0000  0.2500  0.3493   1
[  4]    0.5  0.2500  0.1818  0.0000  0.2500  0.0000  0.0000  0.0187   2
. . .

Boolean variables are encoded as 0.0 or 0.5. Ordinal variables that have a natural ordering are equal-interval encoded. Numeric variables are min-max normalized. Categorical variables are one-over-n-hot encoded. For data that is to be used for classification, for example if the goal is to predict political leaning, the target variable is zero-based ordinal encoded.

I usually perform normalization and encoding manually, typically by dropping the raw source data into an Excel spreadsheet and manipulating it there. But sometimes I need to programmatically normalize and encode. In those situations, I usually write a custom one-off program that hard-codes all the relevant conversion information specific to the problem.

But one rainy Pacific Northwest Saturday morning, I decided to try and implement a general normalize-and-encode program that accepts conversion information as parameters. So I did. I used the C# language. The key parts of the demo calling code are:

string src = "..\\..\\..\\Data\\people_raw_20.txt";

// B = binary, O = ordinal, N = numeric,
// C = categorical, T = classification target
string[] colTypes = ["B","O","N","C","N","T"];

string[][] catValues = 
[
 ["M", "F"],
 ["short", "medium", "tall"],
 ["NA"],
 ["arkansas", "colorado", "delaware", "illinois"],
 ["NA"],
 ["conservative", "moderate", "liberal"]
];

double[][] trainX = 
  NormEncode(src, colTypes, catValues, ',', "#");
Console.WriteLine("Normalized and encoded: ");
MatShow(trainX, 
  new int[] { 1,4,4,4,4,4,4,4,0 }, 20, true);

The program was a bit trickier to implement than I had expected. I used the newer C# syntax for instantiating string arrays. The older style instantiation syntax is:

string[] colTypes = 
  new string[] { "B","O","N","C","N","T" };
string[][] catValues = new string[6][];
catValues[0] = 
  new string[] { "M", "F" };
catValues[1] = 
  new string[] { "short", "medium", "tall" };
catValues[2] = 
  new string[] { "NA" };
catValues[3] = 
  new string[] { "arkansas", "colorado", "delaware", "illinois" };
catValues[4] = 
  new string[] { "NA" };
catValues[5] = 
  new string[] { "conservative", "moderate", "liberal" };

Good fun.

Normalizing and encoding data is a kind of transformation. In science fiction movies and TV, there are many examples where aliens are transformed into looking like humans, usually with bad intentions. An example where a human is transformed into an alien is very rare.

Right: In the TV mini-series “V” (1983, 1984), aliens arrive on Earth. Their secret goal is to take over the planet. They have a conversion process to convert humans into their own reptilian form.

Demo code. Replace “lt” (less than), “gt”, “lte”, “gte” with Boolean operator symbols — my blog editor chokes on them.

using System;
using System.IO;

namespace NormEncodeForEuclidean
{
  internal class Program
  {
    static void Main(string[] args)
    {
      Console.WriteLine("\nProgrammatic" +
        " normalize and encode demo ");
      string src = "..\\..\\..\\Data\\people_raw_20.txt";

      string[] rawFile = FileLoad(src, "#");
      Console.WriteLine("\nSource file: ");
      for (int i = 0; i "lt" rawFile.Length; ++i)
        Console.WriteLine(rawFile[i]);

      //string[] colTypes = ["B","O","N","C","N","T"];
      string[] colTypes = 
        new string[] { "B", "O", "N", "C", "N", "T" };
      //string[][] catValues = 
      //[
      // ["M", "F"],
      // ["short", "medium", "tall"],
      // ["NA"],
      // ["arkansas", "colorado", "delaware", "illinois"],
      // ["NA"],
      // ["conservative", "moderate", "liberal"]
      //];
      string[][] catValues = new string[6][];
      catValues[0] = 
        new string[] { "M", "F" };
      catValues[1] = 
        new string[] { "short", "medium", "tall" };
      catValues[2] = 
        new string[] { "NA" };
      catValues[3] = 
        new string[] { "arkansas", "colorado",
          "delaware", "illinois" };
      catValues[4] = 
        new string[] { "NA" };
      catValues[5] = 
        new string[] { "conservative", 
          "moderate", "liberal" };


      double[][] trainX = 
        NormEncode(src, colTypes, catValues, ',', "#");
      Console.WriteLine("\nNormalized and encoded: ");
      MatShow(trainX, 
        new int[] { 1,4,4,4,4,4,4,4,0 }, 20, true);

      Console.WriteLine("\nEnd demo ");
      Console.ReadLine();
    } // Main

    // ------------------------------------------------------

    public static double[][] NormEncode(string fn,
      string[] colTypes, string[][] catValues,
      char delimit, string comment)
    {
      // colTypes: 
      // B = binary, O = ordinal, N = numeric,
      // C = categorical, T = target classification

      // determine number source columns
      int nSrcCols = colTypes.Length;

      // determine number of destination cols
      int nCols = 0;
      for (int i = 0; i "lt" colTypes.Length; ++i)
      {
        if (colTypes[i] == "B") nCols += 1;
        else if (colTypes[i] == "N") nCols += 1;
        else if (colTypes[i].StartsWith("O")) nCols += 1;
        else if (colTypes[i].StartsWith("T")) nCols += 1;
        else if (colTypes[i].StartsWith("C"))
        {
          int cLevels = catValues[i].Length;
          nCols += cLevels;
        }
        else
        {
          Console.WriteLine("Invalid colType string ");
        }
      }

      // determine number src rows == destination rows
      // and also compute mins and maxs of N-type columns
      int nRows = 0;
      string line = "";
      
      // compute mins-maxs of the N type columns
      // non-Numeric cols get 0 and 0
      double[][] minsMaxs = new double[2][];
      string[] lineTokens;
      minsMaxs[0] = new double[nSrcCols];  // mins
      minsMaxs[1] = new double[nSrcCols];  // maxs
      for (int j = 0; j "lt" nSrcCols; ++j)
      {
        if (colTypes[j] != "N") continue;
        minsMaxs[0][j] = double.MaxValue;
        minsMaxs[1][j] = double.MinValue;
      }

      FileStream ifs = new FileStream(fn, FileMode.Open);
      StreamReader sr = new StreamReader(ifs);
      while ((line = sr.ReadLine()) != null)
      {
        if (line.StartsWith(comment) == true) continue;

        ++nRows;
        line = line.Trim();
        lineTokens = line.Split(delimit);

        if (nSrcCols != lineTokens.Length)
          Console.WriteLine("Fatal: nSrcCols != " +
            "lineTokens.Length");

        for (int j = 0; j "lt" lineTokens.Length; ++j)
        {
          if (colTypes[j] != "N") continue;

          double val = double.Parse(lineTokens[j]);
          if (val "lt" minsMaxs[0][j]) minsMaxs[0][j] = val;
          if (val "gt" minsMaxs[1][j]) minsMaxs[1][j] = val;
        }
      }
      sr.Close(); ifs.Close();  // could reset fp instead

      // set up result matrix
      double[][] result = new double[nRows][];
      for (int r = 0; r "lt" nRows; ++r)
        result[r] = new double[nCols];

      // fill in result
      //string[][] catValues =
      //[
      // ["M", "F"],
      // ["short", "medium", "tall"],
      // ["NA"],
      // ["arkansas", "colorado", "delaware", "illinois"],
      // ["NA"],
      // ["conservative", "moderate", "liberal"]
      //];

      ifs = new FileStream(fn, FileMode.Open);
      sr = new StreamReader(ifs);
      int rowPtr = 0;
      
      while ((line = sr.ReadLine()) != null)
      {
        if (line.StartsWith(comment) == true) continue;
        line = line.Trim();
        lineTokens = line.Split(delimit);
        // like "M", "tall", "36", "colorado", "32500", "moderate"

        //Console.WriteLine(line); Console.ReadLine();
        int colPtr = 0;
        for (int j = 0; j "lt" lineTokens.Length; ++j)
        {
          if (colTypes[j] == "B")  // binary
          {
            if (lineTokens[j] == catValues[j][0])
              result[rowPtr][colPtr] = 0.0;
            else if (lineTokens[j] == catValues[j][1])
              result[rowPtr][colPtr] = 0.5;
            ++colPtr;
          }
          else if (colTypes[j].StartsWith("O"))  // ordinal
          {
            int numLevels = catValues[j].Length;
            for (int k = 0; k "lt" numLevels; ++k)
            {
              if (lineTokens[j] == catValues[j][k]) 
                result[rowPtr][colPtr] = 
                  ((k + 1) * 1.0) / (numLevels + 1);
            }
            ++colPtr;
          }
          else if (colTypes[j] == "N")  // numeric, do min-max
          {
            double val = double.Parse(lineTokens[j]);
            result[rowPtr][colPtr] = 
              (val - minsMaxs[0][j]) / 
              (minsMaxs[1][j] - minsMaxs[0][j]);
            ++colPtr;
          }
          else if (colTypes[j].StartsWith("C"))
          {
            int numValues = catValues[j].Length;
            for (int k = 0; k "lt" numValues; ++k)
            {
              if (lineTokens[j] == catValues[j][k])
              {
                result[rowPtr][colPtr + k] = 
                  1.0 / numValues;
              }
            }
            colPtr += numValues;
          }
          else if (colTypes[j].StartsWith("T"))  // target
          {
            int numValues = catValues[j].Length;
            for (int k = 0; k "lt" numValues; ++k) 
            {
              if (lineTokens[j] == catValues[j][k])  
              {
                result[rowPtr][colPtr] = k;
              }
            }
            ++colPtr;
          }
          else // should never happen
          {
            Console.WriteLine("Fatal: Unknown column type ");
            ++colPtr;
          }
          
        } // for j (end of rwo)
        ++rowPtr;
      }
      sr.Close(); ifs.Close();
      return result;
    }

    // ------------------------------------------------------

    public static void MatShow(double[][] m, int[] decs,
      int nRows, bool showIndices)
    {
      // decs[] = number decimals display for each column
      for (int i = 0; i "lt" nRows; ++i)
      {
        if (showIndices == true)
          Console.Write("[" + i.ToString().
            PadLeft(3) + "]  ");
        for (int j = 0; j "lt" m[0].Length; ++j)
        {
          double v = m[i][j];
          Console.Write(v.ToString("F" + decs[j]).
            PadLeft(decs[j] + 4));
        }
        Console.WriteLine("");
      }
      if (nRows "lt" m.Length)
        Console.WriteLine(" . . . ");
    }

    // ------------------------------------------------------

    public static string[] FileLoad(string fn,
      string comment)
    {
      List"lt"string"gt" lst = new List"lt"string"gt"();
      FileStream ifs = new FileStream(fn, FileMode.Open);
      StreamReader sr = new StreamReader(ifs);
      string line = "";
      while ((line = sr.ReadLine()) != null)
      {
        if (line.StartsWith(comment)) continue;
        line = line.Trim();
        lst.Add(line);
      }
      sr.Close(); ifs.Close();
      string[] result = lst.ToArray();
      return result;
    }

    // ------------------------------------------------------

    public static double[][] MatLoad(string fn,
      int[] usecols, char sep, string comment)
    {
      // self-contained
      int nRows = 0;
      string line = "";
      FileStream ifs = new FileStream(fn, FileMode.Open);
      StreamReader sr = new StreamReader(ifs);
      while ((line = sr.ReadLine()) != null)
        if (line.StartsWith(comment) == false)
          ++nRows;
      sr.Close(); ifs.Close();  // could reset fp instead

      int nCols = usecols.Length;
      double[][] result = new double[nRows][];
      for (int r = 0; r "lt" nRows; ++r)
        result[r] = new double[nCols];

      line = "";
      string[] tokens = null;
      ifs = new FileStream(fn, FileMode.Open);
      sr = new StreamReader(ifs);

      int i = 0;
      while ((line = sr.ReadLine()) != null)
      {
        if (line.StartsWith(comment) == true)
          continue;
        tokens = line.Split(sep);
        for (int j = 0; j "lt" nCols; ++j)
        {
          int k = usecols[j];  // into tokens
          result[i][j] = double.Parse(tokens[k]);
        }
        ++i;
      }
      sr.Close(); ifs.Close();
      return result;
    }

    // ------------------------------------------------------

  } // Program
} // ns

Demo data:

# people_raw_20.txt
#
F,short,24,arkansas,29500,liberal
M,tall,39,delaware,51200,moderate
F,short,63,colorado,75800,conservative
M,medium,36,illinois,44500,moderate
F,short,27,colorado,28600,liberal
F,short,50,colorado,56500,moderate
F,medium,50,illinois,55000,moderate
M,tall,19,delaware,32700,conservative
F,short,22,illinois,27700,moderate
M,tall,39,delaware,47100,liberal
F,short,34,arkansas,39400,moderate
M,medium,22,illinois,33500,conservative
F,medium,35,delaware,35200,liberal
M,tall,33,colorado,46400,moderate
F,short,45,colorado,54100,moderate
F,short,42,illinois,50700,moderate
M,tall,33,colorado,46800,moderate
F,tall,25,delaware,30000,moderate
M,medium,31,colorado,46400,conservative
F,short,27,arkansas,32500,liberal