# Data conversion utility with group-wise serialization

## Introduction

I'm new to Pandas. I'm trying to write a vectorized converter for the situation described in What is an efficient ways to parse a bar separated usr file in Python . All code presented here is my own and the data are synthetic.

For these data:

HeaderG|Header1|Header2|Header3
A|Entry1|Entry2|Entry3
B|Entry1|Entry2|Entry3
A|Eggs|Sausage|Bacon
A|aa|bb|cc
B|dd|ee|ff
A|4aa|4bb|4cc
B|4dd|4ee|4ff
FooterG|Footer1|Footer2|Footer3


The converter is responsible for parsing out the header and footer, which have nearly nothing to do with the body of the data; and then parsing out one "payload" per set of groups (above, the groups being A and B). In the above sample there are two groups, three "entry columns", and four payloads.

The groups, headers and footers are parametric but well-known. The converter is responsible for producing maps of the header, footer and groups given some additional metadata. The algorithm roughly goes:

• Deserialize the pipe-separated file into one big dataframe
• Trim off the header and footer
• Validate, then trim off the first group column
• Make a Cartesian-product multi-index frame
• Construct and assign the multi-index
• Iterate over the multi-indexed data body to produce the payloads as plain dictionaries

I am aware of both the to_json and to_dict methods of DataFrame but I was unable to get them working as I wanted to, so I had to roll my own. This code does exactly what it should, but I'm sure there's a better way to use Pandas. I want to optimize for speed first, code simplicity second, and memory basically not at all, given that input files are all less than 10 kB each.

My specific concerns:

• make_multi_index is quite ugly and uses a non-vectorized generator conversion of a dictionary; and also has not made (cannot make?) use of MultiIndex.from_product
• It smells like it could make use of np.meshgrid but there was a catch in the nature of the third axis that prevented me from doing so
• There must be a simpler way to assign header and footer names and produce dictionaries
• Heavy groupby abuse and lack of vectorization in payloads

## The code

from typing import Iterable
from pprint import pprint
import pandas as pd
import numpy as np

group_names = {'A': ('A1ValueKey', 'A2ValueKey', 'A3ValueKey'),
'B': ('B1ValueKey', 'B2ValueKey', 'B3ValueKey')}
footer_names = ('FooterKeyG', 'FootKey1', 'FootKey2', 'FootKey3')

n_groups = len(group_names)

group_indices = np.tile(
np.array(
[
(k, e)
for k, entries in group_names.items()
for e in entries
],
dtype=object
),
)
indices = np.empty(
(group_indices.shape[0], 3),
dtype=object
)
indices[:, 0] = np.repeat(np.arange(n_payloads), n_groups * n_entries)
indices[:, 1:] = group_indices

return pd.MultiIndex.from_frame(
pd.DataFrame(indices),
names=(
'group',
'entry',
),
)

def parse(fn: str) -> (pd.Series, pd.Series, pd.DataFrame):

n_payloads, leftover = divmod(df.shape[0] - 2, n_groups)
assert leftover == 0
assert n_entries == df.shape[1] - 1

footer = df.iloc[-1, :]
body = df.iloc[1:-1, :]

assert (
body.iloc[:, 0] == np.tile(
np.array(tuple(group_names.keys())),
)
).all()
body.drop(0, axis=1, inplace=True)

entries = pd.DataFrame(
body.values.flatten(),
)

base = {
'footer': dict(zip(footer_names, footer)),
}

d = dict(base)
d['groups'] = {
groupname: {
g: din.values[0, 0]
for g, din in d.groupby(level=2)
}
}
yield d

def main():
print('Multi-index entry representation:')
print(entries)
print()

pprint(pay)

main()


## Output

