uio.utility.datasets.pandas

  1"""
  2Data processing with [Pandas](https://pandas.pydata.org) tables.
  3
  4It is important to import this module with an alias, for example:
  5
  6``` py
  7from uio.utility.datasets import pandas as pnd
  8```
  9
 10Otherwise it will collide with the `pandas` module itself.
 11"""
 12
 13import pandas
 14
 15from typing import Optional, List, Union
 16
 17from ..logs.log import logger
 18
 19
 20def mergeTables(frames: List[pandas.DataFrame]) -> pandas.DataFrame:
 21    """
 22    Merge (*concatenate*) several tables into one. Sorts the index
 23    and verifies integrity (*will raise an exception
 24    on duplicate/overlapping index*).
 25
 26    Example:
 27
 28    ``` py
 29    from uio.utility.datasets import pandas as pnd
 30
 31    frames = []
 32    # ...
 33    # frames.append(someTbl1)
 34    # frames.append(someTbl2)
 35    # frames.append(someTbl3)
 36    # ...
 37    tbl = pnd.mergeTables(frames)
 38    #print(tbl.head(15))
 39    ```
 40    """
 41
 42    mergedTable = pandas.concat(
 43        frames,
 44        verify_integrity=True
 45    ).sort_index()
 46
 47    logger.debug(f"Total records in the resulting table: {len(mergedTable)}")
 48    # logger.debug("Preview of the first rows:")
 49    # logger.debug(mergedTable.head(15))
 50
 51    return mergedTable
 52
 53
 54def deduplicateTable(
 55    tbl: pandas.DataFrame,
 56    returnUniques: bool = True
 57) -> pandas.DataFrame:
 58    """
 59    Find duplicate rows in the table:
 60
 61    - with `returnUniques` set to `True`, returns a table without duplicate
 62    rows. The first occurrence is always considered to be unique
 63    in the resulting table, even if it has duplicates;
 64    - with `returnUniques` set to `False` returns only duplicate rows.
 65
 66    Example:
 67
 68    ``` py
 69    import pandas
 70    from uio.utility.datasets import pandas as pnd
 71
 72    tbl = pandas.DataFrame(
 73        [
 74            [1, 2, 3],
 75            [4, 5, 6],
 76            [1, 2, 3],
 77            [7, 8, 9],
 78            [4, 5, 6],
 79            [3, 1, 2],
 80            [4, 5, 6]
 81        ],
 82        index=[1, 2, 3, 4, 5, 6, 7],
 83        columns=["a", "b", "c"]
 84    )
 85
 86    deduplicatedTable = pnd.deduplicateTable(tbl, True)
 87    #print(deduplicatedTable)
 88    #    a  b  c
 89    # 1  1  2  3
 90    # 2  4  5  6
 91    # 4  7  8  9
 92    # 6  3  1  2
 93    #print(deduplicatedTable.index.values)
 94    # [1 2 4 6]
 95
 96    duplicateRows = pnd.deduplicateTable(tbl, False)
 97    #print(duplicateRows)
 98    #    a  b  c
 99    # 3  1  2  3
100    # 5  4  5  6
101    # 7  4  5  6
102    #print(duplicateRows.index.values)
103    # [3 5 7]
104    ```
105    """
106    duplicateRows = tbl.duplicated()
107    duplicateRows = duplicateRows[duplicateRows].index
108
109    logger.debug(f"Unique rows count: {len(tbl) - len(duplicateRows)}")
110    logger.debug(f"Duplicate rows count: {len(duplicateRows)}")
111    # logger.debug(f"Indexes of duplicate rows: {duplicateRows}")
112
113    duplicatesIndex = tbl.index.isin(duplicateRows)
114
115    return (
116        tbl[~duplicatesIndex] if returnUniques
117        else tbl[duplicatesIndex]
118    )
119
120
121def dropMeaninglessRows(
122    tbl: pandas.DataFrame,
123    # the types in Union can be extended with whatever else is supported
124    indicatorColumn: Optional[Union[str, int, float]] = None
125) -> pandas.DataFrame:
126    """
127    Drop/remove meaningless rows from the table. For instance, if a table
128    represents some event timeline, in which there are periods
129    of activity/changes with periods of no changes between them, then it might
130    make sense to drop those rows.
131
132    At the same time, the meaningless rows just before and right after
133    meaningful rows should are not dropped/removed, as they show points of time
134    when activity started/ended. The first row is always kept and is used
135    as a baseline for comparison.
136
137    If `indicatorColumn` is provided, than that cell value from the baseline
138    row is used to determine meaningless rows. Otherwise, the entire rows
139    will be compared with the baseline one.
140
141    Example:
142
143    ``` py
144    import pandas
145    from uio.utility.datasets import pandas as pnd
146
147    tbl = pandas.DataFrame(
148        {
149            "a": [0, 0, 23, 2,  0, 0, 0, 19, 1,  0, 0],
150            "b": [0, 0, 2,  3,  0, 0, 0, 4,  3,  0, 0],
151            "c": [0, 0, 65, 34, 0, 0, 0, 45, 2,  0, 0],
152            "d": [0, 0, 33, 7,  0, 0, 0, 64, 33, 0, 0],
153            "e": [0, 0, 0,  45, 0, 0, 0, 12, 11, 0, 0]
154        },
155        index=[4, 21, 30, 57, 59, 62, 71, 80, 81, 102, 126]
156    )
157    #print(tbl)
158    #       a  b   c   d   e
159    # 4     0  0   0   0   0
160    # 21    0  0   0   0   0
161    # 30   23  2  65  33   0
162    # 57    2  3  34   7  45
163    # 59    0  0   0   0   0
164    # 62    0  0   0   0   0
165    # 71    0  0   0   0   0
166    # 80   19  4  45  64  12
167    # 81    1  3   2  33  11
168    # 102   0  0   0   0   0
169    # 126   0  0   0   0   0
170
171    rez = pnd.dropMeaninglessRows(tbl)
172    #rez = pnd.dropMeaninglessRows(tbl, "a")
173    #print(rez)
174    #       a  b   c   d   e
175    # 4     0  0   0   0   0
176    # 21    0  0   0   0   0
177    # 30   23  2  65  33   0
178    # 57    2  3  34   7  45
179    # 59    0  0   0   0   0
180    # 71    0  0   0   0   0
181    # 80   19  4  45  64  12
182    # 81    1  3   2  33  11
183    # 102   0  0   0   0   0
184
185    rez = pnd.dropMeaninglessRows(tbl, "e")
186    #print(rez)
187    #       a  b   c   d   e
188    # 4     0  0   0   0   0
189    # 30   23  2  65  33   0
190    # 57    2  3  34   7  45
191    # 59    0  0   0   0   0
192    # 71    0  0   0   0   0
193    # 80   19  4  45  64  12
194    # 81    1  3   2  33  11
195    # 102   0  0   0   0   0
196    ```
197    """
198    logger.debug(f"Original table:\n{tbl}")
199
200    indexLength = len(tbl.index)
201    rowsToDelete = []
202    if indicatorColumn:  # it is enough to compare just one column values
203        if indicatorColumn not in tbl.columns:
204            raise ValueError(f"Table has no column [{indicatorColumn}]")
205        for i in range(1, indexLength):  # first row is always kept
206            if (
207                tbl.at[tbl.index[i], indicatorColumn]
208                == tbl.at[tbl.index[0], indicatorColumn]
209                and
210                tbl.at[tbl.index[i-1], indicatorColumn]
211                == tbl.at[tbl.index[0], indicatorColumn]
212            ):
213                if i < indexLength - 1:  # not the last row
214                    if (
215                        tbl.at[tbl.index[i+1], indicatorColumn]
216                        == tbl.at[tbl.index[0], indicatorColumn]
217                    ):
218                        rowsToDelete.append(tbl.index[i])
219                else:
220                    rowsToDelete.append(tbl.index[i])
221    else:  # have to compare the entire rows
222        for i in range(1, indexLength):  # first row is always kept
223            if (
224                tbl.iloc[i].equals(tbl.iloc[0])
225                and
226                tbl.iloc[i-1].equals(tbl.iloc[0])
227            ):
228                if i < indexLength - 1:  # not the last row
229                    if tbl.iloc[i+1].equals(tbl.iloc[0]):
230                        rowsToDelete.append(tbl.index[i])
231                else:
232                    rowsToDelete.append(tbl.index[i])
233
234    logger.debug(f"Indexes of the rows to drop: {rowsToDelete}")
235
236    tbl = tbl.drop(index=rowsToDelete)
237
238    logger.debug(f"Table without meaningless rows:\n{tbl}")
239
240    return tbl