Teradata Package for Python Function Reference on VantageCloud Lake - 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 on VantageCloud Lake
- Deployment
- VantageCloud
- Edition
- Lake
- Product
- Teradata Package for Python
- Release Number
- 20.00.00.02
- Published
- September 2024
- Language
- English (United States)
- Last Update
- 2024-10-17
- dita:id
- TeradataPython_FxRef_Lake_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