( cudf.Series([None, 123, None, 1], dtype="uint32"), pd.Series([None, 123, None, 1], dtype=pd.UInt32Dtype()), ), ( cudf.Series([234, 2323, 23432, None, None, 224], dtype="uint64"), pd.Series([234, 2323, 23432, None, None, 224], dtype=pd.UInt64Dtype()), ), ( cudf.Series([-10, 1, None, -1, None, 3], dtype="int8"), pd.Series([-10, 1, None, -1, None, 3], dtype=pd.Int8Dtype()), ), ( cudf.Series([111, None, 222, None, 13], dtype="int16"), pd.Series([111, None, 222, None, 13], dtype=pd.Int16Dtype()), ), ( cudf.Series([11, None, 22, 33, None, 2, None, 3], dtype="int32"), pd.Series([11, None, 22, 33, None, 2, None, 3], dtype=pd.Int32Dtype()), ), ( cudf.Series([32431, None, None, 32322, 0, 10, -32324, None], dtype="int64"), pd.Series( [32431, None, None, 32322, 0, 10, -32324, None], dtype=pd.Int64Dtype(), ), ), (
np.uint64: pa.uint64(), np.uint32: pa.uint32(), np.uint16: pa.uint16(), np.uint8: pa.uint8(), np.datetime64: pa.date64(), np.object_: pa.string(), np.str_: pa.string(), } cudf_dtypes_to_pandas_dtypes = { np.dtype("uint8"): pd.UInt8Dtype(), np.dtype("uint16"): pd.UInt16Dtype(), np.dtype("uint32"): pd.UInt32Dtype(), np.dtype("uint64"): pd.UInt64Dtype(), np.dtype("int8"): pd.Int8Dtype(), np.dtype("int16"): pd.Int16Dtype(), np.dtype("int32"): pd.Int32Dtype(), np.dtype("int64"): pd.Int64Dtype(), np.dtype("bool_"): pd.BooleanDtype(), np.dtype("object"): pd.StringDtype(), } SIGNED_INTEGER_TYPES = {"int8", "int16", "int32", "int64"} UNSIGNED_TYPES = {"uint8", "uint16", "uint32", "uint64"} INTEGER_TYPES = SIGNED_INTEGER_TYPES | UNSIGNED_TYPES FLOAT_TYPES = {"float32", "float64"} SIGNED_TYPES = SIGNED_INTEGER_TYPES | FLOAT_TYPES NUMERIC_TYPES = SIGNED_TYPES | UNSIGNED_TYPES DATETIME_TYPES = { "datetime64[s]", "datetime64[ms]",
from dask_sql._compat import FLOAT_NAN_IMPLEMENTED from dask_sql.java import SqlTypeName logger = logging.getLogger(__name__) # Default mapping between python types and SQL types _PYTHON_TO_SQL = { np.float64: SqlTypeName.DOUBLE, np.float32: SqlTypeName.FLOAT, np.int64: SqlTypeName.BIGINT, pd.Int64Dtype(): SqlTypeName.BIGINT, np.int32: SqlTypeName.INTEGER, pd.Int32Dtype(): SqlTypeName.INTEGER, np.int16: SqlTypeName.SMALLINT, pd.Int16Dtype(): SqlTypeName.SMALLINT, np.int8: SqlTypeName.TINYINT, pd.Int8Dtype(): SqlTypeName.TINYINT, np.uint64: SqlTypeName.BIGINT, pd.UInt64Dtype(): SqlTypeName.BIGINT, np.uint32: SqlTypeName.INTEGER, pd.UInt32Dtype(): SqlTypeName.INTEGER, np.uint16: SqlTypeName.SMALLINT, pd.UInt16Dtype(): SqlTypeName.SMALLINT, np.uint8: SqlTypeName.TINYINT, pd.UInt8Dtype(): SqlTypeName.TINYINT, np.bool8: SqlTypeName.BOOLEAN, pd.BooleanDtype(): SqlTypeName.BOOLEAN, np.object_: SqlTypeName.VARCHAR, pd.StringDtype(): SqlTypeName.VARCHAR, np.datetime64: SqlTypeName.TIMESTAMP,
np.uint64: pa.uint64(), np.uint32: pa.uint32(), np.uint16: pa.uint16(), np.uint8: pa.uint8(), np.datetime64: pa.date64(), np.object_: pa.string(), np.str_: pa.string(), } np_dtypes_to_pandas_dtypes = { np.dtype("uint8"): pd.UInt8Dtype(), np.dtype("uint16"): pd.UInt16Dtype(), np.dtype("uint32"): pd.UInt32Dtype(), np.dtype("uint64"): pd.UInt64Dtype(), np.dtype("int8"): pd.Int8Dtype(), np.dtype("int16"): pd.Int16Dtype(), np.dtype("int32"): pd.Int32Dtype(), np.dtype("int64"): pd.Int64Dtype(), np.dtype("bool_"): pd.BooleanDtype(), np.dtype("object"): pd.StringDtype(), } pyarrow_dtypes_to_pandas_dtypes = { pa.uint8(): pd.UInt8Dtype(), pa.uint16(): pd.UInt16Dtype(), pa.uint32(): pd.UInt32Dtype(), pa.uint64(): pd.UInt64Dtype(), pa.int8(): pd.Int8Dtype(), pa.int16(): pd.Int16Dtype(), pa.int32(): pd.Int32Dtype(), pa.int64(): pd.Int64Dtype(),
def modify_and_save_unformed(df, car_classes_df, cities_df, geo_df, date): """ Transforms and saves to file unformed orders dataframe :param df: Pandas DataFrame with unformed orders :param car_classes_df: Pandas DataFrame with car classes codes and names :param cities_df: Pandas DataFrame with cities id's and names :param geo_df: Pandas DataFrame with geo zones names, their boundary points and city names :param date: date to load in "YYYY-MM-DD" format :return: None, but saving file to local network server "//bigshare/Выгрузки ТФ/Выгрузки My_TK/'year'/'month'" """ df['points'] = df['points'].apply( len) # transitional points list to len of that list df['type_auto'] = df.type_auto.astype(int) df['is_taxo'] = pd.array(df.is_taxo.replace('', np.NaN), dtype=pd.Int8Dtype()) # so I use Int8 df['base_price'] = df['base_price'].fillna(0).astype(int) df['base_price2'] = df['base_price2'].fillna(0).astype(int) # FIXME duct tape for compatibility if 'is_b2' in df.columns: df['is_b2'] = df.is_b2.replace({1.: 'Да'}) df['proc_a_in'] = df.proc_a_in / 100 # FIXME duct tape for compatibility if 'k_jam' in df.columns: df['k_jam'] = df.k_jam.fillna(1.) # Extract car serving time from autos_time df['autos_time'] = df.apply( lambda x: extract_unf_car_time(x.type_auto, x.autos_time), axis=1) # axis ==1 => apply to each row df['autos_time'] = pd.array(df.autos_time, dtype=pd.Int16Dtype()) # Merge with car classes df = df.merge(car_classes_df, left_on='type_auto', right_on='id', how='left') # retrieve car classes names df.drop(columns=['type_auto', 'id'], inplace=True) # cleaning after merge df.rename({'name': 'type_auto'}, axis='columns', inplace=True) # cleaning after merge # Merge with cities names df and drop non-taxi entries df = df.merge(cities_df, left_on='city', right_on='id', how='left', suffixes=('', '_source')) df = df[df.type_source.str.startswith('taxi', na=False)] df.drop(columns=['id', 'type_source', 'city', 'to_local_time_corr'], inplace=True) df.rename(columns={'name': 'city'}, inplace=True) # Add 'Регион' field df['Регион'] = df.city.map(secrets.region_dict) # Separate 'date' column to date and time df['date'] = pd.to_datetime(df.date) new_dates, new_times = zip(*[(d.date(), d.time()) for d in df['date']]) df = df.assign(Дата=new_dates, Время=new_times) df.drop(columns='date', inplace=True) # Drop duplicates! df.drop_duplicates(subset=['Дата', 'Время', 'phone'], ignore_index=True, inplace=True) # Incoming source mapping df['type'] = df.type.map(renaming_dicts.incoming_type) # Get rid of possible bug entries df = df[df.x_in != 0.] # Map geo zones get_zone(df=df, geozone_df=geo_df, mode='in') get_zone(df=df, geozone_df=geo_df, mode='out') # Generate key field: MMDDhhmmss&id (or &phone[-7:] if id == 0) df['Номер_неоформленного'] = np.where( df.id_client == 0, df.Дата.astype(str).str.replace('-', '', regex=True).apply(lambda x: x[-4:]) + df.Время.astype(str).str.replace(':', '', regex=True) + df.phone.astype(str).apply(lambda x: x[-7:]), df.Дата.astype(str).str.replace('-', '', regex=True).apply(lambda x: x[-4:]) + df.Время.astype(str).str.replace(':', '', regex=True) + df.id_client.astype(str)) # Final renaming, dropping and saving df.rename(renaming_dicts.unf, axis='columns', inplace=True) df.drop(columns=[ 'option_1', 'option_2', 'option_3', 'c_auto_all', 'proc_a_in_all', 'id_user' ], inplace=True) df['Статус'] = 'Неоформленный' df = df.replace(r'^\s*$', np.NaN, regex=True) # replace all empty strings with NaNs # df.to_csv(f"data/{date}_неоф.csv", sep=';', index=False) saving_path = tk_u.set_bigshare_dir(date) df.to_csv(f"{saving_path}/{date}_неоф.csv", sep=';', index=False)
"""Semantic representation of a :class:`pandas.Int64Dtype`.""" type = pd.Int64Dtype() bit_width: int = 64 @Engine.register_dtype(equivalents=[pd.Int32Dtype, pd.Int32Dtype()]) @immutable class INT32(INT64): """Semantic representation of a :class:`pandas.Int32Dtype`.""" type = pd.Int32Dtype() bit_width: int = 32 @Engine.register_dtype(equivalents=[pd.Int16Dtype, pd.Int16Dtype()]) @immutable class INT16(INT32): """Semantic representation of a :class:`pandas.Int16Dtype`.""" type = pd.Int16Dtype() bit_width: int = 16 @Engine.register_dtype(equivalents=[pd.Int8Dtype, pd.Int8Dtype()]) @immutable class INT8(INT16): """Semantic representation of a :class:`pandas.Int8Dtype`.""" type = pd.Int8Dtype() bit_width: int = 8
'uint64': (parquet_thrift.Type.INT64, parquet_thrift.ConvertedType.UINT_64, 64), 'float32': (parquet_thrift.Type.FLOAT, None, 32), 'float64': (parquet_thrift.Type.DOUBLE, None, 64), 'float16': (parquet_thrift.Type.FLOAT, None, 16), } revmap = { parquet_thrift.Type.INT32: np.int32, parquet_thrift.Type.INT64: np.int64, parquet_thrift.Type.FLOAT: np.float32, parquet_thrift.Type.DOUBLE: np.float64 } pdoptional_to_numpy_typemap = { pd.Int8Dtype(): np.int8, pd.Int16Dtype(): np.int16, pd.Int32Dtype(): np.int32, pd.Int64Dtype(): np.int64, pd.UInt8Dtype(): np.uint8, pd.UInt16Dtype(): np.uint16, pd.UInt32Dtype(): np.uint32, pd.UInt64Dtype(): np.uint64, pd.BooleanDtype(): np.bool } def find_type(data, fixed_text=None, object_encoding=None, times='int64'): """ Get appropriate typecodes for column dtype Data conversion do not happen here, see convert().
df['create_order_time'] = pd.to_datetime(df['create_order_time']) df['date'] = df['create_order_time'].dt.date df['day'] = df['create_order_time'].dt.day df['hour'] = df['create_order_time'].dt.hour df = pd.merge(df, item, how='left', on='item_id') memory = df.memory_usage().sum() / 1024**2 print('Before memory usage of properties dataframe is :', memory, " MB") dtype_dict = { 'buyer_admin_id': 'int32', 'item_id': 'int32', 'store_id': pd.Int32Dtype(), 'irank': 'int16', 'item_price': pd.Int16Dtype(), 'cate_id': pd.Int16Dtype(), 'is_train': 'int8', 'day': 'int8', 'hour': 'int8', } df = df.astype(dtype_dict) memory = df.memory_usage().sum() / 1024**2 print('After memory usage of properties dataframe is :', memory, " MB") del train, test gc.collect() # Before memory usage of properties dataframe is : 1292.8728713989258 MB # After memory usage of properties dataframe is : 696.1623153686523 MB
from collections import OrderedDict import datetime import numpy as np import pandas as pd from .librdata import Writer from .custom_errors import PyreadrError # configuration int_types = {np.dtype('int32'), np.dtype('int16'), np.dtype('int8'), np.dtype('uint8'), np.dtype('uint16'), np.int32, np.int16, np.int8, np.uint8, np.uint16} int_mixed_types = {pd.Int8Dtype(), pd.Int16Dtype(), pd.Int32Dtype(), pd.UInt8Dtype(), pd.UInt16Dtype()} float_types = {np.dtype('int64'), np.dtype('uint64'), np.dtype('uint32'), np.dtype('float'), np.int64, np.uint64, np.uint32, np.float, pd.Int64Dtype(), pd.UInt32Dtype(), pd.UInt64Dtype()} datetime_types = {datetime.datetime, np.datetime64} pyreadr_to_librdata_types = {"INTEGER": "INTEGER", "NUMERIC": "NUMERIC", "LOGICAL": "LOGICAL", "CHARACTER": "CHARACTER", "OBJECT": "CHARACTER", "DATE": "CHARACTER", "DATETIME":"CHARACTER"} librdata_min_integer = -2147483648 def get_pyreadr_column_types(df): """ From a pandas data frame, get an OrderedDict with column name as key
def general_data_from_search(team, year, playertype="pitcher", date1="", date2="", addid=False): """Gets data from Baseball Savant's search function. This function gets every pitch event in the given time frame. The playertype argument decides whether you get batter or pitcher data. If creating an exhaustive database, note that you can get overlapping data because one teams pitcher data will return the same events of another teams batter data with the only change being the "player_name" field. See 'addid' if interested in adding a unique event id for simple duplicate detection. Args: team (string): Team abbreviation in form 'XXX' year (int): Year number between 2012-2021 in form YYYY playertype (str, optional): Either 'pitcher' or 'batter'. Defaults to "pitcher". date1 (str, optional): The bottom date range to search for. Defaults to empty string. yyyy-mm-dd date2 (str, optional): The top date range to search for. Defaults to empty string. yyyy-mm-dd addid (bool, optional): Adds a custom ID. Defaults False. clean (bool, optional): Clean data for DB. Defaults True. Returns: pandas dataframe: dateframe of every event from search parameters """ url = ( "https://baseballsavant.mlb.com/statcast_search/csv?all=true&hfPT=&hfAB=" f"&hfGT=R%7CPO%7C&hfPR=&hfZ=&stadium=&hfBBL=&hfNewZones=&hfPull=&hfC=&hfSea={year}%7C&hfSit=" f"&player_type={playertype}&hfOuts=&opponent=&pitcher_throws=&batter_stands=&hfSA=&game_date_gt={date1}" f"&game_date_lt={date2}&hfInfield=&team={team}&position=&hfOutfield=&hfRO=&home_road=&hfFlag=&hfBBT=" "&metric_1=&hfInn=&min_pitches=0&min_results=0&group_by=name&sort_col=pitches&player_event_sort=api_p_release_speed" "&sort_order=desc&min_pas=0&type=details&") # Read in the csv and specify certain columns to have nullable int types scrapedData = pandas.read_csv(url, dtype={ 'zone': pandas.Int16Dtype(), 'hit_location': pandas.Int16Dtype(), 'on_1b': pandas.Int32Dtype(), 'on_2b': pandas.Int32Dtype(), 'on_3b': pandas.Int32Dtype(), 'hit_distance_sc': pandas.Int16Dtype(), 'launch_angle': pandas.Int16Dtype(), 'release_spin_rate': pandas.Int32Dtype(), 'launch_speed_angle': pandas.Int16Dtype(), 'spin_axis': pandas.Int32Dtype() }) # Data comes with columns that will always be empty **EVEN IN YEARS WHERE THIS DATA WAS NOT DEPRECATED, ITS ALL DELETED** # Dropping player name, pitcher.1 and fielder_2.1 because they are duplicates of other cols scrapedData.drop([ 'umpire', 'spin_dir', 'spin_rate_deprecated', 'break_angle_deprecated', 'break_length_deprecated', 'tfs_deprecated', 'tfs_zulu_deprecated', 'player_name', 'pitcher.1', 'fielder_2.1' ], axis=1, inplace=True) scrapedData.rename(columns={"type": "type_"}, inplace=True) if (addid): addPitchIds(scrapedData) return scrapedData
np.uint64: pa.uint64(), np.uint32: pa.uint32(), np.uint16: pa.uint16(), np.uint8: pa.uint8(), np.datetime64: pa.date64(), np.object_: pa.string(), np.str_: pa.string(), } cudf_dtypes_to_pandas_dtypes = { cudf.dtype("uint8"): pd.UInt8Dtype(), cudf.dtype("uint16"): pd.UInt16Dtype(), cudf.dtype("uint32"): pd.UInt32Dtype(), cudf.dtype("uint64"): pd.UInt64Dtype(), cudf.dtype("int8"): pd.Int8Dtype(), cudf.dtype("int16"): pd.Int16Dtype(), cudf.dtype("int32"): pd.Int32Dtype(), cudf.dtype("int64"): pd.Int64Dtype(), cudf.dtype("bool_"): pd.BooleanDtype(), cudf.dtype("object"): pd.StringDtype(), } pyarrow_dtypes_to_pandas_dtypes = { pa.uint8(): pd.UInt8Dtype(), pa.uint16(): pd.UInt16Dtype(), pa.uint32(): pd.UInt32Dtype(), pa.uint64(): pd.UInt64Dtype(), pa.int8(): pd.Int8Dtype(), pa.int16(): pd.Int16Dtype(), pa.int32(): pd.Int32Dtype(), pa.int64(): pd.Int64Dtype(),
parquet_thrift.Type.FLOAT: np.dtype('float32'), parquet_thrift.Type.DOUBLE: np.dtype('float64'), parquet_thrift.Type.BOOLEAN: pd.BooleanDtype(), parquet_thrift.Type.INT96: np.dtype('S12'), parquet_thrift.Type.BYTE_ARRAY: np.dtype("O"), parquet_thrift.Type.FIXED_LEN_BYTE_ARRAY: np.dtype("O") } complex = { parquet_thrift.ConvertedType.UTF8: np.dtype("O"), parquet_thrift.ConvertedType.DECIMAL: np.dtype('float64'), parquet_thrift.ConvertedType.UINT_8: pd.UInt8Dtype(), parquet_thrift.ConvertedType.UINT_16: pd.UInt16Dtype(), parquet_thrift.ConvertedType.UINT_32: pd.UInt32Dtype(), parquet_thrift.ConvertedType.UINT_64: pd.UInt64Dtype(), parquet_thrift.ConvertedType.INT_8: pd.Int8Dtype(), parquet_thrift.ConvertedType.INT_16: pd.Int16Dtype(), parquet_thrift.ConvertedType.INT_32: pd.Int32Dtype(), parquet_thrift.ConvertedType.INT_64: pd.Int64Dtype(), parquet_thrift.ConvertedType.TIME_MILLIS: np.dtype('<m8[ns]'), parquet_thrift.ConvertedType.DATE: np.dtype('<M8[ns]'), parquet_thrift.ConvertedType.TIMESTAMP_MILLIS: np.dtype('<M8[ns]'), parquet_thrift.ConvertedType.TIME_MICROS: np.dtype('<m8[ns]'), parquet_thrift.ConvertedType.TIMESTAMP_MICROS: np.dtype('<M8[ns]') } def typemap(se): """Get the final dtype - no actual conversion""" if se.converted_type is None: if se.type in simple: return simple[se.type]
class DataMapping: """ Map primary data between different supported data frameworks, preserving equivalent data types. DataMapping is for primary data, to map metadata types and values use :py:class:`TypeMapping <tracdap.rt.impl.type_system.TypeMapping>` and :py:class:`TypeMapping <tracdap.rt.impl.type_system.MetadataCodec>`. """ __log = _util.logger_for_namespace(_DataInternal.__module__ + ".DataMapping") # Matches TRAC_ARROW_TYPE_MAPPING in ArrowSchema, tracdap-lib-data __TRAC_DECIMAL_PRECISION = 38 __TRAC_DECIMAL_SCALE = 12 __TRAC_TIMESTAMP_UNIT = "ms" __TRAC_TIMESTAMP_ZONE = None __TRAC_TO_ARROW_BASIC_TYPE_MAPPING = { _meta.BasicType.BOOLEAN: pa.bool_(), _meta.BasicType.INTEGER: pa.int64(), _meta.BasicType.FLOAT: pa.float64(), _meta.BasicType.DECIMAL: pa.decimal128(__TRAC_DECIMAL_PRECISION, __TRAC_DECIMAL_SCALE), _meta.BasicType.STRING: pa.utf8(), _meta.BasicType.DATE: pa.date32(), _meta.BasicType.DATETIME: pa.timestamp(__TRAC_TIMESTAMP_UNIT, __TRAC_TIMESTAMP_ZONE) } # Check the Pandas dtypes for handling floats are available before setting up the type mapping __PANDAS_FLOAT_DTYPE_CHECK = _DataInternal.float_dtype_check() __PANDAS_DATETIME_TYPE = pd.to_datetime([]).dtype # Only partial mapping is possible, decimal and temporal dtypes cannot be mapped this way __ARROW_TO_PANDAS_TYPE_MAPPING = { pa.bool_(): pd.BooleanDtype(), pa.int8(): pd.Int8Dtype(), pa.int16(): pd.Int16Dtype(), pa.int32(): pd.Int32Dtype(), pa.int64(): pd.Int64Dtype(), pa.uint8(): pd.UInt8Dtype(), pa.uint16(): pd.UInt16Dtype(), pa.uint32(): pd.UInt32Dtype(), pa.uint64(): pd.UInt64Dtype(), pa.float16(): pd.Float32Dtype(), pa.float32(): pd.Float32Dtype(), pa.float64(): pd.Float64Dtype(), pa.utf8(): pd.StringDtype() } @staticmethod def arrow_to_python_type(arrow_type: pa.DataType) -> type: if pa.types.is_boolean(arrow_type): return bool if pa.types.is_integer(arrow_type): return int if pa.types.is_floating(arrow_type): return float if pa.types.is_decimal(arrow_type): return decimal.Decimal if pa.types.is_string(arrow_type): return str if pa.types.is_date(arrow_type): return dt.date if pa.types.is_timestamp(arrow_type): return dt.datetime raise _ex.ETracInternal( f"No Python type mapping available for Arrow type [{arrow_type}]") @classmethod def python_to_arrow_type(cls, python_type: type) -> pa.DataType: if python_type == bool: return pa.bool_() if python_type == int: return pa.int64() if python_type == float: return pa.float64() if python_type == decimal.Decimal: return pa.decimal128(cls.__TRAC_DECIMAL_PRECISION, cls.__TRAC_DECIMAL_SCALE) if python_type == str: return pa.utf8() if python_type == dt.date: return pa.date32() if python_type == dt.datetime: return pa.timestamp(cls.__TRAC_TIMESTAMP_UNIT, cls.__TRAC_TIMESTAMP_ZONE) raise _ex.ETracInternal( f"No Arrow type mapping available for Python type [{python_type}]") @classmethod def trac_to_arrow_type(cls, trac_type: _meta.TypeDescriptor) -> pa.DataType: return cls.trac_to_arrow_basic_type(trac_type.basicType) @classmethod def trac_to_arrow_basic_type( cls, trac_basic_type: _meta.BasicType) -> pa.DataType: arrow_type = cls.__TRAC_TO_ARROW_BASIC_TYPE_MAPPING.get( trac_basic_type) if arrow_type is None: raise _ex.ETracInternal( f"No Arrow type mapping available for TRAC type [{trac_basic_type}]" ) return arrow_type @classmethod def trac_to_arrow_schema(cls, trac_schema: _meta.SchemaDefinition) -> pa.Schema: if trac_schema.schemaType != _meta.SchemaType.TABLE: raise _ex.ETracInternal( f"Schema type [{trac_schema.schemaType}] cannot be converted for Apache Arrow" ) arrow_fields = [(f.fieldName, cls.trac_to_arrow_basic_type(f.fieldType)) for f in trac_schema.table.fields] return pa.schema(arrow_fields, metadata={}) @classmethod def trac_arrow_decimal_type(cls) -> pa.Decimal128Type: return pa.decimal128(cls.__TRAC_DECIMAL_PRECISION, cls.__TRAC_DECIMAL_SCALE) @classmethod def pandas_datetime_type(cls): return cls.__PANDAS_DATETIME_TYPE @classmethod def view_to_pandas(cls, view: DataView, part: DataPartKey) -> pd.DataFrame: deltas = view.parts.get(part) # Sanity checks if not view.arrow_schema: raise _ex.ETracInternal(f"Data view schema not set") if not deltas: raise _ex.ETracInternal( f"Data view for part [{part.opaque_key}] does not contain any items" ) if len(deltas) == 1: return cls.item_to_pandas(deltas[0]) batches = { batch for delta in deltas for batch in ( delta.batches if delta.batches else delta.table.to_batches()) } table = pa.Table.from_batches(batches) # noqa return table.to_pandas() @classmethod def item_to_pandas(cls, item: DataItem) -> pd.DataFrame: if item.pandas is not None: return item.pandas.copy() if item.table is not None: return cls.arrow_to_pandas(item.table) if item.batches is not None: table = pa.Table.from_batches(item.batches, item.schema) # noqa return cls.arrow_to_pandas(table) raise _ex.ETracInternal(f"Data item does not contain any usable data") @classmethod def arrow_to_pandas(cls, table: pa.Table) -> pd.DataFrame: return table.to_pandas( ignore_metadata=True, # noqa date_as_object=False, # noqa timestamp_as_object=False, # noqa types_mapper=cls.__ARROW_TO_PANDAS_TYPE_MAPPING.get) @classmethod def pandas_to_view(cls, df: pd.DataFrame, prior_view: DataView, part: DataPartKey): item = cls.pandas_to_item(df, prior_view.arrow_schema) return cls.add_item_to_view(prior_view, part, item) @classmethod def pandas_to_item(cls, df: pd.DataFrame, schema: tp.Optional[pa.Schema]) -> DataItem: table = cls.pandas_to_arrow(df, schema) return DataItem(table.schema, table) @classmethod def pandas_to_arrow(cls, df: pd.DataFrame, schema: tp.Optional[pa.Schema] = None) -> pa.Table: # Here we convert the whole Pandas df and then pass it to conformance # An optimization would be to filter columns before applying conformance # To do this, we'd need the case-insensitive field matching logic, including output of warnings # Also, note that schema is not applied in from_pandas # This is because the conformance logic allows for a wider range of conversions # Applying the schema directly would fail for some types where casting is possible if len(df) == 0: df_schema = pa.Schema.from_pandas(df, preserve_index=False) # noqa table = pa.Table.from_batches(list(), df_schema) # noqa else: table = pa.Table.from_pandas(df, preserve_index=False) # noqa # If there is no explict schema, give back the table exactly as it was received from Pandas # There could be an option here to coerce types to the appropriate TRAC standard types # E.g. unsigned int 32 -> signed int 64, TRAC standard integer type if schema is None: return table else: return DataConformance.conform_to_schema(table, schema, df.dtypes) @classmethod def add_item_to_view(cls, view: DataView, part: DataPartKey, item: DataItem) -> DataView: prior_deltas = view.parts.get(part) or list() deltas = [*prior_deltas, item] parts = {**view.parts, part: deltas} return DataView(view.trac_schema, view.arrow_schema, parts)
def standardize_snf_flag_values(data_frame: pd.DataFrame) -> pd.DataFrame: """Replace values of store_and_forward with standardized versions.""" data_frame['store_and_forward'] = data_frame['store_and_forward'].apply( store_and_fwd_flag_mapping_function).astype(pd.Int16Dtype()) return data_frame
from apache_beam.utils import proto_utils __all__ = ('BatchRowsAsDataFrame', 'generate_proxy', 'UnbatchPandas', 'element_type_from_dataframe') T = TypeVar('T', bound=NamedTuple) # Generate type map (presented visually in the docstring) _BIDIRECTIONAL = [ (bool, bool), (np.int8, np.int8), (np.int16, np.int16), (np.int32, np.int32), (np.int64, np.int64), (pd.Int8Dtype(), Optional[np.int8]), (pd.Int16Dtype(), Optional[np.int16]), (pd.Int32Dtype(), Optional[np.int32]), (pd.Int64Dtype(), Optional[np.int64]), (np.float32, Optional[np.float32]), (np.float64, Optional[np.float64]), (object, Any), (pd.StringDtype(), Optional[str]), (pd.BooleanDtype(), Optional[bool]), ] PANDAS_TO_BEAM = { pd.Series([], dtype=dtype).dtype: fieldtype for dtype, fieldtype in _BIDIRECTIONAL } BEAM_TO_PANDAS = {fieldtype: dtype for dtype, fieldtype in _BIDIRECTIONAL}
def pandas_type_casting(df): import numpy as np import pandas as pd global n # df = pd.read_csv("users-isprep.zip") old = df.memory_usage() / 1024/1024 #numeric cols number_cols = list(df.select_dtypes("number").columns) n = df[number_cols].fillna(0).agg([min,max]).T.add_suffix("_") get_cols_names(0, 255, pd.UInt8Dtype()) get_cols_names(256, 65535, pd.UInt16Dtype()) get_cols_names(65536, 4294967295, pd.UInt32Dtype()) get_cols_names(-128, 127, pd.Int8Dtype()) get_cols_names(-32768, 32767, pd.Int16Dtype()) get_cols_names(-2147483648, 2147483647, pd.Int32Dtype()) # date and catagorical datacols catagoriacal_cols = list(df.select_dtypes("O").columns) date_cols = [] for i in catagoriacal_cols: x = df[i][~df[i].isna()].head() try: pd.to_datetime(x) date_cols.append(i) except: pass catagoriacal_cols = [i for i in catagoriacal_cols if not i in date_cols] c = df[catagoriacal_cols].apply(lambda x:x.nunique()/len(df)*100) for i in c[c<5].index: d[i] = "category" # del df # df = pd.read_csv("users-isprep.zip", parse_dates=date_cols, dtype=d) for i in d: df[i] = df[i].astype(d[i]) new = df.memory_usage() / 1024/1024 m = pd.DataFrame({"new" : new, "old" : old, "Imporovement" : old - new}) m['Dtype'] = [None] + list(df[list(new.index.drop("Index"))].dtypes.astype(str).values) c = df[catagoriacal_cols].apply(lambda x:x.nunique()/len(df)*100) m["nunique"] = None m.loc[c.index, "nunique"] = list(df[c.index].apply(lambda x:x.nunique() / len(df) * 100).values) print("Before :", round(m.old.sum())) print("After :", round(m.new.sum())) print("Diff :", round(m.Imporovement.sum())) print("Diff % :", round(m.Imporovement.sum()/m.old.sum(), 2)) print("\n\nImprovement:") print(m.groupby("Dtype").Imporovement.agg([min, max, sum, np.mean, np.median, "count"])) print("\n\nDetailed Summary:") print(m.to_string()) return df
import pandas as pd import pyorc import cudf from cudf.tests.utils import assert_eq from cudf.utils.dtypes import ( pandas_dtypes_to_cudf_dtypes, pyarrow_dtypes_to_pandas_dtypes, ) ALL_POSSIBLE_VALUES = "ALL_POSSIBLE_VALUES" _PANDAS_TO_AVRO_SCHEMA_MAP = { np.dtype("int8"): "int", pd.Int8Dtype(): ["int", "null"], pd.Int16Dtype(): ["int", "null"], pd.Int32Dtype(): ["int", "null"], pd.Int64Dtype(): ["long", "null"], pd.BooleanDtype(): ["boolean", "null"], pd.StringDtype(): ["string", "null"], np.dtype("bool_"): "boolean", np.dtype("int16"): "int", np.dtype("int32"): "int", np.dtype("int64"): "long", np.dtype("O"): "string", np.dtype("str"): "string", np.dtype("float32"): "float", np.dtype("float64"): "double", np.dtype("<M8[ns]"): { "type": "long", "logicalType": "timestamp-millis"
import pandas as pd import numpy as np data = io.StringIO( """ id,age,height,weight 129237,32,5.4,126 123083,20,6.1, 123087,25,4.5,unknown """ ) df = pd.read_csv( data, dtype={ "id": np.int32, "age": np.int8, "height": np.float16, "weight": pd.Int16Dtype(), }, na_values=["unknown"], index_col=[0], ) print(df) print(df.memory_usage(deep=True)) print(df.dtypes) print(df.index.dtype)
class INT16(INT32): """Semantic representation of a :class:`pandas.Int16Dtype`.""" type = pd.Int16Dtype() bit_width: int = 16
VERSION_CATEGORICAL: pd.CategoricalDtype = pd.CategoricalDtype( version.ALL_VERSIONS) # Types of parameters that can only have a single value. CONFIG_SCALAR: Dict[str, Any] = { parameters.ADD_NSV: np.int8, parameters.BUNDLE_ID: pd.Int32Dtype(), parameters.COVARIATE_ID: pd.Int32Dtype(), parameters.CROSSWALK_VERSION_ID: pd.Int32Dtype(), parameters.DATA_TRANSFORM: DATA_TRANSFORM_CATEGORICAL, parameters.DECOMP_STEP: object, parameters.GBD_ROUND_ID: np.uint8, parameters.GPR_AMP_CUTOFF: pd.Int32Dtype(), parameters.GPR_AMP_FACTOR: np.float64, parameters.GPR_AMP_METHOD: GPR_AMP_METHOD_CATEGORICAL, parameters.GPR_DRAWS: pd.Int16Dtype(), parameters.HOLDOUTS: pd.Int16Dtype(), parameters.LOCATION_SET_ID: np.uint32, parameters.MODEL_INDEX_ID: pd.Int32Dtype(), parameters.MODELABLE_ENTITY_ID: pd.Int32Dtype(), parameters.NOTES: object, parameters.PATH_TO_CUSTOM_COVARIATES: object, parameters.PATH_TO_CUSTOM_STAGE_1: object, parameters.PATH_TO_DATA: object, parameters.PREDICT_RE: np.int8, parameters.PREDICTION_UNITS: str, parameters.RAKE_LOGIT: np.int8, parameters.ST_VERSION: VERSION_CATEGORICAL, parameters.STAGE_1_MODEL_FORMULA: object, parameters.TRANSFORM_OFFSET: np.float, parameters.YEAR_END: np.uint16,
"uint8": "UInt8", "float64": "Float64", "float32": "Float32", "int64": "Int64", "int32": "Int32", "int16": "Int16", "int8": "Int8", "datetime64[D]": "Date", "datetime64[ns]": "DateTime", } PD2CH = keymap(np.dtype, MAPPING) PD_INT_TYPES = [ pd.Int8Dtype(), pd.Int16Dtype(), pd.Int32Dtype(), pd.Int64Dtype(), pd.UInt8Dtype(), pd.UInt16Dtype(), pd.UInt32Dtype(), pd.UInt64Dtype(), ] for typ in PD_INT_TYPES: PD2CH[typ] = f"Nullable({typ.name})" CH2PD = itemmap(reversed, MAPPING) CH2PD["Null"] = "object" CH2PD["Nothing"] = "object"
actual_column = cudf.core.column.as_column(cudf.core.Buffer(data), dtype=data.dtype) assert_eq(cudf.Series(actual_column), cudf.Series(expected)) @pytest.mark.parametrize( "pd_dtype,expect_dtype", [ # TODO: Nullable float is coming (pd.StringDtype(), np.dtype("O")), (pd.UInt8Dtype(), np.dtype("uint8")), (pd.UInt16Dtype(), np.dtype("uint16")), (pd.UInt32Dtype(), np.dtype("uint32")), (pd.UInt64Dtype(), np.dtype("uint64")), (pd.Int8Dtype(), np.dtype("int8")), (pd.Int16Dtype(), np.dtype("int16")), (pd.Int32Dtype(), np.dtype("int32")), (pd.Int64Dtype(), np.dtype("int64")), (pd.BooleanDtype(), np.dtype("bool")), ], ) def test_build_df_from_nullable_pandas_dtype(pd_dtype, expect_dtype): if pd_dtype == pd.StringDtype(): data = ["a", pd.NA, "c", pd.NA, "e"] elif pd_dtype == pd.BooleanDtype(): data = [True, pd.NA, False, pd.NA, True] else: data = [1, pd.NA, 3, pd.NA, 5] pd_data = pd.DataFrame.from_dict({"a": data}, dtype=pd_dtype) gd_data = cudf.DataFrame.from_pandas(pd_data)
#%% first try to generate data with NaN # for problems with NaN see 'ex01-pd.dtypes.py' r=10; c=3; nnans=7 arr = np.random.randint(0, r*c, (r, c)).astype("float32") arr.dtype rows = np.random.randint(0, r, (nnans,)) cols = np.random.randint(0, c, (nnans,)) arr[rows, cols] = np.nan # ok but only because we set dtype=float32 # otherwise there would be an error! # in numpy NaN may only be for floats... BAD!!! df = pd.DataFrame(arr) df.dtypes # not good having floats for int... for c in df.columns: df[c] = df[c].astype(pd.Int16Dtype()) df # so Pandas accept NaN for int types but they are Pandas' ints!!! #!!! there's really no shorter way !!! #%% back to replacing missing data df countries = np.array(['SK', 'CZ', 'HG', 'PL']) key = countries[np.random.randint(0, 4, 10)] dfg = df.groupby(key) dfg.groups dfg.get_group('PL') dfg.count() # non NA counts !
def compute_trips(id_, host_url, offset, limit, sql_dir, psql_credentials, csv_dir, suffix, chunksize): """ Compute query result from Open Trip Planner and save to RESULTS.trips (for example see Google Docs) for each given trip, defined by the following attributes/parameters: 1. OA ID 2. POI ID 3. Timestamp (date + time) The function does the following: 1. Loop: Read `chunksize` rows of MODEL.trips into memory, generate corresponding OTP queries 2. Run the queries and save results to `results.csv` 3. Save `results.csv` back to RESULTS.trips Parameters ---------- id_ : int The id number of the portion of the trips table that is being read (e.g. if 6 OTP's are available, we'd split into 6 ID numbers) host_url : str Base url (of local server) for an OTP query Example: 'http://localhost:8080' offset : int Number of rows to offset, to begin portion {id_} of the table limit : int Number of rows to limit query to. {offset} + {limit} gives the end trip number of the table sql_dir : str Directory that stores query_trip_info.sql psql_credentials : dict Dictionary of PSQL credentials in order to create SQLAlchemy engine csv_dir : str Directory to save results in csv formats suffix : str Suffix to append to 'results.trips' as the table name chunksize: int Rows will be read in batches of this size at a time; all rows will be read at once if not specified Returns ------- None """ print(f"{id_} on {host_url} for offset {offset} limit {limit}") query_sql_file = os.path.join(sql_dir, 'query_trip_info.sql') params = {'suffix': suffix, 'limit': limit, 'offset': offset} engine = create_connection_from_dict(psql_credentials, 'postgresql') count = 1 # We chunk up the portion received in order to not crash a DF's memory for chunk in execute_sql(query_sql_file, engine, read_file=True, return_df=True, params=params, chunksize=chunksize): # Get OTP response print( f"Getting response from Chunk {count} on OTP {host_url}, for results.trip{suffix}{id_}" ) chunk['response'] = chunk.apply(lambda row: otp.request_otp( host_url, row.oa_lat, row.poi_lat, row.oa_lon, row.poi_lon, row. date, row.time), axis=1) # Parse OTP response chunk[[ "departure_time", "arrival_time", "total_time", "walk_time", "transfer_wait_time", "initial_wait_time", "transit_time", "walk_dist", "transit_dist", "total_dist", "num_transfers", "fare" ]] = chunk.apply( lambda row: otp.parse_response(row.response, row.date, row.time), axis=1, result_type="expand") chunk = chunk[[ "trip_id", "departure_time", "arrival_time", "total_time", "walk_time", "transfer_wait_time", "initial_wait_time", "transit_time", "walk_dist", "transit_dist", "total_dist", "num_transfers", "fare" ]] chunk.num_transfers = chunk.num_transfers.astype(pd.Int16Dtype()) chunk.set_index('trip_id', inplace=True) # Write response to CSV print( f"Writing response to CSV from chunk {count} on OTP {host_url}, for results.trip{suffix}{id_}" ) chunk.to_csv(os.path.join(csv_dir, f"trips{suffix}{id_}.csv"), mode='a', header=False) count += 1 # Copy CSV with this portion to DB print(f"Copying csv's to db for results.trips{suffix}{id_}") copy_text_to_db(os.path.join(csv_dir, f"trips{suffix}{id_}.csv"), f'results.trips{suffix}', engine, mode='append', header=False) # Update the model trips table so we know these trips have been computed, if we ever re-run the pipeline with "append" mode update_sql_file = os.path.join(sql_dir, 'update_computed_model_trips.sql') execute_sql(update_sql_file, engine, read_file=True, params=params)