- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
loop through column names which has same string and create subset
Hi,
I have a JMP table "Sheet 1" with 300 columns. I am trying to create subset for all same columns which has a string in column name as "Ahu1" and then make changes to those subset like adding the columns,updating them and generating graphs and finally saving them in a folder. I was able to write script for "Ahu1". But i have to do the same for all other "Ahu's" . I want this process to be repeated for other column names containing stings: which are Ahu2,Ahu3,Ahu4,Ahu5,Ahu6...Ahu52. How to do this in a for loop?
The Script is below.
original_dt = Current Data Table();
Data Table( "Sheet1" ) << Subset(
Output Table( "Ahu1", link to original data table( 0 ) ),
All rows,
columns(
:Timestamp,
:donSteedOccupancy,
:Name( "Raeford, NC Temp" ),
:DonSteedES Ahu1 ClgVlvCmd,
:DonSteedES Ahu1 HtgVlvCmd,
:DonSteedES Ahu1 DaT,
:DonSteedES Ahu1 SaFanCmd,
:DonSteedES Ahu1 ZnT,
:DonSteedES Ahu1 ZnTStpt
)
);
Ahu1_dt = Data Table( "Ahu1" );
Ahu1_dt << New Column( "Occupancy",
character,
Nominal,
Formula( Munger( :donSteedOccupancy, Length( :donSteedOccupancy ) - 4, Length( :donSteedOccupancy ) ) )
);
Column( "Occupancy" ) << delete formula;
Ahu1_dt << delete columns( "donSteedOccupancy" );
Ahu1_dt << New Column( "SaFanCmd",
character,
Nominal,
Formula(
Munger(
:DonSteedES Ahu1 SaFanCmd,
Length( :DonSteedES Ahu1 SaFanCmd ) - 4,
Length( :DonSteedES Ahu1 SaFanCmd )
)
)
);
Column( "SaFanCmd" ) << delete formula;
Ahu1_dt << delete columns( "DonSteedES Ahu1 SaFanCmd" );
Ahu1_dt << Column( "Occupancy" );
For Each Row( :Occupancy = Match( :Occupancy, " 1min", " 1", " 0min", " 0", :Occupancy ) );
Column( "Occupancy" ) << Data type( character ) << Set Data Type( Numeric );
Ahu1_dt << Column( "SaFanCmd" );
For Each Row( :SaFanCmd = Match( :SaFanCmd, " 1min", " 1", " 0min", " 0", :SaFanCmd ) );
Column( "SaFanCmd" ) << Data type( character ) << Set Data Type( Numeric );
Ahu1_dt:DonSteedES Ahu1 ClgVlvCmd << Format( "percent" );
Ahu1_dt:DonSteedES Ahu1 HtgVlvCmd << Format( "percent" );
Column( "SaFanCmd" ) << Modeling type( continuous );
optstartgraph = Ahu1_dt << Graph Builder(
Size( 1377, 923 ),
Variables(
X( :Timestamp ),
Y( :DonSteedES Ahu1 ClgVlvCmd ),
Y( :DonSteedES Ahu1 HtgVlvCmd, Position( 1 ) ),
Y( :Name( "Raeford, NC Temp" ) ),
Y( :DonSteedES Ahu1 DaT, Position( 2 ) ),
Y( :DonSteedES Ahu1 ZnT, Position( 2 ) ),
Y( :DonSteedES Ahu1 ZnTStpt, Position( 2 ) ),
Y( :SaFanCmd, Position( 2 ), Side( "Right" ) ),
Color( :Occupancy )
),
Elements( Position( 1, 1 ), Line( X, Y( 1 ), Y( 2 ), Color( 0 ), Legend( 5 ) ), Heatmap( X, Legend( 8 ) ) ),
Elements(
Position( 1, 2 ),
Line( X, Y( 1 ), Y( 2 ), Y( 3 ), Y( 4 ), Color( 0 ), Legend( 6 ) ),
Line( X, Y( 5 ), Color( 0 ), Legend( 7 ) ),
Heatmap( X, Legend( 9 ) )
),
SendToReport(
Dispatch( {"Line[DonSteedES Ahu1 ClgVlvCmd...]"}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Heatmap[DonSteedES Ahu1 ClgVlvCmd...]"}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Line[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Line[SaFanCmd] (right)"}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Heatmap[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
Dispatch(
{},
"Timestamp",
ScaleBox,
{Min( 3537633600 ), Max( 3538263600 ), Interval( "Hour" ), Inc( 1 ), Minor Ticks( 0 ),
Label Row( Label Orientation( "Angled" ) )}
),
Dispatch( {}, "DonSteedES Ahu1 ClgVlvCmd", ScaleBox, {Min( 0 )} ),
Dispatch(
{},
"400",
ScaleBox,
{Legend Model(
8,
Properties(
0,
{Transparency( 0.35 ), gradient(
{Color Theme(
{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227,
0}, {221, 221, 0}, {238, 119, 0}, {252, 11, 11}, {158, 33, 33}}, {0,
0.193732193732194, 0.2, 0.495726495726496, 0.501424501424502, 0.507122507122507,
0.943019943019943, 0.95, 0.954415954415954}}
)}
)}
)
), Legend Model(
9,
Properties(
0,
{Transparency( 0.35 ), gradient(
{Color Theme(
{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227,
0}, {221, 221, 0}, {238, 119, 0}, {252, 11, 11}, {158, 33, 33}}, {0,
0.193732193732194, 0.2, 0.495726495726496, 0.501424501424502, 0.507122507122507,
0.943019943019943, 0.95, 0.954415954415954}}
)}
)}
)
)}
),
Dispatch(
{},
"400",
LegendBox,
{Legend Position( {5, [0, 1], 8, [2], 6, [-3, -3, 4, 5], 7, [-3], 9, [3]} ),
Position( {0, 1, 2, -3, -3, 4, 5, -3, 3} )}
)
)
);
optstartgraph << Save Picture( "C:\Projects\Projects\Hoke\Year 1\Jmp\Year 0\DonSteed\Feb16\Ahu1.jpg", "JPG" );
optstartgraph << close window;
Please help me out.
Thanks.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: loop through column names which has same string and create subset
Here is a version of your code that will loop across all 50 of your Ahu column sets. See the comments in the script as to what was done to make the looping possible
Names Default To Here( 1 );
original_dt = Data Table( "Sheet1" );
// Loop across all 50 Ahu column sets
// The number of column sets could be programatically determined
// if desired
For( i = 1, i <= 50, i++,
// Set the Ahu version to be used
targetAhu = "Ahu" || Char( i );
// Create the subsetted data table, naming it by the
// name of the current Ahu version
Ahu_dt = original_dt << Subset(
Output Table( targetAhu, link to original data table( 0 ) ),
All rows,
columns(
original_dt:Timestamp,
original_dt:donSteedOccupancy,
original_dt:Name( "Raeford, NC Temp" ),
Column( original_dt, "DonSteedES " || targetAhu || " ClgVlvCmd" ),
Column( original_dt, "DonSteedES " || targetAhu || " HtgVlvCmd" ),
Column( original_dt, "DonSteedES " || targetAhu || " DaT" ),
Column( original_dt, "DonSteedES " || targetAhu || " SaFanCmd" ),
Column( original_dt, "DonSteedES " || targetAhu || " ZnT" ),
Column( original_dt, "DonSteedES " || targetAhu || " ZnTStpt" )
)
);
// no need for this statement, since it is set when the data table is created
//Ahu_dt = Data Table( targetAhu );
// No changes needed here, except for pointing to the current Ahu data table
Ahu_dt << New Column( "Occupancy",
character,
Nominal,
Formula( Munger( :donSteedOccupancy, Length( :donSteedOccupancy ) - 4, Length( :donSteedOccupancy ) ) )
);
Column( Ahu_dt, "Occupancy" ) << delete formula;
Ahu_dt << delete columns( "donSteedOccupancy" );
// This section of code has required changes embedded within a formula
// JMP will not resolve the code properly and therefore, Substitution
// is required to force the generation of the exact code required for
// the formula
Eval(
Substitute(
Expr(
Ahu_dt << New Column( "SaFanCmd",
character,
Nominal,
Formula( Munger( __SaFanCmd__, Length( __SaFanCmd__ ) - 4, Length( __SaFanCmd__ ) ) )
)
),
Expr( __SaFanCmd__ ), Parse( ":DonSteedES " || targetAhu || " SaFanCmd" )
)
);
Column( Ahu_dt, "SaFanCmd" ) << delete formula;
// Minor changes, just making sure the current target data table is being
// pointed to
Ahu_dt << delete columns( "DonSteedES " || targetAhu || " SaFanCmd" );
Ahu_dt << Column( "Occupancy" );
For Each Row( ahu_dt:Occupancy = Match( Ahu_dt:Occupancy, " 1min", " 1", " 0min", " 0", Ahu_dt:Occupancy ) );
// I don't understand this next line of code....changing to character and then changing to numeric
// unless you are using JMP to get rid of non numeric data
Column( ahu_dt, "Occupancy" ) << Data type( character ) << Set Data Type( Numeric );
Ahu_dt << Column( "SaFanCmd" );
For Each Row( ahu_dt:SaFanCmd = Match( ahu_dt:SaFanCmd, " 1min", " 1", " 0min", " 0", ahu_dt:SaFanCmd ) );
Column( ahu_dt, "SaFanCmd" ) << Data type( character ) << Set Data Type( Numeric );
Column( Ahu_dt, "DonSteedES " || targetAhu || " ClgVlvCmd" ) << Format( "percent" );
Column( Ahu_dt, "DonSteedES " || targetAhu || " HtgVlvCmd" ) << Format( "percent" );
Column( Ahu_dt, "SaFanCmd" ) << Modeling type( continuous );
// The Graph Builder Platform has a few places where code will not be resolved, and therefore
// once again Substitution() is what is needed to make it work
Eval(
Substitute(
Expr(
optstartgraph = Ahu_dt << Graph Builder(
Size( 1377, 923 ),
Variables(
X( :Timestamp ),
Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " ClgVlvCmd" ) ),
Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " HtgVlvCmd" ), Position( 1 ) ),
Y( :Name( "Raeford, NC Temp" ) ),
Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " DaT" ), Position( 2 ) ),
Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " ZnT" ), Position( 2 ) ),
Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " ZnTStpt" ), Position( 2 ) ),
Y( :SaFanCmd, Position( 2 ), Side( "Right" ) ),
Color( :Occupancy )
),
Elements( Position( 1, 1 ), Line( X, Y( 1 ), Y( 2 ), Color( 0 ), Legend( 5 ) ), Heatmap( X, Legend( 8 ) ) ),
Elements(
Position( 1, 2 ),
Line( X, Y( 1 ), Y( 2 ), Y( 3 ), Y( 4 ), Color( 0 ), Legend( 6 ) ),
Line( X, Y( 5 ), Color( 0 ), Legend( 7 ) ),
Heatmap( X, Legend( 9 ) )
),
SendToReport(
Dispatch( {__LineClgVlvCmd__}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {__HeatmapClgVlvCmd__}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Line[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Line[SaFanCmd] (right)"}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Heatmap[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
Dispatch(
{},
"Timestamp",
ScaleBox,
{Min( 3537633600 ), Max( 3538263600 ), Interval( "Hour" ), Inc( 1 ), Minor Ticks( 0 ),
Label Row( Label Orientation( "Angled" ) )}
),
Dispatch( {}, "DonSteedES " || targetAhu || " ClgVlvCmd", ScaleBox, {Min( 0 )} ),
Dispatch(
{},
"400",
ScaleBox,
{Legend Model(
8,
Properties(
0,
{Transparency( 0.35 ), gradient(
{Color Theme(
{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227, 0}, {221, 221, 0
}, {238, 119, 0}, {252, 11, 11}, {158, 33, 33}}, {0, 0.193732193732194, 0.2, 0.495726495726496,
0.501424501424502, 0.507122507122507, 0.943019943019943, 0.95, 0.954415954415954}}
)}
)}
)
), Legend Model(
9,
Properties(
0,
{Transparency( 0.35 ), gradient(
{Color Theme(
{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227, 0}, {221, 221, 0
}, {238, 119, 0}, {252, 11, 11}, {158, 33, 33}}, {0, 0.193732193732194, 0.2, 0.495726495726496,
0.501424501424502, 0.507122507122507, 0.943019943019943, 0.95, 0.954415954415954}}
)}
)}
)
)}
),
Dispatch(
{},
"400",
LegendBox,
{Legend Position( {5, [0, 1], 8, [2], 6, [-3, -3, 4, 5], 7, [-3], 9, [3]} ), Position(
{0, 1, 2, -3, -3, 4, 5, -3, 3}
)}
)
)
)
),
Expr( __LineClgVlvCmd__ ), "Line[DonSteedES " || targetAhu || " ClgVlvCmd...]",
Expr( __HeatmapClgVlvCmd__ ), "Heatmap[DonSteedES " || targetAhu || " ClgVlvCmd...]"
)
);
// Save the current graphic, replacing the jpg name with the current target Ahu version
optstartgraph << Save Picture( "C:\Projects\Projects\Hoke\Year 1\Jmp\Year 0\DonSteed\Feb16\" || targetAhu || ".jpg", "JPG" );
optstartgraph << close window;
// You can also close the current target data table if you want
close( dt_Ahu, no save );
); // End of For Loop
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: loop through column names which has same string and create subset
@himajafeb8,
A couple of suggestions,
1. When you share code, please kindly use the "Insert JSL Script" or "Insert Code" option, so the code is formatted to enable others to easily read what you are sharing.
2. Please break down your problem and explain which part of the code is actually giving you issues.
Going by your title, I would think doing something like the following would work. However, if you have more questions, request you to kindly break it down :
Clear Log(); Clear Globals();
// Open Sample Data
dt = Open( "$SAMPLE_DATA/Football.jmp" );
// Get Column Names
ColNames = dt << Get Column Names("String");
// Select Desired Columns
DesTerm = "Speed";
DesCols = {}; // Initialize empty list to store selected columns
for(i = 1 , i <= N Items(ColNames), i++,
If(Contains(ColNames[i],DesTerm),
Insert Into(DesCols,i);
Col = Column(dt,i);
Col << Set Selected(1);
);
);
// Subset
dt1 = dt << Subset(Selected Rows(0),Selected Columns(1));
Uday
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: loop through column names which has same string and create subset
@uday_guntupalli Thanks for all the Suggestions. I will keep in my mind before posting anything .
I have posted my code below and it's working absolutely fine. But I need to run this code in a loop.
I have my original data table "Sheet 1". It has 300 columns like ("Timestamp" , "donsteedOccupancy" , "Name( "Raeford, NC Temp" )" , "x Ahu1 y" , "x Ahu1 z" , "x Ahu1 m" ,..."x Ahu2 y" , "x Ahu2 z" , "x Ahu2 m"......."x Ahu70 y" , "x Ahu70 z" , "x Ahu70 m".
I want to create a subset from main datatable with first 3 columns ("Timestamp" , "donsteedOccupancy" , "Name( "Raeford, NC Temp" )" ) and also all the column names consisting "Ahu1" to a new data table and perform various actions as i mentioned in the code.(Creating extra columns,deleting columns,Change Column properties and generating a graph).
So bascially in this code were there is "Ahu1" it has to be replaced to "Ahu[i]" and run in the loop.
I want to do something like this.
For( i=0; i<= 52, i++
Data Table("Sheet1") << subset(
Output table( "Ahu[i]"),
columns(Timestamp,..., "x Ahu[i] y" , "x Ahu[i] z" , "x Ahu[i] m"));
Ahu[i]_dt= Data Table("Ahu[i]");
.
.
.
Close( Ahu[i]_dt, save("Ahu[i].jmp") );
All the fields in the code which has "Ahu1" has to be replaced to "Ahu[i]"
Thanks for the help.
original_dt = Current Data Table();
/* Sheet 1 is my original data table. I am creating a subset from original sheet which has column names as : Timestamp,donsteedOccupancy, Name( "Raeford, NC Temp" )
and all the columns which has a string "Ahu1". I am naming this output table as Ahu1.*/
Data Table( "Sheet1" ) << Subset(
Output Table( "Ahu1", link to original data table( 0 ) ),
All rows,
columns(
:Timestamp,
:donSteedOccupancy,
:Name( "Raeford, NC Temp" ),
:DonSteedES Ahu1 ClgVlvCmd,
:DonSteedES Ahu1 HtgVlvCmd,
:DonSteedES Ahu1 DaT,
:DonSteedES Ahu1 SaFanCmd,
:DonSteedES Ahu1 ZnT,
:DonSteedES Ahu1 ZnTStpt
)
);
/* Assigning the output data table to "Ahu1_dt" */
Ahu1_dt = Data Table( "Ahu1" );
/* Creating new columns called "Occupancy and SafanCmd" with formula's and deleting the formula link and also the original column's */
Ahu1_dt << New Column( "Occupancy",
character,
Nominal,
Formula( Munger( :donSteedOccupancy, Length( :donSteedOccupancy ) - 4, Length( :donSteedOccupancy ) ) )
);
Column( "Occupancy" ) << delete formula;
Ahu1_dt << delete columns( "donSteedOccupancy" );
Ahu1_dt << New Column( "SaFanCmd",
character,
Nominal,
Formula( Munger( :DonSteedES Ahu1 SaFanCmd, Length( :DonSteedES Ahu1 SaFanCmd ) - 4, Length( :DonSteedES Ahu1 SaFanCmd ) ) )
);
Column( "SaFanCmd" ) << delete formula;
Ahu1_dt << delete columns( "DonSteedES Ahu1 SaFanCmd" );
/* Using Recode to change values for columns "Occupancy and SaFanCmd " and also changing the column Data Types*/
Ahu1_dt << Column( "Occupancy" );
For Each Row( :Occupancy = Match( :Occupancy, " 1min", " 1", " 0min", " 0", :Occupancy ) );
Column( "Occupancy" ) << Data type( character ) << Set Data Type( Numeric );
Ahu1_dt << Column( "SaFanCmd" );
For Each Row( :SaFanCmd = Match( :SaFanCmd, " 1min", " 1", " 0min", " 0", :SaFanCmd ) );
Column( "SaFanCmd" ) << Data type( character ) << Set Data Type( Numeric );
/* Changing Format for the column's "DonSteedES Ahu1 ClgVlvCmd" and "Ahu1_dt:DonSteedES Ahu1 HtgVlvCmd" */
Ahu1_dt:DonSteedES Ahu1 ClgVlvCmd << Format( "percent" );
Ahu1_dt:DonSteedES Ahu1 HtgVlvCmd << Format( "percent" );
/* Changing Modeling type for the column : SaFanCmd */
Column( "SaFanCmd" ) << Modeling type( continuous );
/* Generating a Graph using Graph Builder for "Ahu1_dt" and assigning it to a object called "optstartgraph" .*/
optstartgraph = Ahu1_dt << Graph Builder(
Size( 1377, 923 ),
Variables(
X( :Timestamp ),
Y( :DonSteedES Ahu1 ClgVlvCmd ),
Y( :DonSteedES Ahu1 HtgVlvCmd, Position( 1 ) ),
Y( :Name( "Raeford, NC Temp" ) ),
Y( :DonSteedES Ahu1 DaT, Position( 2 ) ),
Y( :DonSteedES Ahu1 ZnT, Position( 2 ) ),
Y( :DonSteedES Ahu1 ZnTStpt, Position( 2 ) ),
Y( :SaFanCmd, Position( 2 ), Side( "Right" ) ),
Color( :Occupancy )
),
Elements( Position( 1, 1 ), Line( X, Y( 1 ), Y( 2 ), Color( 0 ), Legend( 5 ) ), Heatmap( X, Legend( 8 ) ) ),
Elements(
Position( 1, 2 ),
Line( X, Y( 1 ), Y( 2 ), Y( 3 ), Y( 4 ), Color( 0 ), Legend( 6 ) ),
Line( X, Y( 5 ), Color( 0 ), Legend( 7 ) ),
Heatmap( X, Legend( 9 ) )
),
SendToReport(
Dispatch( {"Line[DonSteedES Ahu1 ClgVlvCmd...]"}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Heatmap[DonSteedES Ahu1 ClgVlvCmd...]"}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Line[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Line[SaFanCmd] (right)"}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Heatmap[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
Dispatch(
{},
"Timestamp",
ScaleBox,
{Min( 3537633600 ), Max( 3538263600 ), Interval( "Hour" ), Inc( 1 ), Minor Ticks( 0 ), Label Row( Label Orientation( "Angled" ) )}
),
Dispatch( {}, "DonSteedES Ahu1 ClgVlvCmd", ScaleBox, {Min( 0 )} ),
Dispatch(
{},
"400",
ScaleBox,
{Legend Model(
8,
Properties(
0,
{Transparency( 0.35 ), gradient(
{Color Theme(
{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227, 0}, {221, 221, 0}, {238, 119, 0},
{252, 11, 11}, {158, 33, 33}}, {0, 0.193732193732194, 0.2, 0.495726495726496, 0.501424501424502, 0.507122507122507,
0.943019943019943, 0.95, 0.954415954415954}}
)}
)}
)
), Legend Model(
9,
Properties(
0,
{Transparency( 0.35 ), gradient(
{Color Theme(
{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227, 0}, {221, 221, 0}, {238, 119, 0},
{252, 11, 11}, {158, 33, 33}}, {0, 0.193732193732194, 0.2, 0.495726495726496, 0.501424501424502, 0.507122507122507,
0.943019943019943, 0.95, 0.954415954415954}}
)}
)}
)
)}
),
Dispatch(
{},
"400",
LegendBox,
{Legend Position( {5, [0, 1], 8, [2], 6, [-3, -3, 4, 5], 7, [-3], 9, [3]} ), Position( {0, 1, 2, -3, -3, 4, 5, -3, 3} )}
)
)
);
/*Saving the object in jpg format to a specfied file location. */
optstartgraph << Save Picture( "C:\Projects\Projects\Hoke\Year 1\Jmp\Year 0\DonSteed\Feb16\Ahu1.jpg", "JPG" );
/* Closing the object */
optstartgraph << close window;
/* close and save the data table "Ahu1_dt" */
Close( Ahu1_dt, save("C:\Projects\Projects\Hoke\Year 1\Jmp\Year 0\DonSteed\Feb16\Ahu1.jmp") );
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: loop through column names which has same string and create subset
@himajafeb8,
Can you share a sample of your data ?
Uday
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: loop through column names which has same string and create subset
I have attached the sample data.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: loop through column names which has same string and create subset
@himajafeb8,
If you were to try something like this - essentially use regular expressions to try and match Ahu on a loop every single time but edit the conditions to increment from 1 - n, I would think it would work. I dont have a working example. I will try a little more on the regex front
dt_Raw = Current Data Table();
ColNames = dt_Raw << Get Column Names("String");
RegexToMatch = "^(a|A)hu[0-9]$";
for(i = 1 , i <= N Items(ColNames), i++,
If(!IsMissing(Regex(Char(ColNames[i]),RegexToMatch)),
Col = Column(dt_Raw,i);
Col << Set Selected(1);
);
);
Uday
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: loop through column names which has same string and create subset
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: loop through column names which has same string and create subset
@himajafeb8,
At this point, I couldn't get it to work. I would contact support@jmp.com .
Uday
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: loop through column names which has same string and create subset
Here is a version of your code that will loop across all 50 of your Ahu column sets. See the comments in the script as to what was done to make the looping possible
Names Default To Here( 1 );
original_dt = Data Table( "Sheet1" );
// Loop across all 50 Ahu column sets
// The number of column sets could be programatically determined
// if desired
For( i = 1, i <= 50, i++,
// Set the Ahu version to be used
targetAhu = "Ahu" || Char( i );
// Create the subsetted data table, naming it by the
// name of the current Ahu version
Ahu_dt = original_dt << Subset(
Output Table( targetAhu, link to original data table( 0 ) ),
All rows,
columns(
original_dt:Timestamp,
original_dt:donSteedOccupancy,
original_dt:Name( "Raeford, NC Temp" ),
Column( original_dt, "DonSteedES " || targetAhu || " ClgVlvCmd" ),
Column( original_dt, "DonSteedES " || targetAhu || " HtgVlvCmd" ),
Column( original_dt, "DonSteedES " || targetAhu || " DaT" ),
Column( original_dt, "DonSteedES " || targetAhu || " SaFanCmd" ),
Column( original_dt, "DonSteedES " || targetAhu || " ZnT" ),
Column( original_dt, "DonSteedES " || targetAhu || " ZnTStpt" )
)
);
// no need for this statement, since it is set when the data table is created
//Ahu_dt = Data Table( targetAhu );
// No changes needed here, except for pointing to the current Ahu data table
Ahu_dt << New Column( "Occupancy",
character,
Nominal,
Formula( Munger( :donSteedOccupancy, Length( :donSteedOccupancy ) - 4, Length( :donSteedOccupancy ) ) )
);
Column( Ahu_dt, "Occupancy" ) << delete formula;
Ahu_dt << delete columns( "donSteedOccupancy" );
// This section of code has required changes embedded within a formula
// JMP will not resolve the code properly and therefore, Substitution
// is required to force the generation of the exact code required for
// the formula
Eval(
Substitute(
Expr(
Ahu_dt << New Column( "SaFanCmd",
character,
Nominal,
Formula( Munger( __SaFanCmd__, Length( __SaFanCmd__ ) - 4, Length( __SaFanCmd__ ) ) )
)
),
Expr( __SaFanCmd__ ), Parse( ":DonSteedES " || targetAhu || " SaFanCmd" )
)
);
Column( Ahu_dt, "SaFanCmd" ) << delete formula;
// Minor changes, just making sure the current target data table is being
// pointed to
Ahu_dt << delete columns( "DonSteedES " || targetAhu || " SaFanCmd" );
Ahu_dt << Column( "Occupancy" );
For Each Row( ahu_dt:Occupancy = Match( Ahu_dt:Occupancy, " 1min", " 1", " 0min", " 0", Ahu_dt:Occupancy ) );
// I don't understand this next line of code....changing to character and then changing to numeric
// unless you are using JMP to get rid of non numeric data
Column( ahu_dt, "Occupancy" ) << Data type( character ) << Set Data Type( Numeric );
Ahu_dt << Column( "SaFanCmd" );
For Each Row( ahu_dt:SaFanCmd = Match( ahu_dt:SaFanCmd, " 1min", " 1", " 0min", " 0", ahu_dt:SaFanCmd ) );
Column( ahu_dt, "SaFanCmd" ) << Data type( character ) << Set Data Type( Numeric );
Column( Ahu_dt, "DonSteedES " || targetAhu || " ClgVlvCmd" ) << Format( "percent" );
Column( Ahu_dt, "DonSteedES " || targetAhu || " HtgVlvCmd" ) << Format( "percent" );
Column( Ahu_dt, "SaFanCmd" ) << Modeling type( continuous );
// The Graph Builder Platform has a few places where code will not be resolved, and therefore
// once again Substitution() is what is needed to make it work
Eval(
Substitute(
Expr(
optstartgraph = Ahu_dt << Graph Builder(
Size( 1377, 923 ),
Variables(
X( :Timestamp ),
Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " ClgVlvCmd" ) ),
Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " HtgVlvCmd" ), Position( 1 ) ),
Y( :Name( "Raeford, NC Temp" ) ),
Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " DaT" ), Position( 2 ) ),
Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " ZnT" ), Position( 2 ) ),
Y( Column( Ahu_dt, "DonSteedES " || targetAhu || " ZnTStpt" ), Position( 2 ) ),
Y( :SaFanCmd, Position( 2 ), Side( "Right" ) ),
Color( :Occupancy )
),
Elements( Position( 1, 1 ), Line( X, Y( 1 ), Y( 2 ), Color( 0 ), Legend( 5 ) ), Heatmap( X, Legend( 8 ) ) ),
Elements(
Position( 1, 2 ),
Line( X, Y( 1 ), Y( 2 ), Y( 3 ), Y( 4 ), Color( 0 ), Legend( 6 ) ),
Line( X, Y( 5 ), Color( 0 ), Legend( 7 ) ),
Heatmap( X, Legend( 9 ) )
),
SendToReport(
Dispatch( {__LineClgVlvCmd__}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {__HeatmapClgVlvCmd__}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Line[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Line[SaFanCmd] (right)"}, "", OutlineBox, {Close( 0 )} ),
Dispatch( {"Heatmap[Raeford, NC Temp...]"}, "", OutlineBox, {Close( 0 )} ),
Dispatch(
{},
"Timestamp",
ScaleBox,
{Min( 3537633600 ), Max( 3538263600 ), Interval( "Hour" ), Inc( 1 ), Minor Ticks( 0 ),
Label Row( Label Orientation( "Angled" ) )}
),
Dispatch( {}, "DonSteedES " || targetAhu || " ClgVlvCmd", ScaleBox, {Min( 0 )} ),
Dispatch(
{},
"400",
ScaleBox,
{Legend Model(
8,
Properties(
0,
{Transparency( 0.35 ), gradient(
{Color Theme(
{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227, 0}, {221, 221, 0
}, {238, 119, 0}, {252, 11, 11}, {158, 33, 33}}, {0, 0.193732193732194, 0.2, 0.495726495726496,
0.501424501424502, 0.507122507122507, 0.943019943019943, 0.95, 0.954415954415954}}
)}
)}
)
), Legend Model(
9,
Properties(
0,
{Transparency( 0.35 ), gradient(
{Color Theme(
{"mine", 16387, {{79, 16, 79}, {119, 0, 119}, {42, 63, 255}, {0, 170, 170}, {0, 227, 0}, {221, 221, 0
}, {238, 119, 0}, {252, 11, 11}, {158, 33, 33}}, {0, 0.193732193732194, 0.2, 0.495726495726496,
0.501424501424502, 0.507122507122507, 0.943019943019943, 0.95, 0.954415954415954}}
)}
)}
)
)}
),
Dispatch(
{},
"400",
LegendBox,
{Legend Position( {5, [0, 1], 8, [2], 6, [-3, -3, 4, 5], 7, [-3], 9, [3]} ), Position(
{0, 1, 2, -3, -3, 4, 5, -3, 3}
)}
)
)
)
),
Expr( __LineClgVlvCmd__ ), "Line[DonSteedES " || targetAhu || " ClgVlvCmd...]",
Expr( __HeatmapClgVlvCmd__ ), "Heatmap[DonSteedES " || targetAhu || " ClgVlvCmd...]"
)
);
// Save the current graphic, replacing the jpg name with the current target Ahu version
optstartgraph << Save Picture( "C:\Projects\Projects\Hoke\Year 1\Jmp\Year 0\DonSteed\Feb16\" || targetAhu || ".jpg", "JPG" );
optstartgraph << close window;
// You can also close the current target data table if you want
close( dt_Ahu, no save );
); // End of For Loop
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content