xtab.py is a Python module and command-line program that rearranges data from a normalized format to a crosstabulated format. It takes data in this form:
| Station | Date | Value |
|---|---|---|
| WQ-01 | 2006-05-23 | 4.5 |
| WQ-02 | 2006-05-23 | 3.7 |
| WQ-03 | 2006-05-23 | 6.8 |
| WQ-01 | 2006-06-15 | 9.7 |
| WQ-02 | 2006-05-15 | 5.1 |
| WQ-03 | 2006-06-15 | 7.2 |
| WQ-01 | 2006-07-19 | 10 |
| WQ-02 | 2006-07-19 | 6.1 |
| WQ-03 | 2006-07-19 | 8.8 |
and rearranges it into this form:
| Station | 2006-05-23 | 2006-06-15 | 2006-07-19 |
|---|---|---|---|
| WQ-01 | 4.5 | 3.7 | 6.8 |
| WQ-02 | 9.7 | 5.1 | 7.2 |
| WQ-03 | 10 | 6.1 | 8.8 |
Input and output are both text (CSV) files.
Capabilities
You can use the xtab program to:
- Rearrange data exported from a database to better suit its subsequent usage in statistical, modeling, graphics, or other software, or for easier browsing.
- Convert a single file of data to a SQLite database.
- Check for multiple rows of data in a text file with the same key values.
Required and Optional Arguments
Required Arguments
-i <filename>
The name of the input file from which to read data.
This must be a text file, with data in a normalized format.
The first line of the file must contain column names.
-o <filename>
The name of the output file to create.
The output file will be created as a .csv file.
Required argument.
-r <column_name1> [column_name2 [...]]
One or more column names to use as row headers (space delimited).
Unique values of these columns will appear at the beginning of every
output line.
-c <column_name1> [column_name2 [...]]
One or more column names to use as column headers in the output (space
delimited). A crosstab column (or columns) will be created for every
unique combination of values of these fields in the input.
-v <column_name1> [column_name2 [...]]
One or more column names with values to be used to fill the cells
of the cross-table. If n columns names are specified, then there will
be n columns in the output table for each of the column headers
corresponding to values of the -c argument. The column names specified
with the -v argument will be appended to the output column headers
created from values of the -c argument. There should be only one value
of the -v column(s) for each combination of the -r and -c columns;
if there is more than one, a warning will be printed and only the first
value will appear in the output. (That is, values are not combined in
any way when there are multiple values for each output cell.)
Optional Arguments
-d[1|2|3|4]
Controls the format of column headers. The four alternatives are:
-d1 or no option specified
One row of column headers, with elements joined by underscores
to facilitate parsing by other programs.
-d or -d2
Two rows of column headers. The first row contains values of the
columns specified by the -c argument, and the second row contains
the column names specified by the -v argument.
-d3
One header row for each of the values of the columns specified by
the -c argument, plus one row with the column names specified by
the -v argument.
-d4
Like -d3, but the values of the columns specified by the -c
argument are labeled with (preceded by) the column names.
-f
Use a temporary (sqlite) file instead of memory for intermediate
storage.
-n
Use the specified default string in the output wherever an empty or
null value would otherwise appear.
-k
Keep (i.e., do not delete) the sqlite file. Only useful with the
"-f" option. Unless the "-t" option is also used, the table name will
be "src".
-t <tablename>
Name to use for the table in the intermediate sqlite database. Only
useful with the "-f" and "-k" options.
-e [filename]
Log all error messages, to a file if the filename is specified or to the
console if the filename is not specified.
-q <filename>
Log the sequence of SQL commands used to extract data from the input
file to write the output file, including the result of each command.
-h
Print this help and exit.
Notes
- The xtab program does not carry out any summarization or calculation on the data values, and therefore there should be no more than one data value to be placed in each cell of the output table. More than one value per cell is regarded as an error, and in such cases only one of the multiple values will be put in the cell.
- Multiple data values can be crosstabbed, as specified by multiple column names supplied with the -c argument.
- Column names should be specified in the same case as they appear in the input file.
- The -f option creates a temporary file in the same directory as the output file. This file has the same name as the input file, but an extension of '.sqlite'.
- There are no inherent limits to the number of rows or columns in the input or output files. (So the output may exceed the limits of some other software.)
- Missing required arguments will result in an exception rather than an error message, whatever the error logging option. If no error logging option is specified, then if there are multiple values to be put in a cell (the most likely data error), a single message will be printed on the console. If an error logging option is specified, then the SQL for all individual cases where there are multiple values per cell will be logged. The occurrence of multiple values for a cell can also be seen in the output from the -q option.
- The -d option produces output in a format that is intended to facilitate visual examination, rather than automated processing by subsequent software.
Copyright and License
Copyright (c) 2008, 2011 R.Dreas Nielsen
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. The GNU General Public License is available at http://www.gnu.org/licenses/.