Pandas read_fwf is limiting string data to 127 characters upon read in

user_19489412 0
Viewed 48

I am reading a fixed-width file into a pandas dataframe, but I notice that the data is not being properly stored into the dataframe. The cells in the dataframe are being restricted to 127 characters.

Input file:

Column 1                                        Column 2       Column 3     
*see sentence below                              18.0           True
this sentence is under 127 characters            12.0           False

For the sentence over 127 characters, imagine the sentence is this:

You think darkness is your ally. But you merely adopted the dark; I was born in it. Moulded by it. I didn't see the light until I was already a man. By then it was nothing to me but blinding!

Code:

df = pd.read_fwf(input_file_path, index_col=False)
df.to_csv('output.csv', index=False, encoding='utf8')

Output CSV:

Column 1,Column 2,Column 3
You think darkness is your ally. But you merely adopted the dark; I was born in it. Moulded by it. I didn't see the light until,18.0,True
this sentence is under 127 characters,12.0,False

Is there an argument I can put into the read_fwf to fix this issue, or is it likely just the autoparsing being problematic and cutting off too soon? Thanks!

Edit: I see that in my own version of the file I am reading, the long lines are over 100 lines below some much shorter lines. I believe that because colspecs='infer' is default for the first 100 rows, the column specs are not being properly determined, and hence cut off the longer values farther down. Does anyone have suggestions for this?

pandas export-to-csv python
1 Answers

As an alternative, you could read the input file with Python readlines. Then, apply Pandas rsplit with n=2 using space by default, or any other pattern (rsplit with pat parameter) that is consistent with your last 2 columns, whilst using expand=True to split the data into separate columns.

import pandas as pd

with open('sample.csv') as f:
    data = f.readlines()

df = pd.DataFrame(data[1:]) # discard the header column
print(df)

# use expand to split strings into separate columns
df_out = df[0].str.rsplit(n=2, expand=True)
# fix column names
df_out.columns = [f'Column_{i+1}' for i in df_out.columns]

df_out['LEN'] = df_out['Column_1'].apply(len)
print(df_out)

Output df_out

                                            Column_1 Column_2 Column_3  LEN
0  You think darkness is your ally. But you merel...     18.0     True  191
1              this sentence is under 127 characters     12.0    False   37
2  Hope. Every man who has rotted here over the c...   847.11     True  498
user_131441430
Related