Pandas read_csv on file without space?

user_14979795 0
Viewed 108

Given a set of data that looks like the following, each line are 10 characters in length. They are links of a network, comprised of combinations of 4 or 5 character node numbers. Below is an example of the situations I would face:

|10637 4652|

| 1038 1037|

|7061219637|

|82004 2082|

As the dataset doesn't care much about spacing, While lines 1, 2 and 4 can be read in Pandas easily with either sep=' ' or delim_whitespace=True, I'm afraid I can't do the same for line 3. There is very little I can do to the input data file as it's generated from a third party software (apart from doing some formatting in Excel, which seemed counterintuitive...) Please, is there something in Pandas allowing me to specify the number of characters (in my case, 5) as a delimiter?

Advice much appreciated.

pandas dataframe python
4 Answers

I think what you're looking for is pd.read_fwf to read a fixed-width file. In this case, you would specify column specifications:

pd.read_fwf(io.StringIO('''|10637 4652|
| 1038 1037|
|7061219637|
|82004 2082|'''), colspecs=[(1, 6), (6, 11)], header=None)

The column specifications are 0-indexed and end-exclusive. You could also use the widths parameter, but I would avoid using it before stripping the | out to ensure that your variables are properly read in as numbers rather than as strings starting or ending with a pipe.

This would, in this case, produce:

       0      1
0  10637   4652
1   1038   1037
2  70612  19637
3  82004   2082

I passed header=None due to the lack of a header in your sample data. You may need to adjust as needed. I also stripped out all the blank lines in your input. If there are in fact blank lines in the input, then I would first run: '\n'.join((s for s in input_string.split('\n') if len(s.strip()) != 0)) before passing it to be parsed. There, you would also need to first load the file as a string, clean it, and then pass it with io.StringIO to read_fwf.

user_27410910

With read_csv, you can specify the sep as a group of 4 or 5 digits, then keep only the columns with the numbers.

from io import StringIO

s = '''
|10637 4652|
| 1038 1037|
|7061219637|
|82004 2082|
'''
print(
    pd.read_csv(StringIO(s), sep='(\d{4,5})', 
                engine='python', usecols=[1,3],
                index_col=False, header=None)
)
       1      3
0  10637   4652
1   1038   1037
2  70612  19637
3  82004   2082
user_92747320

Or you can just load the data and take the advance of the textwrap module just specify the width and It'll generate the columns for you.

import textwrap
df['<col_name>'].apply(textwrap.wrap, width = 5).apply(pd.Series)

OUTPUT:

    0        1
0   10637   4652
1   1038    1037
2   70612   19637
3   82004   2082
user_154380330

I would just use df['my_col'].str[0:6] after reading it in as one column.

If your file is just this data then @ifly6's use of read_fwf is more appropriate. I just assumed that this is one column as part of a larger CSV in which case this is the approach I would use.

user_82171120
Related