Use Pandas to to read whitespace delimited triangular matrix with empty cells as zeros

user_10321780 0
Viewed 92

I'm trying to use pandas.read_table() to read a whitespace delimited lower triangular matrix from a text file. The zeros entries of the matrix are left blank.

    C1  C2  C3
R1   1
R2   2   3
R3   5   6   7

For now, I have the following ugly two step solution.

header = pd.read_table('test.txt', delim_whitespace=True, nrows=0)
names = list(header.columns.values)
names.insert(0, '')

df = pd.read_table('test.txt', delim_whitespace=True,
                        names=names, skiprows=1, index_col=0)

Which does give me what I want. Output:

    C1   C2  C3
R1  1   NaN NaN
R2  2   3.0 NaN
R3  5   6.0 7.0

Is there a "cleaner" way to do something similar?

data-munging pandas python
1 Answers

Use pandas.read_fwf to read a fixed width file

text = """\
    C1  C2  C3
R1   1
R2   2   3
R3   5   6   7"""

pd.read_fwf(pd.io.common.StringIO(text), index_col=0)

    C1   C2   C3
R1   1  NaN  NaN
R2   2  3.0  NaN
R3   5  6.0  7.0
user_23366540
Related