How to read the custom table in pandas which has number string number number?

user_5200329 0
Viewed 55

I have been trying to read a custom table in pandas but am getting errors for a long time.

Here is the outline of table:

Number string number number

  • there is only one white space between two words
  • a word is a number or just an English word
  • there are no NANS

filename: station.tsv

794 Kissee Mills MO 140 73 
824 Loma Mar CA 49 131 
603 Sandy Hook CT 72 148 
478 Tipton IN 34 98 
619 Arlington CO 75 93 
711 Turner AR 50 101 
839 Slidell LA 85 152 
411 Negreet LA 99 105 
588 Glencoe KY 46 136 
665 Chelsea IA 99 60
957 South El Monte CA 74 80


Note that the row `957 South El Monte CA 74 80` is  
actually 33rd row for my data.
If it was only 11th row, 
pandas gives no error, 
but if it is large nth row it gives error.

My attempt

df = pd.read_csv('station.tsv', header=None, sep=' ')

ParserError: Error tokenizing data. 
C error: Expected 7 fields in line 33, saw 8

Question

Is there a way to parse the data with some regex something like:

regexp = r'(\d+)\s+(\w+)\s+(\d+)\s+(\d+)'

To read the text data and make an array from them.

I am expecting to use NUMPY, PANDAS or any other python library for this.

pandas numpy python
1 Answers

You can specify a delimiter that is a space not preceded by a letter (?<![a-zA-Z])\s, or | a space that is followed by a number \s(?=\d).

sep = r'(?<![a-zA-Z])\s|\s(?=\d)'
df = pd.read_csv('station.tsv', engine='python', sep=sep, header=None)

      0                  1    2    3
0   794    Kissee Mills MO  140   73
1   824        Loma Mar CA   49  131
2   603      Sandy Hook CT   72  148
3   478          Tipton IN   34   98
4   619       Arlington CO   75   93
5   711          Turner AR   50  101
6   839         Slidell LA   85  152
7   411         Negreet LA   99  105
8   588         Glencoe KY   46  136
9   665         Chelsea IA   99   60
10  957  South El Monte CA   74   80

df.dtypes
#0     int64
#1    object
#2     int64
#3     int64
user_43333590
Related