I've got two CSV files that are loaded into Data Wrangler that are intended to augment each other. The tables have some columns that are the same (in name) and some that are not, many of the rows are missing entries for many of the columns. The two tables represent separate datasets. Consider the example below:
Table 1:
| Filename | LabelA | LabelB |
|---|
| ./A/001.dat | 1 | 1 |
| ./A/002.dat | 0 | 1 |
Table 2:
| Filename | LabelB | LabelC |
|---|
| ./B/001.dat | | 0 |
| ./B/002.dat | 0 | 1 |
I am looking to merge / concatenate the two table. The problem is that neither Data Wrangler join nor concatenate seems to work (at least as expected).
Desired result:
| Filename | LabelA | LabelB | LabelC |
|---|
| ./A/001.dat | 1 | 1 | |
| ./A/002.dat | 0 | 1 | |
| ./B/001.dat | | | 0 |
| ./B/002.dat | | 0 | 1 |
When using a "Full Outer" join and ask to combine "Filename" and "LabelB" columns, it will take all the values from Table 1 OR Table 2 even if Table 1 does not have that entry (for example, some rows will have Filename = <nothing> rather than Filename = ./B/001.dat).
When using concatenate, Data Wrangler errors on the fact that it cannot match EVERY column between the tables.
Now in my example there are many columns and many rows which precludes a manual process of joining without merging columns and then going through a renaming and merging process one-by-one. How do get these tables to simply merge? I feel I must be missing something obvious. I am about to give up on Data Wrangler and do it all in a python script using pandas, but I thought I should give Data Wrangler a try while learning the MLops process.
AWS OFFICIALUpdated 2 years ago
Thanks for the reply. It has helped me to understand the philosophy of join. Seeing as concatenate is not tenable with the column matching issue, I'll just use a pandas in a notebook to handle it in code. Any insight into why Data Wrangler handles empty entries, but won't just assume that for unmatched columns in a concatenate?
You can do it with Pandas, but you can also concatenate by adding the missing columns in the Data Wrangler's data flow.
Since concatenate is a function to concatenate datasets with the same structure, it seems that the same column is required.