Comment construire une colonne A, dont les entrées sont égales à la moyenne/médiane/mode des 10 dernières entrées non nulles d'une autre colonne B ?Bonjour,
J'ai une colonne A (environ 15 000 lignes) qui contient des nombres et de nombreuses valeurs nulles (peut-être 70 à 80 % nulles).
Je voudrais construire une colonne B qui, à la ligne i, est égale à la moyenne de : les * 10 dernières valeurs non nulles de A *
--> cela signifie prendre A[row i-1] , A[row i-2], ... et revenir en arrière jusqu'à ce que vous ayez 10 valeurs non nulles (ou si vous atteignez la ligne 1, prenez n'importe quel nombre <= 10 de valeurs non nulles que vous obtenez)
Je voudrais aussi faire en plus de signifier, une médiane et aussi un mode, mais une version lissée du mode où :
Je fais d'abord un lissage gaussien des 10 valeurs non nulles ... et prends l'argmax (mode) de ces distributions.
Merci pour toute idée ou suggestion !
***
Idées de solutions : une pensée que j'ai eue était parce que je veux faire plusieurs calculs différents avec les 10 dernières valeurs non nulles, je devrais d'abord construire une colonne C dont les entrées sont des tableaux égaux à ces 10 dernières valeurs non nulles ... puis je peut faire 3 formules qui opèrent sur la colonne C pour produire B1 (moyenne), B2 (médiale), B3 (mode lissé).
Voici quelques fragments de code évoluant vers la construction de cette colonne C de tableaux :
//step 1 grab the last say 50 entries (some fixed amount that will likely have 10 non-nulls)
For Each Row( dt_join,
to = Row();
If( to - 50 < 0,
from = 1,
from = to - 50); // Quantile( 0.5,
values = :away_1g_pull_seconds[Index( From, To )] < 50, Break());
);
//step 2 convert array to list, drop nulls, convert list of first 10 entries back to array ...
//I don't know how to assign this to a row of C
num_lag = 10;
values = dt_join:A[Index( From, To )] ;
MyList = Remove(as list(values)[1], as list(Loc(as list(values)[1], .)) ); //convert array to list and remove nulls
Mat = Number Col Box("", MyList[1:10]) << get as matrix; //hack convert to array
Tableau de donnéesScriptRe : Comment construire une colonne A, dont les entrées sont égales à la moyenne/médiane/mode des 10 dernières entrées non nulles d'une autre colonne B ?Voici un début pour votre première demande.Cela créera une moyenne pour les 10 dernières valeurs de la colonne A et mettra la moyenne dans la colonne B
Je n'ai pas le temps pour l'instant de travailler plus dessus.Peut-être plus tard ce week-end.
Names Default To Here( 1 );
dt = Current Data Table();
theMatrix = [];
dt << New Column( "B" );
For Each Row(
If( Is Missing( :A ) == 0,
theMatrix = Matrix( :a ) || theMatrix
);
If( Length( theMatrix ) > 10,
theMatrix[1, 11] = []
);
:B = Mean( theMatrix );
);
Re : Comment construire une colonne A, dont les entrées sont égales à la moyenne/médiane/mode des 10 dernières entrées non nulles d'une autre colonne B ?@DonMcCormack cela ferait un bon problème de défi.
Je pense que cela fonctionnera et sera raisonnablement efficace.
nr = 15000;
dt = New Table( "Untitled",
Add Rows( nr ),
New Column( "a", Numeric, "Continuous", Format( "Best", 12 ), Set Values( J( nr, 1, If( Random Uniform() < .3, Random Integer( 0, 1 ), . ) ) ) )
);
lookback = 10;
dt << New Column( "b" || Char( lookback ),
formula(
// make a helper to help look up the non-missing rows
helper = As Constant( // AsConstant is for efficiency
temp = dt << getrowswhere( !Is Missing( a ) );
// temp is the non-missing row numbers. Make sure row 1
// is included, even if missing, because LocSorted will
// return [1] when a smaller value would have been better.
If( temp[1] != 1,
Insert Into( temp, 1, 1 )
);
temp; // this is assigned to 'helper'
);
currentrow = Row(); // this is the target for a new mean, etc
end = Loc Sorted( helper, currentrow )[1]; // end is the last non missing row before or at the target
start = Max( 1, end - lookback + 1 ); // start is the row 'lookback' rows earlier
rowset = helper[start :: end]; // rowset is the set of nonmissing rows, possibly including a missing value at row 1.
// it is OK if a missing value is present; mean/median/mode ignore it.
// grab the rowset from column a and report the statistic...
Mean( dt[rowset, {a}] );// median, mode
)
);
Re : Comment construire une colonne A, dont les entrées sont égales à la moyenne/médiane/mode des 10 dernières entrées non nulles d'une autre colonne B ?Bonjour,
Voici une autre façon de procéder, en utilisant un tableau intermédiaire sans manquants, ce qui permet une formule plus simple.
Names Default To Here( 1 );
dt = Current Data Table();
//get nonmissing rows of table and put them in a new table
m = dt << get rows where( !Is Missing( :a ) );
dt2 = New Table( "xxx", invisible, <<ADD rows=""></ADD>
Lorsque la table d'origine est petite (comme la vôtre... environ 15 000 lignes), les deux approches fonctionnent de la même manière : la première est un peu plus rapide :
Au fur et à mesure que la table d'origine grandit, les échelles d'approche "table séparée" semblent s'adapter sensiblement mieux, en s'exécutant environ 1/10ème du temps. Voici un exemple de ligne de 150 000 :
Un deal-breaker possible (??) est que cette approche ne produit pas de formule dans la table source, vous devrez donc la réexécuter chaque fois que de nouvelles données sont ajoutées.
J'ai joint le fichier de lignes 150K au cas où vous voudriez expérimenter.
Acclamations,
brady
Re : Comment construire une colonne A, dont les entrées sont égales à la moyenne/médiane/mode des 10 dernières entrées non nulles d'une autre colonne B ?Voici quelques résultats supplémentaires. La méthode "séparée" semble évoluer de manière linéaire. La méthode "même table" semble heurter un mur quelque part au nord de 450 000 lignes. Je ne sais pas pourquoi.
Re : Comment construire une colonne A, dont les entrées sont égales à la moyenne/médiane/mode des 10 dernières entrées non nulles d'une autre colonne B ?Je suppose que c'est toujours N^2 pour le helper=AsConstant. Cela copiera un tableau de 100K 400K fois ...
edit : supposition confirmée. Mais même retirer l'assistant de la formule laisse mon code environ deux fois moins rapide que celui de Brady.
Mais... le code de Jim peut être modifié pour être plus rapide...
dt = Current Data Table();
startTime = Tick Seconds();
theMatrix = {., ., ., ., ., ., ., ., ., .};
z = .;
dt << New Column( "B" );
dt << begindataupdate;
For Each Row(
v = :A;
If( v == v,
Insert Into( theMatrix, v );
Remove From( theMatrix, 1 );
z = Mean( theMatrix );
);
:B = z;
);
stopTime = Tick Seconds();
Show( stopTime - startTime );
dt << enddataupdate;Re : Comment construire une colonne A, dont les entrées sont égales à la moyenne/médiane/mode des 10 dernières entrées non nulles d'une autre colonne B ?Merci beaucoup pour toutes vos réponses - je suis époustouflé !
Je n'ai pas eu l'occasion de les lire en détail mais j'y répondrai davantage quand je le ferai.
Meilleur,
Jo
Re : Comment construire une colonne A, dont les entrées sont égales à la moyenne/médiane/mode des 10 dernières entrées non nulles d'une autre colonne B ?C'est vraiment sympa - beaucoup plus élégant que mon approche.
Re : Comment construire une colonne A, dont les entrées sont égales à la moyenne/médiane/mode des 10 dernières entrées non nulles d'une autre colonne B ?Wow ...... cet exercice m'a-t-il appris beaucoup de choses.Mon code original fonctionnait, mais était tellement plus lent que les autres méthodes, que j'ai pris le temps de les étudier.Code très rapide et intelligent.Alors @Craige_Hales a fourni son dernier code, qui est très rapide, mais adopte une approche très proche de l'approche de mon code d'origine.@Craige_Hales le code est beaucoup plus rapide.Et la plupart de la différence est dans mon manque d'utilisation
Begin Data Update;
.....
End Data Update;
J'ai donc maintenant acquis une grande appréciation pour le retard des messages de mise à jour jusqu'à la fin de la mise à jour.
Merci pour le plaisir !
