☰

将表转换为 json

tag-icon • tag-icon text-processing json
将表转换为 json

我有一个很大的数据表，我想将其转换为 json，并且不确定像 jq、mlr 或类似的工具是否能够执行这样的任务，而不必求助于我糟糕的 awk 技能。

样本表：

Balance_sheet for AAPL:

                                                        2023-09-30      2022-09-30      2021-09-30      2020-09-30
Treasury Shares Number                                         0.0             NaN             NaN             NaN
Ordinary Shares Number                               15550061000.0   15943425000.0   16426786000.0   16976763000.0

首选输出：

{
    "Balance_sheet for AAPL": {
        "Treasury Shares Number": {
            "2023-09-30": "0.0",
            "2022-09-30": "NaN",
            "2021-09-30": "NaN",
            "2020-09-30": "NaN"
        },
        "Ordinary Shares Number": {
            "2023-09-30": "15550061000.0",
            "2022-09-30": "15943425000.0",
            "2021-09-30": "16426786000.0",
            "2020-09-30": "16976763000.0"
        }
    }
}

以下格式也可以使用，但不太理想：

{
    "Balance_sheet for AAPL": {
        "2023-09-30": {
            "Treasury Shares Number": "0.0",
            "Ordinary Shares Number": "15550061000.0"
        },
        "2022-09-30": {
            "Treasury Shares Number": "NaN",
            "Ordinary Shares Number": "15943425000.0"
        },
        "2021-09-30": {
            "Treasury Shares Number": "NaN",
            "Ordinary Shares Number": "16426786000.0"
        },
        "2020-09-30": {
            "Treasury Shares Number": "NaN",
            "Ordinary Shares Number": "16976763000.0"
        }
    }
}

有谁知道完成此任务的明智方法？

答案1

我会用perl：

$ perl -MJSON::PP -ae '
  if (/^(.*):$/) {$sheet = $1}
  elsif (/^\h+\d/) {$n = (@dates = @F)}
  elsif (/^(.*?)((?:\h+)\H+){$n}$/) {
    $i = -$n;
    $j{$sheet}->{$1} = {map {$_ => $F[$i++]} @dates}
  }
  END {print JSON::PP->new->pretty->encode(\%j)}' your-file
{
   "Balance_sheet for AAPL" : {
      "Ordinary Shares Number" : {
         "2023-09-30" : "15550061000.0",
         "2020-09-30" : "16976763000.0",
         "2022-09-30" : "15943425000.0",
         "2021-09-30" : "16426786000.0"
      },
      "Treasury Shares Number" : {
         "2020-09-30" : "NaN",
         "2023-09-30" : "0.0",
         "2021-09-30" : "NaN",
         "2022-09-30" : "NaN"
      }
   }
}

根据正则表达式区分输入中的 3 种类型的行：

  • 那些以:决定当前“工作表”结尾的内容（顶级对象中的键）
  • 以至少一个 ( +)\h水平空格开头的行，后跟一位\d小数位，这些数字是日期（第三级对象中的键），我们将它们记录在数组中，@dates并将它们的编号记录在$n.
  • 至少包含$n空白分隔字段的行，其中最后一个字段之前的部分$n构成第二级对象中的键，并且我们使用@datesas 键和最后一个$n字段作为值来为该键构建第三级对象。
  • 其他任何内容（在示例输入中只是空行）都将被忽略。

请注意，由于 JSON 对象是 perl 关联数组的表示，因此其中成员的顺序将是随机的。您可以通过设置canonical标志 ( )来获得一致的顺序JSON::PP->new->pretty->canonical->encode(\%j)，其中每个对象的成员都按键排序。

如果 JSON 对象中字段的顺序反映表中的顺序很重要（如 中所述）perldoc JSON::PP，您可以领带这些数组到不同类型的哈希，使用类似的东西来保留顺序

$ perl -MData::Dumper -MTie::Hash::Indexed -MJSON::PP -ae '
  BEGIN{tie %j, $m = "Tie::Hash::Indexed"}
  if (/^(.*):$/) {tie my %s, $m; $j{$sheet = $1} = \%s}
  elsif (/^\h+\d/) {$n = (@dates = @F)}
  elsif (/^(.*?)((?:\h+)\H+){$n}$/) {
    tie my %s, $m;
    $i = -$n;
    %s = map {$_ => $F[$i++]} @dates;
    $j{$sheet}->{$1} = \%s
  }
  END {print JSON::PP->new->pretty->encode(\%j)}' your-file
{
   "Balance_sheet for AAPL" : {
      "Treasury Shares Number" : {
         "2023-09-30" : "0.0",
         "2022-09-30" : "NaN",
         "2021-09-30" : "NaN",
         "2020-09-30" : "NaN"
      },
      "Ordinary Shares Number" : {
         "2023-09-30" : "15550061000.0",
         "2022-09-30" : "15943425000.0",
         "2021-09-30" : "16426786000.0",
         "2020-09-30" : "16976763000.0"
      }
   }
}

