Writing formatted dataframe with space-tab delimiters

Question

The conventional csv is not human reader friendly and therefore, I am writing tab-separated pandas dataframe using following command:

df.to_csv ('output.txt', index = False, header=True, float_format='%.3f', sep='\t')

This results in the output in the following format:

A       B       C       D
90.856  1.214   0.417   1.858
363.424 0.616   0.302   1.858
1817.121    0.318   0.000   1.858
2180.545    0.296   0.000   1.858

However, I want the output file to look like this:

A        B       C       D
90.856   1.214  0.417   1.858
363.424  0.616  0.302   1.858
1817.121 0.318  0.000   1.858
2180.545 0.296  0.000   1.858

I do not care about how much space is between the columns but I want the column data aligned properly. How to achieve this?

score 1 · Answer 1 · answered May 01 '21 at 23:45

1

You can try np.savetxt:

np.savetxt(
    r"file.txt",
    df.values,
    fmt="%-10.3f",
    header="".join("{:11}".format(c) for c in df.columns),
    comments="",
)

Saves file.txt:

A          B          C          D          
90.856     1.214      0.417      1.858     
363.424    0.616      0.302      1.858     
1817.121   0.318      0.000      1.858     
2180.545   0.296      0.000      1.858

answered May 01 '21 at 23:45

Andrej Kesely

168,389
15
48
91

Is there a pandas way to achieve what I am looking for? – SKPS May 01 '21 at 23:57
@SKPS You're essentially searching for `pd.to_fwf` (because `pd.read_fwf` exists), but this isn't implemented in current pandas. More here: https://stackoverflow.com/questions/16490261/python-pandas-write-dataframe-to-fixed-width-file-to-fwf – Andrej Kesely May 01 '21 at 23:59

Henry Ecker · Accepted Answer · 2021-05-02T05:27:08.720

Writing to a file with DataFrame.to_string is also a close option:

import pandas as pd

df = pd.DataFrame({'A': {0: 90.856, 1: 363.424, 2: 1817.121, 3: 2180.545},
                   'B': {0: 1.214, 1: 0.616, 2: 0.318, 3: 0.296},
                   'C': {0: 0.417, 1: 0.302, 2: 0.0, 3: 0.0},
                   'D': {0: 1.858, 1: 1.858, 2: 1.858, 3: 1.858}})

with open('output.txt', 'w') as f:
    f.write(df.to_string(index=False))

Output.txt

       A     B     C     D
  90.856 1.214 0.417 1.858
 363.424 0.616 0.302 1.858
1817.121 0.318 0.000 1.858
2180.545 0.296 0.000 1.858

The floating point precision can be controlled with float_format:

with open('output.txt', 'w') as f:
    f.write(df.to_string(index=False, float_format="{:.1f}".format))

Output.txt

     A   B   C   D
  90.9 1.2 0.4 1.9
 363.4 0.6 0.3 1.9
1817.1 0.3 0.0 1.9
2180.5 0.3 0.0 1.9

score 0 · Answer 3 · answered May 01 '21 at 23:46

0

As a general tip, there is a __repr__() method used to represent the class object as a string in python. We can use that special method here with the dataframe.

with open('output.txt', 'w') as fo:
    fo.write(df.__repr__())

answered May 01 '21 at 23:46

Divyansh Rai

190
1
5

Thanks, this option basically prints the dataframe to the file. So there are index as well in output file. How to avoid index on writing output? – SKPS May 01 '21 at 23:59

Writing formatted dataframe with space-tab delimiters

3 Answers3