Python: Break down large file, filter based on criteria, and put all data into new csv file
<p>I have a super large csv.gzip file that has 59 mill rows. I want to filter that file for certain rows based on certain criteria and put all those rows in a new master csv file. As of now, I broke the gzip file into 118 smaller csv files and saved them on my computer. I did that with the following code:</p>

<pre><code>import pandas as pd
num = 0
df = pd.read_csv('google-us-data.csv.gz', header = None, 
compression =   'gzip', chunksize = 500000,
names = ['a','b','c','d','e','f','g','h','i','j','k','l','m'],
error_bad_lines = False, warn_bad_lines = False)

for chunk in df:
    num = num + 1
    chunk.to_csv('%ggoogle us'%num ,sep='\t', encoding='utf-8'
</code></pre>

<p>The code above worked perfectly and I now have a folder with my 118 small files. I then wrote code to go through the 118 files one by one, extract rows that matched certain conditions, and append them all to a new csv file that I've created and named 'google final us'. Here is the code: </p>

<pre><code>import pandas as pd
import numpy
for i in range (1,118)
    file = open('google final us.csv','a')
    df = pd.read_csv('%ggoogle us'%i, error_bad_lines = False, 
    warn_bad_lines = False)
    df_f = df.loc[(df['a']==7) &amp; (df['b'] == 2016) &amp; (df['c'] =='D') &amp; 
    df['d'] =='US')]
    file.write(df_f)
</code></pre>

<p>Unfortunately, the code above is giving me the below error: </p>

<pre><code>KeyError                                  Traceback (most recent call last)
C:\Users\...\Anaconda3\lib\site-packages\pandas\indexes\base.py in
get_loc(self, key, method, tolerance)
   1875             try:
-&gt; 1876                 return self._engine.get_loc(key)
   1877             except KeyError:
pandas\index.pyx in pandas.index.IndexEngine.get_loc (pandas\index.c:4027)()
pandas\index.pyx in pandas.index.IndexEngine.get_loc (pandas\index.c:3891)()
pandas\hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item  
(pandas\hashtable.c:12408)()
pandas\hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item 
(pandas\hashtable.c:12359)()
KeyError: 'a'
During handling of the above exception, another exception occurred:
KeyError                                  Traceback (most recent call last)
&lt;ipython-input-9-0ace0da2fbc7&gt; in &lt;module&gt;()
      3 file = open('google final us.csv','a')
      4 df = pd.read_csv('1google us')
----&gt; 5 df_f = df.loc[(df['a']==7) &amp; (df['b'] == 2016) &amp; 
      (df['c'] =='D') &amp; (df['d'] =='US')]
      6 file.write(df_f)
C:\Users\...\Anaconda3\lib\site-packages\pandas\core\frame.py in 
__getitem__(self, key)
   1990             return self._getitem_multilevel(key)
   1991         else:
-&gt; 1992             return self._getitem_column(key)
   1993 
   1994     def _getitem_column(self, key):
C:\Users\...\Anaconda3\lib\site-packages\pandas\core\frame.py in 
_getitem_column(self, key)
   1997         # get column
   1998         if self.columns.is_unique:
-&gt; 1999             return self._get_item_cache(key)
   2000 
   2001         # duplicate columns &amp; possible reduce dimensionality
C:\Users\...\Anaconda3\lib\site-packages\pandas\core\generic.py in 
_get_item_cache(self, item)
  1343         res = cache.get(item)
  1344         if res is None:
-&gt; 1345             values = self._data.get(item)
  1346             res = self._box_item_values(item, values)
  1347             cache[item] = res
C:\Users\...\Anaconda3\lib\site-packages\pandas\core\internals.py in 
get(self, item, fastpath)
   3223 
   3224             if not isnull(item):
-&gt; 3225                 loc = self.items.get_loc(item)
   3226             else:
   3227                 indexer = np.arange(len(self.items)) 
 [isnull(self.items)]
C:\Users\...\Anaconda3\lib\site-packages\pandas\indexes\base.py in 
get_loc(self, key, method, tolerance)
   1876                 return self._engine.get_loc(key)
   1877             except KeyError:
-&gt; 1878                 return 
   self._engine.get_loc(self._maybe_cast_indexer(key))
   1879 
   1880         indexer = self.get_indexer([key], method=method, 
   tolerance=tolerance)
pandas\index.pyx in pandas.index.IndexEngine.get_loc (pandas\index.c:4027)()
pandas\index.pyx in pandas.index.IndexEngine.get_loc (pandas\index.c:3891)()
pandas\hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item 
(pandas\hashtable.c:12408)()
pandas\hashtable.pyx in pandas.hashtable.PyObjectHashTable.get_item  
(pandas\hashtable.c:12359)()
KeyError: 'a'
</code></pre>

<p>Any ideas what's going wrong? I've read numerous other stackoverflow posts (eg. <a href="http://stackoverflow.com/questions/38215009/create-dataframes-from-unique-value-pairs-by-filtering-across-multiple-columns">Create dataframes from unique value pairs by filtering across multiple columns</a> or <a href="http://stackoverflow.com/questions/33839540/how-can-i-break-down-a-large-csv-file-into-small-files-based-on-common-records-b">How can I break down a large csv file into small files based on common records by python</a>), but still not sure how to do this. Also, if you have a better way to extract data than this method - please let me know!</p>

<p>When you use file.write(df_f) you are effectively saving a string representation of the DataFrame, which is meant for humans to look at. By default that representation will truncate rows and columns so that large frames can be displayed on the screen in a sensible manner. As a result column "a" may get chopped.</p>

<pre><code>with open('google final us.csv','a') as file:
    for i in range(1, 118):
        headers = i == 1
        ...
        df_f.to_csv(file, headers=headers)
</code></pre>

<p>I did not test the above snippet, but you should get an idea how to get going now.</p>

<p>There other issues with this code, which you may want to correct:</p>

<ol>
<li><p>Open the file to write before the loop, close it after. Best to use context manager.</p></li>
<li><p>If the entire data fits in memory why go through a trouble to split it into 118 files? Simply filter it and save the resulting DataFrame using df.to_csv() method.</p></li>
<li>Instead of pandas consider using csv.DictReader and filter the lines on the fly.</li>
</ol>

<p>Lastly, if this a one-time job, why even write code for something that you could accomplish with a grep command (on Unix-like systems)?</p>

python csv pandas