Python, Postgres, and integers with blank values?
Question: So I have some fairly sparse data columns where most of the values are blank
but sometimes have some integer value. In Python, if there is a blank then
that column is interpreted as a float and there is a .0 at the end of each
number.

I tried two things:

  * Changed all of the columns to text and then stripped the .0 from everything
  * Filled blanks with 0 and made each column an integer

Stripping the .0 is kind of time consuming on about 2mil+ rows per day and
then the data is in text format which means I can't do quick sums and stuff.

Filling blanks seems somewhat wasteful because some columns literally have
just a few actual values out of millions. My table for just one month is
already over 80gigs (200 columns, but many of the columns after about 30 or so
are pretty sparse).

What postgres datatype is best for this? There are NO decimals because the
columns contain the number of seconds and it must be pre-rounded by the
application.

Edit - here is what I am doing currently (but this bloats up the size and
seems wasteful):

    
    
    def create_int(df, col):
        df[col].fillna(0, inplace=True)
        df[col] = df[col].astype(int)
    

If I try to create the column astype(int) without filling in the 0s I get the
error:

error: Cannot convert NA to integer

Here is the link the the Gotcha about this.

<http://pandas.pydata.org/pandas-docs/stable/gotchas.html#support-for-integer-
na>

So it makes each int a float. Should I change the datatypes in postgres to
numeric or something? I do not need high precision because there are no values
after the decimal.


Answer: You could take advantage of the fact you are using POSTGRESQL (9.3 or above),
and implement a "poor man's sparse row" by converting your data into Python
dictionaries and then using a JSON datatype (JSONB is better).

The following Python snippets generate random data in the format you said you
have yours, convert them to apropriate json, and upload them into a PostgreSQL
table with a JSONB column.

    
    
    import psycopg2
    import json
    import random
    
    def row_factory(n=200, sparcity=0.1):
       return [random.randint(0, 2000) if random.random() < sparcity else None for i in range(n)]
    
    
    def to_row(data):
        result = {}
        for i, element in enumerate(data):
            if element is not None: result[i] = element
        return result
    
    
    def from_row(row, lenght=200):
        result = [None] * lenght
        for index, value in row.items():
            result[int(index)] = value
        return result
    
    
    con = psycopg2.connect("postgresql://...")
    cursor = con.cursor()
    cursor.execute("CREATE TABLE numbers (values JSONB)")
    
    def upload_data(rows=100):
        for i in range(rows):
            cursor.execute("INSERT INTO numbers VALUES(%s)", (json.dumps(to_row(row_factory(sparcity=0.5))),) )
    
    upload_data()
    
    # To retrieve the sum of all columns:
    
    cursor.execute("""SELECT {} from numbers limit 10""".format(", ".join("sum(CAST(values->>'{}' as int))".format(i) for i in range(200))))
    result = cursor.fetchall()
    

It took me a while to find out how to perform numeric operations on the JSONB
data inside Postgresql (if you will be using them from Python you can just use
the snippet `from_row` function above). But the last two lines have a Select
operation that performs a SUM on all columns - the select statement itself is
assembled using Python string formatting methods - the key to use a Json value
as number is to select it with the `->>` operator, and them cast it to
number.(the `sum(CAST(values->>'0' as int))` part)

