Table ¶

append_avro(target: Path | str, schema: dict | None = None, sample: int = 9, **avro_args) → str¶

Append table to an existing Avro file.

In order to use this method, you must have the fastavro library installed. You can install it with pip install parsons[avro].

This method assume that each column has values with the same type for all rows of the source table.

Parameters:

target (Path | str) – The file path for an existing avro file.
schema (dict | None) – Defines the rows field structure of the file. Check fastavro documentation and Avro schema reference for details.
sample (int) – Defines how many rows are inspectedfor discovering the field types and building a schema for the avro file when the schema argument is not passed.
**avro_args – Additional options to foward directly to fastavro. See fastavro documentation for reference.

Returns:

The path of the updated file

Return type:

append_csv(local_path: Path | str, encoding: str | None = None, errors: str = 'strict', **csvargs) → str¶

Append table to an existing CSV.

Additional keyword arguments are passed to csv.writer(). So, e.g., to override the delimiter from the default CSV dialect, provide the delimiter keyword argument.

Parameters:

local_path (Path | str) – The local path of an existing CSV file. If it ends in .gz, the file will be compressed.
encoding (str | None) – The CSV encoding type for csv.writer()
errors (str) – Raise an Error if encountered
**csvargs – Additional keyword arguments are passed to csv.writer().

Returns:

The path of the updated csv file

Return type:

chunk(rows: int) → list[Table]¶

Divides a Parsons table into smaller tables of a specified row count.

If the table cannot be divided evenly, then the final table will only include the remainder.

Parameters:: rows (int) – The number of rows of each new Parsons table
Return type:: list[Table]

coalesce_columns(dest_column: str, source_columns: Sequence[str], remove_source_columns: bool = True) → Self¶

Coalesces values from one or more source columns into a destination column.

The first non-empty value will be used. If the destination column doesn’t exist, it will be added.

Parameters:

dest_column (str) – Name of destination column
source_columns (Sequence[str]) – List of source column names
remove_source_columns (bool) – Whether to remove the source columns after the coalesce. If the destination column is also one of the source columns, it will not be removed.

Return type:

Self

concat(*tables: Table, missing: Any | None = None) → None¶

Concatenates one or more tables onto this one.

Note that the tables do not need to share exactly the same fields. Any missing fields will be padded with None, or whatever is provided via the missing keyword argument.

Parameters:

tables (Table) – A single table, multiple tables, or a list/tuple of tables.
missing (Any | None) – The value to use when padding missing values

Return type:

None

convert_column(column: str | Iterable[str], updater: Callable | str | dict[Any, Any], *args, **kwargs) → Self¶

Transform values under one or more fields.

Transformation is possible via arbitrary functions, method invocations or dictionary translations.

This leverages petl.convert(). Example usage can be found here.

Parameters:

column (str | Iterable[str]) – Column(s) to convert (name, iterable)
updater (Callable | str | dict[Any, Any]) – Update via Callable, method name, dict translation, or variable to process the update
*args – Additional positional arguments to pass to petl.convert()
**kwargs – Additional keyword arguments to pass to petl.convert()

Return type:

Self

convert_columns_to_str() → Self¶

Convert all non-string or mixed columns strings.

Can be very useful for comparison operations.

Return type:: Self

convert_table(updater: Callable | str | dict[Any, Any], *args, **kwargs) → Self¶

Transform all cells in a table.

Useful for cleaning fields and data hygiene functions such as regex.

Transformation is possible via arbitrary functions, method invocations or dictionary translations.

This leverages petl.convert(). Example usage can be found here.

Parameters:

updater (Callable | str | dict[Any, Any]) – Update via Callable, method name, dict translation, or variable to process the update
*args – Additional positional arguments to pass to petl.convert()
**kwargs – Additional keyword arguments to pass to petl.convert()

Return type:

Self

cut(*columns: str) → Table¶

Return the selected columns as a new Table.

Parameters:: *columns (str) – Columns in the parsons table
Return type:: Table

deduplicate(keys: str | Sequence[str] | None = None, presorted: bool = False) → Self¶

Deduplicate table.

All keys specified in the keys argument are considered when deduplicating, not each key individually. For example, if keys=['a', 'b'], the method will not remove a record unless it’s identical to another record in both columns a and b.

tbl.table¶
a	b
1	3
1	2
1	2
2	3

Remove all subsequent rows with {‘a’: 1}¶

tbl = Table([['a', 'b'], [1, 3], [1, 2], [1, 2], [2, 3]])
tbl.deduplicate('a')

tbl.table¶
a	b
1	3
2	3

Remove all subsequent rows with {‘a’: 1} and {‘b’: 3}¶

tbl = Table([['a', 'b'], [1, 3], [1, 2], [1, 2], [2, 3]])
tbl.deduplicate(['a', 'b'])

# Table is deduplicated on both ('a', 'b'), so as (1, 2) was placed
# before (1, 3) second instance of {'a': 1} or {'b': 3} was not removed.

