Compress excel file in python

Question

Right now my final output is in excel format. I wanted to compressed my excel file using gzip. Is there a way to do it ?

import pandas as pd
import gzip
import re

def renaming_ad_unit():
    with gzip.open('weekly_direct_house.xlsx.gz') as f:
        df = pd.read_excel(f)
        result = df['Ad unit'].to_list()
        for index, a_string in enumerate(result):
            modified_string = re.sub(r"\([^()]*\)", "", a_string)
            df.at[index,'Ad unit'] = modified_string

    return df.to_excel('weekly_direct_house.xlsx',index=False)

Answer 1

You could also use io.BytesIO to create file in memory and write excel in this file and next write this file as gzip on disk.

I used link to excel file from Nick ODell answer.

import pandas as pd
import gzip
import io

df = pd.read_excel('https://www2.census.gov/programs-surveys/decennial/2020/data/apportionment/apportionment-2020-table02.xlsx')

buf = io.BytesIO()

df.to_excel(buf)

buf.seek(0)  # move to the beginning of file

with gzip.open('output.xlsx.gz', 'wb') as f:
    f.write(buf.read())

---

Similar to Nick ODell answer.

import pandas as pd
import gzip
import io

df = pd.read_excel('https://www2.census.gov/programs-surveys/decennial/2020/data/apportionment/apportionment-2020-table02.xlsx')

with io.BytesIO() as buf:
    df.to_excel(buf)

    buf.seek(0)  # move to the beginning of file

    with gzip.open('output.xlsx.gz', 'wb') as f:
        f.write(buf.read())

---

Tested on Linux