From CSVs to Tables: Infer Schema Data Types From Raw Spreadsheets
Tags: bigdata, python, dataengineering, sql
Published: 2019-01-25 06:43:01

---
title: From CSVs to Tables: Infer Schema Data Types From Raw Spreadsheets
published: true
tags: Big Data,Python,Data Engineering,SQL
canonical_url: https://hackersandslackers.com/infer-datatypes-from-csvs-to-create/
---

 ![From CSVs to Tables: Infer Schema Data Types From Raw Spreadsheets](https://res-2.cloudinary.com/hackers-and-slackers/image/upload/f_auto,q_auto/v1/images/schema.jpg)

Back in August of last year (roughly 8 months ago), I hunched over my desk at 4 am desperate to fire off a post before boarding a flight the next morning. The article was titled **_Creating Database Schemas: a Job for Robots, or Perhaps Pandas_**. It was my intent at the time to solve a common annoyance: creating database tables out of raw data, without the obnoxious process of explicitly setting each column's datatype. I had a few leads that led me to believe I had the answer... boy was I wrong.

The task seems somewhat reasonable from the surface. Surely we can spot columns where the data is always in integers, or match the expected format of a date, right? If anything, we'll fall back to **text** or **varchar** and call it a day. Hell, even MongoDB's Compass does a great job of this by merely uploading a CSV... this has got to be some trivial task handled by third-party libraries by now.

For one reason or another, searching for a solution to this problem almost always comes up empty. Software developers probably have little need for dynamically generated tables if their applications run solely on self-defined models. Full-time Data Scientists have access to plenty of expensive tools which seem to claim this functionality, yet it all seems so... inaccessible.

## Is This NOT a Job For Pandas?

From my experience, no. Pandas does offer hope but doesn't seem to get the job done quite right. Let's start with a dataset so you can see what I mean. Here's a bunch of fake identities I'll be using to mimic the outcome I experienced when working with real data:

| id | initiated | hiredate | email | firstname | lastname | title | department | location | country | type |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 100035435 | 2015-12-11T09:16:20.722-08:00 | 3/22/67 | GretchenRMorrow@jourrapide.com | Gretchen | Morrow | Power plant operator | Physical Product | Britling Cafeterias | United Kingdom | Employee |
| 100056435 | 2015-12-15T10:11:24.604-08:00 | 6/22/99 | ElizabethLSnow@armyspy.com | Elizabeth | Snow | Oxygen therapist | Physical Product | Grade A Investment | United States of America | Employee |
| 100037955 | 2015-12-16T14:31:32.765-08:00 | 5/31/74 | AlbertMPeterson@einrot.com | Albert | Peterson | Psychologist | Physical Product | Grass Roots Yard Services | United States of America | Employee |
| 100035435 | 2016-01-20T11:15:47.249-08:00 | 9/9/69 | JohnMLynch@dayrep.com | John | Lynch | Environmental hydrologist | Physical Product | Waccamaw's Homeplace | United States of America | Employee |
| 100057657 | 2016-01-21T12:45:38.261-08:00 | 4/9/83 | TheresaJCahoon@teleworm.us | Theresa | Cahoon | Personal chef | Physical Product | Cala Foods | United States of America | Employee |
| 100056747 | 2016-02-01T11:25:39.317-08:00 | 6/26/98 | KennethHPayne@dayrep.com | Kenneth | Payne | Central office operator | Frontline | Magna Consulting | United States of America | Employee |
| 100035435 | 2016-02-01T11:28:11.953-08:00 | 4/16/82 | LeifTSpeights@fleckens.hu | Leif | Speights | Staff development director | Frontline | Rivera Property Maintenance | United States of America | Employee |
| 100035435 | 2016-02-01T12:21:01.756-08:00 | 8/6/80 | JamesSRobinson@teleworm.us | James | Robinson | Scheduling clerk | Frontline | Discount Furniture Showcase | United States of America | Employee |
| 100074688 | 2016-02-01T13:29:19.147-08:00 | 12/14/74 | AnnaDMoberly@jourrapide.com | Anna | Moberly | Playwright | Physical Product | The Wiz | United States of America | Employee |
| 100665778 | 2016-02-04T14:40:05.223-08:00 | 9/13/66 | MarjorieBCrawford@armyspy.com | Marjorie | Crawford | Court, municipal, and license clerk | Physical Product | The Serendipity Dip | United Kingdom | Employee |
| 100876876 | 2016-02-24T12:39:25.872-08:00 | 12/19/67 | LyleCHackett@fleckens.hu | Lyle | Hackett | Airframe mechanic | Physical Product | Infinity Investment Plan | United States of America | Employee |
| 100658565 | 2016-02-29T15:52:12.933-08:00 | 11/17/83 | MaryJDensmore@jourrapide.com | Mary | Densmore | Employer relations representative | Frontline | One-Up Realtors | United States of America | Employee |
| 100766547 | 2016-03-01T12:32:53.357-08:00 | 10/1/87 | CindyRDiaz@armyspy.com | Cindy | Diaz | Student affairs administrator | Physical Product | Mr. AG's | United States of America | Employee |
| 100045677 | 2016-03-02T12:07:44.264-08:00 | 8/16/65 | AndreaTLigon@einrot.com | Andrea | Ligon | Railroad engineer | Central Growth | Robinson Furniture | United States of America | Employee |

There are some juicy datatypes in there: **integers** , **timestamps** , **dates** , **strings**.... and those are only the first four columns! Let's load this thing into a DataFrame and see what information we can get that way:

```
import pandas as pd

csv = 'data/fake.csv'

workers_df = pd.read_csv(csv, header=0, encoding='utf-8')
meta = workers_df.info(verbose=True)
print(meta)

```

Using Pandas' `info()` should do the trick! This returns a list of columns and their data types:

```
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 22 entries, 0 to 21
Data columns (total 11 columns):
id 14 non-null float64
initiated 14 non-null object
hiredate 14 non-null object
email 14 non-null object
firstname 14 non-null object
lastname 14 non-null object
title 14 non-null object
department 14 non-null object
location 14 non-null object
country 14 non-null object
type 14 non-null object
dtypes: float64(1), object(10)
memory usage: 2.0+ KB
None

```

...Or not. What is this garbage? Only one of our 11 columns identified a data type, and it was incorrectly listed as a **float**! Okay, so maybe Pandas doesn't have a secret one-liner for this. So who does?

## What about PySpark?

It's always been a matter of time before we'd turn to Apache's family of aged data science products. Hadoop, Spark, Kafka... all of them have a particular musty stench about them that tastes like "I feel like I should be writing in Java right now." Heads up: they _do_ want you to write in Java. Misery loves company.

Nonetheless, **PySpark** _does_ support reading data as DataFrames in Python, and also comes with the elusive ability to infer schemas. Installing Hadoop and Spark locally still kind of sucks for solving this one particular problem. Cue **[Databricks](https://databricks.com/)**: a company that spun off from the Apache team way back in the day, and offers free cloud notebooks integrated with- you guessed it: Spark.

With Databricks, we can upload our CSV and load it into a DataFrame by spinning up a free notebook. The source looks something like this:

```
# File location and type
file_location = "/FileStore/tables/fake.csv"
file_type = "csv"

# CSV options
infer_schema = "true"
first_row_is_header = "true"
delimiter = ","

df = spark.read.format(file_type) \
  .option("inferSchema", infer_schema) \
  .option("header", first_row_is_header) \
  .option("sep", delimiter) \
  .load(file_location)

display(df)

```

Let's see out the output looks:

```
df:pyspark.sql.dataframe.DataFrame
id:integer
initiated:timestamp
hiredate:string
email:string
firstname:string
lastname:string
title:string
department:string
location:string
country:string
type:string

```

Not bad! We correctly 'upgraded' our ID from float to integer, and we managed to get the timestamp correct also. With a bit of messing around, we could probably have even gotten the date correct too, given that we stated the format beforehand.

![From CSVs to Tables: Infer Schema Data Types From Raw Spreadsheets](https://res-4.cloudinary.com/hackers-and-slackers/image/upload/f_auto,q_auto/v1/images/Screen-Shot-2019-01-22-at-8.41.30-PM.png)<figcaption>A look at the Databricks Notebook interface.</figcaption>

### And Yet, This Still Kind of Sucks

Even though we can solve our problem in a notebook, we still haven't solved the use case: I want a drop-in solution to create tables out of CSVs... whenever I want! I want to accomplish this while writing any app, at the drop of a hat without warning. I don't want to install Hadoop and have Java errors coming back at me through my terminal. Don't _EVER_ let me see Java in my terminal. UGH:

```
py4j.protocol.Py4JJavaError: An error occurred while calling o43.count.
: java.lang.IllegalArgumentException: Unsupported class file major version 55
        at org.apache.xbean.asm6.ClassReader.<init>(ClassReader.java:166)
        at org.apache.xbean.asm6.ClassReader.<init>(ClassReader.java:148)
        at org.apache.xbean.asm6.ClassReader.<init>(ClassReader.java:136)
        at org.apache.xbean.asm6.ClassReader.<init>(ClassReader.java:237)
        at org.apache.spark.util.ClosureCleaner$.getClassReader(ClosureCleaner.scala:49)
        at org.apache.spark.util.FieldAccessFinder$$anon$3$$anonfun$visitMethodInsn$2.apply(ClosureCleaner.scala:517)
        at org.apache.spark.util.FieldAccessFinder$$anon$3$$anonfun$visitMethodInsn$2.apply(ClosureCleaner.scala:500)
        at scala.collection.TraversableLike$WithFilter$$anonfun$foreach$1.apply(TraversableLike.scala:733)
        at scala.collection.mutable.HashMap$$anon$1$$anonfun$foreach$2.apply(HashMap.scala:134)
        at scala.collection.mutable.HashMap$$anon$1$$anonfun$foreach$2.apply(HashMap.scala:134)

        at scala.collection.mutable.HashTable$class.foreachEntry(HashTable.scala:236)
        at scala.collection.mutable.HashMap.foreachEntry(HashMap.scala:40)
        at scala.collection.mutable.HashMap$$anon$1.foreach(HashMap.scala:134)
        at scala.collection.TraversableLike$WithFilter.foreach(TraversableLike.scala:732)
        at org.apache.spark.util.FieldAccessFinder$$anon$3.visitMethodInsn(ClosureCleaner.scala:500)
        at org.apache.xbean.asm6.ClassReader.readCode(ClassReader.java:2175)
        at org.apache.xbean.asm6.ClassReader.readMethod(ClassReader.java:1238)
        at org.apache.xbean.asm6.ClassReader.accept(ClassReader.java:631)

```

## Python's "tableschema" Library

Thankfully, there's at least one other person out there who has shared this desire. That brings us to [tableschema](https://github.com/frictionlessdata/tableschema-py), a not-quite-perfect-but-perhaps-good-enough library to gunsling data like some kind of wild data cowboy. Let's give it a go:

```
import csv
from tableschema import Table

data = 'data/fake.csv'
schema = infer(data, limit=500, headers=1, confidence=0.85)
print(schema)

```

If our dataset is particularly large, we can use the `limit` attribute to limit the sample size to the first **X** number of rows. Another nice feature is the `confidence` attribute: a 0-1 ratio for allowing casting errors during the inference. Here's what comes back:

```
{
  "fields": [{
    "name": "id",
    "type": "integer",
    "format": "default"
  }, {
    "name": "initiated",
    "type": "string",
    "format": "default"
  }, {
    "name": "hiredate",
    "type": "date",
    "format": "default"
  }, {
    "name": "email",
    "type": "string",
    "format": "default"
  }, {
    "name": "firstname",
    "type": "string",
    "format": "default"
  }, {
    "name": "lastname",
    "type": "string",
    "format": "default"
  }, {
    "name": "title",
    "type": "string",
    "format": "default"
  }, {
    "name": "department",
    "type": "string",
    "format": "default"
  }, {
    "name": "location",
    "type": "string",
    "format": "default"
  }, {
    "name": "country",
    "type": "string",
    "format": "default"
  }, {
    "name": "type",
    "type": "string",
    "format": "default"
  }],
  "missingValues": [""]
}

```

Hey, that's good enough for me! Now let's automate the shit out this.

## Creating a Table in SQLAlchemy With Our New Schema

I'm about to throw a bunch in your face right here. Here's a monster of a class:

```
from sqlalchemy import create_engine
import config
import pandas as pd
import psycopg2
from tableschema import Table, infer, Schema
from functions.recursivejson import extract_values
from sqlalchemy.types import Integer, Text, Date

class CreateTablesFromCSVs:
    """Infer a table schema from a CSV."""

    __uri = config.PG_URI
    __engine = create_engine(__uri, convert_unicode=True, echo=True)
    __data = 'data/fake.csv'
    
    @classmethod
    def get_data(cls):
        """Pull latest data."""
        test_df = pd.read_csv(cls.__data, header=0, encoding='utf-8')
        return test_df

    @classmethod
    def get_schema_from_csv(cls, csv):
        """Infers schema from CSV."""
        table = Table(csv)
        table.infer(limit=500, confidence=0.55)
        schema = table.schema.descriptor
        names = cls.get_column_names(schema, 'name')
        datatypes = cls.get_column_datatypes(schema, 'type')
        schema_dict = dict(zip(names, datatypes))
        return schema_dict

    @classmethod
    def get_column_names(cls, schema, key):
        """Get names of columns."""
        names = extract_values(schema, key)
        return names

    @classmethod
    def get_column_datatypes(cls, schema, key):
        """Convert schema to recognizable by SQLAlchemy."""
        values = extract_values(schema, key)
        for i, value in enumerate(values):
            if value == 'integer':
                values[i] = Integer
            elif value == 'string':
                values[i] = Text
            elif value == 'date':
                values[i] = Date
        return values
        
    @classmethod
    def create_new_table(cls, data, schema):
          """Create new table from CSV and generated schema."""
          workday_table.to_sql('faketable',
                               con=cls.__engine,
                               schema='testschema',
                               if_exists='replace',
                               chunksize=300,
                               dtype=schema)
                                 
data = CreateTablesFromCSVs.get_schema_from_csv()
schema = CreateTablesFromCSVs.get_schema_from_csv(data)
CreateTablesFromCSVs.create_new_table(data, schema)

```

The first thing worth mentioning is I'm [importing a function](https://gist.github.com/toddbirchard/b6f86f03f6cf4fc9492ad4349ee7ff8b) from my personal secret library to extract values from JSON objects. I've [spoken about it before](https://hackersandslackers.com/extract-data-from-complex-json-python/).

Let's break down this class:

- `get_data()` reads our CSV into a Pandas DataFrame.
- `get_schema_from_csv()` kicks off building a Schema that SQLAlchemy can use to build a table.
- `get_column_names()` simply pulls column names as half our schema.
- `get_column_datatypes()` manually replaces the datatype names we received from **tableschema** and replaces them with SQLAlchemy datatypes.
- `create_new_table` Uses a beautiful marriage between Pandas and SQLAlchemy to create a table in our database with the correct datatypes mapped.

### Promising Potential, Room to Grow

While **tableschema** works some of the time, it isn't perfect. The base of what we accomplish still stands: we now have a reliable formula for how we would create schemas on the fly if we trust our schemas to be accurate.

Just wait until next time when we introduce **Google BigQuery** into the mix.