Processor to format raw table data into format suitable for google sheets charts.

lightningInteractions.php

<?php

/**
 * Formats data generated by Sumo into a Google Sheets charts-friendly format.
 *
 * Add to or change the data in the data array at the bottom (formatted as such
 * so that you can just copy/paste data out of a table). The constructor will
 * process and format all of the data for you. Then you can spit it out using
 * the process() method.
 */

class lightningInteractions {

  /**
   * @var array
   *   Superset of site names that appear at least once in any month.
   */
  protected $formattedNameData;

  /**
   * @var array
   *   Associative array of site/count values separated by month.
   */
  protected $formattedMonthData;

  /**
   * @var array
   *   Associative array of site/count values separated by month with values for
   *   sites that don't appear in a given month set to zero.
   */
  protected $filledMonths;

  /**
   * @var int[]
   *   An array of total values expected for each category keyed by set.
   */
  protected $counts;

  /**
   * Converts site/count pair values copied from a spreadsheet into an
   * associative array.
   *
   * @param string $tsv
   *   A string of tab-separated site/count pairs with each pair separated by a
   *   newline.
   *
   * @return array
   *   Associative array of site/count pairs.
   */
  protected function convertSpreadheetData($tsv) {
    $raw = explode("\n", trim($tsv));
    $formatted = [];
    $last = null;
    foreach ($raw as $row) {
      $row = explode("\t", $row);
      $formatted[$row[0]] = $row[1];
    }
    return $formatted;
  }

  /**
   * lightningInteractions constructor.
   */
  public function __construct() {
    $formatted = [];
    foreach ($this->data as $set_name => $set) {
      foreach ($set as $month => $values) {
        $formatted[$set_name][$month] = $this->convertSpreadheetData($values);
      }
    }
    $this->formattedMonthData = $formatted;

    $this->setNames();

    $this->count = [];
    foreach ($this->formattedNameData as $set_name => $names) {
      $this->count[$set_name] = count($this->formattedNameData[$set_name]);
    }

    $filled_months = [];
    foreach ($this->data as $set_name => $set) {
      foreach ($set as $month => $month_data) {
        $filled_months[$set_name][$month] = $this->fillMonth($set_name, $month);
      }
    }
    $this->filledMonths = $filled_months;
  }

  /**
   * Takes the superset of sites from all months and adds zero values to those
   * that don't appear in a certain month. E.g., if jan has values for sites A,
   * B, and D but no value for C - and C does have a value in another month,
   * a zero value will be added for C. We use strings instead of integers
   * because integers were causing problems when pasting data.
   *
   * @param string $set_name
   *   One of layout, media, or workflow.
   *
   * @param string $month
   *   Three-letter string representing the month to process.
   *
   * @return array
   *   The month with zeros inserted for sites that didn't make the top-25.
   */
  protected function fillMonth($set_name, $month) {
    $month = $this->formattedMonthData[$set_name][$month];
    $filled_month = [];
    foreach ($this->formattedNameData[$set_name] as $name) {
      $filled_month[$name] = (isset($month[$name]) ? $month[$name] : "0" );
    }
    return $filled_month;
  }

  /**
   * Generates the superset of site names across all months.
   */
  protected function setNames() {
    foreach ($this->formattedMonthData as $set_name => $set) {
      $names_array = [];
      foreach ($set as $month => $data) {
        $names = array_keys($data);
        $names_array = array_unique(array_merge($names, $names_array));
      }
      $this->formattedNameData[$set_name] = $names_array;
    }
  }

  /**
   * Formats the superset of names so that it can be pasted into google sheets.
   *
   * @param string $set_name
   *   One of layout, media, or workflow.
   *
   * @return string
   *   Semicolon separated list of the superset of site names.
   */
  protected function getSemicolonSeparatedNames($set_name) {
    return implode(";", $this->formattedNameData[$set_name]);
  }

