load text file with separate columns in python pandas

Question

I have a text file that looks like this:

# Pearson correlation [n=344 #col=2]
# Name             Name              Value   BiasCorr   2.50%   97.50%  N: 2.50% N:97.50%
# ---------------  ---------------  -------- -------- -------- -------- -------- --------
  101_DGCA3.1D[0]  101_LEC.1D[0]    +0.85189 +0.85071 +0.81783 +0.87777 +0.82001 +0.87849

I have loaded it into python pandas using the following code:

import pandas as pd

data = pd.read_table('test.txt')
print data

However, I can't seem to access the different columns separately. I have tried using sep=' ' and copying the spaces between the columns in the text file, but I still don't get any column names and trying to print data[0] gives me an error:

Traceback (most recent call last):
  File "cut_afni_output.py", line 3, in <module>
    print data[0]
  File "/home/user/anaconda2/lib/python2.7/site-packages/pandas/core/frame.py", line 1969, in __getitem__
    return self._getitem_column(key)
  File "/home/user/anaconda2/lib/python2.7/site-packages/pandas/core/frame.py", line 1976, in _getitem_column
    return self._get_item_cache(key)
  File "/home/user/anaconda2/lib/python2.7/site-packages/pandas/core/generic.py", line 1091, in _get_item_cache
    values = self._data.get(item)
  File "/home/user/anaconda2/lib/python2.7/site-packages/pandas/core/internals.py", line 3211, in get
    loc = self.items.get_loc(item)
  File "/home/user/anaconda2/lib/python2.7/site-packages/pandas/core/index.py", line 1759, in get_loc
    return self._engine.get_loc(key)
  File "pandas/index.pyx", line 137, in pandas.index.IndexEngine.get_loc (pandas/index.c:3979)
  File "pandas/index.pyx", line 157, in pandas.index.IndexEngine.get_loc (pandas/index.c:3843)
  File "pandas/hashtable.pyx", line 668, in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12265)
  File "pandas/hashtable.pyx", line 676, in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12216)
KeyError: 0

I haven't been able to set the header row manually because it seems like python views the whole thing as one column. How do I make the text file be read in as separate columns that I can call?

edited to include the error since it was too long to include in a comment — Maria
– Maria, Commented Jul 12, 2017 at 17:15
What do you think you're supposed to get with data[0] That is attempting to get a column named 0 which doesn't exist. Are you trying to get the first row? data.iloc[[0]] — piRSquared
– piRSquared, Commented Jul 12, 2017 at 17:17

MaxU - stand with Ukraine · Accepted Answer · 2017-07-12 17:17:44Z

4

Try this:

In [33]: df = pd.read_csv(filename, comment='#', header=None, delim_whitespace=True)

In [34]: df
Out[34]:
                 0              1        2        3        4        5        6        7
0  101_DGCA3.1D[0]  101_LEC.1D[0]  0.85189  0.85071  0.81783  0.87777  0.82001  0.87849

answered Jul 12, 2017 at 17:17

MaxU - stand with Ukraine

212k37 gold badges402 silver badges437 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

load text file with separate columns in python pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related