6
\$\begingroup\$

Having a text file './inputs/dist.txt' as:

     1         1      2.92
     1         2     70.75
     1         3     60.90
     2         1     71.34
     2         2      5.23
     2         3     38.56
     3         1     61.24
     3         2     38.68
     3         3      4.49

I'm reading the text file to store it in a dataframe by doing:

from pandas import DataFrame
import pandas as pd
import os


def get_file_name( path):
    return os.path.basename(path).split(".")[0].strip().lower() 


name = get_file_name('./inputs/dist.txt')
with open('./inputs/dist.txt') as f:
    df = DataFrame(0.0, index=[1,2,3], columns=[1,2,3])
    for line in f:
        data = line.strip().split()
        row,column,value = [int(i) if i.isdigit() else float(i) for i in data]
        df.set_value(row,column,value)
m[name] = df 

and I end up with a dataframe of the data. I have to read more bigger files that follow this format. Is there a faster way to redo this to improve runtime?

\$\endgroup\$
6
\$\begingroup\$

When opening very large files, first concern would be memory availability on your system to avoid swap on slower devices (i.e. disk).

Pandas is shipped with built-in reader methods. For example the pandas.read_table method seems to be a good way to read (also in chunks) a tabular data file.

In the specific case:

import pandas

df = pandas.read_table('./input/dists.txt', delim_whitespace=True, names=('A', 'B', 'C'))

will create a DataFrame objects with column named A made of data of type int64, B of int64 and C of float64.

You can by the way force the dtype giving the related dtype argument to read_table. For example forcing the second column to be float64.

import numpy as np
import pandas

df = pandas.read_table('./input/dists.txt', delim_whitespace=True, names=('A', 'B', 'C'),
                   dtype={'A': np.int64, 'B': np.float64, 'C': np.float64})
|improve this answer|||||
\$\endgroup\$
  • 2
    \$\begingroup\$ Could you be more specific about how to use read_table()? The code is also doing a transformation on the data as it reads it. \$\endgroup\$ – 200_success Jan 10 '17 at 2:35
3
\$\begingroup\$

pandas has a good fast (compiled) csv reader (may be more than one)

In [279]: df=pd.read_csv('cr152194.csv')
In [280]: df
Out[280]: 
        1         1      2.92
0       1         2     70.75
1       1         3     60.90
2       2         1     71.34
3       2         2      5.23
4       2         3     38.56
5       3         1     61.24
6       3         2     38.68
7       3         3      4.49
In [281]: df.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 8 entries, 0 to 7
Data columns (total 1 columns):
     1         1      2.92    8 non-null object
dtypes: object(1)
memory usage: 96.0+ bytes

Read its docs for more control options. For example as I used it, it used the first row as labels, whereas it looks more like data.

This is better:

In [294]: df=pd.read_csv('cr152194.csv', header=None)
In [295]: df
Out[295]: 
                            0
0       1         1      2.92
1       1         2     70.75
.....

On SO there are lots of questions about reading csv files. I've mostly dealt with the ones that use numpy readers like loadtxt and genfromtxt. Those written in Python and I can outline their behavior. But to generate a DataFrame, using this pd function is simpler and faster.

Same load via np.genfromtxt:

In [285]: data = np.genfromtxt('cr152194.csv', dtype=None)
In [286]: data
Out[286]: 
array([(1, 1, 2.92), (1, 2, 70.75), (1, 3, 60.9), (2, 1, 71.34),
       (2, 2, 5.23), (2, 3, 38.56), (3, 1, 61.24), (3, 2, 38.68),
       (3, 3, 4.49)], 
      dtype=[('f0', '<i4'), ('f1', '<i4'), ('f2', '<f8')])
In [287]: pd.DataFrame(data)
Out[287]: 
   f0  f1     f2
0   1   1   2.92
1   1   2  70.75
2   1   3  60.90
3   2   1  71.34
4   2   2   5.23
5   2   3  38.56
6   3   1  61.24
7   3   2  38.68
8   3   3   4.49

genfromtxt with dtype=None determines datatype from the first data row, and then uses that to convert all the other rows. Note the data.dtype, which specifies which columns are integer and which are floats.

|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ great thanks, is always good to learn something new. how ever i timed your solution and it takes around 6 min in comparison to @SolidSnake which takes 40 seconds. i think ill stick with the faster one \$\endgroup\$ – Daniel Jan 10 '17 at 23:28
  • \$\begingroup\$ Which was slow, genfromtxt or read_csv? Looks like read_csv uses the same compiled reader as read_table; just different defaults. \$\endgroup\$ – hpaulj Jan 10 '17 at 23:59
  • \$\begingroup\$ genfromtxt i think using dtype=None makes is slow since evaluates the data one line at the time \$\endgroup\$ – Daniel Jan 11 '17 at 0:00
  • \$\begingroup\$ genfromtxt, regardless of dtype, reads the file line by line (with regular Python functions), and builds a list of lists. It converts that an array once, at the end. The Pandas readers use a compiled _reader. \$\endgroup\$ – hpaulj Jan 11 '17 at 1:56

Not the answer you're looking for?Browse other questions tagged or ask your own question.