Read in txt file into multiple dataframes split by empty gaps between the data

user_13830167 0
Viewed 374

I am trying to clean some data files. I have this one file with large gaps in between data sets. I would like to read in each dataset into a dataframe. Essentially, I want to read the txt file into different dataframes.

An example file:

Random stuff here

Object 1    data    data    data
Object 2    data    data    data
Object 3    data    data    data




Object 1    dataA   dataB   dataC
Object 2    dataA   dataB   dataC

What I would like to have in the end: df1

object      A       B       C
Object 1    data    data    data
Object 2    data    data    data
Object 3    data    data    data

df2:

Object 1    dataA   dataB   dataC
Object 2    dataA   dataB   dataC

I have tried

names = ['object', 'A', 'B', 'C']
df=pd.read_table('test_file.txt', skiprows=range(0, 2), names=names, index_col='object')

with output like:

             A       B      C
object          
Object 1    data    data    data
Object 2    data    data    data
Object 3    data    data    data
Object 1    dataA   dataB   dataC
Object 2    dataA   dataB   dataC

I have tried to explore other options, but I cannot think of how to apply a loop to create a new dataframe when the read encounters a multiline gap.

pandas text-files dataframe import python
1 Answers

A natural solution would be to first read the file line by line, adding each line to a string and starting a new string when you find a blank line.

Then you can use io.StringIO, which prompts python to treat a string like a file, to read each string into a dataframe.

with open("FILE.txt") as F:
    Lines = F.readlines()[2:]

columns='Object    A    B    C\n'
dfs = [columns]
for line in Lines:
    if line.strip(): dfs[-1] += line #~ Add non-empty lines to the latest string
    elif dfs[-1]!=columns: dfs.append(columns) #~ If the latest string has data and you've hit a blank line, start a new string
    else: continue #~ If you've just added a new blank string and you're still on blank lines, carry on

dfs = [pd.read_table(StringIO(df)) for df in dfs]

#~ dfs[0] --
#~                Object    A    B    C
#~  0  Object 1    data    data    data
#~  1  Object 2    data    data    data
#~  2  Object 3    data    data    data

#~ dfs[1] --
#~                 Object    A    B    C
#~  0  Object 1    dataA   dataB   dataC
#~  1  Object 2    dataA   dataB   dataC

EDIT: Added the hardcoded "columns" string in this case, since the datasets don't come with headers in your example. If the dataset does come with headers, the code is similar, or you can just use dfs = [pd.read_table(StringIO(df[len(columns):])) for df in dfs] there at the end.

user_138106260
Related