How to group by data format

Saw this the other day and just getting some time to respond. The structure of the data makes it a little bit of a pain to parse. I wrote some code that works assuming the ids in the tsv file are ordered. If your looking for this type of result sometimes it’s better to transform the data into another format. For this purpose I thought a struct would be the appropriate data structure to use.

defstruct [headers: [], data_rows: [], column_count: 0]

headers is a list that will accumulate all the headers.
data_rows will contain a list of maps for each row in the file.
column_count will keep track of how many header columns are needed.

So thinking in terms of rows of data the desired result is to take this input

| id | name | lang |
| 1 | Bob  | Elixir |
| 1 | Bob  | Java |
| 1 | Bob  | Ruby |
| 2 | Tom  | Elixir |
| 2 | Tom  | C# |
| 3 | Dave | Python |
| 3 | Dave | Java |
| 3 | Dave | Ruby |
| 4 | Jane | Python |

and reduce them to single rows of maps

%{id: 1, name: "Bob", languages: ["Elixir", "Java", "Ruby"]},
%{id: 2, name: "Tom, "languages: ["C#", "Elixir"]},
%{id: 3, name: "Dave,"languages: ["Ruby", "Java", "Python"]},
%{id: 4, name: "Jane", languages: ["Python"]}

This is what I came up with hope it helps.

defmodule FormatTsv do
  defstruct [headers: [], data_rows: [], column_count: 0]
  NimbleCSV.define(TSV, separator: " | ")
  @file_input "priv/data.tsv"
  @file_output "priv/output.tsv"

  def convert do
    @file_input
    |> File.stream!()
    |> TSV.parse_stream()
    |> Stream.map(fn [id, name, lang] -> new_data_row(id, name, lang) end)
    |> transform_data()
    |> write_to_file()
  end

  def transform_data(data_rows) do
    data_rows
    |> group_data_rows()
    |> add_last_row()
    |> add_headers()
    |> reverse_rows()
  end

  def add_last_row({row, struct}) do
    Map.put(struct, :data_rows, [row|struct.data_rows])
  end

  def add_headers(struct) do
    Map.put(struct, :headers, headers(struct.column_count))
  end

  def reverse_rows(struct) do
    Map.put(struct, :data_rows, Enum.reverse(struct.data_rows))
  end

  def group_data_rows(rows) do
    Enum.reduce(rows, {%{}, %FormatTsv{}}, fn(row, acc) ->
      {map, struct} = acc
      group_data(row, map, struct)
    end)
  end

  def group_data(row, map, struct) when map == %{} do
    {row, struct}
  end

  def group_data(row, map, struct) do
    case row.id == map.id do
      true ->
        [language] = row.languages
        {add_language(map, language), struct}

      false ->
        {row, add_data_row(struct, map)}
    end
  end

  def add_language(map, language) do
    Map.put(map, :languages, [language|map.languages])
  end

  def add_data_row(struct, map) do
    Map.put(struct, :data_rows, [map|struct.data_rows])
    |> update_column_count(map)
  end

  def update_column_count(struct, map) do
    header_count = Enum.count(map.languages)
    column_count = struct.column_count

    case header_count > column_count do
      true ->
        Map.put(struct, :column_count, header_count)

      false ->
        struct
    end
  end

  def headers(num) do
    ["id", "name"] ++ Enum.map(1..num, &("lang#{&1}"))
  end

  def header_row(struct) do
    "| " <> Enum.map_join(struct.headers, " | ", &(&1)) <> " |\n"
  end

  def data_row_to_string(row, lang_count, column_count) when lang_count < column_count do
    "| #{row.id} | #{row.name} |" <> " " <> Enum.join(row.languages, " | ") <> String.duplicate(" |", column_count - lang_count) <> " |\n"
  end

  def data_row_to_string(row, _lang_count, _column_count) do
    "| #{row.id} | #{row.name} |" <> " " <> Enum.join(row.languages, " | ") <> " |\n"
  end

  def write_to_file(struct) do
    File.write(@file_output, header_row(struct), [:append])

    for row <- struct.data_rows do
      lang_count = Enum.count(row.languages)
      column_count = struct.column_count
      data_row = data_row_to_string(row, lang_count, column_count)
      File.write(@file_output, data_row, [:append])
    end

  end

  def new_data_row(id, name, language) do
    %{}
    |> put_id(id)
    |> put_name(name)
    |> put_language(language)
  end

  def put_id(map, id) do
    id = String.trim_leading(id, "| ")
    Map.put(map, :id, id)
  end

  def put_name(map, name) do
    name = String.trim_trailing(name)
    Map.put(map, :name, name)
  end

  def put_language(map, language) do
    language = String.trim_trailing(language, " |")
    Map.put(map, :languages, [language])
  end
end