Pandas: How to correctly parse a dashed text flat file into a dataframe

This data is really tricky as you said, So, we have to pay logic another way around:

raw_data as provided:

$ cat weired_data
----------------------------------------
|Order    |Warehouse|Stock|Price|Vendor|
|--------------------------------------|
|31434    |WA12     |200  |160  |AS12  |
|31435    |WA11     |26   |12   |AS11  |
|31436    |WA13     |202  |161  |AS16  |
----------------------------------------
2020-06-14
----------------------------------------
|Order    |Warehouse|Stock|Price|Vendor|
|--------------------------------------|
|31437    |WA12     |200  |160  |AS12  |
|31438    |WA11     |26   |12   |AS11  |
|31439    |WA13     |202  |161  |AS16  |
----------------------------------------

Cleaning data to form DataFrame:

This is how you can parse your data to create a desired DataFrame:

#!/home/nxf59093/Karn_python3/bin/python
import pandas as pd
##### Python pandas, widen output display to see more columns. ####
#pd.set_option('display.height', None)
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
pd.set_option('display.width', None)
pd.set_option('expand_frame_repr', True)
##################################################################
# read the with `read_csv`
df = pd.read_csv("weired_data")
# replace all hyphens and pipes using `str.replace`
df['2020-06-13'] = df['2020-06-13'].str.replace('|', '')
df['2020-06-13'] = df['2020-06-13'].str.replace('-', '')
# as every row contains `31` and that's the way to pick the lines  
df = df[df['2020-06-13'].str.contains('31')]
# as default it has `2020-06-13` column name hence we'll Limit 
# number of splits in output by using `n=5` as we need 5 columns which 
# will return ` 0     1    2    3     4` columns names
df = df['2020-06-13'].str.split(n=5, expand=True)
# as we have got column names which we can rename as we want them to be
df = df.rename(columns={0: 'Order', 1: 'Warehouse', 2: 'Stock',  3: 'Price', 4: 'Vendor'})
print(df)

Desired Parsed DataFrame:

 $ ./test_data.py
    Order Warehouse Stock Price Vendor
3   31434      WA12   200   160   AS12
4   31435      WA11    26    12   AS11
5   31436      WA13   202   161   AS16
11  31437      WA12   200   160   AS12
12  31438      WA11    26    12   AS11
13  31439      WA13   202   161   AS16

Another Method with less slicing:

df = pd.read_csv("weired_data", sep="|", skiprows=2, index_col=0)
df = df.dropna(axis=0,how='all')
df.columns = df.columns.str.replace(' ', '')
df = df[df['Order'].str.contains('31')]
df = df.reset_index()
df = df.dropna(axis=1,how='all')
print(df)

Result:

   Order      Warehouse  Stock  Price  Vendor
0  31434      WA12       200    160    AS12
1  31435      WA11       26     12     AS11
2  31436      WA13       202    161    AS16
3  31437      WA12       200    160    AS12
4  31438      WA11       26     12     AS11
5  31439      WA13       202    161    AS16

In case you want your data to be looked like as sql tables then you need to use tabulate and print that .

from tabulate import tabulate
print(tabulate(df,  headers='keys', tablefmt='psql', showindex=False))


+-----------+-------------+---------+---------+----------+
|     Order | Warehouse   |   Stock |   Price | Vendor   |
|-----------+-------------+---------+---------+----------|
| 31434     | WA12        |   200   |   160   | AS12     |
| 31435     | WA11        |   26    |   12    | AS11     |
| 31436     | WA13        |   202   |   161   | AS16     |
| 31437     | WA12        |   200   |   160   | AS12     |
| 31438     | WA11        |   26    |   12    | AS11     |
| 31439     | WA13        |   202   |   161   | AS16     |
+-----------+-------------+---------+---------+----------+

Maybe this will work for you.

import pandas as pd
import re

file = open('weird_data.txt', 'r').read()

###Removing strange characters###
to_replace = ['\n', '--------------------------------------', ' ']
for char in to_replace:
    file = file.replace(char, '')
    
file = file.replace('--', '')

###Removing column names. I will only work with the pattern###    
column_names = ['Order','Warehouse','Stock','Price','Vendor']
for name in column_names:
    file = file.replace(name, '')
    
file = file.replace('|||||||||', '|')

###Here we get all the possible dates###
dates = re.findall('\d{4}-\d{2}-\d{2}', file)

###Here we split out string between every possible date###
undated_rows = re.split('\d{4}-\d{2}-\d{2}', file)[1::]

print('Date|Order|Warehouse|Stock|Price|Vendor', file = open('regular_data.txt', 'w')) ###This is the first line of our csv with the column names###


rows = []
for t in zip(dates, undated_rows): ###This zip every line with it's respective date
    row = list(t[1])
    row[0] = t[0]
    row.insert(1, '|')
    row = ''.join(row)
    row = row.replace('||', '\n'+t[0]+'|')
    if row[-1] == '|':
        row = row[0:-1]
    print(row, file = open('regular_data.txt', 'a'))

df = pd.read_csv('regular_data.txt', sep = '|')

I believe this a very manual solution, but it works.