tbl.table¶
a	b
1	2
1	3
2	3

Remove all subsequent rows with {‘a’: 1} and then all with {‘b’: 3}¶

tbl = Table([['a', 'b'], [1, 3], [1, 2], [1, 2], [2, 3]]) # reset
tbl.deduplicate('a').deduplicate('b')

tbl.table¶
a	b
1	3

The order of deduplication matters¶

tbl = Table([['a', 'b'], [1, 3], [1, 2], [1, 2], [2, 3]]) # reset
tbl.deduplicate('b').deduplicate('a')

tbl.table¶
a	b
1	2

Parameters:

keys (str | Sequence[str] | None) – keys to deduplicate (and optionally sort) on.
presorted (bool) – If False, the row will also be sorted.

Return type:

Self

fill_column(column_name: str, fill_value: Any) → Self¶

Fill all values of a column in a table.

Parameters:

column_name (str) – The column to fill
fill_value (Any) – A fixed or calculated value

Return type:

fillna_column(column_name: str, fill_value: Any) → Self¶

Fill only None values of a column in a table.

Parameters:

column_name (str) – The column to fill
fill_value (Any) – A fixed or calculated value

Return type:

classmethod from_avro(local_path: Path | str, limit: int | None = None, skips: int | None = 0, **avro_args) → Table¶

Create a Table from an Avro file.

Parameters:

local_path (Path | str) – The path to the Avro file.
limit (int | None) – The maximum number of rows to extract. Default is None (all rows).
skips (int | None) – The number of rows to skip from the start.
**avro_args – Additional arguments passed to fastavro.reader().

Return type:

Create a Table from a BigQuery statement.

To pull an entire BigQuery table, use a query like SELECT * FROM {{ table }}.

Parameters:

sql (str) – str A valid SQL statement
app_creds (str | None) – str A credentials json string or a path to a json file. Not required if GOOGLE_APPLICATION_CREDENTIALS env variable set.
project (str | None) – str The project which the client is acting on behalf of. If not passed then will use the default inferred environment.
sql_parameters (list | dict | None) – To include python variables in your query, it is recommended to pass them as parameters. Using the sql_parameters argument ensures that values are escaped properly, and avoids SQL injection attacks.

Return type:

Table | None

classmethod from_columns(cols: Sequence[Sequence[str]], header: Sequence[str] | None = None) → Table¶

Create a Table from a list of lists organized as columns.

Parameters:

cols (Sequence[Sequence[str]]) – A list of lists organized as columns
header (Sequence[str] | None) – List of column names. If not specified, will use dummy column names

Return type:

classmethod from_csv(local_path: Path | str, **csvargs) → Table¶

Create a Table from a CSV file.

Parameters:

local_path (Path | str) – A csv formatted local path, url or ftp. If this is a file path that ends in .gz, the file will be decompressed first.
**csvargs – Additional arguments to pass to csv.reader()

Return type:

classmethod from_csv_string(csv_string: str, *, str: str | None = None, **csvargs) → Table¶

Create a Table from a string representing a CSV.

Parameters:

csv_string (str) – The string object to convert to a table
**csvargs – Additional arguments to pass to csv.reader()
str (str | None) – Deprecated, use csv_string instead

Return type:

classmethod from_dataframe(dataframe: DataFrame, include_index: bool = False) → Table¶

Create a Table from a Pandas dataframe.

Parameters:

dataframe (DataFrame) – A valid Pandas dataframe objectt
include_index (bool) – Include index column

Return type:

classmethod from_json(local_path: Path | str, header: Sequence[str] | None = None, line_delimited: bool = False) → Table¶

Create a Table from a json file.

Parameters:

local_path (Path | str) – A JSON formatted local path, url or ftp. If this is a file path that ends in .gz, the file will be decompressed first.
header (Sequence[str] | None) – List of columns to use for the destination table. If omitted, columns will be inferred from the initial data in the file.
line_delimited (bool) – Whether the file is line-delimited JSON (with a row on each line), or a proper JSON file. If True, local_path must not be a remote file path.

Return type:

Create a Table from a Postgres query.

Parameters:

sql (str) – A valid SQL statement
username (str | None) – Required if env variable PGUSER not populated
password (str | None) – Required if env variable PGPASSWORD not populated
host (str | None) – Required if env variable PGHOST not populated
db (str | None) – Required if env variable PGDATABASE not populated
port (int | None) – Required if env variable PGPORT not populated.
sql_parameters (list | None) – To include python variables in your query, it is recommended to pass them as parameters, following the psycopg style. Using the sql_parameters argument ensures that values are escaped properly, and avoids SQL injection attacks.

Return type:

Table | None

Create a Table from a Redshift query.

To pull an entire Redshift table, use a query like SELECT * FROM tablename.

Parameters:

sql (str) – A valid SQL statement
username (str | None) – Required if env variable REDSHIFT_USERNAME not populated
password (str | None) – Required if env variable REDSHIFT_PASSWORD not populated
host (str | None) – Required if env variable REDSHIFT_HOST not populated
db (str | None) – Required if env variable REDSHIFT_DB not populated
port (int | None) – Required if env variable REDSHIFT_PORT not populated. Port 5439 is typical.
sql_parameters (list[Any] | dict[str, Any] | None) – To include python variables in your query, it is recommended to pass them as parameters, following the documentation for passing parameters to SQL queries. Using the sql_parameters argument ensures that values are escaped properly, and avoids SQL injection attacks.

Return type:

Table | None

classmethod from_s3_csv(bucket: str, key: str, from_manifest: bool = False, aws_access_key_id: str | None = None, aws_secret_access_key: str | None = None, **csvargs) → Table¶

Create a Table from a key in an S3 bucket.

Parameters:

bucket (str) – The S3 bucket.
key (str) – The S3 key
from_manifest (bool) – bool If True, treats key as a manifest file and loads all urls into a Table.
aws_access_key_id (str | None) – Required if not included as environmental variable.
aws_secret_access_key (str | None) – Required if not included as environmental variable.
**csvargs – Additional arguments to pass to csv.reader()

Return type:

get_column_max_width(column: str) → int¶

Return the maximum width of the column.

Parameters:: column (str) – The column name
Return type:: int

get_column_types(column: str) → list[str]¶

Return a list of python types found in a given column.

Parameters:: column (str) – Name of the column to analyze
Return type:: list[str]

get_columns_type_stats() → list[ColumnTypes]¶

Return descriptive stats for all columns.

Returns:: A list of dicts, each containing a column name and a type list.
Return type:: list[ColumnTypes]

static get_normalized_column_name(column_name: str) → str¶

Return a column name with whitespace and non-alphanumeric characters removed, and everything lowercased.

Parameters:: column_name (str)
Return type:: str

head(n: int = 5) → Self¶

Return the first n rows of the table.

Parameters:: n (int)
Return type:: Self

long_table(key: Sequence[str], column: str, key_rename: dict[str, str] | None = None, retain_original: bool = False, prepend: bool = True, prepend_value: str | None = None) → Table¶

Create a new long parsons table from a column, including the foreign key.

# Begin with nested dicts in a column
json = [
    {
        'id': '5421',
        'name': 'Jane Green',
        'emails': [
            {'home': 'jane@gmail.com'},
            {'work': 'jane@mywork.com'}
        ]
    }
]
tbl = Table(json)
print (tbl)
>>> {'id': '5421', 'name': 'Jane Green', 'emails': [{'home': 'jane@gmail.com'}, {'work': 'jane@mywork.com'}]}
>>> {'id': '5421', 'name': 'Jane Green', 'emails': [{'home': 'jane@gmail.com'}, {'work': 'jane@mywork.com'}]}

# Create skinny table of just the nested dicts
email_skinny = tbl.long_table(['id'], 'emails')

print (email_skinny)
>>> {'id': '5421', 'emails_home': 'jane@gmail.com', 'emails_work': None}
>>> {'id': '5421', 'emails_home': None, 'emails_work': 'jane@mywork.com'}

Parameters:

key (Sequence[str]) – The columns to retain in the long table (e.g. foreign keys)
column (str) – The column name to make long
key_rename (dict[str, str] | None) – The new name for the foreign key to better identify it. For example, you might want to rename id to person_id. Ex. {'KEY_NAME': 'NEW_KEY_NAME'}
retain_original (bool) – Retain the original column from the source table.
prepend (bool) – Prepend the column name of the unpacked values. Useful for avoiding duplicate column names.
prepend_value (str | None) – Value to prepend new columns if prepend is True. If None, will set to column name.

Return type:

map_and_coalesce_columns(column_map: dict[str, Sequence[str]]) → Self¶

Coalesce columns based on multiple possible values.

The columns in the map do not need to be in your table, so you can create a map with all possibilities.

The coalesce will occur in the order that the columns are listed, unless the destination column name already exists in the table, in which case that value will be preferenced.

Helpful when your input table might have multiple / unknown column names.

tbl = [
    {'first': None},
    {'fn': 'Jane'},
    {'lastname': 'Doe'},
    {'dob': '1980-01-01'}
]

column_map = {
    'first_name': ['fn', 'first', 'firstname'],
    'last_name': ['ln', 'last', 'lastname'],
    'date_of_birth': ['dob', 'birthday']
}

tbl.map_and_coalesce_columns(column_map)

print (tbl)
>> {{'first_name': 'Jane', 'last_name': 'Doe', 'date_of_birth': '1908-01-01'}}

Parameters:: column_map (dict[str, Sequence[str]]) – A dictionary of columns and possible values that map to it
Return type:: Self

map_columns(column_map: dict[str, Sequence[str]], exact_match: bool = True) → Self¶

Standardize column names based on multiple possible values.

Helpful when your input table might have multiple / unknown column names.

tbl = [
    {'fn': 'Jane'},
    {'lastname': 'Doe'},
    {'dob': '1980-01-01'}
]

