在R中聚合不同属性的n+m组合数据。

huangapple
go评论108阅读模式
英文:

Aggregating data with n+m combinations of different attributes in R

问题

I have a data table containing information on the volumes for a varying product mix sold to different customers over several years.

在R中聚合不同属性的n+m组合数据。

The code for this Data structure is -

  1. Data <- data.table(Region="USA", Customer=c("ABC","ABC","ABC","DEF","DEF","DEF","ABC","ABC","ABC","XYZ","XYZ"), ProductName=c("Radio123","Radio234","Radio345","Radio123","Radio234","Radio345","BB123","BB234","BB345","Radio123","BB123"),CustomerID=c("123","123","123","125","125","125","123","123","123","124","124"),'2015'=c(0,123,0,0,0,0,4,0,0,0,0),'2016'=c(0,0,0,0,0,0,10093,0,0,234,0),Class=c("R1","R1","R1","R1","R1","R1","R2","R2","R2","R1","R1"))

For the sake of simplicity, I have only kept 2015 and 2016.
As you can see, there are several different types (6) of products listed under the Product Name and the corresponding volumes sold are listed under the Year Number. There is a key to differentiate the customers and that is the Customer ID. You can also see under the Class that the different products essentially fall into two categories, i.e., R1 and R2.

What I would like to do is to aggregate the data on the Customer ID so I can get a total of the volume sold each year to every customer, i.e., have one row only for each customer.

This i can do with the following -

  1. Data[, lapply(.SD, sum, na.rm = TRUE), .SDcols = c("2015", "2016"), by = c("Region", "Customer ID", "Customer")]

However, instead of a simple aggregate, I would like to pick a combination of 3R1+1R2 type and consider that as one unit of volume sold. Eg: If I aggregate for Customer ID = 123, I will get 127 (123 Radio234 + 4 BB123) under column 2015 and 10093 (BB123) under column 2016 etc. Instead I would like to show 1 unit as 3 Radio234 + 1 BB123 of complete pairs (4 under 2015 in this example) and in another column show the remaining incomplete pairs (111 Radio234 under 2015 in this example).

Any idea how this can be achieved in R? Should I use a simple divide function to get a count of complete groups in each Class and then a min to get the value of complete pairs?

I am unsure of how to program it but this is the logical I am thinking of -

  1. Aggregate Class based on Customer ID which gives exactly two rows per Customer, i.e., 1 for volumes sold of type R1 and one for R2.

  2. Divide R1 by 3 to get a count of complete sets that can be formed using R1 and then do a min(R1,R2) to see how many sets we can make. Eg: For 2015, we have 123/3 giving us a dividend of 41 in case of R1 and we see we have 4 R2. Then we do a min(41,4) which gives us the value of complete sets we can make using the formula (3R1+R2), thus giving us 4. We are left with 123-(3x4) = 111 R1 Class products or 37 incomplete pairs (missing R2).

Possible Output -

  1. Data_Agg <- data.table(Region="USA", 
  2.                        Customer=c("ABC","DEF","XYZ"),
  3.                        CustomerID=c("123","125","124"),
  4.                        '2015_complete'= c(4,0,0),
  5.                        '2016_complete'=c(0,0,0),
  6.                        '2015_incomplete'=c("37_R1",0,0),
  7.                        '2016_incomplete'=c("10093_R2",0,"78_R1"))
  8. > Data_Agg
  9. #   Region Customer CustomerID 2015_complete 2016_complete 2015_incomplete 2016_incomplete
  10. #1:    USA      ABC        123             4             0           37_R1        10093_R2
  11. #2:    USA      DEF        125             0             0               0               0
  12. #3:    USA      XYZ        124             0             0               0           78_R1

Or is there a more efficient way to do this? As an extension, I would also like to create an array or key value pair where I can store the Product Name composition of each row aggregate (how many Radio123 or Radio234 or BB123 make up a particular aggregated row).

Thanks for the help!

英文:

I have a data table containing information on the volumes for a varying product mix sold to different customers over several years.

在R中聚合不同属性的n+m组合数据。

The code for this Data structure is -

  1. Data <- data.table(Region="USA", Customer=c("ABC","ABC","ABC","DEF","DEF","DEF","ABC","ABC","ABC","XYZ","XYZ"), ProductName=c("Radio123","Radio234","Radio345","Radio123","Radio234","Radio345","BB123","BB234","BB345","Radio123","BB123"),CustomerID=c("123","123","123","125","125","125","123","123","123","124","124"),'2015'=c(0,123,0,0,0,0,4,0,0,0,0),'2016'=c(0,0,0,0,0,0,10093,0,0,234,0),Class=c("R1","R1","R1","R1","R1","R1","R2","R2","R2","R1","R1"))

