Data cleaning handles values and formats that would otherwise disrupt processing: missing markers, invalid characters, extra whitespace, awkward ordering, and numbers stored as text. It is often part of transforming a data file into a consistent, usable format. section 21.4 section 21.7
Choose a treatment that fits the next operation:
- Replace a marker such as "Missing" with None or an empty string, or leave it in place and skip that value during calculations. section 21.4.1
- Remove a suffix such as % before converting its text to a number; divide by 100 if the result should be a fraction. section 24.3.2
- Discard fields you do not need, and filter out records or trailing documentation that do not belong in the output. section 21.6.2
Make two fields usable for calculations
A heat-index field may contain "Missing", while a coverage field contains text such as "35.11%": row = ["Missing", "35.11%"] heat = None if row[0] == "Missing" else float(row[0]) coverage = float(row[1].replace("%", "")) / 100 print(heat, coverage) # None 0.3511 Exclude the missing heat value when computing an average; do not pass it to arithmetic as though it were a temperature. section 21.4.1 section 24.3.2
import csv
with (open("Illinois_weather_1979-2011.txt") as infile,
open("Illinois_weather_1979-2011.csv", "w")
as outfile): #1
for row in csv.reader(infile, delimiter="\t"):
if row[0] == '---': #2
break
if "Cook" in row[3]:
del row[0:2] #3
row = [item.replace("Missing", "")
for item in row] #4
row[-1] = row[-1].replace("%", "") #5
row[-1] = (float(row[-1])/100 if str.isdigit(row[-1])
else row[-1]) #6
csv.writer(outfile).writerow(row)
For a complete file pass, this Python csv example reads tab-delimited rows, stops at a documentation separator, selects the desired county, removes fields, cleans values, and writes CSV rows. section 21.6.2
Pandas offers a column-wise alternative. When loading a CSV, use read_csv with na_values=['Missing'] to turn that marker into NaN, and use usecols to omit unwanted columns. For a percentage column, strip %, convert with pd.to_numeric, then divide by 100. Plain Python remains an option; pandas is not the best fit for every dataset or near-real-time task. section 24.3.2 section 24.7
temp[17] = pd.to_numeric(temp[17].str.strip("%")).div(100)
This compact pandas expression combines stripping the suffix, numeric conversion, and conversion to a fraction for one column. section 24.3.2
