nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Automating individual data corrections

NancyJ

I have identified very specific errors in my dataset. I can fix them by hand very easily, but for various reasons, I need to automate/script it. Specifically, I need to change the value in the column "Task" from "Contributor" to "Reviewer" but only for very specific cells out of many. These cells can be identified by content in one or more other columns, "Report#" or "Name".

Both the names and the Task values are valid and show up in other records that do not need to be changed.

The attached file is a small example where "Contributor" should be replaced by "Reviewer" for rows 1 & 2.

I could not figure out how to change only those records using Col=>Recode.

I checked the Log and tried workflow builder, but manual edits to the table or using the Row Editor do now show up.

I'm trying to learn JSL. Even though this seems simple, I got stuck and would appreciate a pointer to the right section in the documentation or concepts to use.

IndividualValueEditsExample.jmp

Accepted answers

jthi

Not really as I don't understand what those selections should be based on, but here are few examples what can be done.

Using conditions

Names Default To Here(1);

dt = Open("$DOWNLOADS/IndividualValueEditsExample.jmp");
rows_to_update = dt << Get Rows Where(:"Report#" == "123456" & :Task == "Contributor" & :Name == "John Doe");
dt[rows_to_update, "Task"] = "Reviewer";

Using selected rows

Names Default To Here(1);

dt = Open("$DOWNLOADS/IndividualValueEditsExample.jmp");

dt << Select Rows(1::2);
sel_rows = dt << Get Selected Rows;
dt[sel_rows, "Task"] = "Reviewer";

All comments

jthi

Data table subscripting , << Get Rows Where, << Select Where, For Each Row, Associative Array() are some things you most likely need.

I didn't understand this "Both the names and the Task values are valid and show up in other records that do not need to be changed. " so I cannot give more exact response.

NancyJ

Thank you for requesting clarification.

What I meant is the I cannot recode all the records involving "John Doe" or "Jane Doe" because most of them should not be changed.

John Doe's name is spelled correct and doesn't need to be changed.

John Doe is a correctly listed as a contributor and a reviewer on other reports.

I cannot recode all the records for "Contributor" task because most of those are correct.

I cannot recode all the records for a specific report because most rows will be correct.

I need to change a specific cell, such as report #123456 for John Doe from Contributor to Reviewer.

It is basically a Venn diagram problem. I can select the rows that need changes but I don't know how to change the value in a specific column for the selected row(s).

Does that make it clearer?

jthi

Not really as I don't understand what those selections should be based on, but here are few examples what can be done.

Using conditions

Names Default To Here(1);

dt = Open("$DOWNLOADS/IndividualValueEditsExample.jmp");
rows_to_update = dt << Get Rows Where(:"Report#" == "123456" & :Task == "Contributor" & :Name == "John Doe");
dt[rows_to_update, "Task"] = "Reviewer";

Using selected rows

Names Default To Here(1);

dt = Open("$DOWNLOADS/IndividualValueEditsExample.jmp");

dt << Select Rows(1::2);
sel_rows = dt << Get Selected Rows;
dt[sel_rows, "Task"] = "Reviewer";

txnelson

Any specific data cell in a JMP data table can be directly accessed by

Column[row]

Therefore to change the value of Task for rows 1 and 2, the most direct JSL would be

names default to here(1);

:Task[1] = "Reviewer";
:Task[2] = "Reviewer";

Evaluation of the rules to determine which rows need to be changed, based upon your statements, is what I cannot determine. More clarification/specifics need to be stated.

NancyJ

Thank you very much! That is exactly what I needed.