Analyse salaires région Bordelaise 2021 - Script

README.txt

Il s'agit du script utilisé sur cette spreadsheet :
https://docs.google.com/spreadsheets/d/10ArRZuLm1K60jxD6Oe5Wu4C6d-Z3xzUnFNgz_i3bEn8/edit?usp=sharing
(pour construire les données des nuages de points en T1 sur l'onglet "Données retravaillées", cf fonction PREPARE_BUBBLES())

Code.gs

function computeSalariesPerXPResults(anneesXPCells, revenusCells, typesEntrepriseCells) {
  if(anneesXPCells.length !== revenusCells.length || anneesXPCells.length !== typesEntrepriseCells.length) {
    throw new Error("Different number of rows in inputs: "+JSON.stringify([anneesXPCells.length, revenusCells.length, typesEntrepriseCells.length]));
  }

  const distinctEntTypes = new Set();

  const salariesPerXPResults = anneesXPCells.reduce((salariesPerXP, anneeXPRow, idx) => {
    const anneeXP = Number(anneeXPRow[0]);
    const salary = Number(revenusCells[idx][0]);
    const entType = typesEntrepriseCells[idx][0];

    if(salary !== 0) {
      let yearlyResultEntry = salariesPerXP.find(r => r.yearlyXP === anneeXP);
      if(!yearlyResultEntry) {
        yearlyResultEntry = { yearlyXP: anneeXP, salaries: [] }
        salariesPerXP.push(yearlyResultEntry);
      }

      yearlyResultEntry.salaries.push({ salary, entType });
      distinctEntTypes.add(entType);
    }

    return salariesPerXP;
  }, []);

  salariesPerXPResults.sort((r1, r2) => r1.yearlyXP - r2.yearlyXP);

  return {salariesPerXPResults, distinctEntTypes: [...distinctEntTypes]};
}

function PREPARE_NUAGE_POINTS(anneesXPCells, revenusCells, typesEntrepriseCells) {
  const {salariesPerXPResults, distinctEntTypes} = computeSalariesPerXPResults(anneesXPCells, revenusCells, typesEntrepriseCells);

  const resultRows = salariesPerXPResults.reduce((resultRows, salariesPerXP) => {
    while(salariesPerXP.salaries.length) {
      const cells = [ salariesPerXP.yearlyXP ];
      distinctEntTypes.forEach(entType => {
        const salaryMatchingEntTypeIndex = salariesPerXP.salaries.findIndex(s => s.entType === entType);
        if(salaryMatchingEntTypeIndex === -1) {
          cells.push("");
        } else {
          const salaryEntry = salariesPerXP.salaries.splice(salaryMatchingEntTypeIndex, 1)[0];
          cells.push(salaryEntry.salary);          
        }
      })
      resultRows.push(cells);
    }
    return resultRows;
  }, []);

  return [ [ "Années d'XP", ...distinctEntTypes ] ].concat(resultRows);
}

const DEFAULT_ABSORPTION_RATIO = 0.05;
function PREPARE_BUBBLES(anneesXPCells, revenusCells, typesEntrepriseCells, absorbtionRatio) {
  const {salariesPerXPResults, distinctEntTypes} = computeSalariesPerXPResults(anneesXPCells, revenusCells, typesEntrepriseCells);

  const weightedSalaries = putSalaryWeightsOn(salariesPerXPResults, distinctEntTypes, absorbtionRatio || DEFAULT_ABSORPTION_RATIO);

// return JSON.stringify(weightedSalaries);
  
  const results = weightedSalaries.reduce((results, salariesPerXP) => {
    salariesPerXP.salaries.forEach(salaryEntry => {
      const cells = [ salariesPerXP.yearlyXP, salaryEntry.entType, salaryEntry.salary, salaryEntry.weight ];
      /*
      const entTypeIndex = distinctEntTypes.findIndex(et => et === salaryEntry.entType);
      cells[entTypeIndex+1] = salaryEntry.salary;
      cells[distinctEntTypes.length + 1] = salaryEntry.weight;
      */
      results.push(cells);
    })
    // while(salariesPerXP.salaries.length) {
    //   const cells = [ salariesPerXP.yearlyXP ];
    //   distinctEntTypes.forEach(entType => {
    //     const salaryMatchingEntTypeIndex = salariesPerXP.salaries.findIndex(s => s.entType === entType);
    //     if(salaryMatchingEntTypeIndex === -1) {
    //       cells.push("");
    //     } else {
    //       const salaryEntry = salariesPerXP.salaries.splice(salaryMatchingEntTypeIndex, 1)[0];
    //       cells.push(salaryEntry.salary);          
    //     }
    //   })
    //   resultRows.push(cells);
    // }
    return results;
  }, []);

  return [ [ "Années d'XP", "Ent type", "Salary"/* ...distinctEntTypes */, "Weight" ] ].concat(results);

/*
  return JSON.stringify([ 
      [ "XP Years", "Enterprise type", "Salary average", "Salary average weight" ] 
    ].concat(results.map(r => 
      [  ]
    ))
  );
  */
}

function putSalaryWeightsOn(salariesPerXPResults, distinctEntTypes, absorptionRatio) {
  return salariesPerXPResults.map(salariesPerXPResult => {
    const salaries = distinctEntTypes.reduce((salaries, entType) => {
      const salariesForEntType = salariesPerXPResult.salaries.filter(s => s.entType === entType);
      if(salariesForEntType.length) {
        const salariesAggregates = new ValuesAggregates(salariesForEntType.map(s => s.salary), absorptionRatio)
        salariesAggregates.computeUntilNoFurtherChanges();
        salariesAggregates.aggregates.forEach(aggreg => {
          salaries.push({ salary: aggreg.avg(), weight: /* aggreg.size()/1000 */ aggreg.values.length, entType, aggregate: aggreg });
        })
      }

      return salaries;
    }, []);
    return { yearlyXP: salariesPerXPResult.yearlyXP, salaries };
  })
}

function testing() {

  const values = [
            33000,
            45000,
            53000,
            62000,
            62600,
            63000,
            65000,
            65000,
            65000,
            67100,
            74000
  ];

  const aggregates = new ValuesAggregates(values, DEFAULT_ABSORPTION_RATIO);
  aggregates.computeUntilNoFurtherChanges();
  const out = aggregates.show();
  out = out;
}

class ValuesAggregate {
  constructor(values, absorptionRatio) {
    if(!values.length){ throw `values cannot be empty when instantiating ValuesAggregate !`; }
    this.values = values;
    // 10% of absorption ratio means that everytime a new point is absorbed, the aggregate grows 10% bigger per absorbed values
    // (and thus, may absorb new values)
    this.absorptionRatio = absorptionRatio;
  }

  sum() {
    return this.values.reduce((tot, v) => tot+v, 0);
  }
  avg() {
    const result = this.sum() / this.values.length;
    return Math.round(result*100)/100.0;
  }

  canAbsorb(otherAggregate) {
    const otherCenter = otherAggregate.avg();
    const range = this.absorbableRange();
    return range.lower <= otherCenter && otherCenter <= range.upper;
  }

  absorb(otherAggregate) {
    this.values = this.values.concat(otherAggregate.values);
    this.values.sort((v1, v2) => v1-v2);
  }

  absorbableRange() {
    const center = this.avg()
    const size = this.size();
    return {
      lower: center - size,
      upper: center + size 
    };
  }

  size() {
    const center = this.avg()
    const result = center * Math.pow(1+this.absorptionRatio, this.values.length) - center;
    return Math.round(result*100)/100.0;
  }

  show() {
    const range = this.absorbableRange();
    return `A{size=${this.values.length}, avg=${this.avg()}, range=[${range.lower}-${range.upper}], values=${JSON.stringify(this.values)}}`;
  }
}

class ValuesAggregates {
  constructor(values, absorptionRatio) {
    this._updateAggregatesTo(values.map(v => new ValuesAggregate([v], absorptionRatio)));
  }

  _updateAggregatesTo(aggregates) {
    this.aggregates = aggregates;
    this.aggregates.sort((a1, a2) => a1.avg() - a2.avg());
  }

  show() {
    let str = `As{count=${this.aggregates.length}}[\n${this.aggregates.map(agg => `  ${agg.show()}`).join("\n")}\n]`;

    console.log(str);

    return str;
  }

  tryAbsorptions() {
    let changeDetected = false;

    console.log(this.show());

    const newAggregates = ["normal", "reversedOrder"].reduce((aggregates, _) => {
      const newAggregates = [ aggregates[0] ];
      let currentAggregate = newAggregates[0];
      for(var i=1; i<aggregates.length; i++) {
        const aggregateCandidate = aggregates[i];

        if(currentAggregate.canAbsorb(aggregateCandidate)) {
          currentAggregate.absorb(aggregateCandidate)
          changeDetected = true;
        } else {
          currentAggregate = aggregateCandidate;
          newAggregates.push(currentAggregate);
        }
      }

      console.log(`tryAbsorptions[${_}] on aggregates=${aggregates.map(a => a.values.join(","))} => newAggregates=${newAggregates.map(a => a.values.join(","))}`)

      newAggregates.reverse();
      return newAggregates;
    }, this.aggregates );

    if(changeDetected) {
      this._updateAggregatesTo(newAggregates);
    }

    return changeDetected;
  }

  computeUntilNoFurtherChanges() {
    while(this.tryAbsorptions()){
    }
  }
}