Tie::Hash::Indexed（libtie-hash-indexed-perlDebian 软件包）是提供有序哈希的几个此类模块之一。

如果这很重要，则对于更接近您期望的格式（具有 4 个空格缩进并且 s 之后但不是之前的空格）的格式:，请替换pretty为indent->indent_length(4)->space_after( indent_length(2)for jq-style Pretty-printing)。

答案2

使用任何 POSIX awk：

$ cat tst.awk
BEGIN {
    inStep = 4
    print "{"
}

sub(/:$/,"") {
    indent = inStep
    printf "%*s\"%s\": {\n", indent, "", $0
    next
}

!numDates && /^[[:space:]]/ {
    numDates = split($0,dates)
    next
}

numDates && match($0,"[[:space:]]+([^[:space:]]+[[:space:]]*){"numDates"}$") {
    indent += inStep
    printf "%s%*s\"%s\": {\n", (numItems++ ? ",\n" : ""), indent, "", substr($0,1,RSTART-1)

    indent += inStep
    $0 = substr($0,RSTART,RLENGTH)
    for ( i=1; i<=numDates; i++ ) {
        printf "%*s\"%s\": \"%s\"%s\n", indent, "", dates[i], $i, (i<numDates ? "," : "")
    }
    indent -= inStep

    printf "%*s}", indent, ""
    indent -= inStep
}

END {
    printf "\n%*s}\n", indent, ""
    print "}"
}


$ awk -f tst.awk file
{
    "Balance_sheet for AAPL": {
        "Treasury Shares Number": {
            "2023-09-30": "0.0",
            "2022-09-30": "NaN",
            "2021-09-30": "NaN",
            "2020-09-30": "NaN"
        },
        "Ordinary Shares Number": {
            "2023-09-30": "15550061000.0",
            "2022-09-30": "15943425000.0",
            "2021-09-30": "16426786000.0",
            "2020-09-30": "16976763000.0"
        }
    }
}

如果您需要处理多个“资产负债表”块，那么只需添加以下内容：

if ( numTables++ ) {
    printf "\n%*s},\n", indent, ""
}
numDates = numItems = 0

紧接该sub()行下方，例如给出以下输入：

$ cat file2
Balance_sheet for AAPL:

                                                        2023-09-30      2022-09-30      2021-09-30      2020-09-30
Treasury Shares Number                                         0.0             NaN             NaN             NaN
Ordinary Shares Number                               15550061000.0   15943425000.0   16426786000.0   16976763000.0

Balance_sheet for foo:

                                                        2023-09-30      2022-09-30      2021-09-30      2020-09-30
Treasury Shares Number                                         0.0             NaN             NaN             NaN
Ordinary Shares Number                               15550061000.0   15943425000.0   16426786000.0   16976763000.0

这个脚本：

$ cat tst.awk
BEGIN {
    inStep = 4
    print "{"
}

sub(/:$/,"") {
    if ( numTables++ ) {
        printf "\n%*s},\n", indent, ""
    }
    numDates = numItems = 0
    indent = inStep
    printf "%*s\"%s\": {\n", indent, "", $0
    next
}

!numDates && /^[[:space:]]/ {
    numDates = split($0,dates)
    next
}

numDates && match($0,"[[:space:]]+([^[:space:]]+[[:space:]]*){"numDates"}$") {
    indent += inStep
    printf "%s%*s\"%s\": {\n", (numItems++ ? ",\n" : ""), indent, "", substr($0,1,RSTART-1)

    indent += inStep
    $0 = substr($0,RSTART,RLENGTH)
    for ( i=1; i<=numDates; i++ ) {
        printf "%*s\"%s\": \"%s\"%s\n", indent, "", dates[i], $i, (i<numDates ? "," : "")
    }
    indent -= inStep

    printf "%*s}", indent, ""
    indent -= inStep
}

END {
    printf "\n%*s}\n", indent, ""
    print "}"
}

将产生以下输出：

