'separate data in groups based on column values
There is a data table with ~1000 rows.
| Item ID | Location | Position |
|---|---|---|
| 1 | China | left |
| 2 | US | center |
| 3 | Brazil | right |
| 4 | US | left |
| 5 | France | right |
| 6 | France | center |
<...>
I need to break this table into groups, of 10 rows, in such a way, that each subtable contains approximately the same N of items from each 'Location' and of each 'Position' as others. For example, if there are 300 items from France, and 399 items from US, each group will contain 3 items from France, and 4 items from US, but one group will be with 3 US items only + other Locations accordingly. At the same time subtables should have a balance number from each 'Position'.
For example
Table 1
| Item ID | Location | Position |
|---|---|---|
| 5 | France | right |
| 6 | France | center |
| 2 | US | center |
| 4 | US | left |
| 3 | Brazil | right |
| 1 | China | left |
| 7 | China | center |
| 8 | China | left |
| 9 | China | right |
| 10 | UK | left |
Table 2
| Item ID | Location | Position |
|---|---|---|
| 500 | France | left |
| 521 | France | center |
| 643 | US | center |
| 702 | US | left |
| 705 | India | left |
| 765 | China | right |
| 821 | China | right |
| 832 | China | left |
| 976 | Germany | left |
| 977 | UK | right |
and so on.
So notice, that each group has the same number of items from France, almost the same from China, others are in minority, since there are not that many of them, and each table has approximately the same N of right/left/center
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
