python creating columns and rows from a txt file without recognizable rows and columns

Question

Link to txt file example Additional File Link

I want to be able to use python to read a txt file and select the column and row information. The txt in the file is in one single column with the data for the column further down the file like the example below. I have put some additional info in (). I have several different txt files with similar formatting, and so I would love to be able to just replace each file and run them.

My goal is to be able to put the data into a csv or excel file.

Here is a link to the txt file. About 1/3 down you will see in () where the column names are and right below that I have marked where the data for those columns is. There is additional data further down the sheet but I just need to get a good start. I have several similar txt files that I would need to be able to run the code on.

Answer 1

Performing ETL on what is almost a free format data interface is always ugly and unstable Approach

1. consider column definitions as meta data
2. find the meta data, get start and end row of this
3. clean up meta data - there are column definitions that don't exist !
4. now have column names
5. consider rest of rows after last meta data row to be actual data
6. reshape() this into an array cols X max possible rows in remaining data
7. do post transform validation logic to filter to what appears to be interesting data set

import pandas as pd
with open("data1.txt") as f:
    data = f.read()
# pd.read_csv(io.StringIO(data))
dfraw = pd.DataFrame(data.split("\n"))

# find first and last row that contain column meta information
colsi = dfraw.loc[dfraw[0].str.contains("Column")].index
assert len(colsi)==2, "failed to find column labels"

# first stab... based on finding meta information
cols = dfraw.loc[colsi[0]:colsi[1]]
# exclude meta rows that are empty or labeled "Grade"
cols = cols[(cols[0]!="") & (cols[0]!="Grade") & (cols[0]!="(Column Names)--------------------")]

# shift dfraw start row to start of data after column meta data
dfraw = dfraw.loc[colsi[1]+1:].reset_index(drop=True)
# create dataframe with all remaining rows, truncating so reshape does not fail
# using numpy reshape() to transform sets of rows into columns
df = pd.DataFrame(dfraw.loc[:(len(dfraw)-(len(dfraw)%len(cols)))-1].values.reshape(int(len(dfraw)/len(cols)), len(cols)),
            columns=cols[0].values)

# filter to valid "Egg #" and Day0 is defined
df = df[(df["Egg #"].str.match("^[0-9]+[ ]+[A-Z][0-9]")) & (df["Day0"].str.strip()!="")]

print(df.to_string(index=False))

output

                      Egg #  Day0 Maturity    IVF/ICSI                        Day1                   Day2 Grade                     Day 3                 Grade- Frag%                   Day4                   Day5                   Day6                   Day7                  Comment         Fate Freezing ID    (End of Column names)-------------------------
 1   A1 (egg #)------------  MII                  ICSI  2PN/2PB  (Day 1)----------  2BC   (Day 2 grade)--------  6BC   (Day 3)-----------  15   (Grade- Frag%)--------  -    (Day 4) --------  -    (Day 5) --------  -    (Day 6) --------  -    (Day 7) --------       (Comments) -------  F    (Fate)                       A102519-01EV (Freezing ID)-------------
                     2   A2   MII                 ICSI                     2PN/2PB                           5B                       12B                           10                      -                      -                      -                      -                                     F                                                  B102519-01EV
                     3   A3   MII                 ICSI                     2PN/2PB                           5B                        8B                           10                      -                      -                      -                      -                                    ET                                                              
                     4   A4   MII                 ICSI                     2PN/2PB                           5B                       10A                            0                      -                      -                      -                      -                                    ET                                                              
                     5   B1   MII                 ICSI                     2PN/2PB                          5BC                       8BC                           10                      -                      -                      -                      -                                     F                                                  A102519-01EV
                     6   B2    MI                                         MII/ICSI                      2PN/2PB                      SAME                            -                   SAME                      -                      -                      -                                     D