| 1 |
import json |
| 2 |
from functools import lru_cache |
| 3 |
from pathlib import Path |
| 4 |
from typing import TYPE_CHECKING, Union, Callable, Dict, Optional |
| 5 |
|
| 6 |
if TYPE_CHECKING: |
| 7 |
import pandas as pd |
| 8 |
from azure.kusto.data._models import KustoResultTable, KustoStreamingResultTable |
| 9 |
|
| 10 |
# Alias for dataframe_from_result_table converter type |
| 11 |
Converter = Dict[str, Union[str, Callable[[str, "pd.DataFrame"], "pd.Series"]]] |
| 12 |
|
| 13 |
|
| 14 |
def load_bundled_json(file_name: str) -> Dict: |
| 15 |
filename = Path(__file__).absolute().parent.joinpath(file_name) |
| 16 |
with filename.open("r", encoding="utf-8") as data: |
| 17 |
return json.load(data) |
| 18 |
|
| 19 |
|
| 20 |
@lru_cache(maxsize=1, typed=False) |
| 21 |
def default_dict() -> Converter: |
| 22 |
import pandas as pd |
| 23 |
|
| 24 |
return { |
| 25 |
"string": lambda col, df: df[col].astype(pd.StringDtype()) if hasattr(pd, "StringDType") else df[col], |
| 26 |
"guid": lambda col, df: df[col], |
| 27 |
"uuid": lambda col, df: df[col], |
| 28 |
"uniqueid": lambda col, df: df[col], |
| 29 |
"dynamic": lambda col, df: df[col], |
| 30 |
"bool": lambda col, df: df[col].astype(bool), |
| 31 |
"boolean": lambda col, df: df[col].astype(bool), |
| 32 |
"int": lambda col, df: df[col].astype(pd.Int32Dtype()), |
| 33 |
"int32": lambda col, df: df[col].astype(pd.Int32Dtype()), |
| 34 |
"int64": lambda col, df: df[col].astype(pd.Int64Dtype()), |
| 35 |
"long": lambda col, df: df[col].astype(pd.Int64Dtype()), |
| 36 |
"real": lambda col, df: parse_float(df, col), |
| 37 |
"double": lambda col, df: parse_float(df, col), |
| 38 |
"decimal": lambda col, df: parse_float(df, col), |
| 39 |
"datetime": lambda col, df: parse_datetime(df, col), |
| 40 |
"date": lambda col, df: parse_datetime(df, col), |
| 41 |
"timespan": lambda col, df: df[col].apply(parse_timedelta), |
| 42 |
"time": lambda col, df: df[col].apply(parse_timedelta), |
| 43 |
} |
| 44 |
|
| 45 |
|
| 46 |
# Copyright (c) Microsoft Corporation. |
| 47 |
# Licensed under the MIT License |
| 48 |
|
| 49 |
|
| 50 |
def dataframe_from_result_table( |
| 51 |
table: "Union[KustoResultTable, KustoStreamingResultTable]", |
| 52 |
nullable_bools: bool = False, |
| 53 |
converters_by_type: Optional[Converter] = None, |
| 54 |
converters_by_column_name: Optional[Converter] = None, |
| 55 |
) -> "pd.DataFrame": |
| 56 |
f"""Converts Kusto tables into pandas DataFrame. |
| 57 |
:param azure.kusto.data._models.KustoResultTable table: Table received from the response. |
| 58 |
:param nullable_bools: When True, converts bools that are 'null' from kusto or 'None' from python to pandas.NA. This will be the default in the future. |
| 59 |
:param converters_by_type: If given, converts specified types to corresponding types, else uses {default_dict()}. The dictionary maps from kusto |
| 60 |
datatype (https://learn.microsoft.com/azure/data-explorer/kusto/query/scalar-data-types/) to a lambda that receives a column name and a dataframe and |
| 61 |
returns the converted column or to a string type name. |
| 62 |
:param converters_by_column_name: If given, converts specified columns to corresponding types, else uses converters_by_type. The dictionary maps from column |
| 63 |
name to a lambda that receives a column name and a dataframe and returns the converted column. |
| 64 |
:return: pandas DataFrame. |
| 65 |
""" |
| 66 |
import pandas as pd |
| 67 |
|
| 68 |
if not table: |
| 69 |
raise ValueError() |
| 70 |
|
| 71 |
from azure.kusto.data._models import KustoResultTable, KustoStreamingResultTable |
| 72 |
|
| 73 |
if not isinstance(table, KustoResultTable) and not isinstance(table, KustoStreamingResultTable): |
| 74 |
raise TypeError("Expected KustoResultTable or KustoStreamingResultTable got {}".format(type(table).__name__)) |
| 75 |
|
| 76 |
columns = [col.column_name for col in table.columns] |
| 77 |
frame = pd.DataFrame(table.raw_rows, columns=columns) |
| 78 |
default = default_dict() |
| 79 |
|
| 80 |
for col in table.columns: |
| 81 |
column_name = col.column_name |
| 82 |
column_type = col.column_type |
| 83 |
if converters_by_column_name and column_name in converters_by_column_name: |
| 84 |
converter = converters_by_column_name.get(column_name) |
| 85 |
elif converters_by_type and column_type in converters_by_type: |
| 86 |
converter = converters_by_type.get(column_type) |
| 87 |
elif nullable_bools and column_type == "bool": |
| 88 |
converter = lambda col, df: df[col].astype(pd.BooleanDtype()) |
| 89 |
else: |
| 90 |
converter = default.get(column_type) |
| 91 |
if converter is None: |
| 92 |
raise Exception("Unexpected type " + column_type) |
| 93 |
if isinstance(converter, str): |
| 94 |
frame[column_name] = frame[column_name].astype(converter) |
| 95 |
else: |
| 96 |
frame[column_name] = converter(column_name, frame) |
| 97 |
|
| 98 |
return frame |
| 99 |
|
| 100 |
|
| 101 |
def get_string_tail_lower_case(val, length): |
| 102 |
if length <= 0: |
| 103 |
return "" |
| 104 |
|
| 105 |
if length >= len(val): |
| 106 |
return val.lower() |
| 107 |
|
| 108 |
return val[len(val) - length :].lower() |
| 109 |
|
| 110 |
|
| 111 |
# TODO When moving to pandas 2 only - change to the appropriate type |
| 112 |
def parse_float(frame, col): |
| 113 |
import numpy as np |
| 114 |
import pandas as pd |
| 115 |
|
| 116 |
frame[col] = frame[col].replace("NaN", np.nan).replace("Infinity", np.inf).replace("-Infinity", -np.inf) |
| 117 |
frame[col] = pd.to_numeric(frame[col], errors="coerce").astype(pd.Float64Dtype()) |
| 118 |
return frame[col] |
| 119 |
|
| 120 |
|
| 121 |
def parse_datetime(frame, col): |
| 122 |
# Pandas before version 2 doesn't support the "format" arg |
| 123 |
import pandas as pd |
| 124 |
|
| 125 |
args = {} |
| 126 |
if pd.__version__.startswith("2."): |
| 127 |
args = {"format": "ISO8601", "utc": True} |
| 128 |
else: |
| 129 |
# if frame contains ".", replace "Z" with ".000Z" |
| 130 |
# == False is not a mistake - that's the pandas way to do it |
| 131 |
contains_dot = frame[col].str.contains(".") |
| 132 |
frame.loc[contains_dot == False, col] = frame.loc[contains_dot == False, col].str.replace("Z", ".000Z") |
| 133 |
frame[col] = pd.to_datetime(frame[col], errors="coerce", **args) |
| 134 |
return frame[col] |
| 135 |
|
| 136 |
|
| 137 |
def parse_timedelta(raw_value: Union[int, float, str]) -> "pd.Timedelta": |
| 138 |
""" |
| 139 |
Transform a raw python value to a pandas timedelta. |
| 140 |
""" |
| 141 |
import pandas as pd |
| 142 |
|
| 143 |
if isinstance(raw_value, (int, float)): |
| 144 |
# https://docs.microsoft.com/en-us/dotnet/api/system.datetime.ticks |
| 145 |
# Kusto saves up to ticks, 1 tick == 100 nanoseconds |
| 146 |
return pd.to_timedelta(raw_value * 100, unit="ns") |
| 147 |
if isinstance(raw_value, str): |
| 148 |
# The timespan format Kusto returns is 'd.hh:mm:ss.ssssss' or 'hh:mm:ss.ssssss' or 'hh:mm:ss' |
| 149 |
# Pandas expects 'd days hh:mm:ss.ssssss' or 'hh:mm:ss.ssssss' or 'hh:mm:ss' |
| 150 |
parts = raw_value.split(":") |
| 151 |
if "." not in parts[0]: |
| 152 |
return pd.to_timedelta(raw_value) |
| 153 |
else: |
| 154 |
formatted_value = raw_value.replace(".", " days ", 1) |
| 155 |
return pd.to_timedelta(formatted_value) |