Code improvement: groupby and operation on conditionals

[perfringo]

I think that main problem is the way how data is structured. I assume that this is in file.

I would:

- read data from file into four - columns structure (compound, location, value, flag)
- use pd.DataFrame to get data needed

import pandas as pd

with open('max_value_compound.txt', 'r') as f:
    data = []
    headers = next(f)
    for row in f:
        location, *rest = row.split()
        for i, compound in zip(range(0, len(rest), 2), range(1, 4)):
            data.append([compound, location, *rest[i:i+2]])

df = pd.DataFrame(data, columns=['compound', 'location', 'value', 'flag'])

df[df['flag'] == 'U'].pivot_table(index=['location', 'compound'], values=['value'], aggfunc=max)   # both giving same output/result
df[df['flag'] == 'U'].groupby(['location', 'compound']).agg({'value': 'max'})

Output:
                 value
location compound      
A        2          0.4
         3          0.2
B        1            5
         2          3.2
         3            8
C        2         0.16
D        2         0.30
E        1            2

If location is not needed then one can skip 'location':

df[df['flag'] == 'U'].pivot_table(index=['compound'], values=['value'], aggfunc=max) 
df[df['flag'] == 'U'].groupby(['compound']).agg({'value': 'max'})  

which will give:

Output:
         value
compound      
1            5
2          3.2
3            8