Newbie- Help figure out data flow- user input .csv file- tidy- plot

Community Help
1

Hello- I’ve done a couple of Observable notebooks but am still a newbie. I work mostly in R and just stumble through Observable.

I’m trying to create a notebook where the user will upload a .csv file. We can expect the upload to follow the same structure (same set of columns in same order with same names, but different uploads will have different rows with different data). The notebook then needs to do the following:

1- allow the user to upload a .csv file (I’ve done this successfully with viewof csvfile = Inputs.file (etc.)

2- format the column types- even when using typed: true, all the columns import as text. I need to reformat some to dates and some to numbers.

3- rename columns- columns come in with spaces in the column names. This seems to make plots difficult to deal with and I’d like to rename them, but all advice-- like how to refer to columns with spaces in their names in plots— is appreciated.

4- I think the other transformations (group by, summarize, plot) will be relatively easy once the data tidying is done.

Can anyone help figure out steps 2 and 3? I’ve previously imported a .csv file into a notebook and transformed the column types, but what I used previously isn’t working with this data, I get a NAN in converting a column to numeric. I’m wondering if it’s because of the user input of the file, and so everything has to be treated differently, or if it’s something else.

My super messy notebook where I’m trying to figure it all out is here.

viewof csvfile = Inputs.file({label: “CSV file”, accept: “.csv”, typed: true, required: true})

csvfile.csv().then(
function(data) {
data.forEach(function(d) {
d[“Sale Amount”] = +d[“Sale Amount”];
});
return data;
}

Thinking I need to name the csvfile.csv to get a more fixed association of the dataset, I try below, but it yields a “prop is not a function” error

viewof csvfile = Inputs.file({label: “CSV file”, accept: “.csv”, typed: true, required: true})

prop = csvfile.csv()

csvfile.csv().then(
function(data) {
data.forEach(function(d) {
d[“Sale Amount”] = +d[“Sale Amount”];
});
return data;
}

I’ve tried arquero but get run into other walls. Same first two steps above, then below yields sales_amount is NaN

prop_aq3 = aq.from(prop)
.rename({“PARCEL ID”:“parcel”,
“Full Address”: “address”,
“Sale Date”: “sale_date”,
“Sale Amount”: “sale_amount”,
“Bedrooms”: “bedrooms”,
“Baths”: “baths”,
“Total Living Area (sqft)”: “tot_liv_area”,
“Year Built”: “yr_built”})
.derive({sale_date2: d => op.parse_BDY(d.sale_date) })
.derive({sale_amount2: d => +d.sale_amount})
.view()

tried this, I get aq.from(…) .then is not a function
prop_aq33 = aq.from(prop)
.then((data) => {
data.forEach((d) => {
d.sale_amount = +d.sale_amount;
})
})

I’ve tried working with csvfile.csv with a function, I still get NaN for the Sale Amount column.

csvfile.csv().then(
function(data) {
data.forEach(function(d) {
d[“Sale Amount”] = +d[“Sale Amount”];
});
return data;
}
)

Is this because of the input method? Something else? All help is appreciated.

2

Kind of figured it out?
Had to do new = csvfile
.csv()
then the transformations.
The transformations appeared not to work because the larger numbers had commas in the strings. Had to use parseFloat and what I think is a regex replace function to turn the text into numbers.
Solution below:

prop = csvfile
.csv()
.then((data) => {
data.forEach((d) => {
// d[“Sale Date”] = parseTime(d[“Sale Date”]);
d.sale_date_dt = parseTime(d[“Sale Date”]);
d[“Sale Amount”] = parseFloat(d[“Sale Amount”].replace(/,/g, ‘’));
d.Bedrooms = +d.Bedrooms;
d.Baths = +d.Baths;
d[“Total Living Area (sqft)”] = parseFloat(d[“Total Living Area (sqft)”].replace(/,/g, ‘’));
d[“Year Built”] = +d[“Year Built”];
// d.year2 = new Date(d3.timeParse(‘%Y’)(parseTime(d[“Sale Date”])));
d.year = d.sale_date_dt.getFullYear();
});
return data;
})