列數據拆分並重命名列標題你好，
我有Data_original.csv，其中有許多列|或者_或沒有。 在此示例中，我僅顯示|的3列。
我嘗試了數據預覽，以查看是否可以拆分但我的列弄亂了。
Data_original.csv<BR /><BR />Fruit,ID,Colors,Weigth,CountryCode<BR />Apple,123456,5_2|1_4,3|3|3|oz,1_2_5|3_2_1_123_2343_342|1_2<BR />Apricot,5555666,9_0|55_298,5|3|2|1|lb,0_9_1|23<BR />Grape,98746,12_1|23_99,1|2|1|7|6|4|2|1|8|g,9_1_3|23_45_4<BR />Orange,25897,8_8|0_9,2|3|fl,1_0_4|99_999_888|0_8<BR />Pear,113355,9999_77|78_123,4|6|l,-9999_76_123|9<BR />Plum,8855446,32_-8888|00_12,5|9|lb,123_1_55|123_90<BR />Strawberries,981254,981_123|34_123,8|3|g,89_12_45|67_1_23_8|90<BR /><BR /><BR />Open(
"C:\JSL\Data_original.csv",
Import Settings(
End Of Line( CRLF, CR, LF ),
End Of Field( Comma, Other( "|" ), CSV( 0 ) ),
Strip Quotes( 1 ),
Use Apostrophe as Quotation Mark( 0 ),
Scan Whole File( 1 ),
Treat empty columns as numeric( 0 ),
CompressNumericColumns( 0 ),
CompressCharacterColumns( 0 ),
CompressAllowListCheck( 0 ),
Labels( 1 ),
Column Names Start( 1 ),
Data Starts( 2 ),
Lines To Read( "All" ),
Year Rule( "20xx" )
)
);
首先，我要打開Data_original.csv。 刪除的任何列|分割（如果有的話，只保留第一個值分割）。 我在想，一旦拆分，我應該可以使用Column *刪除任何列標題名稱，並將其保存到csv中。
請參閱Data_original_EDIT1.csv的輸出。
Fruit,ID,Colors,Weigth,CountryCode<BR />Apple,123456,5_2,3,1_2_5<BR />Apricot,5555666,9_0,5,0_9_1<BR />Grape,98746,12_1,1,9_1_3<BR />Orange,25897,8_8,2,1_0_4<BR />Pear,113355,9999_77,4,-9999_76_123<BR />Plum,8855446,32_-8888,5,123_1_55<BR />Strawberries,981254,981_123,8,89_12_45
其次，我要打開以前的保存csv Data_original_EDIT1.csv。分割為_列（如果有），並將列標題重命名為相同的列標題，但將序列0..n連接起來。
請參閱Data_original_EDIT2_Final.csv，將最終輸出導出到csv。
Fruit,ID,Colors0,Colors1,Weigth,CountryCode0,CountryCode1,CountryCode2
Apple,123456,5,2,3,1,2,5
Apricot,5555666,9,0,5,0,9,1
Grape,98746,12,1,1,9,1,3
Orange,25897,8,8,2,1,0,4
Pear,113355,9999,77,4,-9999,76,123
Plum,8855446,32,-8888,5,123,1,55
Strawberries,981254,981,123,8,89,12,45
讓我知道如何開始JSL編碼。 謝謝。
腳本編寫回复：列數據拆分並重命名列標題我認為解決方案比您想像的要容易。在查看了數據以及您要使用的數據後，我採取了一種方法，逐個單元地讀取原始csv文件中的內容。然後，我只需檢查數據並根據需要創建和修改列。這是我條目底部的腳本的結果
Names Default To Here( 1 );
dt = Open(
"C:\JSL\Data_original.csv",
columns(
New Column( "Fruit", Character, "Nominal" ),
New Column( "ID", Numeric, "Continuous", Format( "Best", 12 ) ),
New Column( "Colors", Character, "Nominal" ),
New Column( "Weigth", Character, "Nominal" ),
New Column( "CountryCode", Character, "Nominal" )
),
Import Settings(
End Of Line( CRLF, CR, LF ),
End Of Field( Comma, CSV( 0 ) ),
Strip Quotes( 1 ),
Use Apostrophe as Quotation Mark( 0 ),
Use Regional Settings( 0 ),
Scan Whole File( 1 ),
Treat empty columns as numeric( 0 ),
CompressNumericColumns( 0 ),
CompressCharacterColumns( 0 ),
CompressAllowListCheck( 0 ),
Labels( 1 ),
Column Names Start( 1 ),
Data Starts( 2 ),
Lines To Read( "All" ),
Year Rule( "20xx" )
)
);
// Work on the Colors data
// Only keep data up to |
For Each Row( :Colors = Word( 1, :Colors, "|" ) );
// Determine the potential number of colors
// Loop through Colors data for upto 100 rows to find max number of
// colors
dt:Colors << set name( "Colors0" );
stopVal = Min( N Rows( dt ), 100 );
colorsCount = 0;
For( i = 1, i <= stopVal, i++,
cnt = 0;
While( Word( cnt + 1, Trim( :Colors0[i] ), "_" ) != "", cnt++ );
colorsCount = Max( colorsCount, cnt );
);
dt << add multiple columns( "Colors", colorsCount - 1, after( :Colors0 ), numeric );
// Change name of new column if only 2 new column is created
If( colorsCount == 2,
dt:Colors << Set name( "Colors1" )
);
For(i=1,i<=nrows(dt),i++,
for(k=1,k<=colorsCount-1,k++,
column(dt,"Colors" || char(k))[i]=num(word(k+1,:Colors0[i],"_"));
)
);
// Correct Colors0 column
For Each Row(:Colors0 = word(1,:colors0,"_"));
dt:colors0 << data type(numeric) << modeling type(continuous);
// Correct Weigth column
for each row( :Weigth = word(1,:Weigth,"|"));
dt:Weigth << set data type(numeric) << set modeling type(continuous);
// Work on the Country data
// Only keep data up to |
For Each Row( :CountryCode = Word( 1, :CountryCode, "|" ) );
// Determine the potential number of CountryCode
// Loop through CountryCode data for upto 100 rows to find max number of
// CountryCode
dt:CountryCode << set name( "CountryCode0" );
stopVal = Min( N Rows( dt ), 100 );
CountryCodeCount = 0;
For( i = 1, i <= stopVal, i++,
cnt = 0;
While( Word( cnt + 1, Trim( :CountryCode0[i] ), "_" ) != "", cnt++ );
CountryCodeCount = Max( CountryCodeCount, cnt );
);
dt << add multiple columns( "CountryCode", CountryCodeCount - 1, after( :CountryCode0 ), numeric );
// Change name of new column if only 2 new column is created
If( CountryCodeCount == 2,
dt:CountryCode << Set name( "CountryCode1" )
);
For(i=1,i<=nrows(dt),i++,
for(k=1,k<=CountryCodeCount-1,k++,
column(dt,"CountryCode" || char(k))[i]=num(word(k+1,:CountryCode0[i],"_"));
)
);
// Correct CountryCode0 column
For Each Row(:CountryCode0 = word(1,:CountryCode0,"_"));
dt:CountryCode0 << data type(numeric) << modeling type(continuous);
此示例被硬編碼為3種不同的|列，但可以很容易地對其進行修改，使其僅讀取列列表，並指定列名稱，並消除第一個| | |之後的所有內容。和“ _”的計數，腳本可以處理找到的盡可能多的列。
回复：列數據拆分並重命名列標題這是代碼的更好版本，並且我相信它將處理所有|代碼。數據表中的列
Names Default To Here( 1 );
dt = Open(
"C:\JSL\Data_original.csv",
Import Settings(
End Of Line( CRLF, CR, LF ),
End Of Field( Comma, CSV( 0 ) ),
Strip Quotes( 1 ),
Use Apostrophe as Quotation Mark( 0 ),
Use Regional Settings( 0 ),
Scan Whole File( 1 ),
Treat empty columns as numeric( 0 ),
CompressNumericColumns( 0 ),
CompressCharacterColumns( 0 ),
CompressAllowListCheck( 0 ),
Labels( 1 ),
Column Names Start( 1 ),
Data Starts( 2 ),
Lines To Read( "All" ),
Year Rule( "20xx" )
)
);
stopVal = Min( N Rows( dt ), 100 );
// Loop across columns back to front to expand the columns as necessary
For( theColumn = N Cols( dt ), theColumn >= 3, theColumn--,
// Process if a character column
If( Column( dt, theColumn ) << get data type == "Character",
// See if a "|" is in the data value
For( i = 1, i <= stopVal + 1, i++,
If( Contains( Column( dt, theColumn )[i], "|" ),
Break()
)
);
// Get rid of everything after the first "|"
If( i <= stopVal,
For( theRow = 1, theRow <= N Rows( dt ), theRow++,
Column( dt, theColumn )[theRow] = Word( 1, As Column( dt, theColumn )[theRow], "|" )
);
// Determine the potential number of values
// Loop through the column data for upto 100 rows to find max number of values to be processed
Count = 0;
For( i = 1, i <= stopVal, i++,
cnt = 0;
While( Word( cnt + 1, Trim( Column( dt, theColumn )[i] ), "_" ) != "", cnt++ );
Count = Max( Count, cnt );
);
// If Count is == 1, then just change the column to numeric
If( Count == 1,
Column( dt, theColumn ) << data type( numeric ) << modeling type( continuous ),
origName = Column( dt, theColumn ) << get name;
Column( dt, theColumn ) << set name( origName || "0" );
Eval(
Substitute(
Expr(
dt << add multiple columns( __orig__, Count - 1, after( __col__ ), numeric )
),
Expr( __orig__ ), origName,
Expr( __col__ ), Parse( ":" || Char( Column( dt, thecolumn ) << get name ) )
)
);
// Change name of new column if only 2 new column is created
If( Count == 2,
Column( dt, origName ) << Set name( origName || "1" )
);
For( i = 1, i <= N Rows( dt ), i++,
For( k = 1, k <= Count - 1, k++,
Column( dt, origName || Char( k ) )[i] = Num( Word( k + 1, Column( dt, theColumn )[i], "_" ) )
)
);
// Correct original 0 column
For( i = 1, i <= N Rows( dt ), i++,
Column( dt, theColumn )[i] = Word( 1, Column( dt, theColumn )[i], "_" )
);
Column( dt, theColumn ) << data type( numeric ) << modeling type( continuous );
);
);
)
);
回复：列數據拆分和重命名列標題謝謝吉姆的通用解決方案。 我用我的數據集驗證 10 |分裂和 20 _ 分裂。 完美運行！！
