How do I convert a text table to a pandas dataframe?

Question

I have a text table as a string like this and it's quite long. It can also be written to a file,

+--------------+----------+---------+------------+
| Endpoint     | Table    | Request | Is Updated |
+--------------+----------+---------+------------+
| /api/test1   | test1    | True    | True       |
+--------------+----------+---------+------------+
| /api/test2   | test2    | False   | False      |
+--------------+----------+---------+------------+
| /api/test3   | test3    | False   | True       |
+--------------+----------+---------+------------+

I want to convert this to a pandas dataframe. Here is my expected output:

>>> import pandas as pd
>>> df = pd.DataFrame(
    {'Endpoint': ['/api/test1', '/api/test2', '/api/test3'],
     'Table': ['test1', 'test2', 'test3'],
    'Request': [True, False, False],
    'Is Updated': [True, False, True]},
)... ... ... ... ...
>>> df
     Endpoint  Table  Request  Is Updated
0  /api/test1  test1     True        True
1  /api/test2  test2    False       False
2  /api/test3  test3    False        True

Thanks in advance.

Assuming its a text file wouldnt using read line and delimiter be good enough? — Jason Chia, Dec 13 '19 at 11:15
coldspeed wrote a function for this very problem : check out https://stackoverflow.com/a/59262301/9375102 — Umar.H, Dec 13 '19 at 11:18
This is a string type. It can also be exported to a file. And yeap @Jason Chia — E. Zeytinci, Dec 13 '19 at 11:21

Chris Adams · Accepted Answer · 2019-12-13T11:33:04.393

IIUC, use re.sub to replace regex and io.StringIO to read:

import re
from io import StringIO


text = """
+--------------+----------+---------+------------+
| Endpoint     | Table    | Request | Is Updated |
+--------------+----------+---------+------------+
| /api/test1   | test1    | True    | True       |
+--------------+----------+---------+------------+
| /api/test2   | test2    | False   | False      |
+--------------+----------+---------+------------+
| /api/test3   | test3    | False   | True       |
+--------------+----------+---------+------------+
"""

df = pd.read_csv(StringIO(re.sub(r'[-+|]', '', text)), sep='\s{2,}', engine='python')
print(df)

Output:

     Endpoint  Table  Request  Is Updated
0  /api/test1  test1     True        True
1  /api/test2  test2    False       False
2  /api/test3  test3    False        True

Just thinking: isn't `\s\s+` better expressed as `\s{2,}`? – accdias Dec 13 '19 at 11:32 — accdias, Dec 13 '19 at 11:32

How do I convert a text table to a pandas dataframe?

1 Answers1