Visidata: [wishlist] Add ability to expand rows with nested data like ExpandedColumn ("unfurl")

[frosencrantz]

Add an expand-rows command to expand a column row-wise, (selected|all) rows.

I have data that has a column with nested data, usually a list of dicts (lod). The dicts typically have the same keys.
I would like to expand the rows so there is one dict per row, keeping the data in the other rows the same

This
| c1 | c2 | lod | c4 |
| --- | --- | --- | --- |
| 1 | 1 | [3] | 1 |
| 2 | 2 | [2] | 2 |

Would become

| c1 | c2 | lod | c4 |
| --- | --- | --- | --- |
| 1 | 1 | {4} | 1 |
| 1 | 1 | {4} | 1 |
| 1 | 1 | {4} | 1 |
| 2 | 2 | {4} | 2 |
| 2 | 2 | {4} | 2 |

Once rows are expanded it would be useful to run expand-col on the resulting table.

This feature would be useful with json or yaml data when you transpose it, and all the values are in a single column.
With this feature you could expand single or multiple rows containing a list value.

[saulpw]

I think this is similar to @jsvine's unfurl idea! If so, maybe it's time to pull it together formally.

[saulpw]

Kondo'd for now.

[frosencrantz]

You got my hopes up a little on this, then closed. 😞 ... Oh well... 🙂

@saulpw, I didn't find any issues listing 'unfurl'. I was wondering if there was another issue you could reference?

I can workaround this, but something that could natively handle this in Visidata would be useful to me.

[jsvine]

Hi @frosencrantz, the code below is what I've been using (via longname command unfurl-column). It's only compatible with v2.x, hasn't been updated in many months, and might have some bugs. But it's been working for me, and might be useful for you. Provided without warranty, without any promise of maintenance, et cetera:

Usage

This plugin makes it possible to "unfurl" columns containing iterable values, such as lists, dicts, and strings. When you unfurl a column, VisiData creates a new sheet. In that new sheet, the unfurled column's values are iterated over, with each key/value pair getting its own row; the rest of the sheet's columns copied for each of those rows.

Note: When unfurling a column, non-iterable objects (such as integers) are treated as single-item lists, so that they too can be unfurled.

Commands

• unfurl-column unfurls the current column.
• addcol-rowindex adds an ix column to the beginning of the sheet, with the values 0, 1, 2, 3, etc., in the order the rows current appear. This can be helpful for tracking where unfurled values came from.

Code

"""
Filename: vdunfurl.py
Version: -1
Last updated: 2019-12-26
Author: Jeremy Singer-Vine
"""

__version__ = "-1"

from collections.abc import Iterable, Mapping

from visidata import (
    vd,
    Progress,
    Sheet,
    Column,
    ColumnItem,
    SettableColumn,
    SubrowColumn,
    asyncthread,
    clean_to_id,
)

class UnfurledColSheet(Sheet):
    @asyncthread
    def reload(self):
        # Copy over base sheet, using SubrowColumns
        self.columns = []
        for i, col in enumerate(self.source.columns):
            if col is not self.source_col:
                newcol = SubrowColumn(col.name, col, 0)
                self.addColumn(newcol)

        # Set key columns
        self.setKeys([ self.columns[self.source.columns.index(k)]
            for k in self.source.keyCols ])

        # Create two new columns, one for the iterable's keys
        # and one for its corresponding values
        c_key = ColumnItem(self.source_col.name + "_key", 1)
        c_value = ColumnItem(self.source_col.name + "_value", 2)

        self.addColumn(
            c_key,
            index = self.source_col_i + 1,
        )

        self.addColumn(
            c_value,
            index = self.source_col_i + 2
        )

        self.rows = []
        for row in Progress(self.source.rows):
            val = self.source_col.getValue(row)

            if not isinstance(val, Iterable):
                val = [ val ]

            if isinstance(val, Mapping):
                gen = val.items()
            else:
                gen = enumerate(val)

            for key, sub_value in gen:
                new_row = [ row, key, sub_value ]
                self.addRow(new_row)

@Sheet.api
def unfurl_column(sheet, col, col_index):
    clean_id = clean_to_id(col.name)
    vs = UnfurledColSheet(f"{sheet.name}_{clean_id}_unfurled", source=sheet, source_col=col, source_col_i=col_index)
    vd.push(vs)
    vs.cursorVisibleColIndex = vs.visibleCols.index(vs.columns[col_index])


Sheet.addCommand(None, "unfurl-column", "unfurl_column(cursorCol, cursorColIndex)")

def addcol_rowindex(sheet):

    ix_col = SettableColumn("ix", width = 4, type = int)

    @asyncthread
    def calcIndex_async(rows, ix_col):
        for i, row in enumerate(Progress(rows, 'calculating')):
            ix_col.setValue(row, i)

    calcIndex_async(sheet.rows, ix_col)
    sheet.addColumn(ix_col, index = 0)

Sheet.addcol_rowindex = addcol_rowindex
Sheet.addCommand(None, "addcol-rowindex", "vd.sheet.addcol_rowindex()")

[geekscrapy]

So, I actually had a go at this a while a go as well! But instead of doing it based on a python object, I was splitting a column string based on a delimiter (e.g. comma) and it would insert new rows with an individual value from the split string into a new column.

This method came in handy when analysing /etc/groups and seeing what groups a user belonged to. I'd open the file as text, split on : and then run my code on the last column making a row for each user for each of their groups.

It's also very useful when I've come across CSV values in JSON fields (yes strange, but real....!)

[saulpw]

@jsvine Thanks for posting this! Note also that we recently added an addcol-range command (bound to i for "increment") that should work the same as your addcol-rowindex command.

[jsvine]

Ah, great! Thanks for flagging.

[saulpw]

Added unfurl-col in above commit. Unbound for now but maybe it feels like zM?

[geekscrapy]

I bet this is going to be immediately obvious, but why unfurl...?! What does it stand for?

[jsvine]

In my mind, when I came up with the name, it was sort of a visual metaphor, like the unfurling of a fern:

... or a list or other iterable. I hadn't seen it at the time, but I quite like this definition:

To unroll or release something that had been rolled up, typically a sail or a flag.

[frosencrantz]

Wow! I'm absolutely huffed by this. Thanks @jsvine. But now you have me wondering what other unreleased gems you have.

Thanks @saulpw for quickly adding this code.

I did try the Visidata version and it works pretty great.

There were two things that would help my for my data.

• For the data I want to unfurl, the column contains lists of dicts, but
  when there is only one dict there is no list, so the cell has just the dict.

• Other data I've used is JSON data that has a mix of types in a column (lists, dicts, strings, etc).

So it would be useful if unfurl could be limited:

• to only selected rows. unselected rows would be treated like a number is treated now.
• add a way to limit unfurl to a specific type, such as only lists or only dicts.

@geekscrapy: somehow I also find one data format contained in another. har files are great for this.

@anjakefala: I love the new "wish granted" tag.