  /**
   * Returns the filled values for a month formatted for pasting into
   * Google Sheets.
   *
   * @param string $set_name
   *   One of layout, media, or workflow.
   *
   * @param string $month
   *   Three-letter string representing the month whose data should be returned.
   *
   * @return string
   *   A string of semicolon-separated values that correspond to the names
   *   returned from getSemicolonSeparatedNames.
   *
   * @throws \Exception
   *   If the length of the values is different from the length of site names,
   *   something went wrong.
   */
  protected function getSemicolonSeparatedMonthNumbers($set_name, $month) {
    $numbers = $this->filledMonths[$set_name][$month];
    if (count($numbers) !== $this->count[$set_name]) {
      throw new Exception('Values doesn\'t equal count.');
    }
    return implode(";", $numbers);
  }

  /**
   * Prints the formatted data for pasting into Google Sheets to stdout.
   */
  public function process() {
    foreach ($this->data as $set_name => $set) {
      print "\n\n\n" . $set_name . "\n";
      print "names\n" . $this->getSemicolonSeparatedNames($set_name) . "\n\n";
      foreach ($set as $month_name => $data) {
        $months_data[$month_name] = $this->getSemicolonSeparatedMonthNumbers($set_name, $month_name);
        print $month_name . ":\n";
        print $months_data[$month_name] . "\n\n";
      }
    }
  }

