Is it possible to "flatten" structured data (like JSON?)

[marcopaganini]

I spend a lot of time analyzing structured data exported as JSON files. One example would be something like this:

{
  "country": [
    {
      "name": "country X",
      "region": [
        {
          "name": "north",
          "geo": [
            {
              "state": "xx",
              "name": "Northern State",
              "population": "100000"
            },
            {
              "state": "yy",
              "name": "Boreal State",
              "population": "2000"
            }
          ]
        },
        {
          "name": "South",
          "geo": [
            {
              "state": "aa",
              "name": "Southern State",
              "population": "200000"
            },
            {
              "state": "bb",
              "name": "Down under State",
              "population": "3000"
            }
          ]
        }
      ]
    }
  ]
}

Naturally, we can have multiple countries, each country can have multiple states, and each state can have multiple geographical information data attached to it.

When I try to open this file in visidata, I just get a "country" header followed by [1] (indicating one element in the array). I can drill down on these by hitting [Enter] but it's rather painful. z[Enter] produces slightly better results, but only allows me to see one element of the array at a time. I tried multiple combinations of Unfurl and Transpose, but nothing generates something that is easy to work with.

I'm looking for a way to somehow "flatten" these results. Essentially, produce something similar to a csv, formatted as something like:

country.name, country.region, country.region.name, country.region.geo, country.region.geo.state, country.region.geo.name, country.region.geo.population
(...)

For each of the records.

Is that possible in visidata? If not what's the recommended way to deal with these situations?

Thanks in advance.

[anjakefala]

Hi @marcopaganini!

There are two popular options.

One is to use ( (expand-col) on the nested column to flatten it at a single level. You can use z( and then enter 0 to flatten that column fully. gz( is similar, but will open all columns. Columns can be closed (nested) again with the reverse ) (contract-col).

The other option is to use z Shift+M (unfurl-col). It will row-wise expand current column of lists (e.g. [2]) or dicts (e.g. {3}) within that column. So a row with Column Z that contains ['foo', 'bar'] will become two rows, identical except that one will have the first value 'foo' for Column Z, and the other row will have the second value bar. I use this one less often, but others have found it useful.

I hope that helps.

[marcopaganini]

Hi @anjakefala

I tried something similar (didn't know that 0 as levels meant everything). This almost creates the ideal situation, but it repeats each field in the array as headers, instead of copying those over as multiple records.

Basically, the headers become x[0], x[1], etc...
I'd expect the header to be x, and the rows, to be 0, 1, etc with the contents repeated (much like a join in SQL).

Is there a way around it?

[anjakefala]

Yup!

vd.option('fmt_expand_dict', '%s.%s', 'format str to use for names of columns expanded from dict (colname, key)') #1457                                      
vd.option('fmt_expand_list', '%s[%s]', 'format str to use for names of columns expanded from list (colname, index)')  

I think putting options.fmt_expand_list = '%s' in your ~/.visidatarc should work.

[anjakefala]

But, I will caution you, having columns that have identical names in VisiData breaks down with some other commands. E.g. if you use =, and then reference x, it will only reference the leftmost column with that name.

[marcopaganini]

Ah, interesting. But I think this will just change the names of the columns, no? I was thinking of something along the lines of:

country.name, country.region.name, country.region.geo.state, country.region.geo.name, country.region.geo.population
Country X, north, xx, Northern State, 100000
Country X, north, yy, Boreal State, 2000
Country X, South, aa, Southern State, 200000
Country X, South, bb, Down under State, 3000

Basically, a flatten operation. Note how the only headers around are those with real values (the "leaves", so to say). All other values are repeated.

If that's not supported, I wonder how hard it would be to write a plugin for that.

[aborruso]

Ciao @marcopaganini my reply is here off-topic, but I'm adding because it could be useful to you.

I know two specialized flatten tools: flatten-tool, and flatterer.

The first is more powerful.

An example about the second. I use this input.json file.

If you run

flatterer --force ./input.json ./output

you get these CSV files and you can JOIN these in the way you desire

country.csv

_link	_link_input	name
0.country.0	0	country X
0.country.1	0	country Y

country_region.csv

_link	_link_country	_link_input	name
0.country.0.region.0	0.country.0	0	north
0.country.0.region.1	0.country.0	0	South
0.country.1.region.0	0.country.1	0	north a
0.country.1.region.1	0.country.1	0	South b

country_region_geo.csv

_link	_link_country	_link_country_region	_link_input	state	name	population
0.country.0.region.0.geo.0	0.country.0	0.country.0.region.0	0	xx	Northern State	100000
0.country.0.region.0.geo.1	0.country.0	0.country.0.region.0	0	yy	Boreal State	2000
0.country.0.region.1.geo.0	0.country.0	0.country.0.region.1	0	aa	Southern State	200000
0.country.0.region.1.geo.1	0.country.0	0.country.0.region.1	0	bb	Down under State	3000
0.country.1.region.0.geo.0	0.country.1	0.country.1.region.0	0	xx	Northern State a	100000
0.country.1.region.0.geo.1	0.country.1	0.country.1.region.0	0	yy	Boreal State a	2000
0.country.1.region.1.geo.0	0.country.1	0.country.1.region.1	0	aa	Southern State b	200000
0.country.1.region.1.geo.1	0.country.1	0.country.1.region.1	0	bb	Down under State b	3000

[marcopaganini]

Ciao @aborruso!

Oh that's not off topic at all. Those are super useful tools and I'll definitely investigate them. Thanks for the pointers!