Teradata Package for Python Function Reference | 20.00 - trunc - Teradata Package for Python - Look here for syntax, methods and examples for the functions included in the Teradata Package for Python.

Teradata® Package for Python Function Reference - 20.00

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Teradata Package for Python
Release Number
20.00
Published
March 2024
Language
English (United States)
Last Update
2024-04-10
dita:id
TeradataPython_FxRef_Enterprise_2000
Product Category
Teradata Vantage
teradataml.dataframe.sql.DataFrameColumn.trunc = trunc(self, expression=0, formatter=None)
DESCRIPTION:
    Function to truncate the values inside the column based on formatter.
    Numeric type column:
        Function returns the values in column truncated places_value (expression) places to the right or left
        of the decimal point.
        trunc() functions as follows:
            * It truncates places_value places to the right of the decimal point if
              places_value is positive.
            * It truncates (makes 0) places_value places to the left of the decimal
              point if places_value is negative.
            * It truncates to 0 places if places_value is zero or is omitted.
            * If numeric_value or places_value is NULL, the function returns NULL.
    Date type column:
       Function truncates date type based on the format specified by formatter.
       Example:
           First example truncates data to first day of the week.
           Second example truncates data to beginning of the month.
           +------------------------------------------+
           |    data            formatter       result|
           +------------------------------------------+
           |    19/01/16        'D'           19/01/13|
           |    19/02/27        'MON'         19/02/01|
           +------------------------------------------+
PARAMETERS:
    expression:
        Optional Argument.
        Specifies to truncate the "expression" number of digits.
        Note:
            This argument applicable only for Numeric columns.
        Default Value: 0
        Types: ColumnExpression OR int
 
    formatter:
        Optional Argument.
        Specifies a literal string to truncate the values of column.
        If 'formatter' is omitted, date_value is truncated to the nearest day.
        Type: str
        Note:
            * This argument applicable only for Date type columns.
        * Various formatter given below:
            +--------------------------------------------------------------------------------------------------+
            |    FORMATTER                        DESCRIPTION                                                  |
            +--------------------------------------------------------------------------------------------------+
            |    CC                                                                                            |
            |    SCC                              One year greater than the first two digits of a 4-digit year.|
            |                                     Example:                                                     |
            |                                         +-------------------------------------------------+      |
            |                                         | data            formatter           result      |      |
            |                                         +-------------------------------------------------+      |
            |                                         | 19/01/16        CC                  01/01/01    |      |
            |                                         +-------------------------------------------------+      |
            +--------------------------------------------------------------------------------------------------+
            |    SYYY                                                                                          |
            |    YYYY                                                                                          |
            |    YEAR                                                                                          |
            |    SYEAR                            Year. Returns a value of 1, the first day of the year.       |
            |    YYY                                                                                           |
            |    YY                                                                                            |
            |    Y                                                                                             |
            |                                     Example:                                                     |
            |                                         +-------------------------------------------------+      |
            |                                         | data            formatter           result      |      |
            |                                         +-------------------------------------------------+      |
            |                                         | 19/01/16        CC                  19/01/01    |      |
            |                                         +-------------------------------------------------+      |
            +--------------------------------------------------------------------------------------------------+
            |    IYYY                                                                                          |
            |    IYY                              ISO year                                                     |
            |    IY                                                                                            |
            |    I                                                                                             |
            |                                     Example:                                                     |
            |                                         +-------------------------------------------------+      |
            |                                         | data            formatter           result      |      |
            |                                         +-------------------------------------------------+      |
            |                                         | 19/01/16        CC                  18/12/31    |      |
            |                                         +-------------------------------------------------+      |
            +--------------------------------------------------------------------------------------------------+
            |    MONTH                                                                                         |
            |    MON                              Month. Returns a value of 1, the first day of the month.     |
            |    MM                                                                                            |
            |    RM                                                                                            |
            |                                     Example:                                                     |
            |                                         +-------------------------------------------------+      |
            |                                         | data            formatter           result      |      |
            |                                         +-------------------------------------------------+      |
            |                                         | 19/02/16        RM                  19/02/01    |      |
            |                                         +-------------------------------------------------+      |
            +--------------------------------------------------------------------------------------------------+
            |    Q                                Quarter. Returns a value of 1, the first day of the quarter. |
            |                                     Example:                                                     |
            |                                         +-------------------------------------------------+      |
            |                                         | data            formatter           result      |      |
            |                                         +-------------------------------------------------+      |
            |                                         | 19/02/16        Q                  19/01/01     |      |
            |                                         +-------------------------------------------------+      |
            +--------------------------------------------------------------------------------------------------+
            |    WW                               Same day of the week as the 1st day of the year.             |
            |                                     Example:                                                     |
            |                                         +-------------------------------------------------+      |
            |                                         | data            formatter           result      |      |
            |                                         +-------------------------------------------------+      |
            |                                         | 19/02/16        WW                  19/02/15    |      |
            |                                         +-------------------------------------------------+      |
            +--------------------------------------------------------------------------------------------------+
            |    IW                               Same day of the week as the first day of the ISO year.       |
            |                                     Example:                                                     |
            |                                         +-------------------------------------------------+      |
            |                                         | data            formatter           result      |      |
            |                                         +-------------------------------------------------+      |
            |                                         | 19/02/16        RM                  19/02/14    |      |
            |                                         +-------------------------------------------------+      |
            +--------------------------------------------------------------------------------------------------+
            |    W                                Same day of the week as the first day of the month.          |
            |                                     Example:                                                     |
            |                                         +-------------------------------------------------+      |
            |                                         | data            formatter           result      |      |
            |                                         +-------------------------------------------------+      |
            |                                         | 19/02/16        W                  19/02/15     |      |
            |                                         +-------------------------------------------------+      |
            +--------------------------------------------------------------------------------------------------+
            |    DDD                                                                                           |
            |    DD                               Day.                                                         |
            |    J                                                                                             |
            |                                     Example:                                                     |
            |                                         +-------------------------------------------------+      |
            |                                         | data            formatter           result      |      |
            |                                         +-------------------------------------------------+      |
            |                                         | 19/02/16        DDD                 19/02/16    |      |
            |                                         +-------------------------------------------------+      |
            +--------------------------------------------------------------------------------------------------+
            |    DAY                                                                                           |
            |    DY                               Starting day of the week.                                    |
            |    D                                                                                             |
            |                                     Example:                                                     |
            |                                         +-------------------------------------------------+      |
            |                                         | data            formatter           result      |      |
            |                                         +-------------------------------------------------+      |
            |                                         | 19/01/16        DAY                  19/02/13   |      |
            |                                         +-------------------------------------------------+      |
            +--------------------------------------------------------------------------------------------------+
            |    HH                                                                                            |
            |    HH12                             Hour.                                                        |
            |    HH24                                                                                          |
            |                                     Example:                                                     |
            |                                         +-------------------------------------------------+      |
            |                                         | data            formatter           result      |      |
            |                                         +-------------------------------------------------+      |
            |                                         | 19/02/16        HH                  19/02/16    |      |
            |                                         +-------------------------------------------------+      |
            +--------------------------------------------------------------------------------------------------+
            |    MI                               Minute.                                                      |
            |                                     Example:                                                     |
            |                                         +-------------------------------------------------+      |
            |                                         | data                       formatter  result    |      |
            |                                         +-------------------------------------------------+      |
            |                                         | 2016-01-06 09:08:01.000000    MI      16/01/06  |      |
            |                                         +-------------------------------------------------+      |
            +--------------------------------------------------------------------------------------------------+
 