  /**
   * @var array
   *   Raw data generated by Sumo. This will be used to generate the formatted
   *   output.
   */
  private $data = [
    'media' => [
      'jan' =>'
bunte16ctyroajzxe	11,627
elleweb	4,294
schaer	4,136
bazaarweb	3,400
uvamiller	3,189
southdakotastate	2,731
instyleweb	2,680
healthweb	2,614
southnorfolk	2,401
nasdaq01live	2,396
yeshiva	2,386
firstclassholidaysstg	2,211
tolonews	1,819
watfordfc	1,638
usipdotorgstg	1,520
uchicagoharrisstg	1,509
naturafaq	1,486
o2wwwdev	1,432
dkrweb	1,347
ymcaseattle	1,280
foodboom	1,243
pwcnewsportal	1,068
ymcatwincities	965
tatelylecomstg	927
thyssenxb3awpqit4	893
',
      'feb' =>'
bunte16ctyroajzxe	11,214
southdakotastate	9,746
elleweb	7,742
bazaarweb	7,330
schaer	5,764
healthweb	5,463
necmusic	4,240
natgeoeudev	4,010
uvamiller	3,835
easternbank	3,443
usipdotorgstg	3,223
mraljmubasher	3,057
incisivemedia	2,806
o2wwwdev	2,510
instyleweb	2,337
ipsos	2,331
amdcom	2,211
dkrweb	1,797
massgov	1,739
tolonews	1,725
energyaustralia	1,704
prologis	1,602
nasdaq01live	1,475
naturafaq	1,469
reussirlesmarchesf25utkqjer	1,459
',
      'mar' =>'
natgeoeu	45,477
bunte16ctyroajzxe	13,975
natgeoeudev	9,806
healthweb	9,744
necmusic	8,154
southdakotastate	8,092
mraljmubasher	6,715
elleweb	5,869
incisivemedia	5,570
prologis	5,030
dkrweb	4,894
schaer	4,710
bazaarweb	4,179
o2dev	4,010
nasdaq01live	3,716
netscout	3,397
instyleweb	2,736
naturafaq	2,606
labaidsa5mymqakfw	2,375
ipsos	2,363
amdcom	2,206
princetonedu	2,041
uchicagoharrisstg	1,800
tolonews	1,744
nswtreasurylt	1,734
',
      'apr' =>'
natgeoeu	26,186
bunte16ctyroajzxe	13,600
mraljmubasher	6,805
incisivemedia	6,011
natgeoeudev	5,429
superioressex	5,268
nasdaq01live	4,672
schwab01live	4,543
amdcom	4,303
o2uat	4,279
o2dev	3,780
elleweb	3,754
healthweb	3,220
schaer	2,593
apt1	2,571
natgeoeustg	2,527
couchetard	2,420
hughesstg	2,236
hiscoxuk	2,002
southdakotastate	1,911
uchicagoharris	1,885
instyleweb	1,880
naturafaq	1,743
bazaarweb	1,638
powdr	1,630
',
      'may' =>'
natgeoeu	19,030
natgeoeudev	17,979
o2uat	16,485
bunte16ctyroajzxe	15,399
tourismemtl	10,967
mraljmubasher	9,700
incisivemedia	8,056
lesleystg	5,592
superilluwebstagenext	4,923
uscmarshall	4,161
gardastg	4,039
healthweb	3,779
elleweb	3,714
natgeoeustg	3,621
amdcom	3,492
bazaarweb	3,017
southdakotastate	3,006
cobpci01	2,949
schaer	2,931
nasdaq01live	2,926
naturafaq	2,811
guilford	2,712
instyleweb	2,662
ipsos	2,594
nasdaq0202live	2,559
',
    ],
    'layout' => [
      'jan' =>'
agnesianxrxmgjstwy	252
confcatsd8wlfmdd4zkj	5
currentbygedev	8
doctorserkanhjyx379grc	6
doctorserkanrwd7wu2ikp	4
exide	23
exidedev	32
healthwebdev	7
healthwebstg	10
lshtmdev	5
lshtmstg	10
nasdaq01live	455
nasdaq01test	107
nasdaq01uat	25
nasdaqbeta01live	16
nasdaqbeta01test	21
nokiad8	10
scotts01live	4
scotts01test	4
secgovdev	20
secgovstg	5
tatelylecomdev	22
tatelylecomstg	90
uniceforgdev	8
whiskeyhotel	6
',
      'feb' =>'
advauto	11
advautodev	33
agnesiangtcrfvntee	8
agnesianxrxmgjstwy	30
capitalcampdev	20
confcatsd8wvyhiwenaq	13
currentbyge	19
edpmaatdev	11
edpmaatstg	16
ehsf7xujxgbzzm	30
exide	37
lmi3dphuczayrur	10
lshtm	19
nasdaq01live	259
nasdaq01test	43
nasdaq01uat	103
nasdaqbeta01test	6
nokiad8	10
parramattadev	9
scad8	6
secgov	36
tatelylecomdev	50
tatelylecomstg	75
voyaimd8dev	7
wkbelightningdemocr9dfgensq	25
',
      'mar' =>'
advautodev	14
agnesiangtcrfvntee	8
baxteracsf01live	7
baxteracsf01test	14
dk03132017fsa7ngj5vuayv	7
edpmaatstg	32
exide	12
findaevroucxkj2	35
healthweb	6
healthwebpreview	8
hughesstg	11
mosttc4jrmy8qu	6
nasdaq01dev	5
nasdaq01live	427
nasdaq01test	273
nasdaqbeta01live	33
parramatta	12
presales02live	6
scotts01live	20
scotts01test	4
secgov	10
secgovstg	8
swhobio1hvdslx7qc9	4
tatelylecomstg	5
voyaimd8stg	7
',
      'apr' =>'
advautodev	29
advautostg	19
agnesiangtcrfvntee	20
coloradod8mdev	6
currentbyge	18
ehsf7xujxgbzzm	6
hughesstg	33
illuminadev	10
jpa1104liftdemooksascbc63	6
maxonlifterrjqxtnwf	23
medimattyybtmxj79tu	32
mostf4kjjaadp6	6
nasdaq01live	295
nasdaq01test	91
nasdaq0202live	284
nasdaqbeta01live	78
presales02live	11
pupgovmrdev	21
pupgovmrtrcontent	235
scotts01live	15
secgov	14
speedrun	6
twcplatformdnybfwtsnu	11
umnd801dev	7
vigs01dev	21
',
      'may' =>'
baxteracsf01live	96
caitlinliftjdgx6xipzu	24
dr040402017gt8twbw2sz	12
drupalcnckzkliwjwqd	19
earlywarningdev	13
globioo6v4q85jhe	10
hughesstg	35
mostf4kjjaadp6	14
nasdaq01live	134
nasdaq01test	20
nasdaq0202live	240
nasdaq0202test	42
nasdaq0303live	346
nasdaq04live	145
nasdaqbeta01live	25
nasdaqdemoczy78iizw4	12
pupgovmr	29
pupgovmreite	23
pupgovmrstg	337
pupgovmrtrcontent	89
pupgovmrtrdev	21
secgov	27
tb201704bedbathbeyondvzzr7gebpc	27
uniservicescoredev	20
',
    ],
    'workflow' => [
      'jan' => '
netscoutstg	147
smartsourcedotcomdev	108
nasdaq01test	67
netscoutdev	49
nasdaq01uat	40
smartsourcedotcomqa	39
secgovdev	39
nasdaqbeta01test	33
netscout	31
tatelylecomdev	28
amdcomdev	24
princessdemoyfhfstwect	15
nasdaqbeta01live	15
verizonacsf01test	15
incisivemediapreprod	14
ajmubasherpfms3zw7mk	11
tektite	10
verizonacsf01live	9
smartsourcedotcomstg	9
vocatvicgovaubykbomyd3z	9
tektitedev	9
reussirlesmarchesvaxztvqzec	8
acphsqywdkdnlgw	8
d8ixiacomdev2	8
opaag2ndqqykp	8
',

      'feb' => '
gptdotcomstg	503
incisivemediadev	320
tatelylecomstg	265
tatelylecomdev	225
gptdotcomdev	35
incisivemedia	35
scotts01live	33
sygefdkdev	33
ast00101test	25
amdocsdev	23
schwab01dev	22
o2wwwdev	20
vocatvicgovaubykbomyd3z	19
wwfstg	17
kronosd8stg	16
ibmcomstg	14
academyhealth	13
hackeronestg	12
wkbelightningdemocr9dfgensq	12
acphsqywdkdnlgw	11
smartsourcedotcomstg	10
smartsourcedotcomqa	10
nidcrstg	10
nidcrdev	9
yeshiva	9
',

    'mar' => '
netscout	723
utsystemtempstg	442
gptdotcomstg	325
tatelylecomstg	280
gcpapplieddev	266
pctjwbwfcxq68	133
nasdaq01test	111
sevencomdev	104
shmpky4qqk3ve	60
currentbygedev	55
d8ixiacom	52
incisivemedia	50
smartsourcedotcomstg	38
smartsourcedotcomdev	37
baxteracsf01live	36
stevelift3demoqxxulwfsvp	34
mainsitev5rftptvmu	32
travelbelizedev	30
amdcom	30
smartsourcedotcomqa	27
nasdaq01live	25
rutgersbiz1dev	25
rwud8dev	24
comcastchdev	24
swhobio1hvdslx7qc9	19
',

    'apr' => '
pupgovmrdev	670
pupgovmrstg	298
uwstoutdev	154
lsecuritiesstg	93
cobpci01alpha	91
pctjwbwfcxq68	58
tolonewsstg	49
jnjconemead8dev	46
peninsulakurdup74t9	44
mainsitevg68jjh87t	41
mainsite2ftjwyv3nz	36
ymcaseattledev	34
baxteracsf01live	32
incisivemedia	31
acquiaron8ey7lhhuv8z	31
d8ixiacom	29
europexpodev	26
gptdotcomdev	21
pupgovmrtrcontent	21
massgovfeature2	21
gore01live	18
pupgovmrtrdev	16
umnd801dev	16
hackeronedev	16
cobpci01test	16
',

    'may' => '
nyscomptrollerdev	1,756
veteransadvantagedev	257
pupgovmrstg	248
apt1	181
apt1stg	169
naswadev	120
mcormainstg	111
incisivemediadev	96
italiarailtwoddtrfgefvx	92
juilliarddev65dvhvtz9s	90
pupgovmrtrdev	87
pupgovmreite	64
eauclaire	51
oscjaftestskpythramx	47
juilliarddevkhlwq3xtpk	45
gptdotcom	38
snh1wfthjxuubf	37
snh1bnfs3tuctf	36
cbriver	34
pupgovmrsit	33
advautostg	32
advautodev	32
octanneryd7stkgjc3	32
incisivemedia	32
sevencomdev	2
',
      ],
];

}