Data conversion utility with group-wise serialization

Question

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
B|Bread|Lettuce|Tomato
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')}
header_names = ('HeaderKeyG', 'HeaderKey1', 'HeaderKey2', 'HeaderKey3')
footer_names = ('FooterKeyG', 'FootKey1', 'FootKey2', 'FootKey3')

n_groups = len(group_names)
n_entries = len(header_names) - 1


def make_multi_index(n_payloads: int) -> pd.MultiIndex:
    group_indices = np.tile(
        np.array(
            [
                (k, e)
                for k, entries in group_names.items()
                for e in entries
            ],
            dtype=object
        ),
        (n_payloads, 1),
    )
    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=(
            'payload',
            'group',
            'entry',
        ),
    )

def parse(fn: str) -> (pd.Series, pd.Series, pd.DataFrame):
    df = pd.read_csv(fn, sep='|', header=None)

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

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

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

    entries = pd.DataFrame(
        body.values.flatten(),
        index=make_multi_index(n_payloads),
    )

    return header, footer, entries


def payloads(header: Iterable[str], footer: Iterable[str], entries: pd.DataFrame) -> Iterable[dict]:
    base = {
        'header': dict(zip(header_names, header)),
        'footer': dict(zip(footer_names, footer)),
    }

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


def main():
    header, footer, entries = parse('file1.usr')
    print('Multi-index entry representation:')
    print(entries)
    print()

    print('Payloads:')
    for pay in payloads(header, footer, entries):
        pprint(pay)


main()

Output

Multi-index entry representation:
                                0
payload group entry              
0       A     A1ValueKey   Entry1
              A2ValueKey   Entry2
              A3ValueKey   Entry3
        B     B1ValueKey   Entry1
              B2ValueKey   Entry2
              B3ValueKey   Entry3
1       A     A1ValueKey     Eggs
              A2ValueKey  Sausage
              A3ValueKey    Bacon
        B     B1ValueKey    Bread
              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

Payloads:
{'footer': {'FootKey1': 'Footer1',
            'FootKey2': 'Footer2',
            'FootKey3': 'Footer3',
            'FooterKeyG': 'FooterG'},
 'groups': {'A': {'A1ValueKey': 'Entry1',
                  'A2ValueKey': 'Entry2',
                  'A3ValueKey': 'Entry3'},
            'B': {'B1ValueKey': 'Entry1',
                  'B2ValueKey': 'Entry2',
                  'B3ValueKey': 'Entry3'}},
 'header': {'HeaderKey1': 'Header1',
            'HeaderKey2': 'Header2',
            'HeaderKey3': 'Header3',
            'HeaderKeyG': 'HeaderG'}}
{'footer': {'FootKey1': 'Footer1',
            'FootKey2': 'Footer2',
            'FootKey3': 'Footer3',
            'FooterKeyG': 'FooterG'},
 'groups': {'A': {'A1ValueKey': 'Eggs',
                  'A2ValueKey': 'Sausage',
                  'A3ValueKey': 'Bacon'},
            'B': {'B1ValueKey': 'Bread',
                  'B2ValueKey': 'Lettuce',
                  'B3ValueKey': 'Tomato'}},
 'header': {'HeaderKey1': 'Header1',
            'HeaderKey2': 'Header2',
            'HeaderKey3': 'Header3',
            'HeaderKeyG': 'HeaderG'}}
{'footer': {'FootKey1': 'Footer1',
            'FootKey2': 'Footer2',
            'FootKey3': 'Footer3',
            'FooterKeyG': 'FooterG'},
 'groups': {'A': {'A1ValueKey': 'aa', 'A2ValueKey': 'bb', 'A3ValueKey': 'cc'},
            'B': {'B1ValueKey': 'dd', 'B2ValueKey': 'ee', 'B3ValueKey': 'ff'}},
 'header': {'HeaderKey1': 'Header1',
            'HeaderKey2': 'Header2',
            'HeaderKey3': 'Header3',
            'HeaderKeyG': 'HeaderG'}}
{'footer': {'FootKey1': 'Footer1',
            'FootKey2': 'Footer2',
            'FootKey3': 'Footer3',
            'FooterKeyG': 'FooterG'},
 'groups': {'A': {'A1ValueKey': '4aa',
                  'A2ValueKey': '4bb',
                  'A3ValueKey': '4cc'},
            'B': {'B1ValueKey': '4dd',
                  'B2ValueKey': '4ee',
                  'B3ValueKey': '4ff'}},
 'header': {'HeaderKey1': 'Header1',
            'HeaderKey2': 'Header2',
            'HeaderKey3': 'Header3',
            'HeaderKeyG': 'HeaderG'}}

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, 2020 at 2:15
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, 2020 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, 2020 at 10:06

