I have some text data which uses the ASCII data characters 0x1e "group separator" and 0x1d "record separator" to store hierarchical data, as follows:

A 0x1e   B 0x1d C 0x1d D 0x1d E   0x1e F 0x1d G 0x1d H 0x1d I  [newline]
J 0x1e   K 0x1d L 0x1d M 0x1d N   0x1e P 0x1d Q 0x1d R 0x1d S  [newline]

The whitespace is of course fictional, and the actual file contents would be more like

$ hexdump -C file.ext | head -1
00000000  41 1e 42 1d 43 1d 44 1d  45 1e 46 1e 47 1d 48 1d    |A.B.C.D.E.F.G.H.|

I would like pandas to load this data into a MultiIndex'd dataframe, like

       group0    group1                         group2
 idx   thing0    thing1 thing2 thing3 thing4    thing4 thing5 thing6 thing7
 0     A         B      C      D      E         F      G      H      I
 1     J         K      L      M      N         P      Q      R      S

Header names are arbitrary, and could come from names=. The number of fields in each group is variable (but fixed in each table). The data are variable-length strings. Different tables have different-length groups, and I would prefer not to count them by squinting at each data file.

Is there an elegant way to do this import?


1 Answer 1

df = pd.read_csv('data.txt', names=names)

#work-around to identify multi indices
indices = []
for iGroup, tmpGroup in enumerate(df.iloc[0,0].split('1e')):
    for iThing in range(len(tmpGroup.split('1d'))):
        indices.append(tuple(['group' + str(iGroup), 'thing' + str(iThing)]))

#decode and set the data
df = df.iloc[:,0].str.replace('1d','1e').str.split('1e', expand=True)
df = df.applymap(lambda x: bytes.fromhex(x).decode('ASCII'))
df.columns = pd.MultiIndex.from_tuples(indices)

df results in

  group0 group1                      group2 group3       
  thing0 thing0 thing1 thing2 thing3 thing0 thing0 thing1
0      A      B      C      D      E      F      G      H
1      I      P      Q      R      S      T      U      V

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.