Is there a way to estimate the size of file to be written based on the pandas dataframe that holds the data?

user_13747670 0
Viewed 191

I am extracting data from a table in a database and writing it to a CSV file in a windows file directory using pandas and python.

I want to partition the data and split into multiple files if the file size exceeds a certain amount of memory.

So for an example that threshold is 32 MB, if my CSV data file is going to be less than 32 MB, I will write the data in a single CSV file. But if the file size may exceed 32 MB, say 50 MB, I would split the data and write to two files one of 32 MB and other of (50-32)=18 MB.

The only thing I found is how to find the memory a dataframe accommodates using memory_usage method or python's getsizeof function. But I am not able to relate that memory with actual size of the data file. The in-process memory is generally 5-10 times greater than the file size.

Appreciate any suggestions.

pandas memory python
1 Answers

Do some checks in your code. Write a portion of the DataFrame as csv to an io.StringIO() object and examine the length of that object; use the percentage it is over or under your goal to redefine the DataFrame slice; repeat; when sat write to disk then use that slice size to write the rest.

Something like...

import StringIO from io

g = StringIO()
n = 100
limit = 3000
tolerance = .90

while True:
    data[:n].to_csv(g)
    p = g.tell()/limit
    print(n,g.tell(),p)
    if tolerance < p <= 1:
        break
    else:
        n = int(n/p)
        g = StringIO()
    if n >= nrows: break
    _ = input('?')

# with open(somefilename, 'w') as f:
#     g.seek(0)
#     f.write(g.read())

# some type of loop where succesive slices of size n are written to a new file.
# [0n:1n], [1n:2n], [2n:3n] ...

Caveat, the docs for .tell() say:

Return the current stream position as an opaque number. The number does not usually represent a number of bytes in the underlying binary storage

My experience is that .tell() at the end of the stream is the number of bytes for an io.StringIO object - I must be missing something. Maybe if contains multibyte unicode stuff it is different.

Maybe it is safer to use the length of the csv string for testing in which case the io.StringIO object is not needed. This is probably better/simpler. If I had thouroughly read the docs first I would not have proposed the io.StringIO version - #@$%#@.

n = 100
limit = 3000
tolerance = .90
while True:
    q = data[:n].to_csv()
    p = len(q)/limit
    print(f'n:{n}, len(q):{len(q)}, p:{p}')
    if tolerance < p <= 1:
        break
    else:
        n = int(n/p)
    if n >= nrows: break
    _ = input('?')

Another caveat: if the number of characters in each row for the first n rows varies significantly from other other n sized slices it is possible to overshoot or undershoot your limit if you don't test and adjust each slice before you write it.

setup for example:

import numpy as np
import pandas as pd
nrows = 1000
data = pd.DataFrame(np.random.randint(0,100,size=(nrows, 4)), columns=list('ABCD'))
user_28237550
Related