json_tuple_to_cols

json_tuple_to_cols

Convert a column with a JSON tuple in it to two column (typically a name, value pair)

Definition

json_tuple_to_cols(df, column_name, col_config={'cols': {'key_prop': 'Name', 'value_prop': 'Value'},
                                                    'look_up': {'key_prop': 'name', 'value_prop': 'value'}}):
"""
    :param df: the data frame
    :param column_name: column with the json tuple
    :param col_config: conversion config
    :return: a modified dataframe
"""

Example

Input

Objective

Look at some of the entries from the Line column we exploded. You'll notice they are name value pairs in JSON.

To explodes these into new columns, we the json_tuple_to_cols function. We'll need to specify lookup_keys - in our case, the key_prop=name and value_prop=value

# Specify lookup keys
qb_lookup_keys = {'key_prop': 'name', 'value_prop': 'value'}

# Explode these into new columns
invoices = (invoices.pipe(gs.json_tuple_to_cols, 'Line.SalesItemLineDetail.ItemRef',
                  col_config={'cols': {'key_prop': 'Item', 'value_prop': 'Item Id'},
                              'look_up': qb_lookup_keys})
                  .pipe(gs.json_tuple_to_cols, 'Line.SalesItemLineDetail.ItemAccountRef',
                  col_config={'cols': {'key_prop': 'Item Ref', 'value_prop': 'Item Ref Id'},
                              'look_up': qb_lookup_keys}))
invoices[['Id', 'Item', 'Item Id', 'Item Ref', 'Item Ref Id']].head()

Output