For the sake of simplicity, I have only kept 2015 and 2016.
As you can see, there are several different types (6) of products listed under the Product Name and the corresponding volumes sold are listed under the Year Number. There is a key to differentiate the customers and that is the Customer ID. You can also see under the Class that the different products essentially fall into two categories, i.e., R1 and R2.

What I would like to do is to aggregate the data on the Customer ID so I can get a total of the volume sold each year to every customer, i.e., have one row only for each customer.

This i can do with the following -

  1. Data[, lapply(.SD, sum, na.rm = TRUE), .SDcols = c("2015", "2016"), by = c("Region", "Customer ID", "Customer")]

However, instead of a simple aggregate, I would like to pick a combination of 3R1+1R2 type and consider that as one unit of volume sold. Eg: If I aggregate for Customer ID = 123, I will get 127 (123 Radio234 + 4 BB123) under column 2015 and 10093 (BB123) under column 2016 etc. Instead I would like to show 1 unit as 3 Radio234 + 1 BB123 of complete pairs (4 under 2015 in this example) and in another column show the remaining incomplete pairs (111 Radio234 under 2015 in this example).

Any idea how this can be achieved in R? Should I use a simple divide function to get a count of complete groups in each Class and then a min to get the value of complete pairs?

I am unsure of how to program it but this is the logical I am thinking of -

  1. Aggregate Class based on Customer ID which gives exactly two rows per Customer, i.e., 1 for volumes sold of type R1 and one for R2.

  2. Divide R1 by 3 to get a count of complete sets that can be formed using R1 and then do a min(R1,R2) to see how many sets we can make. Eg: For 2015, we have 123/3 giving us a dividend of 41 in case of R1 and we see we have 4 R2. Then we do a min(41,4) which gives us the value of complete sets we can make using the formula (3R1+R2), thus giving us 4. We are left with 123-(3x4) = 111 R1 Class products or 37 incomplete pairs (missing R2).

Possible Output -

  1. Data_Agg <- data.table(Region="USA", 
  2.                        Customer=c("ABC","DEF","XYZ"),
  3.                        CustomerID=c("123","125","124"),
  4.                        '2015_complete'= c(4,0,0),
  5.                        '2016_complete'=c(0,0,0),
  6.                        '2015_incomplete'=c("37_R1",0,0),
  7.                        '2016_incomplete'=c("10093_R2",0,"78_R1"))
  8. > Data_Agg
  9. #   Region Customer CustomerID 2015_complete 2016_complete 2015_incomplete 2016_incomplete
  10. #1:    USA      ABC        123             4             0           37_R1        10093_R2
  11. #2:    USA      DEF        125             0             0               0               0
  12. #3:    USA      XYZ        124             0             0               0           78_R1

Or is there a more efficient way to do this? As an extension, I would also like to create an array or key value pair where I can store the Product Name composition of each row aggregate (how many Radio123 or Radio234 or BB123 make up a particular aggregated row).

Thanks for the help!

答案1

得分: 1

以下是您要翻译的内容：

"Your specific question can be solved in several ways.
However, the first thing I believe you should address is the data shape.
You have column names that contain values ("2015", "2016",...) which should be avoided if possible. I recommend you reading this article by Hadley Wickham about tidy data which is a great guidance in most of the cases. Furthermore, I think the Product Name could be divided into two variables (in case you want to check at Radios or BB-s at some point).

