Click here to learn how to use Format Wrangles in Excel.
Format a date into desired pattern
SampleParameterswrangles:
- format.dates:
input: Date
output: Output Format
format: '%Y-%m-%d' # must be wrapped by quotes " or '
| Date |
Output Format |
| 6/23/1912 |
1912-06-23 |
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
str |
|
| output |
|
str |
If none given, overwrites input |
| format |
✓ |
str |
String pattern to format date |
| where |
|
str |
Filter the data to only apply the wrangle to certain rows using an equivalent to a SQL where criteria, such as column1 = 123 OR column2 = 'abc' |
| where_params |
|
str |
Variables to use in conjunctions with where. This allows the query to be parameterized. This uses sqlite syntax (? or :name) |
| if |
|
str |
A condition that will determine whether the action runs or not as a whole. |
Pad a string to a fixed length
SampleParameterswrangles:
- format.pad:
input: Part Number
pad_length: 5
side: left
char: "-"
| | |
|---|
| Part Number |
| 0458 |
| 396 |
| 84 |
| 98516 |
|
→
|
| Part Number |
| -0458 |
| --396 |
| ---84 |
| 98516 |
|
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
str, list |
|
| output |
|
str, list |
If none given, overwrites input |
| pad_length |
✓ |
int |
Length for the output |
| side |
✓ |
left/ right/ both |
Side from which to fill resulting string. |
| char |
✓ |
str |
The character to pad the input with |
| where |
|
str |
Filter the data to only apply the wrangle to certain rows using an equivalent to a SQL where criteria, such as column1 = 123 OR column2 = 'abc' |
| where_params |
|
str |
Variables to use in conjunctions with where. This allows the query to be parameterized. This uses sqlite syntax (? or :name) |
| if |
|
str |
A condition that will determine whether the action runs or not as a whole. |
Add a prefix to a column.
SampleParameterswrangles:
- format.prefix:
input: Data
output: Prefix
value: anti
| Data |
Prefix |
| freeze |
antifreeze |
| dote |
antidote |
| hero |
antihero |
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
str |
|
| output |
|
str |
If none given, overwrites input |
| value |
✓ |
str |
Prefix value to add |
| where |
|
str |
Filter the data to only apply the wrangle to certain rows using an equivalent to a SQL where criteria, such as column1 = 123 OR column2 = 'abc' |
| where_params |
|
str |
Variables to use in conjunctions with where. This allows the query to be parameterized. This uses sqlite syntax (? or :name) |
| if |
|
str |
A condition that will determine whether the action runs or not as a whole. |
Removed duplicate values in a list.
SampleParameterswrangles:
- format.remove_duplicates:
input: Attack of the Clones
output: Commander
where: Rank = Commander
| | |
|---|
| Attack of the Clones |
Rank |
| ['Cody', 'Cody', 'Cody'] |
Commander |
| ['Rex', 'Rex', 'Rex'] |
Captain |
|
→
|
| Attack of the Clones |
Rank |
Commander |
| ['Cody', 'Cody', 'Cody'] |
Commander |
['Cody'] |
| ['Rex', 'Rex', 'Rex'] |
Captain |
|
|
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
str, list |
|
| output |
|
str, list |
If none given, overwrites input |
| ignore_case |
|
bool |
Ignore case when removing duplicates. |
| where |
|
str |
Filter the data to only apply the wrangle to certain rows using an equivalent to a SQL where criteria, such as column1 = 123 OR column2 = 'abc' |
| where_params |
|
str |
Variables to use in conjunctions with where. This allows the query to be parameterized. This uses sqlite syntax (? or :name) |
| if |
|
str |
A condition that will determine whether the action runs or not as a whole. |
Format a value to a specific number of significant figures
SampleParameterswrangles:
- format.significant_figures:
input: Data
significant_figures: 2
output: Data to 2 Figures
| Data |
Data to 2 Figures |
| 1.25 |
1.2 |
| 12.3 |
12 |
| 55.6 |
55 |
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
str, list |
Name of the input column |
| significant_figures |
Number of significant figures to format to. Default is 3. |
|
|
| output |
|
str, list |
Name of the output column. If none given, overwrites input |
| where |
|
str |
Filter the data to only apply the wrangle to certain rows using an equivalent to a SQL where criteria, such as column1 = 123 OR column2 = 'abc' |
| where_params |
|
str |
Variables to use in conjunctions with where. This allows the query to be parameterized. This uses sqlite syntax (? or :name) |
| if |
|
str |
A condition that will determine whether the action runs or not as a whole. |
Add a suffix to a column
SampleParameterswrangles:
- format.suffix:
input: Data
output: Suffix
value: ic
| Data |
Suffix |
| sto |
stoic |
| hero |
heroic |
| icon |
iconic |
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
str, list |
Name of the input column |
| output |
|
str, list |
If none given, overwrites input |
| value |
✓ |
str |
Suffix value to add |
| where |
|
str |
Filter the data to only apply the wrangle to certain rows using an equivalent to a SQL where criteria, such as column1 = 123 OR column2 = 'abc' |
| where_params |
|
str |
Variables to use in conjunctions with where. This allows the query to be parameterized. This uses sqlite syntax (? or :name) |
| if |
|
str |
A condition that will determine whether the action runs or not as a whole. |
Remove excess whitespace at the start and end of text. Can accept multiple columns.
Note: Non-string values will be passed through unaltered.
SampleParameters- format.trim:
input:
- col1
output: col1 trimmed
| col1 |
col1 trimmed |
| Hello World |
Hello World |
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
str, list |
|
| output |
|
str, list |
If none given, overwrites input |
| where |
|
str |
Filter the data to only apply the wrangle to certain rows using an equivalent to a SQL where criteria, such as column1 = 123 OR column2 = 'abc' |
| where_params |
|
str |
Variables to use in conjunctions with where. This allows the query to be parameterized. This uses sqlite syntax (? or :name) |
| if |
|
str |
A condition that will determine whether the action runs or not as a whole. |