Apply GZIP compression to a CSV in Python Pandas

I am trying to write a dataframe to a gzipped csv in python pandas, using the following:

import pandas as pd
import datetime
import csv
import gzip

# Get data (with previous connection and script variables)
df = pd.read_sql_query(script, conn)

# Create today's date, to append to file
todaysdatestring = str(datetime.datetime.today().strftime('%Y%m%d'))
print todaysdatestring

# Create csv with gzip compression
df.to_csv('foo-%s.csv.gz' % todaysdatestring,
      sep='|',
      header=True,
      index=False,
      quoting=csv.QUOTE_ALL,
      compression='gzip',
      quotechar='"',
      doublequote=True,
      line_terminator='\n')

This just creates a csv called 'foo-YYYYMMDD.csv.gz', not an actual gzip archive.

I've also tried adding this:

#Turn to_csv statement into a variable
d = df.to_csv('foo-%s.csv.gz' % todaysdatestring,
      sep='|',
      header=True,
      index=False,
      quoting=csv.QUOTE_ALL,
      compression='gzip',
      quotechar='"',
      doublequote=True,
      line_terminator='\n')

# Write above variable to gzip
 with gzip.open('foo-%s.csv.gz' % todaysdatestring, 'wb') as output:
   output.write(d)

Which fails as well. Any ideas?

Using df.to_csv() with the keyword argument compression='gzip' should produce a gzip archive. I tested it using same keyword arguments as you, and it worked.

You may need to upgrade pandas, as gzip was not implemented until version 0.17.1, but trying to use it on prior versions will not raise an error, and just produce a regular csv. You can determine your current version of pandas by looking at the output of pd.__version__.

It is done very easily with pandas

import pandas as pd

Write a pandas dataframe to disc as gunzip compressed csv

df.to_csv('dfsavename.csv.gz', compression='gzip')

Read from disc

df = pd.read_csv('dfsavename.csv.gz', compression='gzip')

From documentation

import gzip
content = "Lots of content here"
with gzip.open('file.txt.gz', 'wb') as f:
    f.write(content)

with pandas

import gzip


content = df.to_csv(
      sep='|',
      header=True,
      index=False,
      quoting=csv.QUOTE_ALL,
      quotechar='"',
      doublequote=True,
      line_terminator='\n')

with gzip.open('foo-%s.csv.gz' % todaysdatestring, 'wb') as f:
    f.write(content)

The trick here being that to_csv outputs text if you don't pass it a filename. Then you just redirect that text to gzip's write method.

with gzip.open('foo-%s.csv.gz' % todaysdatestring, 'wb') as f:
    f.write(df.to_csv(sep='|', index=False, quoting=csv.QUOTE_ALL))