column_map = {
    'first_name': ['fn', 'first', 'firstname'],
    'last_name': ['ln', 'last', 'lastname'],
    'date_of_birth': ['dob', 'birthday']
}

tbl.map_columns(column_map)
print (tbl)
>> {{'first_name': 'Jane', 'last_name': 'Doe', 'date_of_birth': '1908-01-01'}}

Parameters:

column_map (dict[str, Sequence[str]]) – A dictionary of columns and possible values that map to it
exact_match (bool) – If True, will only map if an exact match. If False, will ignore case, spaces and underscores.

Return type:

Self

match_columns(desired_columns: Sequence[str], fuzzy_match: bool = True, if_extra_columns: Literal['remove', 'ignore', 'fail'] = 'remove', if_missing_columns: Literal['add', 'ignore', 'fail'] = 'add') → Self¶

Change the column names and ordering in this Table to match a list of desired column names.

Parameters:

desired_columns (Sequence[str]) – Ordered list of desired column names
fuzzy_match (bool) – Whether to normalize column names when matching against the desired column names, removing whitespace and non-alphanumeric characters, and lowercasing everything. Eg. With this flag set, FIRST NAME would match first_name. If the Table has two columns that normalize to the same string (eg. FIRST NAME and first_name), the latter will be considered an extra column.
if_extra_columns (Literal['remove', 'ignore', 'fail']) – If the Table has columns that don’t match any desired columns, either remove them, ignore them, or fail (raising an error).
if_missing_columns (Literal['add', 'ignore', 'fail']) – If the Table is missing some of the desired columns, either add them (with a value of None), ignore them, or fail (raising an error).

Return type:

Self

move_column(column: str, index: int) → Self¶

Move a column to a new index position.

Parameters:

column (str) – The column name to move
index (int) – The new index for the column

Return type:

reduce_rows(columns: Sequence[str], reduce_func: Callable[[Sequence[str], Sequence[Any]], Sequence[Any]], headers: Sequence[str], presorted: bool = False, **kwargs) → Self¶

Group rows by a column or columns, then reduce the groups to a single row.

For example, the output from the query to get a table’s definition is returned as one component per row. The reduce_rows method can be used to reduce all those to a single row containg the entire query.

Based on the rowreduce petl function.

ddl = rs.query(sql_to_get_table_ddl)