Multi-index entry representation:
0
0       A     A1ValueKey   Entry1
A2ValueKey   Entry2
A3ValueKey   Entry3
B     B1ValueKey   Entry1
B2ValueKey   Entry2
B3ValueKey   Entry3
1       A     A1ValueKey     Eggs
A2ValueKey  Sausage
A3ValueKey    Bacon
B2ValueKey  Lettuce
B3ValueKey   Tomato
2       A     A1ValueKey       aa
A2ValueKey       bb
A3ValueKey       cc
B     B1ValueKey       dd
B2ValueKey       ee
B3ValueKey       ff
3       A     A1ValueKey      4aa
A2ValueKey      4bb
A3ValueKey      4cc
B     B1ValueKey      4dd
B2ValueKey      4ee
B3ValueKey      4ff

{'footer': {'FootKey1': 'Footer1',
'FootKey2': 'Footer2',
'FootKey3': 'Footer3',
'FooterKeyG': 'FooterG'},
'groups': {'A': {'A1ValueKey': 'Entry1',
'A2ValueKey': 'Entry2',
'A3ValueKey': 'Entry3'},
'B': {'B1ValueKey': 'Entry1',
'B2ValueKey': 'Entry2',
'B3ValueKey': 'Entry3'}},
{'footer': {'FootKey1': 'Footer1',
'FootKey2': 'Footer2',
'FootKey3': 'Footer3',
'FooterKeyG': 'FooterG'},
'groups': {'A': {'A1ValueKey': 'Eggs',
'A2ValueKey': 'Sausage',
'A3ValueKey': 'Bacon'},
'B2ValueKey': 'Lettuce',
'B3ValueKey': 'Tomato'}},
{'footer': {'FootKey1': 'Footer1',
'FootKey2': 'Footer2',
'FootKey3': 'Footer3',
'FooterKeyG': 'FooterG'},
'groups': {'A': {'A1ValueKey': 'aa', 'A2ValueKey': 'bb', 'A3ValueKey': 'cc'},
'B': {'B1ValueKey': 'dd', 'B2ValueKey': 'ee', 'B3ValueKey': 'ff'}},
{'footer': {'FootKey1': 'Footer1',
'FootKey2': 'Footer2',
'FootKey3': 'Footer3',
'FooterKeyG': 'FooterG'},
'groups': {'A': {'A1ValueKey': '4aa',
'A2ValueKey': '4bb',
'A3ValueKey': '4cc'},
'B': {'B1ValueKey': '4dd',
'B2ValueKey': '4ee',
'B3ValueKey': '4ff'}},

• I feel like this should be included as an example somewhere in the Guide to Asking Questions. Never heard of a usr file, but I could easily follow the question due to its structure. – teauxfu May 1 '20 at 2:15
• @teauxfu Thank you <3 – Reinderien May 1 '20 at 2:16
• Are the two classes A and B always alternating in the file, as in your example data? And are there always exactly two classes? Or in other words, how do you know which row if class A to combine with which row of class B in the final output? – Graipher May 1 '20 at 10:02
• Also, if the input files are only 10KB, parsing it using vanilla Python might be a lot easier. Just read the file line by line and store (or yield, just need to seek to the footer and back first) a payload whenever you see the groups repeat. – Graipher May 1 '20 at 10:06
• @Graphier yes, they always alternate. – Reinderien May 1 '20 at 13:29

I don't really see the necessity for pandas here. If your input files are only 10KB large, just parse them using vanilla Python:

from pprint import pprint

SENTINEL = object()

"""Read the last line of an open file.
Note: file must be opened in binary mode!
Leaves the file pointer at the end of the file."""
# https://stackoverflow.com/a/18603065/4042267
if "b" not in f.mode:
raise IOError("File must be opened in binary mode!")
while f.read(1) != b"\n":  # Until EOL is found...
f.seek(-2, 1)          # ...jump back, over the read byte plus one more.

def parse_row(row, sep):
"""Decode, strip and split a binary data row using sep."""
return row.decode("utf-8").strip().split(sep)

def parse(f, header_names, footer_names, group_names, sep="|"):
"""Parse an open file into payloads.
names as keys and a groups dictionary parsed from the file.
Assumes that the file is ordered correctly, i.e. lines of the same
Group names must also not appear as footer names.
"""
f.seek(0)

for row in f:
group, *data = parse_row(row, sep)
try:
assert len(group_names[group]) == len(data)
except KeyError:
# probably reached the footer, but better make sure:
try:
next(f)
except StopIteration:
break
else:
raise

if __name__ == "__main__":
group_names = {'A': ('A1ValueKey', 'A2ValueKey', 'A3ValueKey'),
'B': ('B1ValueKey', 'B2ValueKey', 'B3ValueKey')}
footer_names = ('FooterKeyG', 'FootKey1', 'FootKey2', 'FootKey3')

with open("file1.usr", "rb") as f:


This is even a generator, so it can deal with arbitrarily large files (although I would expect pd.read_csv to be more optimized and therefore be faster for large files, as long as the resulting dataframe still fits into memory).

You don't say if you need both the multi-level representation and the payloads, I assumed you only need the latter, for which I think this gives the same output as your code (up to ordering of the dictionaries, since I used Python 3.6):

{'footer': {'FootKey1': 'Footer1',
'FootKey2': 'Footer2',
'FootKey3': 'Footer3',
'FooterKeyG': 'FooterG'},
'groups': {'A': {'A1ValueKey': 'Entry1',
'A2ValueKey': 'Entry2',
'A3ValueKey': 'Entry3'},
'B': {'B1ValueKey': 'Entry1',
'B2ValueKey': 'Entry2',
'B3ValueKey': 'Entry3'}},
{'footer': {'FootKey1': 'Footer1',
'FootKey2': 'Footer2',
'FootKey3': 'Footer3',
'FooterKeyG': 'FooterG'},
'groups': {'A': {'A1ValueKey': 'Eggs',
'A2ValueKey': 'Sausage',
'A3ValueKey': 'Bacon'},
'B2ValueKey': 'Lettuce',
'B3ValueKey': 'Tomato'}},
{'footer': {'FootKey1': 'Footer1',
'FootKey2': 'Footer2',
'FootKey3': 'Footer3',
'FooterKeyG': 'FooterG'},
'groups': {'A': {'A1ValueKey': 'aa', 'A2ValueKey': 'bb', 'A3ValueKey': 'cc'},
'B': {'B1ValueKey': 'dd', 'B2ValueKey': 'ee', 'B3ValueKey': 'ff'}},
{'footer': {'FootKey1': 'Footer1',
'FootKey2': 'Footer2',
'FootKey3': 'Footer3',
'FooterKeyG': 'FooterG'},
'groups': {'A': {'A1ValueKey': '4aa',
'A2ValueKey': '4bb',
'A3ValueKey': '4cc'},
'B': {'B1ValueKey': '4dd',
'B2ValueKey': '4ee',
'B3ValueKey': '4ff'}},

Note that I added some docstrings and an if __name__ == "__main__": guard, although I'm pretty sure you already know about those.
• Interesting and smart solution. I'd prefer a validation check in the except KeyError, something simple like if next(f, SENTINEL) is not SENTINEL: raise ValueError() from None. Additionally break would emphasise this relationship more than pass. – Peilonrayz May 1 '20 at 14:46
• @Peilonrayz Agreed. Added something along those lines. I'm never sure whether to use try...except StopIteration or next(..., Sentinel), though... – Graipher May 1 '20 at 17:12
• Oh do I agree with that! I think performance-wise try would be better here. Readability wise I chose next(..., SENTINEL) as it is easier to read in comments. In your above code I think a try could be easier to read for most as then there's no weird SENTINEL. But all of this comes down to my feelings, and I'm sure a substantial amount of people feel the inverse to me. – Peilonrayz May 1 '20 at 17:29