I address the first issue of column names that contain values using data.table's melt function. You can see the end result in the code below."

  1. library(data.table)
  3. Data <-
  4.     data.table(
  5.         Region = "USA",
  6.         Customer = c(
  7.             "ABC",
  8.             "ABC",
  9.             "ABC",
  10.             "DEF",
  11.             "DEF",
  12.             "DEF",
  13.             "ABC",
  14.             "ABC",
  15.             "ABC",
  16.             "XYZ",
  17.             "XYZ"
  18.         ),
  19.         ProductName = c(
  20.             "Radio123",
  21.             "Radio234",
  22.             "Radio345",
  23.             "Radio123",
  24.             "Radio234",
  25.             "Radio345",
  26.             "BB123",
  27.             "BB234",
  28.             "BB345",
  29.             "Radio123",
  30.             "BB123"
  31.         ),
  32.         CustomerID = c(
  33.             "123",
  34.             "123",
  35.             "123",
  36.             "125",
  37.             "125",
  38.             "125",
  39.             "123",
  40.             "123",
  41.             "123",
  42.             "124",
  43.             "124"
  44.         ),
  45.         "2015" = c(0, 123, 0, 0, 0, 0, 4, 0, 0, 0, 0),
  46.         "2016" = c(0, 0, 0, 0, 0, 0, 10093, 0, 0, 234, 0),
  47.         Class = c("R1", "R1", "R1", "R1", "R1", "R1", "R2", "R2", "R2", "R1", "R1")
  48.     )
  49. # initial dataset
  50. head(Data)
  51. #>    Region Customer ProductName CustomerID 2015 2016 Class
  52. #> 1:    USA      ABC    Radio123        123    0    0    R1
  53. #> 2:    USA      ABC    Radio234        123  123    0    R1
  54. #> 3:    USA      ABC    Radio345        123    0    0    R1
  55. #> 4:    USA      DEF    Radio123        125    0    0    R1
  56. #> 5:    USA      DEF    Radio234        125    0    0    R1
  57. #> 6:    USA      DEF    Radio345        125    0    0    R1
  59. # function to find number only elements, obtained from:
  60. # https://stackoverflow.com/questions/43195519/check-if-string-contains-only-numbers-or-only-characters-r/43195554
  61. numbers_only <- function(x)
  62.     ! grepl("\\D", x)
  63. # Get the variables that are not years
  64. id_vars <- names(Data)[!numbers_only(names(Data))]
  66. tidy_data <-
  67.     melt(
  68.         Data,
  69.         id.vars = id_vars,
  70.         variable.name = "year",
  71.         value.name = "volume"
  72.     )
  73. #new format
  74. head(tidy_data)
  75. #>    Region Customer ProductName CustomerID Class year volume
  76. #> 1:    USA      ABC    Radio123        123    R1 2015      0
  77. #> 2:    USA      ABC    Radio234        123    R1 2015    123
  78. #> 3:    USA      ABC    Radio345        123    R1 2015      0
  79. #> 4:    USA      DEF    Radio123        125    R1 2015      0
  80. #> 5:    USA      DEF    Radio234        125    R1 2015      0
  81. #> 6:    USA      DEF    Radio345        125    R1 2015      0
  83. # end result
  84. result <-
  85.     tidy_data[, .(agg_volume = sum(ifelse(Class == "R1", volume, 3 * volume))) ,
  86.               by = c("Region", "Customer", "ProductName", "CustomerID", "year")]
  87. #final result
  88. head(result)
  89. #>    Region Customer ProductName CustomerID year agg_volume
  90. #> 1:    USA      ABC    Radio123        123 2015          0
  91. #> 2:    USA      ABC    Radio234        123 2015        123
  92. #> 3:    USA      ABC    Radio345        123 2015          0
  93. #> 4:    USA      DEF    Radio123        125 2015          0
  94. #> 5:    USA      DEF    Radio234        125 2015          0
  95. #> 6:    USA      DEF    Radio345        125 2015          0

Created on 2020-01-03 by the reprex package (v0.3.0)

P.S. I recommend you using the reprex package to create your examples as it will make your life much easier.

英文:

Your specific question can be solved in several ways.
However, the first thing I believe you should address is the data shape.
You have column names that contain values ("2015", "2016",...) which should be avoided if possible. I recommend you reading this article by Hadley Wickham about tidy data which is a great guidance in most of the cases. Furthermore, I think the Product Name could be divided into two variables (in case you want to check at Radios or BB-s at some point).