ddl.table¶
schemaname	tablename	ddl
‘db_scratch’	‘state_fips’	‘–DROP TABLE db_scratch.state_fips;’
‘db_scratch’	‘state_fips’	‘CREATE TABLE IF NOT EXISTS db_scratch.state_fips’
‘db_scratch’	‘state_fips’	‘(’
‘db_scratch’	‘state_fips’	‘\tstate VARCHAR(1024) ENCODE RAW’
‘db_scratch’	‘state_fips’	‘\t,stusab VARCHAR(1024) ENCODE RAW’

reducer_fn = lambda cols, rows: [
    f"{cols[0]}.{cols[1]}",
    r"\n".join([row[2] for row in rows])
]

ddl.reduce_rows(
    ['schemaname', 'tablename'],
    reducer_fn,
    ['tablename', 'ddl'],
    presorted=True
)

ddl.table¶
tablename	ddl
‘db_scratch.state_fips’	‘–DROP TABLE db_scratch.state_fips;\nCREATE TABLE IF NOT EXISTS db_scratch.state_fips\n(\n\tstate VARCHAR(1024) ENCODE RAW\n\t ,db_scratch.state_fips\n(\n\tstate VARCHAR(1024) ENCODE RAW \n\t,stusab VARCHAR(1024) ENCODE RAW\n\t,state_name VARCHAR(1024) ENCODE RAW\n\t,statens VARCHAR(1024) ENCODE RAW\n)\nDISTSTYLE EVEN\n;’

Parameters:

columns (Sequence[str]) – The column(s) by which to group the rows.
reduce_func (Callable[[Sequence[str], Sequence[Any]], Sequence[Any]]) – The function by which to reduce the rows. Should take the 2 arguments, the columns list and the rows list and return a list. reducer(columns: Sequence[str], rows: Sequence[Any]) -> Sequence[Any]:
headers (Sequence[str]) – The list of headers for modified table. The length of headers should match the length of the list returned by the reduce function.
presorted (bool) – If false, the row will be sorted.
**kwargs – Extra options to pass to petl.rowreduce()

Return type:

Self

remove_column(*columns: str) → Self¶

Remove a column from your table.

Parameters:: *columns (str) – Column names
Return type:: Self

remove_null_rows(columns: str | Sequence[str], null_value: int | float | str | None = None) → Self¶

Remove rows if the values in a column are None.

If multiple columns are passed as list, all rows with null values in any of the passed columns will be removed.

Parameters:

columns (str | Sequence[str]) – The column or columns to analyze
null_value (int | float | str | None) – The null value

Return type:

Self

rename_column(column_name: str, new_column_name: str) → Self¶

Rename an existing column.

Parameters:

column_name (str) – The current column name
new_column_name (str) – The new column name

Raises:

ValueError – If the new column name already exists

Return type:

rename_columns(column_map: dict[str, str]) → Self¶

Rename multiple columns.

Parameters:

column_map (dict[str, str]) –

Old and new column names

{
    'old_name': 'new_name',
    'old_name2': 'new_name2',
}

Raises:

KeyError – If the old column name does not exist
ValueError – If the new column name already exists

Return type:

row_data(row_index: int) → dict[str, Any][source]¶

Return a row in table.

Calling this method excessively will log a warning advising of a more efficient alternative.

Parameters:: row_index (int) – The index of the row to return.
Returns:: A dictionary of the row with the column as the key and the cell as the value.
Return type:: dict[str, Any]

select_rows(*filters: Callable | str) → Table¶

Select specific rows from a Parsons table based on the passed filters.

Example filters:

The filter can be structured in different ways¶

tbl = Table(
    [
        ['foo', 'bar', 'baz'],
        ['c', 4, 9.3],
        ['a', 2, 88.2],
        ['b', 1, 23.3]
    ]
)

# Lambda Function
tbl2 = tbl.select_rows(lambda row: row.foo == 'a' and row.baz > 88.1)
tbl2
>>> {'foo': 'a', 'bar': 2, 'baz': 88.1}

# Expression String
tbl3 = tbl.select_rows("{foo} == 'a' and {baz} > 88.1")
tbl3
>>> {'foo': 'a', 'bar': 2, 'baz': 88.1}

Parameters:: *filters (Callable | str) – Callable | str
Return type:: Table

set_header(new_header: Sequence[str]) → Self¶

Replace the header row of the table.

Parameters:: new_header (Sequence[str]) – List of new header column names
Return type:: Self

sort(columns: Sequence[str] | str | None = None, reverse: bool = False, **kwargs) → Self¶

Sort the rows a table.

Parameters:

sort_columns – Sort by a single column or a list of column. If None, will sort columns from left to right.
reverse (bool) – Sort rows in reverse order.
**kwargs – Extra options to pass to petl.sort()
columns (Sequence[str] | str | None)

Return type:

Self

stack(*tables: Table, missing: Any | None = None) → None¶

Stack Parsons tables on top of one another.

Similar to concat(), except no attempt is made to align fields from different tables.

Parameters:

tables (Table) – A single table, multiple tables, or a list/tuple of tables.
missing (Any | None) – The value to use when padding missing values

Return type:

None

tail(n: int = 5) → Self¶

Return the last n rows of the table.

Parameters:: n (int)
Return type:: Self

to_avro(target: Path | str, schema: dict | None = None, sample: int = 9, codec: Literal['null', 'deflate', 'bzip2', 'snappy', 'zstandard', 'lz4', 'xz'] = 'deflate', compression_level: int | None = None, **avro_args) → str¶

Output table to an Avro file.

In order to use this method, you must have the fastavro library installed. You can install it with pip install parsons[avro].

Write the table into a new avro file according to schema passed.

This method assume that each column has values with the same type for all rows of the source table.

Avro is a data serialization framework that is generally is faster and safer than text formats like Json, XML or CSV.

Parameters:

target (Path | str) – The file path for creating the avro file. Note that if a file already exists at the given location, it will be overwritten.
schema (dict | None) – Defines the rows field structure of the file. Check fastavro documentation and Avro schema reference for details.
sample (int) – Defines how many rows are inspectedfor discovering the field types and building a schema for the avro file when the schema argument is not passed.
codec (Literal['null', 'deflate', 'bzip2', 'snappy', 'zstandard', 'lz4', 'xz']) – The codec argument (string, optional) sets the compression codec used to shrink data in the file.
compression_level (int | None) – Sets the level of compression to use with the specified codec, if supported.
**avro_args – Additional options to foward directly to fastavro. See fastavro documentation for reference.

Returns:

The path of the written file

Return type:

Example usage for writing files¶

table2 = [
    ['name', 'friends', 'age'],
    ['Bob', 42, 33],
    ['Jim', 13, 69],
    ['Joe', 86, 17],
    ['Ted', 23, 51].
]

# Define Avro schema
schema2 = {
    'doc': 'Some people records.',
    'name': 'People',
    'namespace': 'test',
    'type': 'record',
    'fields': [
        {'name': 'name', 'type': 'string'},
        {'name': 'friends', 'type': 'int'},
        {'name': 'age', 'type': 'int'},
    ],
}

# Demonstrate writing with Table.toavro()
from parsons import Table

Table.toavro(table2, 'example.file2.avro', schema=schema2)

# Read back with with Table.fromavro()
tbl2 = Table.fromavro('example.file2.avro')

tbl2¶
name	friends	age
‘Bob’	42	33
‘Jim’	13	69
‘Joe’	86	17
‘Ted’	23	51

to_bigquery(table_name: str, app_creds: str | None = None, project: str | None = None, **kwargs) → None¶

Write a table to BigQuery.

Parameters:

table_name (str) – Table name to write to in BigQuery. This should be in schema.table format.
app_creds (str | None) – A credentials json string or a path to a json file. Not required if GOOGLE_APPLICATION_CREDENTIALS env variable set.
project (str | None) – The project which the client is acting on behalf of. If not passed then will use the default inferred environment.
**kwargs – Additional keyword arguments passed to parsons.google.google_bigquery.GoogleBigQuery.copy(). (if_exists, max_errors, etc.)

Return type:

None

to_civis(table: str, api_key: str | None = None, db: str | None = None, max_errors: int | None = None, existing_table_rows: Literal['fail', 'truncate', 'append', 'drop'] = 'fail', diststyle: Literal['even', 'all', 'key'] | None = None, distkey: str | None = None, sortkey1: str | None = None, sortkey2: str | None = None, wait: bool = True, **civisargs) → CivisFuture | None¶

Write the table to a Civis Redshift cluster.

Additional keyword arguments can passed to civis.io.dataframe_to_civis().

Parameters:

table (str) – str The schema and table you want to upload to (e.g. scratch.table). Schemas or tablenames with periods must be double quoted (e.g. scratch."my.table").
api_key (str | None) – Your Civis API key. If not given, the CIVIS_API_KEY environment variable will be used.
db (str | None) – The Civis Database. Can be database name or ID
max_errors (int | None) – The maximum number of rows with errors to remove from the import before failing.
existing_table_rows (Literal['fail', 'truncate', 'append', 'drop']) – The behaviour if a table with the requested name already exists.
diststyle (Literal['even', 'all', 'key'] | None) – The distribution style for the table.
distkey (str | None) – The column to use as the distkey for the table.
sortkey1 (str | None) – The column to use as the sortkey for the table.
sortkey2 (str | None) – The second column in a compound sortkey for the table.
wait (bool) – Wait for write job to complete before exiting method.

Return type:

CivisFuture | None

Output table to a CSV.

Additional key word arguments are passed to csv.writer(). So, e.g., to override the delimiter from the default CSV dialect, provide the delimiter keyword argument.

Warning

If a file already exists at the given location, it will be overwritten.

Parameters:

local_path (Path | str | None) – The path to write the csv locally. If it ends in .gz or .zip, the file will be compressed. If not specified, a temporary file will be created and returned, and that file will be removed automatically when the script is done running.
temp_file_compression (Literal['gzip', 'zip'] | None) – If a temp file is requested (ie. no local_path is specified), the compression type for that file. Currently None, gzip or zip are supported. If a local_path is specified, this argument is ignored.
encoding (str | None) –
The CSV encoding type for csv.writer()
errors (str) – Raise an Error if encountered
write_header (bool) – Include header in output
csv_name (str | None) – If zip compression (either specified or inferred), the name of csv file within the archive.
**csvargs – Additional arguments to pass to csv.writer()

Returns:

The path of the new file

Return type:

to_dataframe(index: str | Sequence[str] | None = None, exclude: Sequence[str] | None = None, columns: Sequence[str] | None = None, coerce_float: bool = False) → DataFrame¶

Output Table as a Pandas Dataframe.

In order to use this method, you must have the pandas library installed. You can install it with pip install parsons[pandas].

Parameters:

index (str | Sequence[str] | None) – Field of array to use as the index, alternately a specific set of input labels to use.
exclude (Sequence[str] | None) – Columns or fields to exclude
columns (Sequence[str] | None) – Column names to use. If the passed data do not have names associated with them, this argument provides names for the columns. Otherwise this argument indicates the order of the columns in the result (any names not found in the data will become all-NA columns).
coerce_float (bool)

Return type:

DataFrame

to_dicts() → list[dict]¶

Output table as a list of dicts.

Return type:: list[dict]

to_gcs_csv(bucket_name: str, blob_name: str, gcs_client: GoogleCloudStorage | None = None, app_creds: str | None = None, project: str | None = None, compression: Literal['zip', 'gzip'] | None = None, encoding: str | None = None, errors: str = 'strict', write_header: bool = True, public_url: bool = False, public_url_expires: int = 60, **csvargs) → str | None¶

Write the table to a Google Cloud Storage blob as a CSV.

Parameters:

bucket_name (str) – The bucket to upload to
blob_name (str) – The blob to name the file. If it ends in .gz or .zip, the file will be compressed.
gcs_client (GoogleCloudStorage | None) – The GCS client to use. If not specified, a default client will be initialized.
app_creds (str | None) – A credentials json string or a path to a json file. Not required if GOOGLE_APPLICATION_CREDENTIALS env variable set.
project (str | None) – The project which the client is acting on behalf of. If not passed then will use the default inferred environment.
compression (Literal['zip', 'gzip'] | None) – The compression type for the csv. If specified, will override the key suffix.
encoding (str | None) –
The CSV encoding type for csv.writer()
errors (str) – Raise an Error if encountered
write_header (bool) – Include header in output
public_url (bool) – Create a public link to the file
public_url_expire – The time, in minutes, until the url expires if public_url set to True.
**csvargs – Additional arguments to pass to csv.reader()
public_url_expires (int)

Returns:

If public_url is True, the public url of the file. Otherwise None.

Return type:

str | None

Output table to HTML file.

Warning

If a file already exists at the given location, it will be overwritten.

Parameters:

local_path (Path | str | None) – The path to write the html locally. If not specified, a temporary file will be created and returned.
encoding (str | None) – The encoding type for csv.writer()
errors (str | None) – Raise an Error if encountered
index_header (bool) – Prepend index to column names; Defaults to False.
caption (str | None) – A caption to include with the html table.
tr_style (str | Callable | None) – Style to be applied to the table row.
td_styles (str | Callable | dict[str, str | Callable] | None) – Styles to be applied to the table cells.
truncate (int | None) – Length of cell data.

Returns:

The path of the new file

Return type:

to_json(local_path: Path | str | None = None, temp_file_compression: Literal['gzip'] | None = None, line_delimited: bool = False) → str¶

Output table to a JSON file.

Warning

If a file already exists at the given location, it will be overwritten.

Parameters:

local_path (Path | str | None) – The path to write the JSON locally. If it ends in .gz, it will be compressed first. If not specified, a temporary file will be created and returned.
temp_file_compression (Literal['gzip'] | None) – If a temp file is requested (ie. no local_path is specified), the compression type for that file. If a local_path is specified, this argument is ignored.
line_delimited (bool) – Whether the file will be line-delimited JSON (with a row on each line), or a proper JSON file.

Returns:

The path of the new file

Return type:

to_petl() → Table¶

Provide only the petl table.

Return type:: Table

Write a table to a Postgres database.

Parameters:

table_name (str) – The table name and schema (my_schema.my_table) to point the file.
username (str | None) – Required if env variable PGUSER not populated
password (str | None) – Required if env variable PGPASSWORD not populated
host (str | None) – Required if env variable PGHOST not populated
db (str | None) – Required if env variable PGDATABASE not populated
port (int | None) – Required if env variable PGPORT not populated.
**copy_args – See copy() for options.

Return type:

None

Write a table to a Redshift database.

Note, this requires you to pass AWS S3 credentials or store them as environmental variables.

Parameters:

table_name (str) – The table name and schema (my_schema.my_table) to point the file.
username (str | None) – Required if env variable REDSHIFT_USERNAME not populated
password (str | None) – Required if env variable REDSHIFT_PASSWORD not populated
host (str | None) – Required if env variable REDSHIFT_HOST not populated
db (str | None) – Required if env variable REDSHIFT_DB not populated
port (int | None) – Required if env variable REDSHIFT_PORT not populated. Port 5439 is typical.
**copy_args – See copy() for options.

Return type:

None

to_s3_csv(bucket: str, key: str, aws_access_key_id: str | None = None, aws_secret_access_key: str | None = None, compression: Literal['gzip', 'zip'] | None = None, encoding: str | None = None, errors: str = 'strict', write_header: bool = True, acl: str = 'bucket-owner-full-control', public_url: bool = False, public_url_expires: int = 3600, use_env_token: bool = True, **csvargs) → str | None¶

Write the table to an s3 object as a CSV.

Parameters:

bucket (str) – The s3 bucket to upload to
key (str) – The s3 key to name the file. If it ends in .gz or .zip, the file will be compressed.
aws_access_key_id (str | None) – Required if not included as environmental variable
aws_secret_access_key (str | None) – Required if not included as environmental variable
compression (Literal['gzip', 'zip'] | None) – str The compression type for the s3 object. If specified, will override the key suffix.
encoding (str | None) –
The CSV encoding type for csv.writer()
errors (str) – Raise an Error if encountered
write_header (bool) – Include header in output
acl (str) – The S3 permissions on the file
public_url (bool) – Create a public link to the file
public_url_expire – The time, in seconds, until the url expires (if public_url set to True).
use_env_token (bool) – Controls use of the AWS_SESSION_TOKEN environment variable for S3. Defaults to True. Set to False in order to ignore the AWS_SESSION_TOKEN env variable even if the aws_session_token argument was not passed in.
**csvargs – Additional arguments to pass to csv.reader()
public_url_expires (int)

Returns:

If public_url is True, the public url of the file. Otherwise None.

Return type:

str | None

to_sftp_csv(remote_path: str, host: str, username: str, password: str, port: int = 22, encoding: str | None = None, errors: str = 'strict', write_header: bool = True, rsa_private_key_file: Path | str | None = None, **csvargs) → None¶

Write the table to a CSV file on a remote SFTP server.

Parameters:

remote_path (str) – The remote path of the file. If it ends in .gz, the file will be compressed.
host (str) – The remote host
username (str) – The username to access the SFTP server
password (str) – The password to access the SFTP server
port (int) – The port number of the SFTP server
encoding (str | None) –
The CSV encoding type for csv.writer()
errors (str) – Raise an Error if encountered
write_header (bool) – Include header in output
rsa_private_key_file (Path | str | None) – str Absolute path to a private RSA key used to authenticate SFTP connection
**csvargs – Additional keyword arguments passed to csv.writer().

Return type:

None

to_zip_csv(archive_path: Path | str | None = None, csv_name: str | None = None, encoding: str | None = None, errors: str = 'strict', write_header: bool = True, if_exists: Literal['replace', 'append'] = 'replace', **csvargs) → str¶

Output table to a CSV in a zip archive.

Additional key word arguments are passed to csv.writer(). So, e.g., to override the delimiter from the default CSV dialect, provide the delimiter keyword argument. Use this method if you would like to write multiple csv files to the same archive.

Warning

If a file already exists in the archive, it will be overwritten.

Parameters:

archive_path (Path | str | None) – The path to zip achive. If not specified, a temporary file will be created and returned.
csv_name (str | None) – The name of the csv file to be stored in the archive. If None, will use the archive name.
encoding (str | None) –
The CSV encoding type for csv.writer()
errors (str) – Raise an Error if encountered
write_header (bool) – Include header in output
if_exists (Literal['replace', 'append']) – What to do if archive already exists.
**csvargs – Additional keyword arguments passed to csv.writer().

Returns:

The path of the archive

Return type:

unpack_dict(column: str, keys: list | None = None, include_original: bool = False, sample_size: int = 5000, missing: str | None = None, prepend: bool = True, prepend_value: str | None = None) → Self¶

Unpack dictionary values from one column into separate columns.

Parameters:

column (str) – The column name to unpack
keys (list | None) – The dict keys in the column to unpack. If None, will unpack all.
include_original (bool) – Whether to retain original column after unpacking
sample_size (int) – Number of rows to sample before determining columns
missing (str | None) – If a value is missing, fill with this value
prepend (bool) – Prepend the column name of the unpacked values. Useful for avoiding duplicate column names.
prepend_value (str | None) – Value to prepend new columns if prepend=True. If None, will set to column name.

Return type:

unpack_list(column: str, include_original: bool = False, missing: str | None = None, replace: bool = False, max_columns: int | None = None) → Table | None¶

Unpack list values from one column into separate, numbered columns.

# Begin with a list in column
json = [{
    'id': '5421',
    'name': 'Jane Green',
    'phones': ['512-699-3334', '512-222-5478']
}]

tbl = Table(json)
print (tbl)
>>> {'id': '5421', 'name': 'Jane Green', 'phones': ['512-699-3334', '512-222-5478']}

tbl.unpack_list('phones', replace=True)
print (tbl)
>>> {'id': '5421', 'name': 'Jane Green', 'phones_0': '512-699-3334', 'phones_1': '512-222-5478'}

Parameters:

column (str) – The column name to unpack
include_original (bool) – Retain original column after unpacking
sample_size – Number of rows to sample before determining columns
missing (str | None) – If a value is missing, fill it with this value
replace (bool) – Return new table or update existing
max_columns (int | None) – The maximum number of columns to unpack

Return type:

Table | None

unpack_nested_columns_as_rows(column: str, key: str = 'id', expand_original: bool | int = False) → Table¶

Unpack list or dict values from one column into separate rows.

Not recommended for JSON columns (i.e. lists of dicts), but can handle columns with any mix of types. Makes use of petl.melt().

Parameters:

column (str) – The column name to unpack
key (str) – The column to use as a key when unpacking. Defaults to id.
expand_original (bool | int) – If int: Add to original unless the max added per key is above the given number If True: Add resulting unpacked rows (with all other columns) to original If False (default): Return unpacked rows (with key column only) as standalone In all cases, packed list and dict rows are removed from the original.

Returns:

If expand_original is not False, original table with packed rows replaced by unpacked rows. Otherwise, standalone table with key column and unpacked values only

Return type:

use_petl(petl_method: str, *args, **kwargs) → Table¶

Call a petl function on the current table.

This convenience method exposes the petl functions to the current Table. This is useful in cases where one might need a petl function that has not yet been implemented for Table.

For more information on available petl functions, see the transform and util documentation.

tbl = Table(
    [
        ['col1', 'col2'],
        ['# this is a comment row'],
        ['a', 1],
        ['#this is another comment', 'this is also ignored'],
        ['b', 2]
    ]
)
tbl.use_petl('skipcomments', '#', update_table=True)

>>> {'col1': 'a', 'col2': 1}
>>> {'col1': 'b', 'col2': 2}

tbl.table¶
col1	col2
‘a’	1
‘b’	2

Parameters:

petl_method (str) – The name of the petl function to call
*args – Any The arguements to pass to the petl function.
**kwargs – Any The keyword arguements to pass to the petl function. update_table (bool) – If True, updates the Table. Defaults to False. to_petl (bool) – If True, returns a petl table, otherwise a Table. Defaults to False.

Return type: