<jupyter_start><jupyter_text>Introduction This tutorial illustrates how to import an *ObjTables* dataset from a file, export an *ObjTables* dataset to a file, convert an *ObjTables* dataset between two supported file formats, and pretty print a file that contains an *ObjTables* dataset. The tutorial uses an address book of CEOs as an example.  Supported file formats *ObjTables* supports seven file formats:* XLSX workbook* Collection of comma-separated values (CSV) files* Collection of tab-separated values (TSV) files* Single text file which contains multiple comma-separated tables (MULTI.CSV)* Single text file which contains multiple tab-separated tables (MULTI.TSV)* JavaScript Object Notation (JSON) file* YAML Ain't Markup Language (YAML) file  Use cases for each file format * **View or edit a dataset**: We recommend using an XLSX workbook as a graphical user interface for viewing or editing a dataset. *ObjTables* leverages several XLSX features:  * Optionally includes additional worksheets that contain a table of contents and the schema for the dataset.  * Highlights, freezes, and protects the row/column headings of each table.  * Uses comments to provide inline help for each row/column.  * Provides drop-down menus for each enumeration and \*-to-one relationship.  * Uses XLSX validation to help users quickly find and correct errors.  * Hides all unused rows and columns.* **Share or publish a dataset**: We recommend using XLSX workbooks to share and publish datasets because an entire dataset and its schema can be captured by a single file and because XLSX workbooks are easy for humans to read.* **Revision a dataset**: We recommend using collections of CSV or TSV files to revision datasets with version control systems such as Git. Version control systems can easily difference and merge CSV and TSV files.* **Import a dataset into another programming language or export a dataset from another language**: We recommend using JSON files to work with *ObjTables* datasets in other languages because most languages have methods for parsing JSON files.  Define a schema for an address book First, as described in [Tutorial 1](1.%20Building%20and%20visualizing%20schemas.ipynb), use *ObjTables* to define a schema for an address book.<jupyter_code>import enum
import obj_tables
import types


# Define classes to represent companies, their CEOs, and their addresses
class Address(obj_tables.Model):
    street = obj_tables.StringAttribute(unique=True, primary=True, verbose_name='Street')
    city = obj_tables.StringAttribute(verbose_name='City')
    state = obj_tables.StringAttribute(verbose_name='State')
    zip_code = obj_tables.StringAttribute(verbose_name='Zip code')
    country = obj_tables.StringAttribute(verbose_name='Country')

    class Meta(obj_tables.Model.Meta):
        table_format = obj_tables.TableFormat.multiple_cells
        attribute_order = ('street', 'city', 'state', 'zip_code', 'country',)
        verbose_name = 'Address'
        verbose_name_plural = 'Addresses'


class Company(obj_tables.Model):
    name = obj_tables.StringAttribute(unique=True, primary=True, verbose_name='Name')
    url = obj_tables.UrlAttribute(verbose_name='URL')
    address = obj_tables.OneToOneAttribute(Address, related_name='company', verbose_name='Address')

    class Meta(obj_tables.Model.Meta):
        table_format = obj_tables.TableFormat.column
        attribute_order = ('name', 'url', 'address',)
        verbose_name = 'Company'
        verbose_name_plural = 'Companies'


class PersonType(str, enum.Enum):
    family = 'family'
    friend = 'friend'
    business = 'business'


class Person(obj_tables.Model):
    name = obj_tables.StringAttribute(unique=True, primary=True, verbose_name='Name')
    type = obj_tables.EnumAttribute(PersonType, verbose_name='Type')
    company = obj_tables.ManyToOneAttribute(Company, related_name='employees', verbose_name='Company')
    email_address = obj_tables.EmailAttribute(verbose_name='Email address')
    phone_number = obj_tables.StringAttribute(verbose_name='Phone number')
    address = obj_tables.ManyToOneAttribute(Address, related_name='people', verbose_name='Address')

    class Meta(obj_tables.Model.Meta):
        table_format = obj_tables.TableFormat.row
        attribute_order = ('name', 'type', 'company', 'email_address', 'phone_number', 'address',)
        verbose_name = 'Person'
        verbose_name_plural = 'People'


# Add the classes to a module
schema = type('address_book', (types.ModuleType, ), {
    'Address': Address,
    'Company': Company,
    'Person': Person,
    'PersonType': PersonType,
    'models': [Company, Person],
})<jupyter_output><empty_output><jupyter_text>Create an adress book of technology companies and their CEOs Use the address book schema to build an address book of technology companies and their CEOs.<jupyter_code># <NAME> of Apple
apple = Company(name='Apple',
                url='https://www.apple.com/',
                address=Address(street='10600 N Tantau Ave',
                                city='Cupertino',
                                state='CA',
                                zip_code='95014',
                                country='US'))
