how to preserve dtypes of dataframes when using to_csv?

Question

To reduce memory costs, I specified dtypes of my pandas dataframe using astype(),like:

df['A'] = df['A'].astype(int8)

then I use to_csv() to store it, but when I use read_csv() to read it again and check the dtypes, I found it still stored in int64. How can I preserve the dtypes while saving it in local storages?

You're writing text out, there is no concept of dtypes with text, you'd need to specify the dtypes again when reading the csv again see [read_csv](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html#pandas.read_csv) you need to pass a param to `dtype` arg — EdChum, Apr 26 '18 at 15:53

score 10 · Answer 1 · answered Jan 29 '19 at 13:40

A modification of #Aaron N. Brock to allow parse_dates as well (plus not change original DataFrame):

def to_csv(df, path):
    # Prepend dtypes to the top of df
    df2 = df.copy()
    df2.loc[-1] = df2.dtypes
    df2.index = df2.index + 1
    df2.sort_index(inplace=True)
    # Then save it to a csv
    df2.to_csv(path, index=False)

def read_csv(path):
    # Read types first line of csv
    dtypes = {key:value for (key,value) in pd.read_csv(path,    
              nrows=1).iloc[0].to_dict().items() if 'date' not in value}

    parse_dates = [key for (key,value) in pd.read_csv(path, 
                   nrows=1).iloc[0].to_dict().items() if 'date' in value]
    # Read the rest of the lines with the types from above
    return pd.read_csv(path, dtype=dtypes, parse_dates=parse_dates, skiprows=[1])

no upvotes for this? That's just crazy. Nice work. I wonder the dataframe could be subclassed to include these functions. — Robert Lugg, Mar 20 '19 at 03:10
This is a fantastic addition! Good catch that I ignored the mutation of the original dataframe. — Aaron N. Brock, Mar 20 '19 at 16:06

Aaron N. Brock · Accepted Answer · 2018-04-26T20:51:13.447

Here's a way to do it:

import pandas as pd

# Create Example data with types
df = pd.DataFrame({
    'words': ['foo', 'bar', 'spam', 'eggs'],
    'nums': [1, 2, 3, 4]
}).astype(dtype={
    'words': 'object',
    'nums': 'int8'
})

def to_csv(df, path):
    # Prepend dtypes to the top of df (from https://stackoverflow.com/a/43408736/7607701)
    df.loc[-1] = df.dtypes
    df.index = df.index + 1
    df.sort_index(inplace=True)
    # Then save it to a csv
    df.to_csv(path, index=False)

def read_csv(path):
    # Read types first line of csv
    dtypes = pd.read_csv('tmp.csv', nrows=1).iloc[0].to_dict()
    # Read the rest of the lines with the types from above
    return pd.read_csv('tmp.csv', dtype=dtypes, skiprows=[1])


print('Before: \n{}\n'.format(df.dtypes))

to_csv(df, 'tmp.csv')
df = read_csv('tmp.csv')

print('After: \n{}\n'.format(df.dtypes))

Output:

Before: 
nums       int8
words    object
dtype: object

After: 
nums       int8 # still int8
words    object
dtype: object

how to preserve dtypes of dataframes when using to_csv?

2 Answers2

Linked