import text data having spaces using pandas.read_csv

user_172750 0
Viewed 1882

I would like to import a text file using pandas.read_csv:

1541783101     8901951488  file.log             12345  123456
1541783401     21872967680  other file.log       23456     123
1541783701     3  third file.log 23456     123

The difficulty here is that the columns are separated by one or more spaces, but there is one column that contains a file name having spaces. So I can't use sep=r"\s+" to identify the columns as that would fail at the first file name having a space. The file format does not have a fixed column width.

However each file name ends with ".log". I could write separate regular expressions matching each column. Is it possible to use these to identify the columns to import? Or is it possible to write a separator regular expression that selects all characters NOT matching any of the column matching regular expressions?

pandas regex-negation python
1 Answers

Answer for updated question -

Here's the code which will not fail whatever the data width may be. You can modify it as per your needs.

df = pd.read_table('file.txt', header=None)

# Replacing uneven spaces with single space
df = df[0].apply(lambda x: ' '.join(x.split()))

# An empty dataframe to hold the output
out = pd.DataFrame(np.NaN, index=df.index, columns=['col1', 'col2', 'col3', 'col4', 'col5'])

n_cols = 5      # number of columns
for i in range(n_cols-2):
    # 0 1
    if i == 0 or i == 1:
        out.iloc[:, i] = df.str.partition(' ').iloc[:,0]
        df = df.str.partition(' ').iloc[:,2]
    else:
        out.iloc[:, 4] = df.str.rpartition(' ').iloc[:,2]
        df = df.str.rpartition(' ').iloc[:,0]
        out.iloc[:,3] = df.str.rpartition(' ').iloc[:,2]
        out.iloc[:,2] = df.str.rpartition(' ').iloc[:,0]

print(out)

+---+------------+-------------+----------------+-------+--------+
|   |    col1    |      col2   |       col3     |   col4 |   col5 |
+---+------------+-------------+----------------+-------+--------+
| 0 | 1541783101 |  8901951488 | file.log       | 12345 | 123456 |
| 1 | 1541783401 | 21872967680 | other file.log | 23456 |    123 |
| 2 | 1541783701 |           3 | third file.log | 23456 |    123 |
+---+------------+-------------+----------------+-------+--------+

Note - The code is hardcoded for 5 columns. It can be generalized too.

Previous answer -

Use pd.read_fwf() to read files with fixed-width.

In your case:

pd.read_fwf('file.txt', header=None)

+---+----------+-----+-------------------+-------+--------+
|   |    0     |  1  |         2         |   3   |   4    |
+---+----------+-----+-------------------+-------+--------+
| 0 | 20181201 |   3 | file.log          | 12345 | 123456 |
| 1 | 20181201 |  12 | otherfile.log     | 23456 |    123 |
| 2 | 20181201 | 200 | odd file name.log | 23456 |    123 |
+---+----------+-----+-------------------+-------+--------+
user_107345250
Related