Python csv; get max length of all columns then lengthen all other columns to that length
<p>I have a directory full of data files in the following format:</p>

<blockquote>
<pre><code>4 2 5 7
1 4 9 8
8 7 7 1
  4 1 4
    1 5
    2 0
    1 0
    0 0
    0 0
</code></pre>
</blockquote>

<p>They are separated by tabs. The third and fourth columns contain useful information until they reach 'zeroes'.. At which point, they are arbitrarily filled with zeroes until the end of file. </p>

<p>I want to get the length of the longest column where we do not count the 'zero' values on the bottom. In this case, the longest column is column 3 with a length of 7 because we disregard the zeros at the bottom. Then I want to transform all the other columns by packing zeroes on them until their length is equal to the length of my third column (besides column 4 b/c it is already filled with zeroes). Then I want to get rid of all the zeros beyond my max length in all my columns.. So my desired file output will be as follows:</p>

<blockquote>
<pre><code>4 2 5 7
1 4 9 8
8 7 7 1
0 4 1 4
0 0 1 5
0 0 2 0
0 0 1 0
</code></pre>
</blockquote>

<p>These files consist of ~ 100,000 rows each on average... So processing them takes a while. Can't really find an efficient way of doing this. Because of the way file-reading goes (line-by-line), am I right in assuming that in order to find the length of a column, we need to process in the worst case, N rows? Where N is the length of the entire file. When I just ran a script to print out all the rows, it took about 10 seconds per file... Also, I'd like to modify the file in-place (over-write). </p>

<p>Hi I would use Pandas and Numpy for this:</p>

<pre><code>import pandas as pd
import numpy as np

df = pd.read_csv('csv.csv', delimiter='\t')
df = df.replace(0,np.nan)
while df.tail(1).isnull().all().all() == True:
  df=df[0:len(df)-1]
df=df.replace(np.nan,0)
df.to_csv('csv2.csv',sep='\t', index=False) #i used a different name just for testing
</code></pre>

<p>You create a DataFrame with your csv data.<br>
There are a lot of built in functions that deal with <code>NaN</code> values, so change all <code>0</code>s to <code>nan</code>.
Then start at the end <code>tail(1)</code> and check if the row is <code>all()</code> <code>NaN</code>.  If so copy the DF less the last row and repeat.
I did this with 100k rows and it takes only a few seconds.</p>

python csv