Graipher · Accepted Answer · 2020-05-01 18:46:21Z

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()

def read_last_line(f):
    """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!")
    f.seek(-2, 2)              # Jump to the second last byte.
    while f.read(1) != b"\n":  # Until EOL is found...
        f.seek(-2, 1)          # ...jump back, over the read byte plus one more.
    return f.readline()

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.
    Each payload has a header and footer dictionary using the respective
    names as keys and a groups dictionary parsed from the file.
    Assumes that the file is ordered correctly, i.e. lines of the same
    payload follow each other and group names are unique.
    Group names must also not appear as footer names.
    """
    footer = dict(zip(footer_names, parse_row(read_last_line(f), sep)))
    f.seek(0)
    header = dict(zip(header_names, parse_row(next(f), sep)))

    def new_payload():
        return {"header": header, "footer": footer, "groups": {}}

    payload = new_payload()
    for row in f:
        group, *data = parse_row(row, sep)
        if group in payload["groups"]:
            # this group already exists, must be a new payload
            yield payload
            payload = new_payload()
        try:
            assert len(group_names[group]) == len(data)
            payload["groups"][group] = dict(zip(group_names[group], data))
        except KeyError:
            # probably reached the footer, but better make sure:
            try:
                next(f)
            except StopIteration:
                break
            else:
                raise
    yield payload

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

    with open("file1.usr", "rb") as f:
        for payload in parse(f, header_names, footer_names, group_names):
            pprint(payload)

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'}},
 'header': {'HeaderKey1': 'Header1',
            'HeaderKey2': 'Header2',
            'HeaderKey3': 'Header3',
            'HeaderKeyG': 'HeaderG'}}
{'footer': {'FootKey1': 'Footer1',
            'FootKey2': 'Footer2',
            'FootKey3': 'Footer3',
            'FooterKeyG': 'FooterG'},
 'groups': {'A': {'A1ValueKey': 'Eggs',
                  'A2ValueKey': 'Sausage',
                  'A3ValueKey': 'Bacon'},
            'B': {'B1ValueKey': 'Bread',
                  'B2ValueKey': 'Lettuce',
                  'B3ValueKey': 'Tomato'}},
 'header': {'HeaderKey1': 'Header1',
            'HeaderKey2': 'Header2',
            'HeaderKey3': 'Header3',
            'HeaderKeyG': 'HeaderG'}}
{'footer': {'FootKey1': 'Footer1',
            'FootKey2': 'Footer2',
            'FootKey3': 'Footer3',
            'FooterKeyG': 'FooterG'},
 'groups': {'A': {'A1ValueKey': 'aa', 'A2ValueKey': 'bb', 'A3ValueKey': 'cc'},
            'B': {'B1ValueKey': 'dd', 'B2ValueKey': 'ee', 'B3ValueKey': 'ff'}},
 'header': {'HeaderKey1': 'Header1',
            'HeaderKey2': 'Header2',
            'HeaderKey3': 'Header3',
            'HeaderKeyG': 'HeaderG'}}
{'footer': {'FootKey1': 'Footer1',
            'FootKey2': 'Footer2',
            'FootKey3': 'Footer3',
            'FooterKeyG': 'FooterG'},
 'groups': {'A': {'A1ValueKey': '4aa',
                  'A2ValueKey': '4bb',
                  'A3ValueKey': '4cc'},
            'B': {'B1ValueKey': '4dd',
                  'B2ValueKey': '4ee',
                  'B3ValueKey': '4ff'}},
 'header': {'HeaderKey1': 'Header1',
            'HeaderKey2': 'Header2',
            'HeaderKey3': 'Header3',
            'HeaderKeyG': 'HeaderG'}}

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, 2020 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, 2020 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, 2020 at 17:29

Stack Exchange Network

Data conversion utility with group-wise serialization

Introduction

The code

Output

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
python
python-3.x
pandas
or ask your own question.

Linked

Hot Network Questions

Data conversion utility with group-wise serialization

Introduction

The code

Output

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
python
python-3.x
pandas
or ask your own question.