analyse sommaire pour les données dynamiquesJ'ai besoin de résumer un ensemble de données complexe. L'ensemble de données contient un tas d'éléments qui ont été traités au cours des 2 dernières semaines et comment ils ont fonctionné. Afin d'évaluer les performances, nous avons une évaluation pour différentes catégories. Les catégories vont de XZ001 à XZ100. catégories pour chaque catégorie principale. Par exemple - XZ008 a des sous-catégories YZ801 à YZ809.
L'ensemble de données est dynamique, ce qui signifie que l'ensemble de données d'une semaine peut avoir YZ 899, mais pas les autres semaines.
J'ai certaines catégories et sous-catégories classées dans bucket1 et bucket2.Toutes les autres catégories feraient partie de bucket3.
Voici le script que j'ai essayé d'écrire mais ne semble pas fonctionner.
Toute aide appréciée.
cdt = open("C:\Documents\Data\out.csv");
cdt=current data table();
sum_lst2 = {};
sum_lst3 = {};
sum_lst4 = {};
col_lst2 = cdt << get column names( Numeric );
For( i = 1, i <= N Items( col_lst2 ), i++,
colname = Column( col_lst2[i] ) << get name;
If( Contains( {"XZ008","XZ010","XZ015","XZ053","YZ1374","YZ1375","YZ9429","YZ9792","YZ9908", "YZ1930","YZ1933","YZ2744","YZ4621","YZ4625","YZ4630","YZ4640","YZ4681","YZ4830"}, colname ) |(colname >= "YZ901" & colname <= "YZ916") |(colname >= "YZ954" & colname <= "YZ965") |(colname >= "YZ1360" & colname <= "YZ1371") |(colname >= "YZ2581" & colname <= "YZ2586") | (colname >= "YZ2900" & colname <= "YZ2950") | (colname >= "YZ3400" & colname <= "YZ3450"),
Insert Into( sum_lst2, col_lst2[i] )
); //Bucket1
If( Contains( {"XZ098","XZ090"}, colname ) | (colname >= "YZ9700" & colname <= "YZ9791") | (colname >= "YZ9793" & colname <= "YZ9799"),
Insert Into( sum_lst4, col_lst2[i] )
);
);//Bucket2
If(!Contains( {"XZ008","XZ010","XZ015","XZ053","XZ098","XZ090","YZ1374","YZ1375","YZ9429","YZ9792","YZ9908", "YZ1930","YZ1933","YZ2744","YZ4621","YZ4625","YZ4630","YZ4640","YZ4681","YZ4830"}, colname ) |(colname >= "YZ901" & colname <= "YZ916") |(colname >= "YZ954" & colname <= "YZ965") |(colname >= "YZ1360" & colname <= "YZ1371") |(colname >= "YZ2581" & colname <= "YZ2586") | (colname >= "YZ2900" & colname <= "YZ2950") | (colname >= "YZ3400" & colname <= "YZ3450")| (colname >= "YZ9700" & colname <= "YZ9791") | (colname >= "YZ9793" & colname <= "YZ9799"),
Insert Into( sum_lst3, col_lst2[i] )
); //Bucket3
Eval( Eval Expr( cdt << New Column( "Bucket1", formula( Sum( Expr( sum_lst2 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "Bucket3", formula( Sum( Expr( sum_lst3 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "Bucket2", formula( Sum( Expr( sum_lst4 ) ) ) ) ) );
Exemple de jeu de données :
| Article |
YZ0100 |
YZ0801 |
YZ0805 |
YZ0806 |
YZ0807 |
YZ0813 |
YZ0814 |
YZ0815 |
YZ0817 |
YZ0871 |
YZ0874 |
YZ0881 |
YZ1367 |
YZ1374 |
YZ1541 |
YZ1571 |
YZ1573 |
YZ1575 |
YZ1577 |
YZ1583 |
YZ1585 |
YZ1590 |
YZ1591 |
YZ1594 |
YZ1933 |
YZ2701 |
YZ2708 |
YZ2737 |
YZ2740 |
YZ2744 |
YZ2745 |
YZ2749 |
YZ2750 |
YZ3404 |
YZ3407 |
YZ3415 |
YZ3416 |
YZ3417 |
YZ3418 |
YZ3422 |
YZ3423 |
YZ3492 |
YZ3493 |
YZ3502 |
YZ3534 |
YZ4621 |
YZ4625 |
YZ4821 |
YZ9031 |
YZ9429 |
YZ9901 |
YZ9908 |
XZ001 |
XZ008 |
XZ013 |
XZ015 |
XZ019 |
XZ027 |
XZ034 |
XZ035 |
XZ046 |
XZ048 |
XZ090 |
XZ094 |
XZ099 |
| AAA666 |
455 |
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
1 |
5 |
|
1 |
|
|
1 |
|
|
|
1 |
|
|
|
|
|
5 |
3 |
1 |
|
|
|
|
22 |
455 |
2 |
|
|
1 |
7 |
2 |
5 |
4 |
|
|
|
22 |
| AAA755 |
118 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6 |
118 |
|
|
|
|
|
|
|
|
|
|
|
6 |
| AAA757 |
324 |
1 |
|
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
7 |
1 |
|
|
|
33 |
|
3 |
|
|
|
|
1 |
|
3 |
1 |
|
|
1 |
|
3 |
|
1 |
1 |
|
1 |
|
8 |
324 |
2 |
|
|
7 |
37 |
6 |
3 |
1 |
1 |
|
1 |
8 |
| BBA696 |
480 |
|
|
|
|
|
|
|
|
1 |
|
3 |
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
1 |
|
|
1 |
|
|
|
|
|
|
|
|
|
5 |
|
3 |
1 |
|
|
|
100 |
480 |
4 |
|
|
1 |
1 |
1 |
5 |
3 |
1 |
|
|
100 |
| BBA786 |
155 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
1 |
|
|
|
|
|
1 |
155 |
|
|
|
|
1 |
1 |
|
1 |
|
|
|
1 |
| BBA787 |
197 |
|
|
|
|
|
|
|
|
1 |
|
|
|
|
1 |
|
1 |
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
|
|
|
7 |
|
1 |
1 |
|
|
|
59 |
197 |
1 |
|
2 |
1 |
2 |
|
7 |
1 |
1 |
|
|
59 |
| CCA613 |
233 |
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
1 |
1 |
|
1 |
1 |
|
|
2 |
|
1 |
|
|
|
|
12 |
233 |
1 |
|
|
|
1 |
4 |
2 |
1 |
|
|
|
12 |
| CCA614 |
551 |
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
2 |
1 |
|
|
|
7 |
|
|
|
|
|
|
2 |
|
|
|
1 |
|
4 |
|
4 |
|
|
1 |
|
69 |
551 |
1 |
|
|
1 |
dix |
3 |
4 |
4 |
|
|
1 |
69 |
| CCA642 |
382 |
|
|
|
|
1 |
|
|
|
|
1 |
2 |
|
|
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
2 |
|
|
|
|
2 |
|
1 |
|
4 |
|
|
1 |
4 |
1 |
4 |
|
|
|
|
91 |
382 |
4 |
|
2 |
|
2 |
7 |
5 |
5 |
|
|
|
91 |
| CCA642R |
57 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
|
3 |
57 |
|
|
|
|
|
1 |
|
|
|
|
|
3 |
| CCB613 |
165 |
|
|
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
|
1 |
|
|
1 |
2 |
|
|
2 |
|
|
|
1 |
1 |
1 |
|
1 |
|
1 |
1 |
|
|
|
|
|
52 |
165 |
1 |
|
1 |
|
4 |
6 |
1 |
1 |
|
|
|
52 |
| DDA624 |
113 |
6 |
|
4 |
|
|
|
7 |
2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
16 |
113 |
19 |
|
|
|
1 |
1 |
|
|
|
|
|
16 |
| DDA631 |
561 |
|
|
|
|
|
|
|
|
|
|
|
1 |
1 |
|
5 |
6 |
3 |
|
|
|
3 |
|
|
2 |
|
1 |
1 |
1 |
|
|
3 |
|
|
|
1 |
1 |
1 |
1 |
1 |
1 |
|
|
|
1 |
|
3 |
|
|
1 |
|
30 |
561 |
|
2 |
17 |
2 |
6 |
6 |
1 |
3 |
|
|
1 |
30 |
| DDA634 |
569 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
1 |
|
1 |
|
1 |
1 |
1 |
|
1 |
|
|
|
|
|
|
3 |
|
|
|
1 |
3 |
|
1 |
7 |
1 |
1 |
2 |
|
3 |
|
5 |
|
1 |
|
1 |
8 |
569 |
|
|
8 |
1 |
3 |
16 |
3 |
5 |
|
1 |
|
9 |
| EEE624 |
228 |
|
1 |
1 |
|
|
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3 |
|
|
|
|
|
|
1 |
2 |
2 |
1 |
|
|
|
|
|
|
|
15 |
2 |
54 |
228 |
3 |
|
|
|
3 |
6 |
|
|
|
|
15 |
56 |
| EEE627 |
495 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
12 |
15 |
7 |
|
|
|
3 |
|
|
|
|
1 |
|
|
|
|
1 |
2 |
2 |
1 |
1 |
|
1 |
3 |
5 |
2 |
1 |
|
|
7 |
|
3 |
|
1 |
2 |
|
28 |
495 |
|
|
37 |
|
4 |
16 |
7 |
3 |
|
1 |
2 |
28 |
Tableau de donnéesRe : analyse sommaire pour les données dynamiquesLa seule erreur que j'ai trouvée dans votre code, c'est que votre fermeture ")" pour votre
For( i = 1, i <= N Items( col_lst2 ), i++,
a été placé après le seau 2, au lieu d'après le seau 3.
J'ai apporté quelques modifications mineures supplémentaires, mais le code fonctionne pour autant que je sache
Names Default To Here( 1 );
cdt = Open( "C:\Documents\Data\out.csv" );
Current Data Table( cdt );
sum_lst2 = {};
sum_lst3 = {};
sum_lst4 = {};
col_lst2 = cdt << get column names( Numeric );
For( i = 1, i <= N Items( col_lst2 ), i++,
colname = Column( col_lst2[i] ) << get name;
If(
Contains(
{"XZ008", "XZ010", "XZ015", "XZ053", "YZ1374", "YZ1375", "YZ9429", "YZ9792", "YZ9908", "YZ1930", "YZ1933", "YZ2744",
"YZ4621", "YZ4625", "YZ4630", "YZ4640", "YZ4681", "YZ4830"},
colname
) | (colname >= "YZ901" & colname <= "YZ916") | (colname >= "YZ954" & colname <= "YZ965") | (colname >= "YZ1360" &
colname <= "YZ1371") | (colname >= "YZ2581" & colname <= "YZ2586") | (colname >= "YZ2900" & colname <= "YZ2950") | (
colname >= "YZ3400" & colname <= "YZ3450"),
Insert Into( sum_lst2, col_lst2[i] )
); //Bucket1
If(
Contains( {"XZ098", "XZ090"}, colname ) | (colname >= "YZ9700" & colname <= "YZ9791") | (colname >= "YZ9793" & colname
<= "YZ9799"),
Insert Into( sum_lst4, col_lst2[i] )
);//Bucket2
If(
!Contains(
{"XZ008", "XZ010", "XZ015", "XZ053", "XZ098", "XZ090", "YZ1374", "YZ1375", "YZ9429", "YZ9792", "YZ9908", "YZ1930",
"YZ1933", "YZ2744", "YZ4621", "YZ4625", "YZ4630", "YZ4640", "YZ4681", "YZ4830"},
colname
) | (colname >= "YZ901" & colname <= "YZ916") | (colname >= "YZ954" & colname <= "YZ965") | (colname >= "YZ1360" &
colname <= "YZ1371") | (colname >= "YZ2581" & colname <= "YZ2586") | (colname >= "YZ2900" & colname <= "YZ2950") | (
colname >= "YZ3400" & colname <= "YZ3450") | (colname >= "YZ9700" & colname <= "YZ9791") | (colname >= "YZ9793" &
colname <= "YZ9799"),
Insert Into( sum_lst3, col_lst2[i] )
); //Bucket3
);
Eval( Eval Expr( cdt << New Column( "Bucket1", formula( Sum( Expr( sum_lst2 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "Bucket3", formula( Sum( Expr( sum_lst3 ) ) ) ) ) );
Eval( Eval Expr( cdt << New Column( "Bucket2", formula( Sum( Expr( sum_lst4 ) ) ) ) ) );Re : analyse sommaire pour les données dynamiquesAprès avoir changé la position du barcket, mon code fonctionne, merci.Mais les nombres dans bucket3 sont faux.C'est parce que pour bucket3 les valeurs dans la colonne item sont également ajoutées.Comment puis-je inclure uniquement les colonnes avec un nom de col commençant par XZ et YZ dans le seau 3 ?Re : analyse sommaire pour les données dynamiquesJe ne comprends pas le problème.Les colonnes suivantes sont les colonnes qui sont ajoutées pour le bucket 3.
{YZ0100, YZ0801, YZ0805, YZ0806, YZ0807, YZ0813, YZ0814, YZ0815, YZ0817, YZ0871,
YZ0874, YZ0881, YZ1367, YZ1541, YZ1571, YZ1573, YZ1575, YZ1577, YZ1583, YZ1585,
YZ1590, YZ1591, YZ1594, YZ2701, YZ2708, YZ2737, YZ2740, YZ2745, YZ2749, YZ2750,
YZ3404, YZ3407, YZ3415, YZ3416, YZ3417, YZ3418, YZ3422, YZ3423, YZ3492, YZ3493,
YZ3502, YZ3534, YZ4821, YZ9031, YZ9901, XZ001, XZ013, XZ019, XZ027, XZ034, XZ035,
XZ046, XZ048, XZ094, XZ099}
Laquelle de ces colonnes ne devrait pas être là ?
Re : analyse sommaire pour les données dynamiquesJ'ai une instruction !contains pour bucket3.aucune de ces colonnes ne doit être ajoutée.Toutes les colonnes commençant par "XZ" et "YZ" à l'exception de celles ajoutées dans bucket1 et 2 doivent être ajoutées pour bucket3.Re : analyse sommaire pour les données dynamiquesAh, étant donné cette logique, que j'ai manquée, je pense que la façon dont elle doit être énoncée est la suivante :
If(
Contains(
{"XZ008", "XZ010", "XZ015", "XZ053", "XZ098", "XZ090", "YZ1374", "YZ1375", "YZ9429", "YZ9792", "YZ9908", "YZ1930", "YZ1933", "YZ2744",
"YZ4621", "YZ4625", "YZ4630", "YZ4640", "YZ4681", "YZ4830"},
colname
) == 0,
If(
(colname >= "YZ901" & colname <= "YZ916") | (colname >= "YZ954" & colname <= "YZ965") | (colname >= "YZ1360" & colname <= "YZ1371") | (colname
>= "YZ2581" & colname <= "YZ2586") | (colname >= "YZ2900" & colname <= "YZ2950") | (colname >= "YZ3400" & colname <= "YZ3450") | (colname
>= "YZ9700" & colname <= "YZ9791") | (colname >= "YZ9793" & colname <= "YZ9799"),
Insert Into( sum_lst3, col_lst2[i] )
)
); //Bucket3
