CODEWITHSUNDEEP
pythonpandascsvdataframefile-writing
NeStack
NeStack

Reputation: 2012

In pandas-python or tabulate: how to set FIXED column width?

I am creating dataframes with pandas and then writing them into .txt-files with the tabulate package. But I need the columns of the .txt-tables to have exactly 11 characters width (I need to do this, because the .txt are later read by a fortran program that reads only fixed width columns). I find no option in the pandas or tabulate packages to set the width of the table columns. I read about pandas.set_option('display.max_colwidth',100) but this sets only a maximum width, not a fixed width, and it is for the display, not for the writing into files.

Anybody knows an option for setting the width? Or maybe an alternative package to tabulate, that allows this?

SOLUTION DUE TO INSTRUCTIONS IN @zipa's ANSWER:

Output is now indeed in columns of width=11 characters, but in the 5th column from the right one sees that this gets disrupted - because floats <10e+11 get represented in decimal notation, not in exponential/scientific. This is caused by str(x)

seen here, check 5th column from right:

C_2_       ,nan        ,nan        ,nan        ,nan        , C         , C         ,nan        ,nan        ,nan        ,2.49e-08   ,0.0        ,71600      ,2          ,100        ,6.01e+11   ,-1.0       ,71600      ,2          ,100        
CH         ,nan        ,nan        ,nan        ,nan        , C         , H         ,nan        ,nan        ,nan        ,3.16e-10   ,0.0        ,33700      ,2          ,100        ,7630000000.0,-1.0       ,33700      ,2          ,100        
CN         ,nan        ,nan        ,nan        ,nan        , C         , N         ,nan        ,nan        ,nan        ,1e-09      ,0.0        ,71000      ,2          ,100        ,24200000000.0,0.0        ,71000      ,2          ,100        
CO         ,nan        ,nan        ,nan        ,nan        , C         , O         ,nan        ,nan        ,nan        ,0.000152   ,-3.1       ,12900      ,2          ,100        ,3.67e+15   ,-4.1       ,12900      ,2          ,100

For solving this issue first I turned the "nan" into balnk space: df.fillna(' ',inplace=True). Next I followed @zipa's later comment and changed the one line of his suggested code - I made the lambda function take and if-clause and have different behavior depending if the entry is string or float:

df = df.applymap(lambda x: str(x).ljust(11) if type(x)==str else str('{:.3e}'.format(x)).ljust(11))

And this solved my problem! - see output below:

C2         ,           ,           ,           ,           , C         , C         ,           ,           ,           ,2.490e-08  ,0.000e+00  ,7.160e+04  ,2.000e+00  ,1.000e+02  ,6.010e+11  ,-1.000e+00 ,7.160e+04  ,2.000e+00  ,1.000e+02  
CH         ,           ,           ,           ,           , C         , H         ,           ,           ,           ,3.160e-10  ,0.000e+00  ,3.370e+04  ,2.000e+00  ,1.000e+02  ,7.630e+09  ,-1.000e+00 ,3.370e+04  ,2.000e+00  ,1.000e+02  
CN         ,           ,           ,           ,           , C         , N         ,           ,           ,           ,1.000e-09  ,0.000e+00  ,7.100e+04  ,2.000e+00  ,1.000e+02  ,2.420e+10  ,0.000e+00  ,7.100e+04  ,2.000e+00  ,1.000e+02  
CO         ,           ,           ,           ,           , C         , O         ,           ,           ,           ,1.520e-04  ,-3.100e+00 ,1.290e+04  ,2.000e+00  ,1.000e+02  ,3.670e+15  ,-4.100e+00 ,1.290e+04  ,2.000e+00  ,1.000e+02

Upvotes: 1

Views: 9051

Answers (1)

zipa
zipa

Reputation: 27879

Well, could this be what you need, it turns everything into 11 characters long text:

import pandas as pd

df = pd.DataFrame({'a': [12345, 2, 3], 'b': list('ABC')})
df = df.applymap(lambda x: str(x).ljust(11))
df.columns = df.columns.map(lambda x: str(x).ljust(11))
df.to_csv('somefile.txt', index=False)

Upvotes: 1

Related Questions