I address the first issue of column names that contain values using data.table's melt function. You can see the end result in the code below.

  1. library(data.table)
  3. Data &lt;-
  4.    data.table(
  5.        Region = &quot;USA&quot;,
  6.        Customer = c(
  7.            &quot;ABC&quot;,
  8.            &quot;ABC&quot;,
  9.            &quot;ABC&quot;,
  10.            &quot;DEF&quot;,
  11.            &quot;DEF&quot;,
  12.            &quot;DEF&quot;,
  13.            &quot;ABC&quot;,
  14.            &quot;ABC&quot;,
  15.            &quot;ABC&quot;,
  16.            &quot;XYZ&quot;,
  17.            &quot;XYZ&quot;
  18.        ),
  19.        ProductName = c(
  20.            &quot;Radio123&quot;,
  21.            &quot;Radio234&quot;,
  22.            &quot;Radio345&quot;,
  23.            &quot;Radio123&quot;,
  24.            &quot;Radio234&quot;,
  25.            &quot;Radio345&quot;,
  26.            &quot;BB123&quot;,
  27.            &quot;BB234&quot;,
  28.            &quot;BB345&quot;,
  29.            &quot;Radio123&quot;,
  30.            &quot;BB123&quot;
  31.        ),
  32.        CustomerID = c(
  33.            &quot;123&quot;,
  34.            &quot;123&quot;,
  35.            &quot;123&quot;,
  36.            &quot;125&quot;,
  37.            &quot;125&quot;,
  38.            &quot;125&quot;,
  39.            &quot;123&quot;,
  40.            &quot;123&quot;,
  41.            &quot;123&quot;,
  42.            &quot;124&quot;,
  43.            &quot;124&quot;
  44.        ),
  45.        &quot;2015&quot; = c(0, 123, 0, 0, 0, 0, 4, 0, 0, 0, 0),
  46.        &quot;2016&quot; = c(0, 0, 0, 0, 0, 0, 10093, 0, 0, 234, 0),
  47.        Class = c(&quot;R1&quot;, &quot;R1&quot;, &quot;R1&quot;, &quot;R1&quot;, &quot;R1&quot;, &quot;R1&quot;, &quot;R2&quot;, &quot;R2&quot;, &quot;R2&quot;, &quot;R1&quot;, &quot;R1&quot;)
  48.    )
  49. # initial dataset
  50. head(Data)
  51. #&gt;    Region Customer ProductName CustomerID 2015 2016 Class
  52. #&gt; 1:    USA      ABC    Radio123        123    0    0    R1
  53. #&gt; 2:    USA      ABC    Radio234        123  123    0    R1
  54. #&gt; 3:    USA      ABC    Radio345        123    0    0    R1
  55. #&gt; 4:    USA      DEF    Radio123        125    0    0    R1
  56. #&gt; 5:    USA      DEF    Radio234        125    0    0    R1
  57. #&gt; 6:    USA      DEF    Radio345        125    0    0    R1
  59. # function to find number only elements, obtained from:
  60. # https://stackoverflow.com/questions/43195519/check-if-string-contains-only-numbers-or-only-characters-r/43195554
  61. numbers_only &lt;- function(x)
  62.    ! grepl(&quot;\\D&quot;, x)
  63. # Get the variables that are not years
  64. id_vars &lt;- names(Data)[!numbers_only(names(Data))]
  67. tidy_data &lt;-
  68.    melt(
  69.        Data,
  70.        id.vars = id_vars,
  71.        variable.name = &quot;year&quot;,
  72.        value.name = &quot;volume&quot;
  73.    )
  74. #new format
  75. head(tidy_data)
  76. #&gt;    Region Customer ProductName CustomerID Class year volume
  77. #&gt; 1:    USA      ABC    Radio123        123    R1 2015      0
  78. #&gt; 2:    USA      ABC    Radio234        123    R1 2015    123
  79. #&gt; 3:    USA      ABC    Radio345        123    R1 2015      0
  80. #&gt; 4:    USA      DEF    Radio123        125    R1 2015      0
  81. #&gt; 5:    USA      DEF    Radio234        125    R1 2015      0
  82. #&gt; 6:    USA      DEF    Radio345        125    R1 2015      0
  84. # end result
  85. result &lt;-
  86.    tidy_data[, .(agg_volume = sum(ifelse(Class == &quot;R1&quot;, volume, 3 * volume))) ,
  87.              by = c(&quot;Region&quot;, &quot;Customer&quot;, &quot;ProductName&quot;, &quot;CustomerID&quot;, &quot;year&quot;)]
  88. #final result
  89. head(result)
  90. #&gt;    Region Customer ProductName CustomerID year agg_volume
  91. #&gt; 1:    USA      ABC    Radio123        123 2015          0
  92. #&gt; 2:    USA      ABC    Radio234        123 2015        123
  93. #&gt; 3:    USA      ABC    Radio345        123 2015          0
  94. #&gt; 4:    USA      DEF    Radio123        125 2015          0
  95. #&gt; 5:    USA      DEF    Radio234        125 2015          0
  96. #&gt; 6:    USA      DEF    Radio345        125 2015          0

<sup>Created on 2020-01-03 by the reprex package (v0.3.0)</sup>

P.S. I recommend you using the reprex package to create your examples as it will make your life much easier.

huangapple
  • 本文由 发表于 2020年1月3日 21:36:28
  • 转载请务必保留本文链接：https://go.coder-hub.com/59579558.html
  • data.table
  • r
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定