Reading an excel file into a pandas DF that has a pipe and spaces as delimiters

user_15582733 0
Viewed 276

How can I read an excel file into a pandas dataframe that has a pipe and a space as delimiters?

The code I'm currently using is:

DF = pd.read_csv(r'Desktop\Automation\File.xls', sep="|")

I don't know how to add the space as the second delimiter, I have also tried \s+| but to no avail.

ID|Name|Job|Nationality|
123 Cian|IT|-|
222 John|Teacher|Spanish|
pandas delimiter dataframe python
1 Answers

You can use a regex in the sep field:

my_file = '''
ID|Name|Job|Nationality|

123 Cian|IT|-|

222 John|Teacher|Spanish|
'''

df = pd.read_csv(StringIO(my_file), sep='[ |]')
user_41459410
Related