o ;Diځ@sddlmZmZmZddlZddlZddlmZmZddl m Z m Z ddl m Z ddlmZmZmZddlmZddlmZdd lmZmZmZmZmZmZd d Zd d Z  d5ddZee_GdddeZ   d6ddZ!e!e_!   d6ddZ"e"e_"   d6ddZ#e#e_#  d7ddZ$ddZ%e%e_%GdddeZ&d d!Z'  d8d"d#Z(e(e_(Gd$d%d%eZ)d&d'Z*   d6d(d)Z+e+e_+Gd*d+d+eZ,  d9d,d-Z-  d:d/d0Z.Gd1d2d2eZ/e.e_.Gd3d4d4eZ0dS);)absolute_importdivisionprint_functionN) Comparablecomparable_itemgetter)next text_type) ArgumentError)cutcutoutstack)distinct)sort)Table asindicesdataheader rowgetter rowgroupbycsjt|}ttt|}t|}ttt|fdd|D}t|dks)Jdt|dkr3|d}|S)Ncsg|]}|vr|qSr.0frfldsrM/var/www/Datamplify/venv/lib/python3.10/site-packages/petl/transform/joins.py znatural_key..rzno fields in common)rlistmapstrlen)leftrightlhdrlfldsrhdrkeyrrr natural_keys r)cCs||ur|urdurnn t||}}||fS|dur4||ur*dur4nn|}}||fS|durE|durE|durE ||fStd)Nzbad key arguments: either specify key, or specify both lkey and rkey, or provide no key/lkey/rkey arguments at all (natural join))r)r )r#r$r(lkeyrkeyrrrkeys_from_argss"  r,FTc Cs0t|||||\}}t||||||||| | d S)a> Perform an equi-join on the given tables. E.g.:: >>> import petl as etl >>> table1 = [['id', 'colour'], ... [1, 'blue'], ... [2, 'red'], ... [3, 'purple']] >>> table2 = [['id', 'shape'], ... [1, 'circle'], ... [3, 'square'], ... [4, 'ellipse']] >>> table3 = etl.join(table1, table2, key='id') >>> table3 +----+----------+----------+ | id | colour | shape | +====+==========+==========+ | 1 | 'blue' | 'circle' | +----+----------+----------+ | 3 | 'purple' | 'square' | +----+----------+----------+ >>> # if no key is given, a natural join is tried ... table4 = etl.join(table1, table2) >>> table4 +----+----------+----------+ | id | colour | shape | +====+==========+==========+ | 1 | 'blue' | 'circle' | +----+----------+----------+ | 3 | 'purple' | 'square' | +----+----------+----------+ >>> # note behaviour if the key is not unique in either or both tables ... table5 = [['id', 'colour'], ... [1, 'blue'], ... [1, 'red'], ... [2, 'purple']] >>> table6 = [['id', 'shape'], ... [1, 'circle'], ... [1, 'square'], ... [2, 'ellipse']] >>> table7 = etl.join(table5, table6, key='id') >>> table7 +----+----------+-----------+ | id | colour | shape | +====+==========+===========+ | 1 | 'blue' | 'circle' | +----+----------+-----------+ | 1 | 'blue' | 'square' | +----+----------+-----------+ | 1 | 'red' | 'circle' | +----+----------+-----------+ | 1 | 'red' | 'square' | +----+----------+-----------+ | 2 | 'purple' | 'ellipse' | +----+----------+-----------+ >>> # compound keys are supported ... table8 = [['id', 'time', 'height'], ... [1, 1, 12.3], ... [1, 2, 34.5], ... [2, 1, 56.7]] >>> table9 = [['id', 'time', 'weight'], ... [1, 2, 4.5], ... [2, 1, 6.7], ... [2, 2, 8.9]] >>> table10 = etl.join(table8, table9, key=['id', 'time']) >>> table10 +----+------+--------+--------+ | id | time | height | weight | +====+======+========+========+ | 1 | 2 | 34.5 | 4.5 | +----+------+--------+--------+ | 2 | 1 | 56.7 | 6.7 | +----+------+--------+--------+ If `presorted` is True, it is assumed that the data are already sorted by the given key, and the `buffersize`, `tempdir` and `cache` arguments are ignored. Otherwise, the data are sorted, see also the discussion of the `buffersize`, `tempdir` and `cache` arguments under the :func:`petl.transform.sorts.sort` function. Left and right tables with different key fields can be handled via the `lkey` and `rkey` arguments. .. versionchanged:: 1.7.16 To ensure correct results for tables with uneven rows, tables will be squared up before joining to ensure correct results. )r*r+ presorted buffersizetempdircachelprefixrprefixr,JoinView) r#r$r(r*r+r-r.r/r0r1r2rrrjoin/s _ r5c@s$eZdZ   dddZddZdS) r4FNTcCsz||_||_t||d|_t||d|_|s,t|j|| | | d|_t|j|| | | d|_||_||_||_| |_ | |_ dSNmissingr.r/r0) r*r+r r#r$r leftouter rightouterr8r1r2)selfr#r$r*r+r-r:r;r8r.r/r0r1r2rrr__init__s    zJoinView.__init__c Cs,t|j|j|j|j|j|j|j|j|j d S)N)r:r;r8r1r2) iterjoinr#r$r*r+r:r;r8r1r2r<rrr__iter__s zJoinView.__iter__) FFFNNNTNN__name__ __module__ __qualname__r=r@rrrrr4s  r4c C6t|||||\}}t|||||dd|||| | | d S)a Perform a left outer join on the given tables. E.g.:: >>> import petl as etl >>> table1 = [['id', 'colour'], ... [1, 'blue'], ... [2, 'red'], ... [3, 'purple']] >>> table2 = [['id', 'shape'], ... [1, 'circle'], ... [3, 'square'], ... [4, 'ellipse']] >>> table3 = etl.leftjoin(table1, table2, key='id') >>> table3 +----+----------+----------+ | id | colour | shape | +====+==========+==========+ | 1 | 'blue' | 'circle' | +----+----------+----------+ | 2 | 'red' | None | +----+----------+----------+ | 3 | 'purple' | 'square' | +----+----------+----------+ If `presorted` is True, it is assumed that the data are already sorted by the given key, and the `buffersize`, `tempdir` and `cache` arguments are ignored. Otherwise, the data are sorted, see also the discussion of the `buffersize`, `tempdir` and `cache` arguments under the :func:`petl.transform.sorts.sort` function. Left and right tables with different key fields can be handled via the `lkey` and `rkey` arguments. .. versionchanged:: 1.7.16 To ensure correct results for tables with uneven rows, tables will be squared up before joining to ensure correct results. TF r*r+r-r:r;r8r.r/r0r1r2r3 r#r$r(r*r+r8r-r.r/r0r1r2rrrleftjoins + rHc CrE)a Perform a right outer join on the given tables. E.g.:: >>> import petl as etl >>> table1 = [['id', 'colour'], ... [1, 'blue'], ... [2, 'red'], ... [3, 'purple']] >>> table2 = [['id', 'shape'], ... [1, 'circle'], ... [3, 'square'], ... [4, 'ellipse']] >>> table3 = etl.rightjoin(table1, table2, key='id') >>> table3 +----+----------+-----------+ | id | colour | shape | +====+==========+===========+ | 1 | 'blue' | 'circle' | +----+----------+-----------+ | 3 | 'purple' | 'square' | +----+----------+-----------+ | 4 | None | 'ellipse' | +----+----------+-----------+ If `presorted` is True, it is assumed that the data are already sorted by the given key, and the `buffersize`, `tempdir` and `cache` arguments are ignored. Otherwise, the data are sorted, see also the discussion of the `buffersize`, `tempdir` and `cache` arguments under the :func:`petl.transform.sorts.sort` function. Left and right tables with different key fields can be handled via the `lkey` and `rkey` arguments. .. versionchanged:: 1.7.16 To ensure correct results for tables with uneven rows, tables will be squared up before joining to ensure correct results. FTrFr3rGrrr rightjoins+ rIc Cs6t|||||\}}t|||||dd|||| | | d S)a Perform a full outer join on the given tables. E.g.:: >>> import petl as etl >>> table1 = [['id', 'colour'], ... [1, 'blue'], ... [2, 'red'], ... [3, 'purple']] >>> table2 = [['id', 'shape'], ... [1, 'circle'], ... [3, 'square'], ... [4, 'ellipse']] >>> table3 = etl.outerjoin(table1, table2, key='id') >>> table3 +----+----------+-----------+ | id | colour | shape | +====+==========+===========+ | 1 | 'blue' | 'circle' | +----+----------+-----------+ | 2 | 'red' | None | +----+----------+-----------+ | 3 | 'purple' | 'square' | +----+----------+-----------+ | 4 | None | 'ellipse' | +----+----------+-----------+ If `presorted` is True, it is assumed that the data are already sorted by the given key, and the `buffersize`, `tempdir` and `cache` arguments are ignored. Otherwise, the data are sorted, see also the discussion of the `buffersize`, `tempdir` and `cache` arguments under the :func:`petl.transform.sorts.sort` function. Left and right tables with different key fields can be handled via the `lkey` and `rkey` arguments. .. versionchanged:: 1.7.16 To ensure correct results for tables with uneven rows, tables will be squared up before joining to ensure correct results. TrFr3rGrrr outerjoins - rJc #st|} t|} t| t| } t|t| |t} t} fddtt| Dtdur=t}n fddD}durR|| n|fdd| Dt |Vfdd}t j | | d}t j | | d}g}g}t dt d}}z[t|\}}t|\}} ||kr|r||dD]}t |Vqt|\}}n3||kr|r|d|D]}t |Vqt|\}}n|||D]}t |Vqt|\}}t|\}}qt yYnw|r||kr||dD]}t |Vq|D]\}}||dD]}t |Vqq|rG||kr1|d|D]}t |Vq(|D]\}}|d|D]}t |Vq<q3dSdS)Ncg|]}|vr|qSrrrirkindrrrhrziterjoin..cg|] }tt|qSrrrr1rrrocrPrrQrr2rrrsrSc3s|dur|D]}t|}|gtt|VqdS|durM|D]%}gt}tD] \}}||||<q3||t|Vq%dSt|}|D]}|D]}t|}||t|VqWqSdSN)rextendr"tuplezip)_lrowgrp_rrowgrplrowoutrowrrowliri)r%lkindr8rgetvrOrvindrrjoinrowsws.    ziterjoin..joinrowsr()iterrrrranger"rrrVrW itertoolsgroupbyr StopIteration)r#r$r*r+r:r;r8r1r2litritr'lgetkrgetkouthdrrclgitrgitlrowgrprrowgrplkvalrkvalrowr)r%r`r1r8rarOr2rbrr>Us                 r>cOst|i|S)ae Form the cartesian product of the given tables. E.g.:: >>> import petl as etl >>> table1 = [['id', 'colour'], ... [1, 'blue'], ... [2, 'red']] >>> table2 = [['id', 'shape'], ... [1, 'circle'], ... [3, 'square']] >>> table3 = etl.crossjoin(table1, table2) >>> table3 +----+--------+----+----------+ | id | colour | id | shape | +====+========+====+==========+ | 1 | 'blue' | 1 | 'circle' | +----+--------+----+----------+ | 1 | 'blue' | 3 | 'square' | +----+--------+----+----------+ | 2 | 'red' | 1 | 'circle' | +----+--------+----+----------+ | 2 | 'red' | 3 | 'square' | +----+--------+----+----------+ If `prefix` is `True` then field names in the output table header will be prefixed by the index of the input table. .. versionchanged:: 1.7.16 To ensure correct results for tables with uneven rows, tables will be squared up before joining to ensure correct results. ) CrossJoinView)tableskwargsrrr crossjoins"ryc@eZdZddZddZdS)rvcs4|dd_|dd_fdd|D_dS)NprefixFr8csg|] }t|jdqS)r7)r r8)rsourcer?rrrsz*CrossJoinView.__init__..)getr{r8sources)r<r~rxrr?rr=szCrossJoinView.__init__cCst|j|jSrU) itercrossjoinr~r{r?rrrr@szCrossJoinView.__iter__NrArrrrrv rvc#st}t|D]\}|r|fddt|Dq|t|qt|Vdd|D}tj|D]}t}|D]}||q=t|Vq6dS)Ncs$g|]}tddt|qS)r_rQrrMrrr s$z!itercrossjoin..cSsg|]}t|qSr)r)rsrcrrrrs)r enumeraterVrrWrgproduct)r~r{rnsdatasrcsprodr\rurrrrs   rc Cs,t|||||\}}t||||||||dS)a  Return rows from the `left` table where the key value does not occur in the `right` table. E.g.:: >>> import petl as etl >>> table1 = [['id', 'colour'], ... [0, 'black'], ... [1, 'blue'], ... [2, 'red'], ... [4, 'yellow'], ... [5, 'white']] >>> table2 = [['id', 'shape'], ... [1, 'circle'], ... [3, 'square']] >>> table3 = etl.antijoin(table1, table2, key='id') >>> table3 +----+----------+ | id | colour | +====+==========+ | 0 | 'black' | +----+----------+ | 2 | 'red' | +----+----------+ | 4 | 'yellow' | +----+----------+ | 5 | 'white' | +----+----------+ If `presorted` is True, it is assumed that the data are already sorted by the given key, and the `buffersize`, `tempdir` and `cache` arguments are ignored. Otherwise, the data are sorted, see also the discussion of the `buffersize`, `tempdir` and `cache` arguments under the :func:`petl.transform.sorts.sort` function. Left and right tables with different key fields can be handled via the `lkey` and `rkey` arguments. )r#r$r*r+r-r.r/r0)r, AntiJoinView) r#r$r(r*r+r-r.r/r0rrrantijoins ) rc@s"eZdZ  dddZddZdS) rFNTc CsJ|r ||_||_nt|||||d|_t|||||d|_||_||_dS)Nr9)r#r$rr*r+) r<r#r$r*r+r-r.r/r0rrrr=Js zAntiJoinView.__init__cCst|j|j|j|jSrU) iterantijoinr#r$r*r+r?rrrr@WszAntiJoinView.__iter__)FNNTrArrrrrHs  rccsLt|}t|}t|}t|}t|Vt||}t||} t|} t| } tj|| d} tj|| d} g}tdtd}}z:t| \}}t| \}} ||krd|D]}t|VqUt| \}}n||krot| \}}n t| \}}t| \}}qOtyYnw||kr|D]}t|Vq| D]\}}|D]}t|VqqdS)Nrd) rerrWrrrgrhrri)r#r$r*r+rjrkr%r'r`rOrlrmrorprqrsrtrrurrrr[sL            rc Cs2t|||||\}}t||||||||| | | d S)a Perform a left join, but where the key is not unique in the right-hand table, arbitrarily choose the first row and ignore others. E.g.:: >>> import petl as etl >>> table1 = [['id', 'color', 'cost'], ... [1, 'blue', 12], ... [2, 'red', 8], ... [3, 'purple', 4]] >>> table2 = [['id', 'shape', 'size'], ... [1, 'circle', 'big'], ... [1, 'circle', 'small'], ... [2, 'square', 'tiny'], ... [2, 'square', 'big'], ... [3, 'ellipse', 'small'], ... [3, 'ellipse', 'tiny']] >>> table3 = etl.lookupjoin(table1, table2, key='id') >>> table3 +----+----------+------+-----------+---------+ | id | color | cost | shape | size | +====+==========+======+===========+=========+ | 1 | 'blue' | 12 | 'circle' | 'big' | +----+----------+------+-----------+---------+ | 2 | 'red' | 8 | 'square' | 'tiny' | +----+----------+------+-----------+---------+ | 3 | 'purple' | 4 | 'ellipse' | 'small' | +----+----------+------+-----------+---------+ See also :func:`petl.transform.joins.leftjoin`. .. versionchanged:: 1.7.16 To ensure correct results for tables with uneven rows, tables will be squared up before joining to ensure correct results. )r-r8r.r/r0r1r2)r,LookupJoinViewrGrrr lookupjoins ' rc@s$eZdZ   dddZddZdS) rFNTc Csnt||d|_t||d|_|s&t|j|||| d|_t|j|||| d|_||_||_||_| |_| |_dSr6) r r#r$rr*r+r8r1r2) r<r#r$r*r+r-r8r.r/r0r1r2rrrr=s   zLookupJoinView.__init__c Cs$t|j|j|j|j|j|j|jdS)N)r8r1r2)iterlookupjoinr#r$r*r+r8r1r2r?rrrr@szLookupJoinView.__iter__)FNNNTNNrArrrrrs  rc#st|}t|}t|} t|} t| |} t| |tj| } tj} fddtt| Dtdur?t| }n fdd| D}durT| | n| fdd| Dt |Vfdd}t j || d}t j || d}g}d\}}zJt|\}}t|\}} ||kr||dD]}t |Vqt|\}}n$||krt|\}}n|||D]}t |Vqt|\}}t|\}}qt yYnw||kr||dD]}t |Vq|D]\}}||dD]}t |VqqdS) NcrKrrrLrNrrrrz"iterlookupjoin..crPrrQrrRrrrrScrPrrQrrTrrrrSc3sx|dur|D]}t|}|gtt|VqdStt|}|D]}t|}||t|Vq'dSrU)rrVr"rWrre)rYrZr[r\r])r8rarbrrrcs   z iterlookupjoin..joinrowsrd)NN)rerroperator itemgetterrfr"rrrVrWrgrhri)r#r$r*r+r8r1r2rjrkr%r'r`rlrmrnrcrorprqrsrtrrrur)r1r8rarOr2rbrrsb                rrrc Csj|dur"|r |}n t|||||d}t|||} t|||} | | fStt||} tt|||} | | fS)a Split a table into two tables by reversing an inner join. E.g.:: >>> import petl as etl >>> # join key is present in the table ... table1 = (('foo', 'bar', 'baz'), ... ('A', 1, 'apple'), ... ('B', 1, 'apple'), ... ('C', 2, 'orange')) >>> table2, table3 = etl.unjoin(table1, 'baz', key='bar') >>> table2 +-----+-----+ | foo | bar | +=====+=====+ | 'A' | 1 | +-----+-----+ | 'B' | 1 | +-----+-----+ | 'C' | 2 | +-----+-----+ >>> table3 +-----+----------+ | bar | baz | +=====+==========+ | 1 | 'apple' | +-----+----------+ | 2 | 'orange' | +-----+----------+ >>> # an integer join key can also be reconstructed ... table4 = (('foo', 'bar'), ... ('A', 'apple'), ... ('B', 'apple'), ... ('C', 'orange')) >>> table5, table6 = etl.unjoin(table4, 'bar') >>> table5 +-----+--------+ | foo | bar_id | +=====+========+ | 'A' | 1 | +-----+--------+ | 'B' | 1 | +-----+--------+ | 'C' | 2 | +-----+--------+ >>> table6 +----+----------+ | id | bar | +====+==========+ | 1 | 'apple' | +----+----------+ | 2 | 'orange' | +----+----------+ The `autoincrement` parameter controls how an integer join key is reconstructed, and should be a tuple of (`start`, `step`). Nr9)r ConvertToIncrementingCounterViewEnumerateDistinctViewr r r ) tablevaluer( autoincrementr-r.r/r0 tbl_sortedr#r$rrrunjoin:s? rc@rz)rcC||_||_||_dSrUrrrr<tblrrrrrr= z)ConvertToIncrementingCounterView.__init__ccst|j}t|}t|g|}|j}||}t|}d|||<t|V|j \}}t t ||D]\} \} } | D]} t| } | ||| |<t| Vqryrvrrrrrrrrrrrrrrsx      e 2 3 4 z%  /9 . ] W