keep the title of pairs

user_15605043 0
Viewed 36

The text file I have 2 sets of data and its title (Cx, Dx):

C1,D1,,,,,,,,
Layer_00 , 3.46ms,Layer_01 , 3.40ms,Layer_02 , 3.56ms,Layer_03 , 3.49ms,Layer_04 , 3.44ms
Layer_05 , 3.45ms,Layer_06 , 3.44ms,Layer_07 , 3.46ms,Layer_08 , 3.45ms,Layer_09 , 3.48ms
C2,D2,,,,,,,,
Layer_00 , 3.42ms,Layer_01 , 3.39ms,Layer_02 , 3.51ms,Layer_03 , 3.41ms,Layer_04 , 3.43ms
Layer_05 , 3.40ms,Layer_06 , 3.43ms,Layer_07 , 3.45ms,Layer_08 , 3.43ms,Layer_09 , 3.42ms

I tried the below:

with open('text.txt', 'r') as file:
    pairs = re.findall('(Layer_\d+)\s,\s(\d+\.\d+)ms', file.read())
pairs = [(k, float(v)) for k,v in pairs]
df = pd.DataFrame(pairs)

and got all pairs from 2 sets aligned as I wanted:

enter image description here

however I want to keep title C1,D1 and C2,D2, on top of their respective sets, the result should be like this:

enter image description here

Does anyone has idea how to solve it?

pandas csv python
1 Answers

You could use groupby to read the lines as blocks. Then split each line in the block on the comma and read them as pairs of values. Finally build a dataframe for each block, remove the ms and convert to floats:

from itertools import groupby
import pandas as pd

def pairs(iterable):
    args = [iter(iterable)] * 2
    return zip(*args)
    
dfs = []

with open('text.txt') as f_input:
    for c, block in groupby(f_input, lambda x: x.startswith('C')):
        if c:
             c_row = next(block).split(',')[:2]
        else:
            data = []
            
            for line in block:
                data.extend(pairs(v.strip() for v in line.split(',')))
            
            df = pd.DataFrame(data, columns=c_row)
            df[c_row[1]] = df[c_row[1]].str.strip('ms').astype(float)
            dfs.append(df)
        
for df in dfs:
    print(df, '\n')

This would give you a list of dataframes as follows:

         C1    D1
0  Layer_00  3.46
1  Layer_01  3.40
2  Layer_02  3.56
3  Layer_03  3.49
4  Layer_04  3.44
5  Layer_05  3.45
6  Layer_06  3.44
7  Layer_07  3.46
8  Layer_08  3.45
9  Layer_09  3.48 

         C2    D2
0  Layer_00  3.42
1  Layer_01  3.39
2  Layer_02  3.51
3  Layer_03  3.41
4  Layer_04  3.43
5  Layer_05  3.40
6  Layer_06  3.43
7  Layer_07  3.45
8  Layer_08  3.43
9  Layer_09  3.42
user_49857330
Related