Functions to merge data from one or more columns into a single column.
Take dictionaries in multiple columns and merge them to a single dictionary.
Note: For duplicate keys, the last key in the list takes precedence as the first entry in the merged dictionary.
SampleParameters¶ Using Named Columns
wrangles:
- merge.dictionaries:
input:
- Dict 1
- Dict 2
output: Merged
| | |
|---|
| Dict 1 |
Dict 2 |
{'First': 'One'} |
{'Second': 'Two'} |
|
→
|
| Merged |
{'First': 'One', 'Second': 'Two'} |
|
# Using a Wildcard (*)
wrangles:
- merge.dictionaries:
input: Dict *
output: Merged
| | |
|---|
| Dict 1 |
Dict 2 |
{'First': 'One'} |
{'Second': 'Two'} |
|
→
|
| Merged |
{'First': 'One', 'Second': 'Two'} |
|
¶ Using a Wildcard (*) With Not Columns
# Using a Wildcard (*)
wrangles:
- merge.dictionaries:
input:
- Dict *
- -Dict 2
output: Merged
| | |
|---|
| Dict 1 |
Dict 2 |
Dict 3 |
{'First': 'One'} |
{'Second': 'Two'} |
{'Third': 'Three'} |
|
→
|
| Merged |
{'First': 'One', 'Third': 'Three'} |
|
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
list |
|
| output |
✓ |
str |
|
| 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. |
Take the first non-empty value from a series of columns.
Click here to learn how to use Coalesce Wrangles in Excel.
SampleParameters¶ Coalescing 3 Columns
wrangles:
- merge.coalesce:
input:
- Col1
- Col2
- Col3
output: Output Col
where: Col2 = E
| | |
|---|
| Col1 |
Col2 |
Col3 |
| A |
B |
C |
| D |
E |
F |
| G |
H |
I |
|
→
|
| Col1 |
Col2 |
Col3 |
Output Col |
| A |
B |
C |
|
| D |
E |
F |
D |
| G |
H |
I |
|
|
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
list |
|
| output |
✓ |
str |
|
| 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. |
If the input is a list of columns, concatenate multiple columns into one as a delimited string.
Click here to learn how to use Concatenate Wrangles in Excel.
SampleParameters¶ Concatenating 3 Columns
# Using concatenate to combine multiple columns
wrangles:
- merge.concatenate:
input:
- Col1
- Col2
- Col3
output: Join Col
char: ', '
¶ Concatenating a Single Column
If the input is a single column, concatenate a list within that column into a delimited string.
# Using concatenate to join a column that is a list
wrangles:
- merge.concatenate:
input: Col1
output: Join List
char: ' '
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
str, list |
|
| output |
✓ |
str |
|
| char |
|
str |
Character to add between successive values |
| skip_empty |
|
bool |
Whether to skip empty values, defaults to false |
| 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. |
Create a dictionary from keys and values in paired columns.
SampleParameters¶ Using Named Columns
wrangles:
- merge.key_value_pairs:
input:
Letter: Number
output: Pairs
| | |
|---|
| Letter |
Number |
| A |
1 |
| B |
2 |
| C |
3 |
|
→
|
| Pairs |
{'A': 1} |
{'B': 2} |
{'C': 3} |
|
# Using a Wildcard (*)
wrangles:
- merge.key_value_pairs:
input:
key*: value*
output: Object
| | |
|---|
| key 1 |
key 2 |
value 1 |
value 2 |
| A |
One |
a |
First |
| B |
Two |
b |
Second |
| C |
three |
c |
Third |
|
→
|
| Object |
{'A': 'a', 'One': 'First'} |
{'B': 'b', 'Two': 'Second'} |
{'C': 'c', 'three': 'Third'} |
|
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
dict |
Matched pairs of key and value columns |
| output |
✓ |
str |
|
| 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. |
Take lists in multiple columns and merge them to a single list.
SampleParameterswrangles:
- merge.lists:
input:
- col1
- col2
output: Combined Col
remove_duplicates: false
| | |
|---|
| Col1 |
Col2 |
| ['A', 'B'] |
['D', 'E'] |
|
→
|
| Combined Col |
| ['A', 'B', 'D', 'E'] |
|
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
list |
|
| output |
✓ |
str |
|
| remove_duplicates |
|
bool |
Whether to remove duplicates from the created list, defaults to false |
| 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. |
Take multiple columns and merge them to a list.
SampleParameters¶ Merging Multiple Columns to a Single List
wrangles:
merge.to_list:
input:
- Col1
- Col2
- Col3
output: List Col
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
list |
|
| output |
✓ |
str |
|
| include_empty |
|
bool |
Whether to include empty columns in the created list, defaults to false. |
| 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. |
Take multiple columns and merge them to a dictionary (aka object) using the column headers as keys.
SampleParameters¶ Merging Two Columns Into a Dictionary
wrangles:
- merge.to_dict:
input:
- Col1
- Col2
output: Dict Col
| | |
|---|
|
|
→
|
| Dict Col |
{'Col1': 'A', 'Col2': 'B'} |
|
| Parameter |
Required |
Data Type |
Notes |
| input |
✓ |
list |
|
| output |
✓ |
str |
|
| include_empty |
|
bool |
Whether to include empty columns in the created dictionary, defaults to false. |
| 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. |