I have about 95 csv files with different column names saved under main data folder and 95 different sub-folders like \data\dataset1\dataset1.csv, \data\dataset2\dataset2.csv, etc. id1 and id2 columns are common variables across all files. I want to to do the following:
- Import all 95 csv files to JMP (using JMP Pro 16)
- Update a value based on a condition across all 95 files: if (id1 = 'a001' then id2='b001')
- Save updated csv files under different names in respective folders
First row is the file title and can't be removed. Column/variable names start at row 2 and data on row 3.
I managed to import all files by:
dt: Multiple File Import(
<"C:\Data\"
),
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<) << Import Data;
In this code, there is no formula for where column names and data start.
Then, I tried to update a value by using:
dt: subjid <<
:id1 << Set Formula(
If( :id2 == "a001", :id1 == "b001")
);
result, values prior to "a001" in id1 column are deleted and id1 value of "a001" is not updated.
I am stock in this phase and can't even think of next stage of saving files under different names in their respective folders. Any help/guidance is greatly appreciated.
Thank you
Thank you again. This is awesome.
Helal
Thanks, made a post for it because it seems generally useful and some of it isn't really obvious how to do.
MFI Update CSV
Thank you Craige. As you mentioned, the second example worked well. However, it only worked if I open or import one csv but didn't work when I imported multiple CSVs. Also, will you please guide me to also accomplish the last stage of writing csv back to their respective folders with different filenames?
Thank you,
Helal
I'm not sure what your JSL did:
dt: subjid << :id1 << Set Formula( If( :id2 == "a001", :id1 == "b001") )
The << "send" operator should only appear once, and the column you are adding the formula to should be the left-hand-side. Then in the if statement, there should be a condition, followed by a value for the true case, and another value for the false case. Something like this
dt << New Column( "likes",
"character",
formula( If(
Is Missing( name ), // first test
"unknown", // if first test is true
name == "KATIE", // second test
"cats", // if second test is true
"dogs" // if second test is false
)
)
);
which is intended to create a new formula column of cats or dogs depending on the value in column name. If name is blank (missing), "unknown".
Or, if your goal is to overwrite an existing column(s) based on values of other columns, perhaps an explicit loop would be better than a formula column:
foreachrow(dt,
if(
name == "DANNY", // a test with ==
name = "Dani"; // an assignment with =
sex = "F";
, /* else if */ name == "ROBERT",
name = "Roberta";
sex = "F";
);
);
( If Secrets might help understanding the commas and semicolons. ) That leaves rows unchanged, unless the name matches.
Notice how the two examples are different:
The first example must produce an answer in the if(...) that will be assigned to the formula's column. If the column should be unchanged for that row, the formula's value must be the original value for the row. Edit: sorry, that's wrong. Just make sure the formula column produces a value for every row.
The second example only updates rows and columns it chooses.
Since you plan to write out a CSV file (not save a JMP file), the formula expression you add to a column will not be saved (though the updated values from the formula will be.) I think you might want something more like the second example.