cook = Person(name='<NAME>',
              type=PersonType.business,
              company=apple,
              email_address='<EMAIL>',
              phone_number='408-996-1010',
              address=apple.address)

# <NAME> of Netflix
netflix = Company(name='Netflix',
                  url='https://www.netflix.com/',
                  address=Address(street='100 Winchester Cir',
                                  city='Los Gatos',
                                  state='CA',
                                  zip_code='95032',
                                  country='US'))
hastings = Person(name='<NAME>',
                  type=PersonType.business,
                  company=netflix,
                  email_address='<EMAIL>',
                  phone_number='408-540-3700',
                  address=netflix.address)

# Sundar Pichai of Google
google = Company(name='Google',
                 url='https://www.google.com/',
                 address=Address(street='1600 Amphitheatre Pkwy',
                                 city='Mountain View',
                                 state='CA',
                                 zip_code='94043',
                                 country='US'))
pichai = Person(name='<NAME>',
                type=PersonType.business,
                company=google,
                email_address='<EMAIL>',
                phone_number='650-253-0000',
                address=google.address)

# <NAME> of Facebook
facebook = Company(name='Facebook',
                   url='https://www.facebook.com/',
                   address=Address(street='1 Hacker Way #15',
                                   city='Menlo Park',
                                   state='CA',
                                   zip_code='94025',
                                   country='US'))
zuckerberg = Person(name='<NAME>',
                    type=PersonType.business,
                    company=facebook,
                    email_address='<EMAIL>',
                    phone_number='650-543-4800',
                    address=facebook.address)

# Merge the companies and CEOs into a single address book
companies = [apple, facebook, google, netflix]
ceos = [zuckerberg, hastings, pichai, cook]
address_book = companies + ceos
address_book_by_class = {
    Company: companies,
    Person: ceos,
}<jupyter_output><empty_output><jupyter_text>Export the address book to the supported file formats  Import the `obj_tables.io` module<jupyter_code>import obj_tables.io<jupyter_output><empty_output><jupyter_text>Export the address book to XLSX, CSV, TSV, JSON, and YAML files Use `obj_tables.io.Writer` to export the address book to XLSX, CSV, TSV, JSON, and YAML files. Note, the address book will be exported to a set of CSV and TSV files, with one file for each worksheet. The file name of each worksheet will be generated by replacing the `*` in the file name pattern (`Address book.csv/*.csv`) with the name of the worksheet. For example, this will generate files with the names `Address book.csv/Company.csv` and `Address book.csv/People.csv`.<jupyter_code>import os
from IPython.core.display import display, HTML

dir = 'Address book'
xlsx_filename = os.path.join(dir, 'Address book.xlsx')
csv_filename_pattern = os.path.join(dir, 'Address book.csv/*.csv')
tsv_filename_pattern = os.path.join(dir, 'Address book.tsv/*.tsv')
multi_csv_filename = os.path.join(dir, 'Address book.multi.csv')
multi_tsv_filename = os.path.join(dir, 'Address book.multi.tsv')
json_filename = os.path.join(dir, 'Address book.json')
yml_filename = os.path.join(dir, 'Address book.yml')

if not os.path.isdir(dir):
    os.makedirs(dir)

if not os.path.isdir(os.path.join(dir, 'Address book.csv')):
    os.makedirs(os.path.join(dir, 'Address book.csv'))

if not os.path.isdir(os.path.join(dir, 'Address book.tsv')):
    os.makedirs(os.path.join(dir, 'Address book.tsv'))

# export address book
for filename in [xlsx_filename, csv_filename_pattern, tsv_filename_pattern,
                 multi_csv_filename, multi_tsv_filename, json_filename, yml_filename]:
    obj_tables.io.Writer().run(filename, address_book, models=schema.models,
                               write_toc=True,
                               write_schema=True)

