How to create a multiple dataframes from a file?-CodePudding

I have multiple txt files which look like this:

[Level1]
Location = "London"
Type= "GTHY66"
Date = "16-11-2021"
Energy level = "Critical zero"
[Level2]
0.000   26.788
0.027   26.807
0.053   26.860

So from every file I read/process I want to create two data frames (Which eventually I will push to a database).

The dataframe in level1 needs to be df_level1:

Location   Type      Date         Energy
London     GTHY66    16-11-2021   Critical zero

The dataframe under level1 needs to be df_level2:

Speed   Energylevel
0.000   26.788
0.027   26.807
0.053   26.860

This is what I tried, but I got stuck:

energy_root= r'c:\data\Desktop\Studio\Energyfiles'

#create list of file paths
def read_txt_file(path):
    list_file_path = []
    for root, dirs, files in os.walk(path):
        for file in files:
            if file.endswith('.txt'):
                file_name = os.path.basename(file)
                file_path = os.path.join(root, file_name)
                list_file_path.append(file_path)
    
    return list_file_path     

def create_df():
    for file in read_txt_file(energy_root):
        file_name = os.path.basename(file)
        file_path = os.path.join(energy_root, file_name)
        datetime = re.findall(r'_(\d{8}_\d{6})\.', file_name)[0]

        with open(file_path, 'r ') as output:
            reader = output.readlines()
            for row in reader:
                d = row.split('=')
                if len(d) > 1:
                   df_level1 = pd.DataFrame([d[1]], columns=[d[0]])

                   print(df_level1 )
              "then create df_level2 ....."

create_df()

CodePudding user response：

Try this:

def read_txt_file(path):
    n = 0
    pattern = re.compile(r'(. )\s*=\s*\"(. )\"')
    level1 = {}
    with open(path) as fp:
        for line in fp:
            line = line.strip()
            n  = 1
            if line == '[Level2]':
                break
            
            m = pattern.match(line)
            if m is not None:
                key = m.group(1)
                value = m.group(2)
                level1[key] = value

    level1 = pd.DataFrame(level1, index=[0])
    level2 = pd.read_csv(path, sep='\s ', skiprows=n, header=None, names=['Speed', 'EnergyLevel'])
    return level1, level2

Usage:

level1, level2 = read_txt_file('data.txt')

CodePudding user response：

You can use pd.read_csv with the correct separators, but you have to do 2 things:

Before: Split the parts of the file for Level1 and Level2
After: Transpose and set the columns of Level1

Here's the code, straight inside your with open [...] line

reader = output.read() # simply the entire file text, not split into lines
parts = reader.split('[Level2]\n')
lvl1_lines = parts[0].split('[Level1]\n')[1].replace('"','')
lvl2_lines = "Speed   Energylevel\n"   parts[1]

from io import StringIO # to read strings as files for read_csv
df_level1 = pd.read_csv(StringIO(lvl1_lines), sep='=').transpose()
df_level1.columns = df_level1.iloc[0] # set the correct column names
df_level1 = df_level1[1:] # remove the column row
df_level2 = pd.read_csv(StringIO(lvl2_lines), sep='\t')