Home > OS >  Replace value in table based on another table
Replace value in table based on another table

Time:10-17

Is there a best practice in using arquero to reformat a value based on a set of ranges?

For example, I have the following 2 arrays:

const agefmt = [
       {
       'fmtname': 'agefmt',
       'type'  : 'n',
       'format': [
                  {'start': 0,  'end': 10, 'label': '0 - 10'},
                  {'start': 11, 'end': 20, 'label': '11 - 20'},
                  {'start': 21, 'end': 30, 'label': '21 - 30'},
                  {'start': 31, 'end': 40, 'label': '31 - 40'},
                  {'start': 41, 'end': 50, 'label': '41 - 50'},
                  {'start': 51, 'end': 1000, 'label': '>51'}
                 ]
       },  
]

const age = [
    { "AGE": 19 },
    { "AGE": 20 },
    { "AGE": 31 },
    { "AGE": 26 },
    { "AGE": 46 },
    { "AGE": 27 }
]

and I would like to replace the 'age' array with the 'label' within the range of the 'start' and 'end' values defined in the agefmt array.

The resulting array should look like the following:

[
    { "AGE": '0 - 10' },
    { "AGE": '11 - 20' },
    { "AGE": '31 - 40' },
    { "AGE": '21 - 30' },
    { "AGE": '41 - 60' },
    { "AGE": '21 - 30' }
]

I started writing it as a function like so, but found I couldn't pass the 'agefmt' as a parameter:

addFunction(function fmt({infmt, value})  { 
    console.log(value, infmt)
    // calculate ranges here
    return value 
});

r.derive({rowvar : d => op.fmt(rowgrp, d.rowvar)})

Is there a more obvious way to achieve this?

CodePudding user response:

It is just a matter of iterating through the age array, and then checking the nested AGE property against agefmt[0].format in a for loop. Whenever the AGE value is between the start and end values, then a label is considered found and we break out of the for loop.

One thing is that your expected output is incorrect based on your provided data: the first entry has an age of 19 and the matching label should be 11 - 20 not 0 - 10 as you wanted.

See proof-of-concept below:

const agefmt = [
  {
    fmtname: "agefmt",
    type: "n",
    format: [
      { start: 0, end: 10, label: "0 - 10" },
      { start: 11, end: 20, label: "11 - 20" },
      { start: 21, end: 30, label: "21 - 30" },
      { start: 31, end: 40, label: "31 - 40" },
      { start: 41, end: 50, label: "41 - 50" },
      { start: 51, end: 1000, label: ">51" },
    ],
  },
];

const age = [
  { AGE: 19 },
  { AGE: 20 },
  { AGE: 31 },
  { AGE: 26 },
  { AGE: 46 },
  { AGE: 27 },
];

function addLabelToAge(age, labelFormat) {
  return age.map(entry => {
    let matchedLabel = '';
    for (const format of labelFormat) {
      if (entry.AGE >= format.start && entry.AGE <= format.end) {
        matchedLabel = format.label;
        break;
      }
    }
    return { AGE: matchedLabel };
  });
}

console.log(addLabelToAge(age, agefmt[0].format));

If you're comfortable with ES6 syntax, you can make the function a little more concise using object destructuring object property assignment shorthand:

function addLabelToAge(age, labelFormat) {
  return age.map(({ AGE }) => {
    let matchedLabel = '';
    for (const { start, end, label } of labelFormat) {
      if (AGE >= start && AGE <= end) {
        matchedLabel = label;
        break;
      }
    }
    return { AGE: matchedLabel };
  });
}
  • Related