"Start from the second column in the following Excel file, every two columns makes a pair (an eve .."

blackduckie RaqForum 28 No.
1 Reply • 484 View • 3 Years ago

Split and Convert Columns into Rows

excel(115) transpose(9) order-based calculation(12)

Start from the second column in the following Excel file, every two columns makes a pair (an even-numbered column and an odd-numbered column except for the first column, such as the second column and third column). There are a total of 4 pairs of them, as shown below:

	A	B	C	D	E	F	G	H	I
1	Micro	Group	Series	Group1	Series1	Group2	Series2	Group3	Series3
2	1	Back	3	Biceps	1	Delts	1	Traps	1
3	1	Chest	4	Triceps	2	Delts	2
4	1	Biceps	2
5	1	Cuads	4	Glut	2
6	2	Back	4	Biceps	2	Delts	2	Traps	2
7	2	Chest	5	Triceps	3	Delts	3
8	2	Biceps	3
9	2	Cuads	5	Glut	3

Task: Group rows by the first column and all even-numbered columns and summarize the odd-numbered columns in each group. The expected result is as follows:

	A	B	C
1	1	Back	3
2	1	Biceps	3
3	1	Chest	4
4	1	Cuads	4
5	1	Delts	3
6	1	Glut	2
7	1	Traps	1
8	1	Triceps	2
9	2	Back	4
10	2	Biceps	5
11	2	Chest	5
12	2	Cuads	5
13	2	Delts	5
14	2	Glut	3
15	2	Traps	2
16	2	Triceps	3

The key point is to convert the N-column table into a table consisting of 3 columns (which are generated from the first column, all even-numbered columns and all odd-numbered columns). Then it’s easy to perform the grouping and aggregation. To do this, we need to retrieve data from each row N/2 times (the rounded up value). For instance, we get data from the second row 4 times, which are 1/2/3, 1/4/5, 1/6/7 and 1/8/9 respectively. It’s hard to express the order-based calculation using an Excel formula.

Directions to implement the task:

1. Start esProc (Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time).

2. Write and execute script in esProc.

	A
1	=file("data.xlsx").xlsimport@w().to(2,)
2	=A1.news(~.len()\2;A1.~(1):Micro,A1.~(#2):Group,A1.~(#2+1):Series)
3	=A2.groups(Micro,Group;sum(Series):Series)
4	=file("result.xlsx").xlsexport@t(A3)

The script function xlsimport reads the Excel file from the second row to the last row through to(2,) because the column headers are not needed. news function expand one row to multiple rows; ~.len()\2 is the number of rows we want to expand to, which is the rounded up value of N/2, like expanding the second row to 4 rows, and then get column values from the original row in order to form new rows; ~(1) represents the first column; the pound sign # represents the current number of round (the 1^st, 2^nd, 3^rd and 4^th time); ~(#*2) gets an even-numbered column (the 2^nd, 4^th, 6^th and 8^th columns); ~(#*2+1) gets odd-numbered columns (the 3^rd, 5^th, 7^th and 9^th columns). A3 uses groups function to perform grouping and aggregation.