372522_zh-TW

liliconcom

從Excel入職數據-請求數據整理支持

你好，

 

我在轉換excel數據表時遇到了一些挑戰，在這種情況下，數據會像這樣分散。我也附上了電子表格。

 

有什麼想法嗎？如何快速將類似的日期從Excel工作表轉換為JMP數據集？

 

謝謝

 

 

數據表其他回复：從Excel入職數據-請求數據整理支持

使用新的JMP版本16，我只是簡單地完成了交互步驟以修改數據，然後將JSL從日誌中剪切並粘貼到我的腳本中。然後，我將自己的一些首選項應用於代碼，但這不是必需的。

注意：在JMP中將其打開之前，我還從Excel數據表Data wrangler中刪除了圖形和最終結果列。

names default to here(1);
dt = Open(
 "<PATH to="">/Data wrangling .xlsx",
 Worksheets( "Sheet1" ),
 Use for all sheets( 1 ),
 Concatenate Worksheets( 0 ),
 Create Concatenation Column( 0 ),
 Worksheet Settings(
  1,
  Has Column Headers( 1 ),
  Number of Rows in Headers( 1 ),
  Headers Start on Row( 1 ),
  Data Starts on Row( 2 ),
  Data Starts on Column( 1 ),
  Data Ends on Row( 0 ),
  Data Ends on Column( 0 ),
  Replicated Spanned Rows( 1 ),
  Replicated Spanned Headers( 0 ),
  Suppress Hidden Rows( 1 ),
  Suppress Hidden Columns( 1 ),
  Suppress Empty Columns( 1 ),
  Treat as Hierarchy( 0 ),
  Multiple Series Stack( 0 ),
  Import Cell Colors( 0 ),
  Limit Column Detect( 0 ),
  Column Separator String( "-" )
 )
);
// Delete selected rows
dt << Select Where(
 :Column 1 == "" & Is Missing( :Column 2 ) & Is Missing( :Column 3 ) & :Column 4
  == "" & Is Missing( :Column 5 ) & Is Missing( :Column 6 ) &
 Is Missing( :Column 7 )
) << Delete Rows;

// New column: Column 8
dt << New Column( "Group",
 character,
 formula(If( Is Missing( :Column 1 ) & Is Missing( :Column 2 ) & Is Missing( :Column 3 ),
  :Column 4
 ))
);

// Delete column formula: Group
dt:Group << Delete Formula;
For(i=1,i<=nrows(dt),i++,
 if(:group[i]=="",:group[i]=:group[i-1])
);

// Delete selected rows
dt << Select Where(
 :Column 1 == "" & Is Missing( :Column 2 ) & Is Missing( :Column 3 ) &
 Is Missing( :Column 5 ) & Is Missing( :Column 6 ) & Is Missing( :Column 7 ) 
) << Delete Rows;

// Change column info: Column 4
dt:Column 4 << Data Type( Numeric ) <<
Set Modeling Type( "Continuous" ) << Set Field Width( 12 );

// Transpose data table
// → Data Table( "Transpose of Sheet1" )
dtTrans = dt << Transpose(
 columns( :Column 2, :Column 3, :Column 4, :Column 5, :Column 6, :Column 7 ),
 By( :Column 1, :Group ),
 Output Table( "Transpose of Sheet1" )
);

// Change column name: Column 1 → T(Type)
dtTrans:Column 1 << Set Name( "T(Type)" );

// Delete column: Label
dtTrans << Delete Columns( :Label );

// Change column name: Row 1 → Response
dtTrans:Row 1 << Set Name( "Response" );

// New column: Column 4
dtTrans << New Column( "SI. No.",
 Numeric,
 "Continuous",
 Formula( Row() ),
 Format( "Best", 12 )
);

// Delete column formula: SI. No.
Data Table( "Transpose of Sheet1" ):SI. No. << Delete Formula;

// Move selected columns
Data Table( "Transpose of Sheet1" ) <<
Move Selected Columns( {:"T(Type)"n, :Group, :Response}, after( :SI. No. ) );</PATH>