CODEWITHSUNDEEP
pythoncsvpandasblaze
Alex
Alex

Reputation: 4264

Delete column(s) from very large CSV file using pandas or blaze

I have a very large csv file (5 GB), so I do not want to load the whole thing into memory, and I want to delete one or more of its columns. I tried using the following code in blaze, but all it did was append the resulting columns to the existing csv file:

from blaze import Data, odo
d = Data("myfile.csv")
d = d[columns_I_want_to_keep]
odo(d, "myfile.csv")

Is there a way using either pandas or blaze to only keep the columns I want and delete the other ones?

Upvotes: 6

Views: 6623

Answers (4)

loopasam
loopasam

Reputation: 3136

Reading the original CSV by chunk and appending to a new file will print the header each time you save the new chunks to the disk. It can be avoided the following way:

cols_to_keep = ['col1', 'col2'] # or [0, 1]
add_header = True
chunksize = 10**5
for chunk in pd.read_csv(f_in, chunksize=chunksize, usecols=cols_to_keep):
    chunk.to_csv(f_out, mode='a', index=False, header=add_header)
    if add_header:
        # The header should not be printed more than one
        add_header = False

Upvotes: 3

root
root

Reputation: 33773

You can use dask.dataframe, which is syntactically similar to pandas, but does manipulations out-of-core so memory shouldn't be an issue. It also parallelizes the process automatically, so it should be fast.

import dask.dataframe as dd

df = dd.read_csv('myfile.csv', usecols=['col1', 'col2', 'col3'])
df.to_csv('output.csv', index=False)

Timings

I've timed each method posted so far on a 1.4 GB csv file. I kept four columns, leaving the output csv file at 250 MB.

Using Dask:

%%timeit
df = dd.read_csv(f_in, usecols=cols_to_keep)
df.to_csv(f_out, index=False)

1 loop, best of 3: 41.8 s per loop

Using Pandas:

%%timeit
chunksize = 10**5
for chunk in pd.read_csv(f_in, chunksize=chunksize, usecols=cols_to_keep):
    chunk.to_csv(f_out, mode='a', index=False)

1 loop, best of 3: 44.2 s per loop

Using Python/CSV:

%%timeit
inc_f = open(f_in, 'r')
csv_r = csv.reader(inc_f)
out_f = open(f_out, 'w')
csv_w = csv.writer(out_f, delimiter=',', lineterminator='\n')
for row in csv_r:
    new_row = [row[1], row[5], row[6], row[8]]
    csv_w.writerow(new_row)
inc_f.close()
out_f.close()

1 loop, best of 3:  1min 1s per loop

Upvotes: 6

JMoney
JMoney

Reputation: 99

I deal with Large csv files a lot. Here is my solution:

import csv
fname_in = r'C:\mydir\myfile_in.csv' 
fname_out = r'C:\mydir\myfile_out.csv' 
inc_f = open(fname_in,'r')  #open the file for reading
csv_r = csv.reader(inc_f) # Attach the csv "lens" to the input stream - default is excel dialect
out_f = open(fname_out,'w') #open the file for writing
csv_w = csv.writer(out_f, delimiter=',',lineterminator='\n' ) #attach the csv "lens" to the stream headed to the output file
for row in csv_r: #Loop Through each row in the input file
    new_row = row[:]  # initialize the output row
    new_row.pop(5) #Whatever column you wanted to delete
    csv_w.writerow(new_row) 
inc_f.close()
out_f.close()

Upvotes: 0

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210822

i would do it this way:

cols2keep = ['col1','col3','col4','col6'] # columns you want to have in the resulting CSV file
chunksize = 10**5  # you may want to adjust it ... 
for chunk in pd.read_csv(filename, chunksize=chunksize, usecols=cols2keep):
    chunk.to_csv('output.csv', mode='a', index=False)

PS you may also want to consider migrating from CSV to PyTables (HDF5) if it's appropriate for you...

Upvotes: 2

Related Questions