ez-io 0.1.1
Data Tools for Data Scientists, Data Engineers and other humans.
Requires Python: >=3.8.6

[![Tests](https://github.com/bitdotioinc/importer/workflows/Tests/badge.svg)](https://github.com/bitdotioinc/importer/actions?workflow=Tests)

[![Codecov](https://codecov.io/gh/bitdotioinc/importer/branch/master/graph/badge.svg)](https://codecov.io/gh/bitdotioinc/importer)

# EZ-IO

***Data Tools for Data Scientists, Data Engineers and other humans.***

EZ-IO is a tool to easily load source data into Pandas DataFrames. It's **FAST!** It uses ***Pandas*** and ***Python*** to fix all those annoying edge cases so you can immediately use your data.

It fixes things like:
- bad CSV headers
- extraneous comments that corrupt tabular data
- inconsistent data types where on instance of something like 3.0 does not turn an entire column of ints into floats
- inconsistent dates, Excel dates don't become timestamps in Pandas
- inconsistent commas, quotes, separators, line feeds, NaNs and NA, misplaced spaces, empty rows
- Excel files with multiple sheets
- file encodings
- and more...

The end result is will be Pandas DataFrames with consistent data types, no "objects" of mixed data types.


**Currently supported source data files:**
- CSV
- JSON
- Excel files

**Data file type support coming soon:**
- All types of compressed files
- OpenDocument Spreadsheets

---

## Getting Started

### Installation

#### Using Pypi/pip

`$ pip install ez-io`

#### Using Poetry

1. Install poetry if not already installed. (See: https://python-poetry.org/docs/)
1. Clone/setup the code
    - `$ cd <directory to hold the project root>`
    - `$ git clone git@github.com:bitdotioinc/importer.git importer`
    - `$ cd importer  # the project root`
1. Setup virtual environment and install dependencies
    - `$ poetry install`
1. Enter the virtual environment
    - `$ poetry shell`
1. Run pytests
    - `$ cd tests`
    - `$ pytest  # or pytest -v`

#### Using pipenv

```
$ cd <project root>
$ pipenv shell
$ pipenv install
```

#### Using venv

```
$ cd <project root>
$ python3 -m venv venv
$ source venv/bin/activate
$ pip install -r requirements.txt --user
```

---

### Run pytest

```bash
$ pytest
==================================== test session starts =====================================
platform linux -- Python 3.8.5, pytest-6.2.4, py-1.10.0, pluggy-0.13.1
rootdir: YOUR_PATH/importer, configfile: pytest.ini, testpaths: tests
plugins: mock-3.6.1, cov-2.12.0
collected 238 items

tests/test_console.py .                                                                 [  0%]
tests/func/test_create_dataframe.py ...........................                         [ 11%]
tests/func/test_create_dataframe_from_excel.py .....................................    [ 27%]
tests/func/test_create_dataframe_from_json.py ...........                               [ 31%]
tests/func/test_fail_get_metadata.py .........                                          [ 35%]
tests/func/test_get_metadata.py .............................................           [ 54%]
tests/func/test_get_xl_metadata.py ..................................................   [ 75%]
tests/unit/test_dataframe_fail.py ..                                                    [ 76%]
tests/unit/test_dataframe_pass.py .....................                                 [ 85%]
tests/unit/test_real_data_fail.py .....                                                 [ 87%]
tests/unit/test_real_data_pass.py ..............................                        [100%]

==================================== 238 passed in 11.34s =====================================
```
---

## Data-Framer Automation

In an automated fashion DataFramer object can:
1. Receive a list data files (Currentyly supported formats: CSV, Excel and JSON.)
2. Read each data file to determine what type of file it is.
3. If the file is a CSV DataFramer will:
    - Get the CSV dialect, delimiter, quote and escape characters
4. If the file is an *.xls or *.xlsx file DataFramer will:
    - Get the row and column metadata from Excel
5. With both CSV and Excel files Data-Framer will
    - Ensure there is a good header row
    - Remove blank rows
    - Cleanse extraneous comments that lie outside the header row boundaries
    - Reconcile columnar data to a single Dtype and thus eliminate the undefined "object" Dtype
6. By design JSON source files should be have very few errors so all that Data-Framer needs to do is reconcile the columnar data types.
7. With completion of the above steps Data-Framer will then write all the data to your properly configured PostgreSQL database.
8. If any of the foregoing steps fail due to bad data, unsupported files or any other exceptions, Data-Framer will provide debugging information which can be used for a manual source data fix.

The above can be performed with the command line utility as will be demonstrated further below.

Additionally you can use this Pandas-based framework to support your own Python based Data Wrangling as shown in the following usage cases. (Note: you can use any of the provided in the tests/data directory.)

---

## Using the DataReader object

The DataReader can be used to create a Pandas DataFrame from a source data file. Here is a working example from the Python interpreter:
```python
from ez_io.data_loader import DataReader
>>> reader = DataReader("./tests/data/realdata/pass-auto.csv")
>>> df = reader.create_dataframe()
>>> df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 205 entries, 0 to 204
Data columns (total 26 columns):
 #   Column             Non-Null Count  Dtype
---  ------             --------------  -----
 0   symboling          205 non-null    int64
 1   normalized-losses  164 non-null    float64
 2   make               205 non-null    object
 3   fuel-type          205 non-null    object
 4   aspiration         205 non-null    object
 5   num-of-doors       203 non-null    object
 6   body-style         205 non-null    object
 7   drive-wheels       205 non-null    object
 8   engine-location    205 non-null    object
 9   wheel-base         205 non-null    float64
 10  length             205 non-null    float64
 11  width              205 non-null    float64
 12  height             205 non-null    float64
 13  curb-weight        205 non-null    int64
 14  engine-type        205 non-null    object
 15  num-of-cylinders   205 non-null    object
 16  engine-size        205 non-null    int64
 17  fuel-system        205 non-null    object
 18  bore               201 non-null    float64
 19  stroke             201 non-null    float64
 20  compression-ratio  205 non-null    float64
 21  horsepower         203 non-null    float64
 22  peak-rpm           203 non-null    float64
 23  city-mpg           205 non-null    int64
 24  highway-mpg        205 non-null    int64
 25  price              201 non-null    float64
dtypes: float64(11), int64(5), object(10)
memory usage: 41.8+ KB
>>> ### NOTE that the Dtypes have lots of "objects" - Let's clean them up...
>>> from ez_io.data_loader import DataFramer, ConvertOpts
>>> convert_opts = {k: v for k, v in ConvertOpts().items()}
>>> df_cleansed = DataFramer.cleanse_data(df, convert_opts)
>>> df_cleansed.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 205 entries, 0 to 204
Data columns (total 26 columns):
 #   Column             Non-Null Count  Dtype
---  ------             --------------  -----
 0   symboling          205 non-null    Int64
 1   normalized-losses  164 non-null    Int64
 2   make               205 non-null    string
 3   fuel-type          205 non-null    string
 4   aspiration         205 non-null    string
 5   num-of-doors       203 non-null    string
 6   body-style         205 non-null    string
 7   drive-wheels       205 non-null    string
 8   engine-location    205 non-null    string
 9   wheel-base         205 non-null    Float64
 10  length             205 non-null    Float64
 11  width              205 non-null    Float64
 12  height             205 non-null    Float64
 13  curb-weight        205 non-null    Int64
 14  engine-type        205 non-null    string
 15  num-of-cylinders   205 non-null    string
 16  engine-size        205 non-null    Int64
 17  fuel-system        205 non-null    string
 18  bore               201 non-null    Float64
 19  stroke             201 non-null    Float64
 20  compression-ratio  205 non-null    Float64
 21  horsepower         203 non-null    Int64
 22  peak-rpm           203 non-null    Int64
 23  city-mpg           205 non-null    Int64
 24  highway-mpg        205 non-null    Int64
 25  price              201 non-null    Int64
dtypes: Float64(7), Int64(9), string(10)
memory usage: 46.4 KB
>>> ### Okay let's write this back out as another CSV so we can see what changed...
>>> df_cleansed.to_csv("~/my_data_files/cleansed_auto.csv")
>>> ### JSON is best, let's just write the DataFrame to JSON!
>>> df_cleansed.to_json("~/my_data_files/cleansed_auto.json")
>>> ### Good data makes the Data Scientist happy.
```

---

## Using the DataWriter object

The DataWriter can be used to write Pandas DataFrames to a PostgreSQL (v12) database.

For this to work you need to setup your local database. For your security we recommend setting up the following envionment variables (note the very bad password):
- PG_DB_NAME [bitdotio]
- PG_DB_USER [bitdotio]
- PG_DB_PSWD [bitdotio]
- PG_DB_HOST [localhost]
- PG_DB_PORT [5432]

Of course it is up to you and your team to support your database, but here's something to get you started:

From psql as postgres user (or near equivalent DBA user):
```
postgres=# CREATE USER bitdotio PASSWORD 'bitdotio';
postgres=# CREATE TABLESPACE bitdotio OWNER bitdotio LOCATION '/data1/dbs';
postgres=# CREATE DATABASE bitdotio OWNER bitdotio TABLESPACE bitdotio;
bitdotio=# GRANT ALL ON DATABASE bitdotio TO bitdotio;
```

Then test the connection...
```
$ psql -h localhost -d bitdotio -p 5432 -U bitdotio -W
Password:
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
```

Here's a working example for the DataWriter (using the DataFrame from the prior example):

```python
import copy
from ez_io.data_loader import DataReader, DataFramer
>>> from sqlalchemy import create_engine
>>> from ez_io.data_loader import DataWriter
>>> writer = DataWriter(df_cleansed, "my_new_table")
2021-06-08 15:09:03  INFO Connected to postgresql+psycopg2://...
>>> writer.import_db_data()
2021-06-08 15:09:24  INFO Data imported to table my_new_table.
```

---

## Command Line Example

### Usage

#### Fully Automated Pandas DataFrame Creation from Data Files

1.  Go to the source code directory
    `$ cd importer/ez_io`
2.  View the help instructions
    `$ ./import_data.py -h`
3.  This creates a DataFrame from a data source file
    `$ ./import_data.py -f ../tests/data/realdata/mpg.json`


Here's an example of dirty data transformation into good data with verbose output.

Note how the Dtypes in the DEBUG section are fixed in the DataFrame section.

```
$ ./import_data.py -f ../your_data_file

2021-05-27 16:01:02 - File Info
File Name:    your_data_file
File Type:    ASCII text
Encoding:     {'encoding': 'ASCII', 'confidence': 1.0}
Dialect:      SimpleDialect(',', '"', '')
GOOD Header?  True


2021-05-27 16:01:02 - INFO DEBUG
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 10 entries, 0 to 9
Data columns (total 13 columns):
    #   Column            Non-Null Count  Dtype
   ---  ------            --------------  -----
    0   a_string          10 non-null     object
    1   str_comma_quoted  9 non-null      object
    2   str_date          8 non-null      object
    3   mixed_dtypes      5 non-null      object
    4   integers          7 non-null      float64
    5   thousands         8 non-null      float64
    6   ints0             8 non-null      float64
    7   int_and_float1    7 non-null      float64
    8   float12           8 non-null      float64
    9   pct               7 non-null      object

dtypes: float64(5), object(5)
memory usage: 1.1+ KB


2021-05-27 16:01:02 - INFO DataFrame
<class 'pandas.core.frame.DataFrame'>
Int64Index: 10 entries, 0 to 9
Data columns (total 13 columns):
    #   Column            Non-Null Count  Dtype
   ---  ------            --------------  -----
    0   a_string          10 non-null     string
    1   str_comma_quoted  9 non-null      string
    2   str_date          8 non-null      string
    3   mixed_dtypes      5 non-null      string
    4   integers          7 non-null      Int64
    5   thousands         8 non-null      Int64
    6   ints0             8 non-null      Int64
    7   int_and_float1    7 non-null      Float64
    8   float12           8 non-null      Float64
    9   pct               7 non-null      string

dtypes: Float64(2), Int64(3), string(5)
memory usage: 1.2 KB
```

## TODO:

- All tests pass locally so implement codecov service (https://codecov.io)
- Ensure badges are in a good state

## Future Features:

- More supported file types
- Handling compressed files
- Using stringIO
- Multiple excel sheets
- Several high performance features
- Large data features
- and much more...

---

## License

The MIT License (MIT)