How to count rows in multiple csv file

Question

I have csv file like below

file1

A B
1 2
3 4

file2

A B
1 2

file3

A B
1 2
3 4
5 6

I would like to count the rows in all the csv file

I tried

f=pd.read_csv(file1)

f.shape

But When I have a lot of csv file ,it takes too much time.

I would like to get the result like below

      rows
file1  2
file2  1
file3  3

How can I get this result?

Answer 1

You can create dict of length of all files and then Seriesm for DataFrame add to_frame:

import glob
import pandas as pd

files = glob.glob('files/*.csv')

d = {f: sum(1 for line in open(f)) for f in files}

print (pd.Series(d))

print (pd.Series(d).rename('rows').rename_axis('filename').reset_index())

open does not guarantee the file to be closed properly, so another solution:

def file_len(fname):
    with open(fname) as f:
        for i, l in enumerate(f):
            pass
    return i + 1

d = {f: file_len(f) for f in files}

Answer 2

In *nix systems and if you can do it outside of Python:

wc -l *.csv

Should do the trick.

Answer 3

For the sake of completeness as a kind of summary of all what was said about speed and proper opening/closing of files here a solution that works FAST and don't need much fancy code, ... limited to *nix systems(?) (but I think similar technique can be used on other systems too).

The code below runs a tiny bit faster then rawincount() and counts also last lines which don't have a '\n' at the end of line (a problem rawincount() has):

import glob, subprocess, pandas
files = glob.glob('files/*.csv') 
d = {f: subprocess.getoutput("sed -n '$=' " + f) for f in files}
print(pandas.Series(d))

P.S. Here some timings I have run on a set of large text files (39 files with a total size of 3.7 GByte, Linux Mint 18.1, Python 3.6). Fascinating is here the timing of the proposed wc -l *.csv method:

    Results of TIMING functions for getting number of lines in a file:
    -----------------------------------------------------------------
            getNoOfLinesInFileUsing_bash_wc :  1.04  !!! doesn't count last non empty line
          getNoOfLinesInFileUsing_bash_grep :  1.59
  getNoOfLinesInFileUsing_mmapWhileReadline :  2.75
           getNoOfLinesInFileUsing_bash_sed :  3.42
 getNoOfLinesInFileUsing_bytearrayCountLF_B :  3.90  !!! doesn't count last non empty line
          getNoOfLinesInFileUsing_enumerate :  4.37
      getNoOfLinesInFileUsing_forLineInFile :  4.49
  getNoOfLinesInFileUsing_sum1ForLineInFile :  4.82      
 getNoOfLinesInFileUsing_bytearrayCountLF_A :  5.30  !!! doesn't count last non empty line
     getNoOfLinesInFileUsing_lenListFileObj :  6.02
           getNoOfLinesInFileUsing_bash_awk :  8.61

Answer 4

Try this,

it adds each entry with file name and no.of rows and the columns have appropriate labels :

import os      
df = pd.DataFrame(columns=('file_name', 'rows'))
for index,i in enumerate(os.listdir('.')):
    df.loc[index] = [i,len(pd.read_csv(i).index)]

Answer 5

The solutions provided so far are not the quickest when working with very large csv's. Also, using open() in a list comprehension does not guarantee the file to be closed properly as e.g. when using with (see this question). So combining that with the insights from this question for speed:

from itertools import takewhile, repeat

def rawincount(filename):
    with open(filename, 'rb') as f:
        bufgen = takewhile(lambda x: x, (f.raw.read(1024*1024) for _ in repeat(None)))
        return sum(buf.count(b'\n') for buf in bufgen)

And applying the solution provided by @jezrael:

import glob
import pandas as pd

files = glob.glob('files/*.csv')
d = {f: rawincount(f) for f in files}
df = pd.Series(d).to_frame('rows')