valuesFillMap for pivotWider() not being applied

ellemenno commented 3 years ago

hello, i have started using tidy and am finding it awesomely helpful, and am appreciating the many examples in the documentation. thank you!!

i think i have found a bug with valuesFillMap for pivotWider(), where it seems that the map is just ignored.

test case

tidy(
  [
    {n:2, c:'a', e:1001},{n:3, c:'a', e:1002},{n:7, c:'x', e:1003},
    {n:4, c:'b', e:1001},{n:2, c:'r', e:1002},{n:9, c:'y', e:1003},
    {n:6, c:'c', e:1001},{n:1, c:'z', e:1002},{n:1, c:'z', e:1003},
  ],
  pivotWider({
    namesFrom: 'c',
    valuesFrom: 'n',
    valuesFillMap: { a:0, b:0, c:0, r:0, s:0, t:0, x:0, y:0, z:0 },
  }),
)

results

[
  {a: 2, x: undefined, b: 4, r: undefined, y: undefined, c: 6, z: undefined, e: 1001},
  {a: 3, x: undefined, b: undefined, r: 2, y: undefined, c: undefined, z: 1, e: 1002},
  {a: undefined, x: 7, b: undefined, r: undefined, y: 9, c: undefined, z: 1, e: 1003},
]

map values do not seem to be applied; widened keys are given value undefined
map keys do not seem to be applied; s and t are missing from results

expected results

[
  {a: 2, x: 0, b: 4, r: 0, y: 0, c: 6, z: 0, s: 0, t: 0, e: 1001},
  {a: 3, x: 0, b: 0, r: 2, y: 0, c: 0, z: 1, s: 0, t: 0, e: 1002},
  {a: 0, x: 7, b: 0, r: 0, y: 9, c: 0, z: 1, s: 0, t: 0, e: 1003},
]

map values applied to widened keys (as 0)
map keys applied; every row has all columns (a, b, c, r, s, t, x, y, z, and e)

pbeshai commented 3 years ago

Hi there, glad to hear tidy has been helpful for you. The map for valuesFillMap should use the column names pre-pivot – in your case n (they match what is passed to the valuesFrom argument).

tidy(
  [
    { n: 2, c: 'a', e: 1001 },
    { n: 3, c: 'a', e: 1002 },
    { n: 7, c: 'x', e: 1003 },
    { n: 4, c: 'b', e: 1001 },
    { n: 2, c: 'r', e: 1002 },
    { n: 9, c: 'y', e: 1003 },
    { n: 6, c: 'c', e: 1001 },
    { n: 1, c: 'z', e: 1002 },
    { n: 1, c: 'z', e: 1003 },
  ],
  pivotWider({
    namesFrom: 'c',
    valuesFrom: 'n',
    valuesFillMap: { n: 0 },
  })
);

    [
      { a: 2, x: 0, b: 4, r: 0, y: 0, c: 6, z: 0, e: 1001 },
      { a: 3, x: 0, b: 0, r: 2, y: 0, c: 0, z: 1, e: 1002 },
      { a: 0, x: 7, b: 0, r: 0, y: 9, c: 0, z: 1, e: 1003 }
    ]

Or more simply in this case, you could just use valuesFill: 0

tidy(
  [
    { n: 2, c: 'a', e: 1001 },
    { n: 3, c: 'a', e: 1002 },
    { n: 7, c: 'x', e: 1003 },
    { n: 4, c: 'b', e: 1001 },
    { n: 2, c: 'r', e: 1002 },
    { n: 9, c: 'y', e: 1003 },
    { n: 6, c: 'c', e: 1001 },
    { n: 1, c: 'z', e: 1002 },
    { n: 1, c: 'z', e: 1003 },
  ],
  pivotWider({
    namesFrom: 'c',
    valuesFrom: 'n',
    valuesFill: 0,
  })
);

Notably, there's no 's' or 't' in your input data, so there's no 's' or 't' in the output. If you want to explicitly make sure those also get set to zero, you might have better luck using replaceNully explicitly:

tidy(
  [
    { n: 2, c: 'a', e: 1001 },
    { n: 3, c: 'a', e: 1002 },
    { n: 7, c: 'x', e: 1003 },
    { n: 4, c: 'b', e: 1001 },
    { n: 2, c: 'r', e: 1002 },
    { n: 9, c: 'y', e: 1003 },
    { n: 6, c: 'c', e: 1001 },
    { n: 1, c: 'z', e: 1002 },
    { n: 1, c: 'z', e: 1003 },
  ],
  pivotWider({
    namesFrom: 'c',
    valuesFrom: 'n',
    valuesFill: 0,
  }),
  replaceNully({ s: 0, t: 0 }),
);

    [
      { a: 2, x: 0, b: 4, r: 0, y: 0, c: 6, z: 0, e: 1001, s: 0, t: 0 },
      { a: 3, x: 0, b: 0, r: 2, y: 0, c: 0, z: 1, e: 1002, s: 0, t: 0 },
      { a: 0, x: 7, b: 0, r: 0, y: 9, c: 0, z: 1, e: 1003, s: 0, t: 0 }
    ]

Or if you want to use replaceNully on all fields explicitly, you could avoid valuesFill:

tidy(
  [
    { n: 2, c: 'a', e: 1001 },
    { n: 3, c: 'a', e: 1002 },
    { n: 7, c: 'x', e: 1003 },
    { n: 4, c: 'b', e: 1001 },
    { n: 2, c: 'r', e: 1002 },
    { n: 9, c: 'y', e: 1003 },
    { n: 6, c: 'c', e: 1001 },
    { n: 1, c: 'z', e: 1002 },
    { n: 1, c: 'z', e: 1003 },
  ],
  pivotWider({
    namesFrom: 'c',
    valuesFrom: 'n',
  }),
  replaceNully({ a: 0, b: 0, c: 0, r: 0, s: 0, t: 0, x: 0, y: 0, z: 0 }),
);

    [
      { a: 2, x: 0, b: 4, r: 0, y: 0, c: 6, z: 0, e: 1001, s: 0, t: 0 },
      { a: 3, x: 0, b: 0, r: 2, y: 0, c: 0, z: 1, e: 1002, s: 0, t: 0 },
      { a: 0, x: 7, b: 0, r: 0, y: 9, c: 0, z: 1, e: 1003, s: 0, t: 0 }
    ]

ellemenno commented 3 years ago

aha! thank you for a very helpful response. i misunderstood that valuesFillMap applies pre-pivot.

valuesFill worked fine until i realized i need to set defaults for keys that are not always in the data set initially.

replaceNully() is exactly what i'm looking for.

pbeshai / tidy