====== csvmod

A stream-oriented CSV modification tool. Like a stripped-down "sed" command, but for tabular data.

TL;DR

Install:

.. code-block:: bash

$ pip install csvsed

Use:

.. code-block:: bash

given a sample CSV

$ cat sample.csv

Employee ID,Age,Wage,Status 8783,47,"104,343,873.83","All good, but nowhere to go." 2003,32,"98,878,784.00",A-OK

modify that data with a series of csvsed pipes

$ cat sample.csv \ | csvsed -c Wage s/,//g \ # remove commas from the Wage column | csvsed -c Status 'y/A-Z/a-z/' \ # convert Status to all lowercase | csvsed -c Status 's/.(ok|good)./\1/' \ # restrict to keywords 'ok' & 'good' | csvsed -c Age 'e/xargs -I {} echo "{}*2" | bc/' # double the Age column

Employee ID,Age,Wage,Status 8783,94,104343873.83,good 2003,64,98878784.00,ok

Installation

.. code-block:: bash

$ pip install csvsed

Usage and Examples

Installation of the csvsed python package also installs the csvsed command-line tool. Use csvsed --help for all command line options, but here are some examples to get you going. Given the input file sample.csv:

.. code-block:: text

Employee ID,Age,Wage,Status 8783,47,"104,343,873.83","All good, but nowhere to go." 2003,32,"98,878,784.00",A-OK

Removing thousands-separators from the "Wage" column using the "s" (substitute) modifier:

.. code-block:: bash

$ cat sample.csv | csvsed -c Wage s/,//g Employee ID,Age,Wage,Status 8783,47,104343873.83,"All good, but nowhere to go." 2003,32,98878784.00,A-OK

Convert/extract some text using the "s" (substitute) and "y" (transliterate) modifiers:

.. code-block:: bash

$ cat sample.csv | csvsed -c Status 's/^All (.),./\1/' \ | csvsed -c Status 's/^A-(.*)/\1/' \ | csvsed -c Status 'y/a-z/A-Z/' Employee ID,Age,Wage,Status 8783,47,"104,343,873.83",GOOD 2003,32,"98,878,784.00",OK

Square the "Age" column using the "e" (execute) modifier:

.. code-block:: bash

$ cat sample.csv | csvsed -c Age 'e/xargs -I {} echo "{}^2" | bc/' Employee ID,Age,Wage,Status 8783,2209,"104,343,873.83","All good, but nowhere to go." 2003,1024,"98,878,784.00",A-OK

That, however, called the external program for each column (quite inefficient with large data sets)... so let's do that more efficiently, with a "continuous" mode program. Given the following id2name.py program which takes a CSV on STDIN with a single column (an employee ID) and writes a CSV to STDOUT with the IDs converted to names:

.. code-block:: python

!/usr/bin/env python

import sys, csvkit table = {'8783': 'ElfenKyng', '2003': 'Stradivarius'}

NOTE: not using csvkit's reader because it reads-ahead

causing problems since this must be stream-oriented...

writer = csvkit.CSVKitWriter(sys.stdout) while True: item = sys.stdin.readline() if not item: break item = item.strip() writer.writerow([table[item] if item in table else item]) sys.stdout.flush()

Then the following will efficiently convert the 'Employee ID' column to names:

.. code-block:: bash

$ cat sample.csv | csvsed -c 'Employee ID' 'e|./id2name.py|c' Employee ID,Age,Wage,Status ElfenKyng,47,"104,343,873.83","All good, but nowhere to go." Stradivarius,32,"98,878,784.00",A-OK