基于数字加入行(Google Refine，Excel/Google电子表格)

Question

我在CSV文件上有大量的行，看起来像：

name a,1
name b,1
name c,1
name d,2
name e,2

我需要根据数字加入行。结果应为：

name a|name b|name c
name d|name e

我该如何在Google完善或Excel/Google电子表格中进行操作？

我在想，但是没有解决方案。

非常感谢！

Answer 1

这是一个带有开放精炼的建议。我使用的唯一grel公式是：

row.record.cells['myColumn'].value.join('|')

屏幕截图

这是JSON，假设您的第一列被命名为" mycolumn"和第二个"数字"：

[
  {
    "op": "core/column-addition",
    "description": "Create column test at index 2 based on column number using expression grel:value",
    "engineConfig": {
      "mode": "row-based",
      "facets": [
        {
          "omitError": false,
          "expression": "isBlank(value)",
          "selectBlank": false,
          "selection": [
            {
              "v": {
                "v": false,
                "l": "false"
              }
            }
          ],
          "selectError": false,
          "invert": false,
          "name": "ee",
          "omitBlank": false,
          "type": "list",
          "columnName": "ee"
        }
      ]
    },
    "newColumnName": "test",
    "columnInsertIndex": 2,
    "baseColumnName": "number",
    "expression": "grel:value",
    "onError": "set-to-blank"
  },
  {
    "op": "core/column-move",
    "description": "Move column test to position 0",
    "columnName": "test",
    "index": 0
  },
  {
    "op": "core/blank-down",
    "description": "Blank down cells in column test",
    "engineConfig": {
      "mode": "row-based",
      "facets": [
        {
          "omitError": false,
          "expression": "isBlank(value)",
          "selectBlank": false,
          "selection": [
            {
              "v": {
                "v": false,
                "l": "false"
              }
            }
          ],
          "selectError": false,
          "invert": false,
          "name": "ee",
          "omitBlank": false,
          "type": "list",
          "columnName": "ee"
        }
      ]
    },
    "columnName": "test"
  },
  {
    "op": "core/column-addition",
    "description": "Create column concatenation at index 2 based on column myColumn using expression grel:row.record.cells['myColumn'].value.join('|')",
    "engineConfig": {
      "mode": "row-based",
      "facets": [
        {
          "omitError": false,
          "expression": "isBlank(value)",
          "selectBlank": false,
          "selection": [
            {
              "v": {
                "v": false,
                "l": "false"
              }
            }
          ],
          "selectError": false,
          "invert": false,
          "name": "ee",
          "omitBlank": false,
          "type": "list",
          "columnName": "ee"
        }
      ]
    },
    "newColumnName": "concatenation",
    "columnInsertIndex": 2,
    "baseColumnName": "myColumn",
    "expression": "grel:row.record.cells['myColumn'].value.join('|')",
    "onError": "set-to-blank"
  }
]

Answer 2

如果您可以使用python，那么进行此操作将很容易。在下面的代码中，名称和组将从" input.csv"中读取，并且分组名称（以及组）写入" output.csv"。defaultdict用于创建空列表来存储组成员。

import collections
import csv
grouped = collections.defaultdict(list)
with open('input.csv') as fp:
    reader = csv.reader(fp)
    for row in reader:
        name, group = row
        grouped[group].append(name)

with open('output.csv', 'w', newline='') as fp:
    writer = csv.writer(fp, delimiter='|')
    for key in sorted(grouped.keys()):
        writer.writerow([key] + grouped[key])