Reformat Department Table

easy pivot group by hash map

Problem

A Department table has rows (id, revenue, month) where each row is one department's revenue in one month. Reformat it so there is exactly one row per department id, with a separate column for each month's revenue: Jan_Revenue, Feb_Revenue, …, Dec_Revenue. A month missing for a department becomes null.

Input(1, 8000, Jan), (2, 9000, Jan), (3, 10000, Feb), (1, 7000, Feb), (1, 6000, Mar)

Outputid=1 → Jan 8000, Feb 7000, Mar 6000; id=2 → Jan 9000; id=3 → Feb 10000

Each (id, month) cell is filled by the matching input row; absent months stay null.

rows (id:revenue:month, comma-separated)

MONTHS = ["Jan","Feb","Mar","Apr","May","Jun",
          "Jul","Aug","Sep","Oct","Nov","Dec"]

def reformat(rows):
    table = {}
    for id, revenue, month in rows:
        row = table.setdefault(id, {})
        row[month] = revenue
    out = []
    for id in sorted(table):
        out.append([id] + [table[id].get(m) for m in MONTHS])
    return out

const MONTHS = ["Jan","Feb","Mar","Apr","May","Jun",
                "Jul","Aug","Sep","Oct","Nov","Dec"];

function reformat(rows) {
  const table = new Map();
  for (const [id, revenue, month] of rows) {
    if (!table.has(id)) table.set(id, {});
    table.get(id)[month] = revenue;
  }
  const out = [];
  for (const id of [...table.keys()].sort((a, b) => a - b)) {
    const r = table.get(id);
    out.push([id, ...MONTHS.map(m => (m in r ? r[m] : null))]);
  }
  return out;
}

class Solution {
    static final String[] MONTHS = {"Jan","Feb","Mar","Apr","May","Jun",
                                    "Jul","Aug","Sep","Oct","Nov","Dec"};
    public List<long[]> reformat(int[][] rows) {
        Map<Integer, Map<String, Long>> table = new TreeMap<>();
        for (int[] r : rows) {           // r = {id, revenue, monthIdx}
            table.computeIfAbsent(r[0], k -> new HashMap<>())
                 .put(MONTHS[r[2]], (long) r[1]);
        }
        List<long[]> out = new ArrayList<>();
        for (var e : table.entrySet()) {
            long[] row = new long[13];
            row[0] = e.getKey();
            for (int m = 0; m < 12; m++)
                row[m + 1] = e.getValue().getOrDefault(MONTHS[m], -1L);
            out.add(row);
        }
        return out;
    }
}

const vector<string> MONTHS = {"Jan","Feb","Mar","Apr","May","Jun",
                               "Jul","Aug","Sep","Oct","Nov","Dec"};

map<int, unordered_map<string, long>> reformat(vector<tuple<int,long,string>>& rows) {
    map<int, unordered_map<string, long>> table;
    for (auto& [id, revenue, month] : rows) {
        table[id][month] = revenue;   // group by id, scatter by month
    }
    return table;                     // ordered by id; missing months absent
}

Explanation

This is a pivot: the input has one row per (department, month), and we want one row per department with a separate column for each month. The trick is to group the rows by department id and scatter each revenue into the right month slot.

We build a nested map table where each id points to a small dictionary of {month: revenue}. As we read each input row (id, revenue, month), we do table[id][month] = revenue. Rows for the same id just keep adding entries to the same inner dictionary.

Once every row is placed, we emit one output row per id (sorted), filling each month column from the inner map. If a month is missing for that department, the lookup returns nothing, so the cell becomes null.

Example: id 1 appears in Jan (8000), Feb (7000), and Mar (6000), so its inner map is {Jan:8000, Feb:7000, Mar:6000} and the rest of its months are null. id 2 only has Jan 9000; id 3 only has Feb 10000.

Time: O(n + k log k) Space: O(k)