RAISES:
    TypeError, ValueError, TeradataMlException
 
RETURNS:
    ColumnExpression
 
EXAMPLES:
    # Load the data to execute the example.
    >>> load_example_data("dataframe", "admissions_train")
    >>> load_example_data("uaf", "stock_data")
 
    # Create a DataFrame on 'admissions_train' table.
    >>> df = DataFrame("admissions_train").iloc[:4]
    >>> df
       masters   gpa     stats programming  admitted
    id
    3       no  3.70    Novice    Beginner         1
    4      yes  3.50  Beginner      Novice         1
    2      yes  3.76  Beginner    Beginner         0
    1      yes  3.95  Beginner    Beginner         0
 
    # Create a DataFrame on 'stock_data' table.
    >>> df1 = DataFrame("stock_data")
    >>> df1
                seq_no timevalue  magnitude
    data_set_id
    556               3  19/01/16     61.080
    556               5  19/01/30     63.810
    556               6  19/02/06     63.354
    556               7  19/02/13     63.871
    556               9  19/02/27     61.490
    556              10  19/03/06     61.524
    556               8  19/02/20     61.886
    556               4  19/01/23     63.900
    556               2  19/01/09     61.617
    556               1  19/01/02     60.900
 
    # Example 1: Truncate the value of 'gpa' to 0 decimal place and 1 decimal place.
    >>> res = df.assign(col = df.gpa.trunc(),
                           col_1 = df.gpa.trunc(1))
    >>> res
       masters   gpa     stats programming  admitted  col  col_1
    id
    3       no  3.70    Novice    Beginner         1  3.0    3.7
    4      yes  3.50  Beginner      Novice         1  3.0    3.5
    2      yes  3.76  Beginner    Beginner         0  3.0    3.7
    1      yes  3.95  Beginner    Beginner         0  3.0    3.9
 
    # Example 2: Get the records with gpa > 3.7 by rounding the gpa to single decimal point.
    >>> df[df.gpa.trunc(1) > 3.7]
       masters   gpa     stats programming  admitted
    id
    1      yes  3.95  Beginner    Beginner         0
 
    # Example 3: Truncate the value of 'timevalue' to beginning of the month.
    >>> res=df1.assign(timevalue=df1.timevalue.trunc(formatter="MON"))
    >>> res
                    seq_no timevalue  magnitude time_value
    data_set_id
    556               3  19/01/16     61.080   19/01/01
    556               5  19/01/30     63.810   19/01/01
    556               6  19/02/06     63.354   19/02/01
    556               7  19/02/13     63.871   19/02/01
    556               9  19/02/27     61.490   19/02/01
    556              10  19/03/06     61.524   19/03/01
    556               8  19/02/20     61.886   19/02/01
    556               4  19/01/23     63.900   19/01/01
    556               2  19/01/09     61.617   19/01/01
    556               1  19/01/02     60.900   19/01/01