Filtering error: Unrecognized column

[skokenes]

Hi, I am struggling to figure out why I can't filter by a column with arquero. Using it with node v16.16.0

I have used loadCSV to load a local CSV file into a data table like:

const dt = await aq.loadCSV("sample.csv");

If I inspect dt.columnNames(), I get the following result:

[ 'City', 'Name', 'Year', 'AvgAge' ]

Then I try to filter the data table using the following:

dt.filter((d) => aq.op.includes(d.City, "bar"))

This throws an error for me saying "Unrecognized column: City". I cannot figure out what is wrong here. As seen with the columnNames() function, "City" is a column in my table.

Any ideas?

[jheer]

If you can link to a sample.csv file that reproduces the issue (or, an Observable notebook showing the failure), I can take a closer look.

Meanwhile, you might check to ensure that (a) the other column names are working, and if so, (b) confirm that the string 'City' (as typed into your filter function) exactly matches the actual column name string (does dt.columnNames()[0] === 'City'?). If there are "invisible" characters (like a byte order mark) at the beginning of your CSV file, it could cause the issue.

[skokenes]

I tried doing this next, but now get Error: Invalid member expression: "d[cityName]". I'm assuming because I can't use the cityName variable inside of a filter op? What should I do instead?

const cityName = dt.columnNames()[0];
 dt.filter((d) => aq.op.includes(d[cityName], "bar"));

[jheer]

If it is indeed a byte order mark issue, you could try using the column name \ufeffCity as part of rename transform as a cleaning step. That might match and allow you to reference (and thus rename) that column.

Or you might load the CSV as text yourself, and then use aq.fromCSV(csvText.slice(1)) to strip off that leading character prior to parsing.

[skokenes]

EDIT: I renamed using the following:

const newNames = dt.columnNames().map((n) => n.replace("\ufeff", ""));
const cleanedTable = dt.rename(aq.names(newNames));

[jheer]

This also works for me:

const dt2 = dt.rename({ [dt.columnNames()[0]]: 'City' });

[skokenes]

For anyone that finds this thread in the future...I found using trim() to be a nice solution for making sure the column names are clean:

const newNames = dt.columnNames().map((n) => n.trim());
const dt2 = dt.rename(aq.names(newNames));