# display links to generated files
display(
    HTML(
        "<ul>"
        + "<li><a target='_blank' href='{}'>XLSX workbook</a></li>".format(xlsx_filename)
        + "<li><a target='_blank' href='{}'>CSV files</a></li>".format(os.path.dirname(csv_filename_pattern))
        + "<li><a target='_blank' href='{}'>TSV files</a></li>".format(os.path.dirname(tsv_filename_pattern))
        + "<li><a target='_blank' href='{}'>MULTI.CSV file</a></li>".format(multi_csv_filename)
        + "<li><a target='_blank' href='{}'>MULTI.TSV file</a></li>".format(multi_tsv_filename)
        + "<li><a target='_blank' href='{}'>JSON file</a></li>".format(json_filename)
        + "<li><a target='_blank' href='{}'>YAML file</a></li>".format(yml_filename)
        + "</ul>"
    )
)<jupyter_output><empty_output><jupyter_text>Import the address book from the supported file formats Use `obj_tables.io.Reader` to import the address book from XLSX, CSV, TSV, JSON, and YAML files.<jupyter_code>for filename in [xlsx_filename, csv_filename_pattern, tsv_filename_pattern,
             multi_csv_filename, multi_tsv_filename, json_filename, yml_filename]:
    # Read the address book
    address_book_by_class_copy = obj_tables.io.Reader().run(filename,
                                                            models=schema.models)

    # Check that the imported address book is equal to the original
    for cls in address_book_by_class.keys():
        objs = sorted(address_book_by_class[cls], key=lambda obj: obj.name)
        copies = sorted(address_book_by_class_copy[cls], key=lambda obj: obj.name)
        for obj, copy in zip(objs, copies):
            assert obj.is_equal(copy)
            
# print one of the imported copies of the address book
import pprint
pretty_printer = pprint.PrettyPrinter(indent=4)
pretty_printer.pprint(address_book_by_class_copy)<jupyter_output>{   <class '__main__.Address'>: [   <__main__.Address object at 0x7fe447342410>,
                                    <__main__.Address object at 0x7fe447342090>,
                                    <__main__.Address object at 0x7fe447342610>,
                                    <__main__.Address object at 0x7fe447342a10>],
    <class '__main__.Company'>: [   <__main__.Company object at 0x7fe447342590>,
                                    <__main__.Company object at 0x7fe447342510>,
                                    <__main__.Company object at 0x7fe447342990>,
                                    <__main__.Company object at 0x7fe447342f90>],
    <class '__main__.Person'>: [   <__main__.Person object at 0x7fe446560ad0>,
                                   <__main__.Person object at 0x7fe4500e8550>,
                                   <__main__.Person object at 0x7fe490c21ed0>,
                                   <__main__.Person object at 0x7fe490c21d90>]}<jupyter_text>Convert the address book between the supported file formats Use `obj_tables.io.convert` to convert the address book among XLSX, CSV, TSV, JSON, and YAML files.<jupyter_code># Read the address book
obj_tables.io.convert(xlsx_filename, csv_filename_pattern, schema_name='Address book', models=schema.models)

# Check that the converted address book is equal to the original
address_book_by_class_copy = obj_tables.io.Reader().run(csv_filename_pattern,
                                                        schema_name='Address book',
                                                        models=schema.models)

for cls in address_book_by_class.keys():
    for obj, copy in zip(address_book_by_class[cls], address_book_by_class_copy[cls]):
        assert obj.is_equal(copy)<jupyter_output><empty_output><jupyter_text>Pretty print an XLSX file which contains an address book `obj_tables.io.convert` can also be used to pretty print files. This operation makes the following changes to files:  * Orders the worksheets and columns.  * Removes extraneous worksheets and columns that are not controlled by the schema.  * Additional changes to XLSX files:    * Optionally includes additional worksheets that contain a table of contents and the schema for the dataset.    * Highlights, freezes, and protects the column headings of each table.    * Uses comments to provide inline help for each column.    * Provides drop-down menus for each enumeration and \*-to-one relationship.    * Uses XLSX validation to help users quickly find and correct errors.    * Hides all unused rows and columns.<jupyter_code># Pretty print the address book
obj_tables.io.convert(xlsx_filename, xlsx_filename, schema_name='Address book', models=schema.models)

# Check that the pretty printed address book is equal to the original
address_book_by_class_copy = obj_tables.io.Reader().run(xlsx_filename,
                                                        schema_name='Address book',
                                                        models=schema.models)

for cls in address_book_by_class.keys():
    for obj, copy in zip(address_book_by_class[cls], address_book_by_class_copy[cls]):
        assert obj.is_equal(copy)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09807705562064925, 'fraction_numerical': 0.014335929423116687, 'mean_word_length': 2.919502971366829, 'pattern_counts': {'":': 0, '<': 84, '<?xml version=': 0, '>': 84, 'https://': 4, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 33, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '321452', 'license': 'MIT', 'n_tokens_mistral': 4200, 'n_tokens_neox': 3960, 'n_words': 1164, 'path': 'examples/tutorials/3. Importing, exporting, converting, and pretty printing datasets.ipynb', 'repo_name': 'KarrLab/schema', 'size': '14591'}