mismatched columnspecs, therefore wrong read values with pd.read_fwf and using colspecs

Question

I am reading a text file using pd.read_fwf as below:

import pandas as pd

specs_test =[(19, 20),(20, 21),(21, 23),(23,26)]
names_test = ["Record_Type","Resident_Status","State_Occurrence_FIPS",
"County_Occurrence_FIPS"]

test_l = pd.read_fwf('test.txt', header=None, names = names_test, colspecs= specs_test)

and test.txt is as follow:

                  11SC059
                  11SC051
                  11SC019
                  11SC033
                  11SC007
                  11SC041
                  22SC079
                  11SC043
                  11SC045
                  22SC079 

after reading the file test_l is as follow:

    Record_Type Resident_Status State_Occurrence_FIPS   County_Occurrence_FIPS
0   1   S   C0  59
1   1   S   C0  51
2   1   S   C0  19
3   1   S   C0  33
4   1   S   C0  7
5   1   S   C0  41
6   2   S   C0  79
7   1   S   C0  43
8   1   S   C0  45
9   2   S   C0  79

but, based on my colspec it should have the following (I have just added first row as I expected):

1   1  SC  059

What am I missing here? Thank you very much for your help!

Answer 1

First you are off by an index. Try:

specs_test =[(18, 19),(19, 20),(20, 22),(22,25)]

Also, for numerical values the leading zeros will be ignored. To keep them, you can convert to string by adding:

converters = {h:str for h in names_test}

The final code can be:

import pandas as pd

specs_test =[(18, 19),(19, 20),(20, 22),(22,25)] ## Here you where off by an index.

names_test = ["Record_Type","Resident_Status","State_Occurrence_FIPS", "County_Occurrence_FIPS"]

test_l = pd.read_fwf('test.txt', 
                 header=None, 
                 names = names_test, 
                 colspecs= specs_test, 
                 converters = {h:str for h in names_test}) ## If you want to keep the leading 
                                                           ## zeros you can convert to string.

The result:

Record_Type Resident_Status State_Occurrence_FIPS   County_Occurrence_FIPS
0   1   1   SC  059
1   1   1   SC  051
2   1   1   SC  019
3   1   1   SC  033
4   1   1   SC  007
5   1   1   SC  041
6   2   2   SC  079
7   1   1   SC  043
8   1   1   SC  045
9   2   2   SC  079

Answer 2

I got this when pasting your data into a test file and fixing the tuples.

specs_test =[(18, 19),(19, 20),(20, 22),(22,25)]
names_test = ["Record_Type","Resident_Status","State_Occurrence_FIPS",
"County_Occurrence_FIPS"]
pd.read_fwf('test.txt', header=None, names = names_test, colspecs= specs_test )

It's dropping the leading zeroes on te 4th column, so you may have to play around with kwargs to send in data types or fix the column after import

   Record_Type  Resident_Status State_Occurrence_FIPS  County_Occurrence_FIPS
0            1                1                    SC                      59
1            1                1                    SC                      51
2            1                1                    SC                      19
3            1                1                    SC                      33
4            1                1                    SC                       7
5            1                1                    SC                      41
6            2                2                    SC                      79
7            1                1                    SC                      43
8            1                1                    SC                      45
9            2                2                    SC                      79