xsv

xsv is now unmaintained In lieu of xsv, I'd recommend either qsv or xan. xsv is a command line program for indexing, slicing, analyzing, splitting and joining CSV files. Commands should be simple, fast and composable: Simple tasks should be easy. Performance trade offs should be exposed in the CLI interface. Composition should not come at the expense of performance. This README contains information on how to install xsv, in addition to a quick tour of several commands. Dual-licensed under MIT or the UNLICENSE. Available commands cat - Concatenate CSV files by row or by column. count - Count the rows in a CSV file. (Instantaneous with an index.) fixlengths - Force a CSV file to have same-length records by either padding or truncating them. flatten - A flattened view of CSV records. Useful for viewing one record at a time. e.g., xsv slice -i 5 data.csv | xsv flatten. fmt - Reformat CSV data with different delimiters, record terminators or quoting rules. (Supports ASCII delimited data.) frequency - Build frequency tables of each column in CSV data. (Uses parallelism to go faster if an index is present.) headers - Show the headers of CSV data. Or show the intersection of all headers between many CSV files. index - Create an index for a CSV file. This is very quick and provides constant time indexing into the CSV file. input - Read CSV data with exotic quoting/escaping rules. join - Inner, outer and cross joins. Uses a simple hash index to make it fast. partition - Partition CSV data based on a column value. sample - Randomly draw rows from CSV data using reservoir sampling (i.e., use memory proportional to the size of the sample). reverse - Reverse order of rows in CSV data. search - Run a regex over CSV data. Applies the regex to each field individually and shows only matching rows. select - Select or re-order columns from CSV data. slice - Slice rows from any part of a CSV file. When an index is present, this only has to parse the rows in the slice (instead of all rows leading up to the start of the slice). sort - Sort CSV data. split - Split one CSV file into many CSV files of N chunks. stats - Show basic types and statistics of each column in the CSV file. (i.e., mean, standard deviation, median, range, etc.) table - Show aligned output of any CSV data using elastic tabstops. A whirlwind tour Let's say you're playing with some of the data from the Data Science Toolkit, which contains several CSV files. Maybe you're interested in the population counts of each city in the world. So grab the data and start examining it: $ curl -LO https://burntsushi.net/stuff/worldcitiespop.csv $ xsv headers worldcitiespop.csv 1 Country 2 City 3 AccentCity 4 Region 5 Population 6 Latitude 7 Longitude The next thing you might want to do is get an overview of the kind of data that appears in each column. The stats command will do this for you: $ xsv stats worldcitiespop.csv --everything | xsv table field type min max min_length max_length mean stddev median mode cardinality Country Unicode ad zw 2 2 cn 234 City Unicode bab el ahmar Þykkvibaer 1 91 san jose 2351892 AccentCity Unicode Bâb el Ahmar ïn Bou Chella 1 91 San Antonio 2375760 Region Unicode 00 Z9 0 2 13 04 397 Population Integer 7 31480498 0 8 47719.570634 302885.559204 10779 28754 Latitude Float -54.933333 82.483333 1 12 27.188166 21.952614 32.497222 51.15 1038349 Longitude Float -179.983333 180 1 14 37.08886 63.22301 35.28 23.8 1167162 The xsv table command takes any CSV data and formats it into aligned columns using elastic tabstops. You'll notice that it even gets alignment right with respect to Unicode characters. So, this command takes about 12 seconds to run on my machine, but we can speed it up by creating an index and re-running the command: $ xsv index worldcitiespop.csv $ xsv stats worldcitiespop.csv --everything | xsv table ... Which cuts it down to about 8 seconds on my machine. (And creating the index takes less than 2 seconds.) Notably, the same type of "statistics" command in another CSV command line toolkit takes about 2 minutes to produce similar statistics on the same data set. Creating an index gives us more than just faster statistics gathering. It also makes slice operations extremely fast because only the sliced portion has to be parsed. For example, let's say you wanted to grab the last 10 records: $ xsv count worldcitiespop.csv 3173958 $ xsv slice worldcitiespop.csv -s 3173948 | xsv table Country City AccentCity Region Population Latitude Longitude zw zibalonkwe Zibalonkwe 06 -19.8333333 27.4666667 zw zibunkululu Zibunkululu 06 -19.6666667 27.6166667 zw ziga Ziga 06 -19.2166667 27.4833333 zw zikamanas village Zikamanas Village 00 -18.2166667 27.95 zw zimbabwe Zimbabwe 07 -20.2666667 30.9166667 zw zimre park Zimre Park 04 -17.8661111 31.2136111 zw ziyakamanas Ziyakamanas 00 -18.2166667 27.95 zw zizalisari Zizalisari 04 -17.7588889 31.0105556 zw zuzumba Zuzumba 06 -20.0333333 27.9333333 zw zvishavane Zvishavane 07 79876 -20.3