$ awk -f tst.awk file2
{
    "Balance_sheet for AAPL": {
        "Treasury Shares Number": {
            "2023-09-30": "0.0",
            "2022-09-30": "NaN",
            "2021-09-30": "NaN",
            "2020-09-30": "NaN"
        },
        "Ordinary Shares Number": {
            "2023-09-30": "15550061000.0",
            "2022-09-30": "15943425000.0",
            "2021-09-30": "16426786000.0",
            "2020-09-30": "16976763000.0"
        }
    },
    "Balance_sheet for foo": {
        "Treasury Shares Number": {
            "2023-09-30": "0.0",
            "2022-09-30": "NaN",
            "2021-09-30": "NaN",
            "2020-09-30": "NaN"
        },
        "Ordinary Shares Number": {
            "2023-09-30": "15550061000.0",
            "2022-09-30": "15943425000.0",
            "2021-09-30": "16426786000.0",
            "2020-09-30": "16976763000.0"
        }
    }
}

答案3

使用乐（以前称为 Perl_6）

~$ raku -MJSON::Fast -e '
         my $a = lines[0..1].trim-trailing;         \
         my @a = slurp.map("Date" ~ *).lines.map:   \
             *.subst(:global, / <alpha>+ % " " /, { .trans(" " => "_") } ).split(/ \s+ /);  \
         @a = [Z] @a; my %h; for 1..^@a[0].elems -> $j {   \
             %h.append: @a.[0][$j] => %(@a.map( { $_.[0] => $_.[$j] } )[1..*]) };  \ 
         put to-json( $a => %h, :sorted-keys );'   file

上面是用 Raku（Perl 编程语言家族的成员）编写的答案。鉴于OP发布的“资产负债表”测试文件有限，“预处理”表也相应受到限制，并且可能是定制的。因此，用于将数据整理为标准格式的前几行代码应被解释为赋予 Raku 语言的“风味”，而不是代表处理所有“资产负债表”的方法。

  1. Raku模块JSON::Fast在命令行上加载。
  2. 第一条语句将前两行读入$a标量，trim-ming 去掉尾随空格。
  3. 第二个声明 A）。slurp将文件的剩余部分放入内存中， 乙）。前置"Date"字符串， C）。将一切分解为lines,d）。每个都line被map输入，以便：e）。subst设计由单字符空格分隔的字母模式% " "，以便（在替换半部分中）空格变成trans下划线_，从而从行标签中删除空格。然后最后F）。数据在剩余的\s+空白处进行分割，并且这个（现在是矩形的）表存储在@a数组中。
  4. 该@a表[Z]经过“zip”转换，使得行变成列，反之亦然（如果在矩形数据以外的任何数据上使用此运算符，请小心）。
  5. %h声明了一个哈希值。
  6. 迭代“Shares”数据列的数量。A）。%()创建包含键值对的匿名哈希，每对都有一个日期（行标签）为钥匙对于相应的“共享”数据列价值（在这里，使用索引[1..*]会删除多余的“标签”配对）。
  7. 添加另一个级别，乙）。适当的“共享”（即列）标签成为钥匙对于%()匿名（日期/股票）哈希，它成为第二级价值。转换为键/值的“日期/份额”列被append编辑为%h哈希值。
  8. 终于（真有水平了C此时），$a“Balance_Sheet”标题被重新添加为钥匙到%h哈希为价值，并且这个（现在是多级的）最终哈希表被转换to-json()，添加:sorted-keys命名参数，并输出put。

输入示例：

Balance_sheet for AAPL:

                                                        2023-09-30      2022-09-30      2021-09-30      2020-09-30
Treasury Shares Number                                         0.0             NaN             NaN             NaN
Ordinary Shares Number                               15550061000.0   15943425000.0   16426786000.0   16976763000.0

转换后的示例输入表[Z]（减去“Balance_Sheet”标题行）：

(Date Treasury_Shares_Number Ordinary_Shares_Number)
(2023-09-30 0.0 15550061000.0)
(2022-09-30 NaN 15943425000.0)
(2021-09-30 NaN 16426786000.0)
(2020-09-30 NaN 16976763000.0)

最终JSON输出：

{
  "Balance_sheet for AAPL:": {
    "Ordinary_Shares_Number": {
      "2020-09-30": "16976763000.0",
      "2021-09-30": "16426786000.0",
      "2022-09-30": "15943425000.0",
      "2023-09-30": "15550061000.0"
    },
    "Treasury_Shares_Number": {
      "2020-09-30": "NaN",
      "2021-09-30": "NaN",
      "2022-09-30": "NaN",
      "2023-09-30": "0.0"
    }
  }
}

https://raku.land/cpan:TIMOTIMO/JSON::Fast
https://docs.raku.org/language/hashmap
https://docs.raku.org/
https://raku.org

相关内容