<jupyter_start><jupyter_text>Automating Boring Stuff with Python - Chapter 16a> Working with CSV- toc: true- badges: true- comments: true- categories: [basic]- image: images/automate_boring.png In this notebook, we are working with data that came with Colaboratory instead of the example files from the Book, because it's less trouble than if we try to import the files into the Colaboratory data folder.  Fundamentals  "What is CSV?"CSV stands for Comma Separated Values. They are simple files, i.e. lack features of xlsx:* Don’t have types for their values—everything is a string* Don’t have settings for font size or color* Don’t have multiple worksheets* Can’t specify cell widths and heights* Can’t have merged cells* Can’t have images or charts embedded in them  "Why should we use \import CSV?"- CSV files are text files, and can be read as a string (Chapter 9) which we can extract comma-separated values by calling split(',') on each line- BUT:     - not every comma in a CSV file represents the boundary between two cells    - CSV files have their own set of escape characters     - Why? to allow commas (and other char) to be included as values     - split() method doesn’t handle these escape characters  How to Read from CSV files  "How should we extract the values from CSV?"<jupyter_code>import csv      # part of Python standard library, no install required

# Opening the file in 'r' mode, which means 'read'
csv_handle = open("/content/sample_data/california_housing_test.csv", "r")  
print("Output of csv_holder: ", csv_handle)

# Reading the file using CSV library's reader function
csv_read = csv.reader(csv_handle)       # instead of the read() or readlines()
print("\nOutput of csv_read: ", csv_read)

# Converting to a Python list
csv_list = list(csv_read)       # this step loads the data into memory
print("\nOutput of csv_list: ", csv_list)

# Getting the value from Row 2 Col 3
row = 1
col = 3
print(f"\nValue from row {row} col {col}, i.e. csv_list[{row-1}][{col-1}] : ", \
      csv_list[row-1][col-1])  # count from Zero!<jupyter_output>Output of csv_holder:  <_io.TextIOWrapper name='/content/sample_data/california_housing_test.csv' mode='r' encoding='UTF-8'>

Output of csv_read:  <_csv.reader object at 0x7f5ad420ba58>

