Data cleaning homework

1. Data cleaning. Remove any row with a bad value: text where a number should be (like NA, ?, or a number written in words), a weight or diameter that is zero or negative, a number off by orders of magnitude (like 99999 g), or a misspelled color.
2. Imputation. Fill each blank cell with the average of its column, after removing bad rows.
3. Rescaling. Rescale any numeric column with values above 1 by dividing by its maximum.
4. Feature engineering. Replace each column of categories (text values) with one-hot columns.
5. Make two new tabs, named “inputs” and “targets”. Copy the input features into “inputs” and the one-hot fruit columns into “targets”.
6. Train a classifier in Simbrain to predict the fruit.

These assume the data runs from row 2 to row 32, with the column names in row 1. Adjust the letters and row numbers to match your sheet.

Impute a cell. To fill a blank, for example C12, with the average of the cells above and below it, type =AVERAGE(C2:C11, C13:C32).

Rescale. In an empty column, type =A2/MAX(A:A) and fill down.

One-hot. Type the category names (apple, orange, lemon) as column names in row 1 of three empty columns, for example G, H, I. In G2 type =IF($E2=G$1,1,0) and fill down and across. Do the same for color with red, green, yellow, orange.