Reading from a .txt file to a pandas dataframe

Question

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?

Answer 1

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})

Answer 2

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.