Output of csv_list:  [['longitude', 'latitude', 'housing_median_age', 'total_rooms', 'total_bedrooms', 'population', 'households', 'median_income', 'median_house_value'], ['-122.050000', '37.370000', '27.000000', '3885.000000', '661.000000', '1537.000000', '606.000000', '6.608500', '344700.000000'], ['-118.300000', '34.260000', '43.000000', '1510.000000', '310.000000', '809.000000', '277.000000', '3.599000', '176500.000000'], ['-117.810000', '33.780000', '27.000000', '3589.000000', '507.000000', '1484.000000', '495.000000', '5.793400', '270500.000000'], ['-118.360000', '33.820000', '28.000000', '67.000000', '15.000000', '49.000000', '11.000000', '6.135900', '330000.000000'], ['-119.670000', '36.330000', '19.000000', '1241.000000', '244.000000', '850.000000', '237.000000', '2.937500', '81700.000000'], [[...]<jupyter_text>What if the CSV is too large and overloads my system?- Some Kaggle datasets are more than 10-20 GB worth of CSV.- We read the data line by line, and loop through<jupyter_code>import csv      
# once again, we don't actually have to import csv in every cell as long as you 
# ran it once... but just in case you're running this cell only

# Opening the file
csv_handle2 = open("/content/sample_data/california_housing_test.csv", "r")
print("Output of csv_holder: ", csv_handle2)

# Reading the file using CSV library's reader function
csv_read2 = csv.reader(csv_handle2)       # instead of the read() or readlines()
print("\nOutput of csv_read: ", csv_read2)

# This part onwards is different if your CSV is too large
for line in csv_read2:
    print(line)<jupyter_output><empty_output><jupyter_text>How to Write to CSV files  How do we write to a CSV file?- call open() and pass it 'w' to open a file in write mode- pass this handle create the object you can then pass to csv.writer() ➋ to create a writer object.On Windows, you’ll also need to pass a blank string for the open() function’s newline keyword argument. For technical reasons beyond the scope of this book, if you forget to set the newline argument, the rows in output.csv will be double-spaced, as shown in Figure 16-1.<jupyter_code>import csv
# once again, we don't actually have to import csv in every cell as long as you 
# ran it once... but just in case you're running this cell only

outputFile = open('output.csv', 'a+', newline='')    
# the newline param is obligatory in Windows

# Please note, I opened the file above with a 'w+' mode, which means it creates 
# the file if it doesn't exist. Opening with 'a+' mode appends the data.

outputWriter = csv.writer(outputFile)

outputWriter.writerow(['spam', 'eggs', 'bacon', 'ham'])

outputWriter.writerow(['Hello, world!', 'eggs', 'bacon', 'ham'])

outputWriter.writerow([1, 2, 3.141592, 4])

outputFile.close()

# one-liner to open the file as we previously learned
readFile = list(csv.reader(open('output.csv', 'r')))    
print(readFile)<jupyter_output>[['spam', 'eggs', 'bacon', 'ham'], ['Hello, world!', 'eggs', 'bacon', 'ham'], ['1', '2', '3.141592', '4'], ['spam', 'eggs', 'bacon', 'ham'], ['Hello, world!', 'eggs', 'bacon', 'ham'], ['1', '2', '3.141592', '4'], ['spam', 'eggs', 'bacon', 'ham'], ['Hello, world!', 'eggs', 'bacon', 'ham'], ['1', '2', '3.141592', '4'], ['spam', 'eggs', 'bacon', 'ham'], ['Hello, world!', 'eggs', 'bacon', 'ham'], ['1', '2', '3.141592', '4']]<jupyter_text>What if I don't want to use Commas to separate my values when writing?- you want to separate cells with a tab character instead of a comma - you want the rows to be double-spacedfeed the csv.writer function with parameters ```csvWriter = csv.writer(csvFile, delimiter='\t', lineterminator='\n\n')csvWriter.writerow( ...```and you can call it .tsv to represent 'tab separated values'  Working with Header Rows<jupyter_code>import csv
# once again, we don't actually have to import csv in every cell as long as you 
# ran it once... but just in case you're running this cell only

# remember to use 'w+' if you want to create the file if not found
csvFile = open('output.tsv', 'w+', newline='')

csvWriter = csv.writer(csvFile, delimiter='\t', lineterminator='\n\n')

csvWriter.writerow(['apples', 'oranges', 'grapes'])
csvWriter.writerow(['eggs', 'bacon', 'ham'])
csvWriter.writerow(['spam', 'spam', 'spam', 'spam', 'spam', 'spam'])

csvFile.close()

# one-liner to open the file as we previously learned
readFile = list(csv.reader(open('output.tsv', 'r')))
print(readFile)<jupyter_output>[['apples\toranges\tgrapes'], [], ['eggs\tbacon\tham'], [], ['spam\tspam\tspam\tspam\tspam\tspam'], []]<jupyter_text>How can we keep data nicely aligned with headers so we don't make mistakes while working with row and col numbers?- CSV files that contain header rows- more convenient to work with the DictReader and DictWriter objects, rather than the reader and writer objects.- DictReader and DictWriter use dictionaries instead of lists- inside the loop, DictReader object sets row to a dictionary object with keys derived from the headers in the first row. - BUT if you don't have headers in the first row, the DictReader object would use first row values as headers. To avoid this, you can supply the DictReader() function with a second argument containing header names<jupyter_code>import csv
# once again, we don't actually have to import csv in every cell as long as you 
# ran it once... but just in case you're running this cell only

exampleFile = open('/content/sample_data/california_housing_test.csv', 'r')

exampleDictReader = csv.DictReader(exampleFile)

for row in exampleDictReader:
    print("Median Age:", int(float(row['housing_median_age'])), \
          "Median Value:", int(float(row['median_house_value'])), \
          "Population:", int(float(row['population'])))
    # just putting in a stop here so we don't have a super long print
    if float(row['housing_median_age']) <= 15: break

print("\n -----------Separator---------- \n")

# TRY UNCOMMENTING THE FOLLOWING TWO LINES OF CODE AND COMPARE THE RESULTS
exampleFile.close()
exampleFile = open('/content/sample_data/california_housing_test.csv', 'r')


# Input your own header row if the data doesn't have it

exampleDictReader_override = csv.DictReader(exampleFile, ['var_0', \
                                                          'var_1', \
                                                          'var_2', \
                                                          'var_3', \
                                                          'var_4', \
                                                          'var_5', \
                                                          'var_6', \
                                                          'var_7', \
                                                          'var_8'])

for row in exampleDictReader_override:
    # when header text is present, error when converting them to numbers
    try:        
        print("Median Age:", int(float(row['var_2'])), \
              "Median Value:", int(float(row['var_8'])), \
              "Population:", int(float(row['var_5'])))
        # just putting in a stop here so we don't have a super long print
        if float(row['var_2']) <= 15: break     
    except:
        continue<jupyter_output>Median Age: 27 Median Value: 344700 Population: 1537
Median Age: 43 Median Value: 176500 Population: 809
Median Age: 27 Median Value: 270500 Population: 1484
Median Age: 28 Median Value: 330000 Population: 49
Median Age: 19 Median Value: 81700 Population: 850
Median Age: 37 Median Value: 67000 Population: 663
Median Age: 43 Median Value: 67000 Population: 604
Median Age: 19 Median Value: 166900 Population: 1341
Median Age: 15 Median Value: 194400 Population: 1446

 -----------Separator---------- 

Median Age: 27 Median Value: 344700 Population: 1537
Median Age: 43 Median Value: 176500 Population: 809
Median Age: 27 Median Value: 270500 Population: 1484
Median Age: 28 Median Value: 330000 Population: 49
Median Age: 19 Median Value: 81700 Population: 850
Median Age: 37 Median Value: 67000 Population: 663
Median Age: 43 Median Value: 67000 Population: 604
Median Age: 19 Median Value: 166900 Population: 1341
Median Age: 15 Median Value: 194400 Population: 1446<jupyter_text>How do I write Header rows in my CSV?<jupyter_code>import csv
# once again, we don't actually have to import csv in every cell as long as you 
# ran it once... but just in case you're running this cell only

outputFile = open('output.csv', 'w+', newline='')

outputDictWriter = csv.DictWriter(outputFile, ['Name', 'Pet', 'Phone'])

outputDictWriter.writeheader()      
# This will write whatever headers you declared in the above line

outputDictWriter.writerow({'Name': 'Alice', 'Pet': 'cat', 'Phone': '555-1234'})
outputDictWriter.writerow({'Name': 'Bob', 'Phone': '555-9999'})
outputDictWriter.writerow({'Phone': '555-5555', 'Name': 'Carol', 'Pet': 'dog'})
outputFile.close()

outputFile = open('output.csv', 'r')
exampleDictReader = csv.DictReader(outputFile)

for row in exampleDictReader:
    print(row)<jupyter_output>OrderedDict([('Name', 'Alice'), ('Pet', 'cat'), ('Phone', '555-1234')])
OrderedDict([('Name', 'Bob'), ('Pet', ''), ('Phone', '555-9999')])
OrderedDict([('Name', 'Carol'), ('Pet', 'dog'), ('Phone', '555-5555')])<jupyter_text>Project: Removing the Header from CSV Files- useful only if you have many files (we will use os.listdir() )- open file- read file- rewrite file- close file<jupyter_code>#! python3
# removeCsvHeader.py - Removes the header from all CSV files in the current 
# working directory.

import csv, os

os.makedirs('headerRemoved', exist_ok=True)

# Loop through every file in the current working directory.
for csvFilename in os.listdir('.'):
    if not csvFilename.endswith('.csv'):
         continue    # skip non-csv files

    print('Removing header from ' + csvFilename + '...')

    # Read the CSV file in (skipping first row)
    csvRows = []
    csvFileObj = open(csvFilename)
    readerObj = csv.reader(csvFileObj)
    for row in readerObj:
        if readerObj.line_num == 1:
            continue    # skip first row
        csvRows.append(row)
    csvFileObj.close()

    # Write out the CSV file.
    csvFileObj = open(os.path.join('headerRemoved', csvFilename), \
                      'w', newline='')
    csvWriter = csv.writer(csvFileObj)
    for row in csvRows:
        csvWriter.writerow(row)
    csvFileObj.close()<jupyter_output>Removing header from output.csv...
Removing header from exampleWithHeader.csv...<jupyter_text>Additional Info  So what really happens if we don't use CSV reader?<jupyter_code>import csv      # we don't actually have to import this in every cell as long as you ran it once... but just in case


csv_handle = open("/content/sample_data/california_housing_test.csv", "r")
print(csv_handle)

csv_read = csv_handle.read()
print("\n", csv_read[:20])      # everything has been loaded as plain text

csv_split = csv_read.split()   # the rows are split, but the values are not
print("\n", csv_split[:20])

# one-liner
csv_directly = open("/content/sample_data/california_housing_test.csv", \
                    "r").read().split()
print("\n", csv_directly[:20])<jupyter_output><_io.TextIOWrapper name='/content/sample_data/california_housing_test.csv' mode='r' encoding='UTF-8'>

 "longitude","latitud

 ['"longitude","latitude","housing_median_age","total_rooms","total_bedrooms","population","households","median_income","median_house_value"', '-122.050000,37.370000,27.000000,3885.000000,661.000000,1537.000000,606.000000,6.608500,344700.000000', '-118.300000,34.260000,43.000000,1510.000000,310.000000,809.000000,277.000000,3.599000,176500.000000', '-117.810000,33.780000,27.000000,3589.000000,507.000000,1484.000000,495.000000,5.793400,270500.000000', '-118.360000,33.820000,28.000000,67.000000,15.000000,49.000000,11.000000,6.135900,330000.000000', '-119.670000,36.330000,19.000000,1241.000000,244.000000,850.000000,237.000000,2.937500,81700.000000', '-119.560000,36.510000,37.000000,1018.000000,213.000000,663.000000,204.000000,1.663500,67000.000000', '-121.430000,38.630000,43.000000,1009.000000,225.000000,604.000000,218.000000,1.664100,67000.000000', '-120.650000,35.[...]
{'fraction_non_alphanumeric': 0.1345260384161986, 'fraction_numerical': 0.09167186741557451, 'mean_word_length': 3.8805414551607447, 'pattern_counts': {'":': 0, '<': 31, '<?xml version=': 0, '>': 30, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 16, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '370734', 'license': 'Apache-2.0', 'n_tokens_mistral': 5537, 'n_tokens_neox': 4423, 'n_words': 1541, 'path': '_notebooks/2021-02-24-Automate-Ch16-CSV.ipynb', 'repo_name': 'ong-ekken/blog', 'size': '14421'}