unique count of patients in cell / unique count of patients in corresponding columns

[yusongchenasana]

For clinical trials, I need to create a kind table that the rows represent adverse events, the columns represent treatments patients received. In each cell I want a percentage which is the number of unique patients in the cell (patients with a particular treatment who have the adverse event) divided by the number of the patients in the column (represent the treatment). Can you provide me a pointer how can I write an aggregator for this purpose?

[nicolaskruchten]

If you have a row with "patients without adverse event" as well then you could use the built-in "count as fraction of columns" aggregator, right?

[yusongchenasana]

Hi Nicolas,

Thank you very much for your quick response!!!

You are right if each patient has only incidence of an adverse events and only type of adverse events, but the reality is a patients may have multiple incidences of an adverse events and have many kind of adverse events. Of course I can filter out all the “duplicates” (so that the data will be one patient and one adverse event one record) prior to entering pivotable, but the column total is not the total number of patients, the patients with multiple adverse events will be counted more than once.

To sum up: My data: one patients one incidence of one kind of adverse events one record, and a patient can have multiple kind of adverse events, and for each adverse event a patient may have more than one incidences. What I need: (# of patients with a particular adverse events within a treatment) / (total # of patients within a treatment). Maybe something like "Unique count as fraction of columns".

Yusong Chen

From: Nicolas Kruchten @.> Sent: Wednesday, June 2, 2021 8:48 AM To: nicolaskruchten/pivottable @.> Cc: Yusong Chen @.>; Author @.> Subject: Re: [nicolaskruchten/pivottable] unique count of patients in cell / unique count of patients in corresponding columns (#1276)

If you have a row with "patients without adverse event" as well then you could use the built-in "count as fraction of columns" aggregator, right?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/nicolaskruchten/pivottable/issues/1276#issuecomment-852997644, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AMMMID4IGIKU6EOSRHVKBDLTQYR6VANCNFSM455WCKSA.

[nicolaskruchten]

Gotcha, that is a little bit different. In that case you should be able to look at the source for the "count unique" aggregator and the "count as fraction of column" and combine them to meet your needs. If you look here https://github.com/nicolaskruchten/pivottable/blob/master/src/pivot.coffee#L177 you'll see that there is a general-purpose "fraction of" meta-aggregator that you might be able to reuse.

[yusongchenasana]

Hi Nicolas,

Thank you again for your reply.

I am statistician with some R skills but have not experience with JavaScript at all, but I will try.

Yusong Chen

From: Nicolas Kruchten @.> Sent: Wednesday, June 2, 2021 11:27 AM To: nicolaskruchten/pivottable @.> Cc: Yusong Chen @.>; Author @.> Subject: Re: [nicolaskruchten/pivottable] unique count of patients in cell / unique count of patients in corresponding columns (#1276)

Gotcha, that is a little bit different. In that case you should be able to look at the source for the "count unique" aggregator and the "count as fraction of column" and combine them to meet your needs. If you look here https://github.com/nicolaskruchten/pivottable/blob/master/src/pivot.coffee#L177 you'll see that there is a general-purpose "fraction of" meta-aggregator that you might be able to reuse.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/nicolaskruchten/pivottable/issues/1276#issuecomment-853123353, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AMMMID4ND2M6W7RFBOQNG7TTQZES7ANCNFSM455WCKSA.

[yusongchenasana]

Looks like what I need it to add this line "Count Unique as Fraction of Columns": tpl.fractionOf(tpl.countUnique(), "col", usFmtPct) to your aggregators definition, like this:

aggregators = do (tpl = aggregatorTemplates) -> …… "Count Unique as Fraction of Columns": tpl.fractionOf(tpl.countUnique(), "col", usFmtPct) But I do not know how to do it 😊.

I am able to duplicate your countUnique function in rpivotTable like this:

rpivotTable(ae, aggregators = list( countUnique = htmlwidgets::JS('$.pivotUtilities.aggregators["Count Unique Values"];'), testCountUnique = htmlwidgets::JS( 'function(attributeArray) { var attribute = attributeArray[0];

 return function(data, rowKey, colKey) {
    return {
    uniq: [],
    push: function(record) {
                if(this.uniq.indexOf(record[attribute]) < 0) {
                     this.uniq.push(record[attribute]);
                }
          },
  value: function() { return this.uniq.length; },
  format: function(x) { return x; },
  numInputs: 1
};

}; }'

)))

But I am not able to add the denominator. I am wondering whether you can help me out.

Warm Regards,

Yusong Chen

From: Yusong Chen Sent: Wednesday, June 2, 2021 1:33 PM To: nicolaskruchten/pivottable @.>; nicolaskruchten/pivottable @.> Cc: Author @.***> Subject: RE: [nicolaskruchten/pivottable] unique count of patients in cell / unique count of patients in corresponding columns (#1276)

Hi Nicolas,

Thank you again for your reply.

I am statistician with some R skills but have not experience with JavaScript at all, but I will try.

Yusong Chen

From: Nicolas Kruchten @.**@.>> Sent: Wednesday, June 2, 2021 11:27 AM To: nicolaskruchten/pivottable @.**@.>> Cc: Yusong Chen @.**@.>>; Author @.**@.>> Subject: Re: [nicolaskruchten/pivottable] unique count of patients in cell / unique count of patients in corresponding columns (#1276)

Gotcha, that is a little bit different. In that case you should be able to look at the source for the "count unique" aggregator and the "count as fraction of column" and combine them to meet your needs. If you look here https://github.com/nicolaskruchten/pivottable/blob/master/src/pivot.coffee#L177 you'll see that there is a general-purpose "fraction of" meta-aggregator that you might be able to reuse.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/nicolaskruchten/pivottable/issues/1276#issuecomment-853123353, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AMMMID4ND2M6W7RFBOQNG7TTQZES7ANCNFSM455WCKSA.

[yusongchenasana]

Hi Nicolas,

I have figured it out. See below.

I would like to take this opportunity to thank you for your great contribution. This will make the interaction safety data review in pharmaceutical industry much much easier. Event I did not have any experience with JavaScript and CoffeeScript, after some explorations of the design and code of pivotable.js, I found they are very smart.

Again, thank you!!!

Yusong

rpivotTable(ae, aggregators = list( sum = htmlwidgets::JS('$.pivotUtilities.aggregators["Sum"];'), uniqueID = htmlwidgets::JS( 'function(attr) { var attribute = attr[0];

     var fractionOf = $.pivotUtilities.aggregatorTemplates.fractionOf;
     var countUnique = $.pivotUtilities.aggregatorTemplates.countUnique;
     var numFmt = $.pivotUtilities.numberFormat;
     var usFmtPct = numFmt({digitsAfterDecimal: 1,scaler: 100,  suffix: "%"});
     return fractionOf(countUnique(), "col", usFmtPct)([attribute]);
  }'

)))

From: Yusong Chen Sent: Wednesday, June 2, 2021 9:38 PM To: 'nicolaskruchten/pivottable' @.>; 'nicolaskruchten/pivottable' @.> Cc: 'Author' @.***> Subject: RE: [nicolaskruchten/pivottable] unique count of patients in cell / unique count of patients in corresponding columns (#1276)

Looks like what I need it to add this line "Count Unique as Fraction of Columns": tpl.fractionOf(tpl.countUnique(), "col", usFmtPct) to your aggregators definition, like this:

aggregators = do (tpl = aggregatorTemplates) -> …… "Count Unique as Fraction of Columns": tpl.fractionOf(tpl.countUnique(), "col", usFmtPct) But I do not know how to do it 😊.

I am able to duplicate your countUnique function in rpivotTable like this:

rpivotTable(ae, aggregators = list( countUnique = htmlwidgets::JS('$.pivotUtilities.aggregators["Count Unique Values"];'), testCountUnique = htmlwidgets::JS( 'function(attributeArray) { var attribute = attributeArray[0];

 return function(data, rowKey, colKey) {
    return {
    uniq: [],
    push: function(record) {
                if(this.uniq.indexOf(record[attribute]) < 0) {
                     this.uniq.push(record[attribute]);
                }
          },
  value: function() { return this.uniq.length; },
  format: function(x) { return x; },
  numInputs: 1
};

}; }'

)))

But I am not able to add the denominator. I am wondering whether you can help me out.

Warm Regards,

Yusong Chen

From: Yusong Chen Sent: Wednesday, June 2, 2021 1:33 PM To: nicolaskruchten/pivottable @.**@.>>; nicolaskruchten/pivottable @.**@.>> Cc: Author @.**@.>> Subject: RE: [nicolaskruchten/pivottable] unique count of patients in cell / unique count of patients in corresponding columns (#1276)

Hi Nicolas,

Thank you again for your reply.

I am statistician with some R skills but have not experience with JavaScript at all, but I will try.

Yusong Chen

From: Nicolas Kruchten @.**@.>> Sent: Wednesday, June 2, 2021 11:27 AM To: nicolaskruchten/pivottable @.**@.>> Cc: Yusong Chen @.**@.>>; Author @.**@.>> Subject: Re: [nicolaskruchten/pivottable] unique count of patients in cell / unique count of patients in corresponding columns (#1276)

Gotcha, that is a little bit different. In that case you should be able to look at the source for the "count unique" aggregator and the "count as fraction of column" and combine them to meet your needs. If you look here https://github.com/nicolaskruchten/pivottable/blob/master/src/pivot.coffee#L177 you'll see that there is a general-purpose "fraction of" meta-aggregator that you might be able to reuse.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/nicolaskruchten/pivottable/issues/1276#issuecomment-853123353, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AMMMID4ND2M6W7RFBOQNG7TTQZES7ANCNFSM455WCKSA.

[nicolaskruchten]

That's great, I'm so glad you were able to meet your needs with this library! And great job dipping a toe into the wild world of Javascript/Coffeescript 💪

[yusongchenasana]

Hi Nicolas,

I would like my final output to be “n (xx.x%)”, where n is the count of the unique patients and the xx.x% is the fraction of the column in terms of the unique patients. I am able to get the both numbers but I am not able to combine them into a single string.

I gather that this is not JavaScript issue, rather an issue on how pivotTable uses the return of aggregators, so I am wondering whether you can give me some insights.

Warm regards,

Yusong

rpivotTable(ae, aggregators = list( "n of Patients" = htmlwidgets::JS('$.pivotUtilities.aggregators["Count Unique Values"];'), "% of Patients" = htmlwidgets::JS( 'function(attr) { var attribute = attr[0]; var fractionOf = $.pivotUtilities.aggregatorTemplates.fractionOf; var countUnique = $.pivotUtilities.aggregatorTemplates.countUnique; var numFmt = $.pivotUtilities.numberFormat; var usFmtPct = numFmt({digitsAfterDecimal: 1,scaler: 100, suffix: "%"}); return fractionOf(countUnique(), "col", usFmtPct)([attribute]); }' ), "n (%) of Patients" = htmlwidgets::JS( 'function(attr) { var attribute = attr[0]; var fractionOf = $.pivotUtilities.aggregatorTemplates.fractionOf; var countUnique = $.pivotUtilities.aggregatorTemplates.countUnique; var numFmt = $.pivotUtilities.numberFormat; var usFmtPct = numFmt({digitsAfterDecimal: 1,scaler: 100, suffix: "%"}); var usFmtInt = numFmt({digitsAfterDecimal: 0}); var n = countUnique(usFmtInt)([attribute]); // this seems OK var pct = fractionOf(countUnique(), "col", usFmtPct)([attribute]); // this seems OK

     return n + " (" + pct + ")";     // this line does not work.

  }'

) ))

From: Nicolas Kruchten @.> Sent: Thursday, June 3, 2021 8:56 AM To: nicolaskruchten/pivottable @.> Cc: Yusong Chen @.>; Author @.> Subject: Re: [nicolaskruchten/pivottable] unique count of patients in cell / unique count of patients in corresponding columns (#1276)

That's great, I'm so glad you were able to meet your needs with this library! And great job dipping a toe into the wild world of Javascript/Coffeescript 💪

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/nicolaskruchten/pivottable/issues/1276#issuecomment-853846617, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AMMMID4HTFTGHZE2GVV3KNLTQ53WZANCNFSM455WCKSA.

[yusongchenasana]

Hi Nicolas,

I got.

Again thank you for this great package!!

Yusong

n_pct_str <- ' function(attr) { var attribute = attr[0]; var countUnique = $.pivotUtilities.aggregatorTemplates.countUnique; var count = $.pivotUtilities.aggregatorTemplates.count; var numFmt = $.pivotUtilities.numberFormat; var usFmtPct = numFmt({digitsAfterDecimal: 1,scaler: 100, suffix: "%"}); var usFmtInt = numFmt({digitsAfterDecimal: 0}); var indexOf = [].indexOf || function(item) { for (var i = 0, l = this.length; i < l; i++) { if (i in this && this[i] === item) return i; } return -1; } var slice = [].slice;

var fractionOf = function(wrapped, type, formatter) { if (type == null) { type = "total"; } if (formatter == null) { formatter = usFmtPct; } return function() { var x; x = 1 <= arguments.length ? slice.call(arguments, 0) : []; return function(data, rowKey, colKey) { return { selector: { total: [[], []], row: [rowKey, []], col: [[], colKey] }[type], inner: wrapped.apply(null, x)(data, rowKey, colKey), push: function(record) { return this.inner.push(record); }, format: function(x) {return x;}, value: function() { var num = this.inner.value().toFixed(0); var pct = (100*this.inner.value() / data.getAggregator.apply(data, this.selector).inner.value()).toFixed(1); var res = num + " (" + pct + "%)"; return res; }, numInputs: wrapped.apply(null, x)().numInputs }; }; }; };

return fractionOf(countUnique(), "col", usFmtPct)([attribute]); }'

rpivotTable(data = ae, vals = "USUBJID", aggregatorName = "n (%) of Patients", aggregators = list( "n of Patients" = htmlwidgets::JS('$.pivotUtilities.aggregators["Count Unique Values"];'), "n (%) of Patients" = htmlwidgets::JS(n_pct_str) ) )

From: Yusong Chen Sent: Thursday, June 3, 2021 7:55 AM To: nicolaskruchten/pivottable @.>; nicolaskruchten/pivottable @.> Cc: Author @.***> Subject: RE: [nicolaskruchten/pivottable] unique count of patients in cell / unique count of patients in corresponding columns (#1276)

Hi Nicolas,

I have figured it out. See below.

I would like to take this opportunity to thank you for your great contribution. This will make the interaction safety data review in pharmaceutical industry much much easier. Event I did not have any experience with JavaScript and CoffeeScript, after some explorations of the design and code of pivotable.js, I found they are very smart.

Again, thank you!!!

Yusong

rpivotTable(ae, aggregators = list( sum = htmlwidgets::JS('$.pivotUtilities.aggregators["Sum"];'), uniqueID = htmlwidgets::JS( 'function(attr) { var attribute = attr[0];

     var fractionOf = $.pivotUtilities.aggregatorTemplates.fractionOf;
     var countUnique = $.pivotUtilities.aggregatorTemplates.countUnique;
     var numFmt = $.pivotUtilities.numberFormat;
     var usFmtPct = numFmt({digitsAfterDecimal: 1,scaler: 100,  suffix: "%"});
     return fractionOf(countUnique(), "col", usFmtPct)([attribute]);
  }'

)))

From: Yusong Chen Sent: Wednesday, June 2, 2021 9:38 PM To: 'nicolaskruchten/pivottable' @.>; 'nicolaskruchten/pivottable' @.> Cc: 'Author' @.***> Subject: RE: [nicolaskruchten/pivottable] unique count of patients in cell / unique count of patients in corresponding columns (#1276)

Looks like what I need it to add this line "Count Unique as Fraction of Columns": tpl.fractionOf(tpl.countUnique(), "col", usFmtPct) to your aggregators definition, like this:

aggregators = do (tpl = aggregatorTemplates) -> …… "Count Unique as Fraction of Columns": tpl.fractionOf(tpl.countUnique(), "col", usFmtPct) But I do not know how to do it 😊.

I am able to duplicate your countUnique function in rpivotTable like this:

rpivotTable(ae, aggregators = list( countUnique = htmlwidgets::JS('$.pivotUtilities.aggregators["Count Unique Values"];'), testCountUnique = htmlwidgets::JS( 'function(attributeArray) { var attribute = attributeArray[0];

 return function(data, rowKey, colKey) {
    return {
    uniq: [],
    push: function(record) {
                if(this.uniq.indexOf(record[attribute]) < 0) {
                     this.uniq.push(record[attribute]);
                }
          },
  value: function() { return this.uniq.length; },
  format: function(x) { return x; },
  numInputs: 1
};

}; }'

)))

But I am not able to add the denominator. I am wondering whether you can help me out.

Warm Regards,

Yusong Chen

From: Yusong Chen Sent: Wednesday, June 2, 2021 1:33 PM To: nicolaskruchten/pivottable @.**@.>>; nicolaskruchten/pivottable @.**@.>> Cc: Author @.**@.>> Subject: RE: [nicolaskruchten/pivottable] unique count of patients in cell / unique count of patients in corresponding columns (#1276)

Hi Nicolas,

Thank you again for your reply.

I am statistician with some R skills but have not experience with JavaScript at all, but I will try.

Yusong Chen

From: Nicolas Kruchten @.**@.>> Sent: Wednesday, June 2, 2021 11:27 AM To: nicolaskruchten/pivottable @.**@.>> Cc: Yusong Chen @.**@.>>; Author @.**@.>> Subject: Re: [nicolaskruchten/pivottable] unique count of patients in cell / unique count of patients in corresponding columns (#1276)

Gotcha, that is a little bit different. In that case you should be able to look at the source for the "count unique" aggregator and the "count as fraction of column" and combine them to meet your needs. If you look here https://github.com/nicolaskruchten/pivottable/blob/master/src/pivot.coffee#L177 you'll see that there is a general-purpose "fraction of" meta-aggregator that you might be able to reuse.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/nicolaskruchten/pivottable/issues/1276#issuecomment-853123353, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AMMMID4ND2M6W7RFBOQNG7TTQZES7ANCNFSM455WCKSA.