We are naturally interested in order-related operations, and grouping operations may also involve the order. This article will compare the calculation abilities of Python and SPL in ordered grouping.

Grouping by positions

It is possible that the position information of members participates in the grouping and the grouping keys are related to positions (or sequence numbers) of the members. For example:

 

Here are the data:

time                       a                       b

0                          0.5                    -2.0

1                          0.5                    -2.0

2                          0.1                    -1.0

3                          0.1                    -1.0

4                          0.1                    -1.0

5                          0.5                    -1.0

6                          0.5                    -1.0

7                          0.5                    -3.0

8                          0.5                    -1.0

 

It is expected that every three rows are grouped together and the mode number is taken as the result of that group. And the expected result is as follows:

time                       a                       b

2                          0.5                    -2.0

5                          0.1                    -1.0

8                          0.5                    -1.0

 

Python

import pandas as pd file1="D:/data/position.txt" data1=pd.read_csv(file1,sep="\t") pos_seq=[i//3 for i in range(len(data1))] res1=data1.groupby(pos_seq).agg(lambda x:x.mode().iloc[-1]) print(res1)

Derive a column according to positions, and group by the integer quotient divided by 3 Group and aggregate according to the derived column

 

In order to group every three rows, we can divide the sequence number by 3 and take the integer quotient to get the grouping key, by which the grouping operation is performed. To get the mode number of each group, we can use the agg+lambda expression, which is actually an aggregation operation.

 

SPL

	A	B
1	D:\data\position.txt
2	=file(A1).import@t()
3	=A2.group((#-1)\3).new(~.max(time):time,~.mode(a):a,~.mode(b):b)	/group and aggregate

 

In SPL, we can get the grouping key with “#” rather than calculating an extra column of grouping keys separately. Here the grouping key is an integer and can be calculated using the group@n() function in SPL. This function is an efficient grouping method for natural numbers, which can directly obtain the correct grouped subsets with sequence numbers rather than performing the comparison operation, so it calculates much faster. Here we can change the code slightly as:

A3=A2.group@n((#+2)\3)...

 

 

Grouping when values change

Sometimes the data are ordered originally, we want to group only the adjacent records with the same value together during grouping, for example:

 

Here are the data:

      duration  location  user

0        10    house    A

1         5    house    A

2         5      gym    A

3         4      gym    B

4        10     shop    B

5         4      gym    B

6         6      gym    B

 

It is expected to get the sum of “duration” when “user” and “location” are the same consecutively and to get the sum again when the values of “location” are changed. And the expected result is:

   duration  location   user

        15    house    A

         5      gym    A

         4      gym    B

        10     shop    B

        10      gym    B

 

Python

file2="D:/data/value.txt" data2=pd.read_csv(file2,sep="\t") value_seq=(data2.location!=data2.location.shift()).cumsum() res2=data2.groupby(['user','location',value_seq], as_index=False, sort=False)['duration'].sum() print(res2)

The derived column, the values of the column are the same if the adjacent “location” values are the same; otherwise +1  Group and aggregate according to [user, location, derived column]

 

Python cannot group the adjacent members with the same value, but has to derive a column and take it as part grouping keys to complete the grouping operation. However, it takes great effort for us to come up with a relatively simple derived column.

 

SPL

	A	B
…	…
4	D:\data\value.txt
5	=file(A5).import@t()
6	=A6.groups@o(user,location;sum(duration))	/group with changed values

 

The groups@o() function in SPL scans the whole sequence in turn and adds the member to the current grouped subset if the grouping key value is the same as the grouping key of the last member; or generates a new grouped subset and adds the current member to it if the grouping values are not the same. Then after scanning, a batch of grouped subsets are obtained, thus the grouping operation is finally completed. This method in SPL does not need us to derive a new column and the syntax of it is also quite easy with only an additional @o option to the groups function. Such grouping operation only compares the adjacent values, so it executes faster, and this is exactly what the groupby function in Python cannot achieve.

 

Grouping when conditions change

There is another situation for the originally ordered data, that is, grouping the data when the conditions are changed. For example:

 

Here are the data:

ID          code

333_c_132   x

333_c_132   n06

333_c_132   n36

333_c_132   n60

333_c_132   n72

333_c_132   n84

333_c_132   n96

333_c_132   n108

333_c_132   n120

999_c_133   x

999_c_133   n06

999_c_133   n12

999_c_133   n24

998_c_134   x

998_c_134   n06

998_c_134   n12

998_c_134   n18

998_c_134   n36

997_c_135   x

997_c_135   n06

997_c_135   n12

997_c_135   n24

997_c_135   n36

996_c_136   x

996_c_136   n06

996_c_136   n12

996_c_136   n18

996_c_136   n24

996_c_136   n36

995_c_137   x

 

It is expected to retrieve one row out of every two “x”es in code column randomly, and the expected result is:

333_c_132   n06

999_c_133   n12

998_c_134   n18

997_c_135   n36

996_c_136   n18

 

Python

file3="D:/data/condition.txt" data3=pd.read_csv(file3,sep="\t") cond_seq=data3.code.eq('x').cumsum() res3=data3[data3.code.ne('x')].groupby(cond_seq).apply(lambda   x:x.sample(1)).reset_index(level=0,drop=True) print(res3)

The derived column Group according to the derived column and then sample

 

The logic of this operation in Python stays the same with the previous two examples, i.e., deriving a column of grouping keys on our own arduously and grouping according to that column. Also, Python provides the sample() function for sampling the data, making the sampling more effective.

 

SPL

	A	B
…	…
9	D:\data\condition.txt
10	=file(A9).import@t()
11	=A10.group@i(code=="x").conj((l=~.len(),if(l<2,,~.m(2:)(rand(l-1)+1))))	/group with changed conditions

 

The grouping key of the group@i() function in SPL is an expression, which generates a new grouped subset if the return result is true, that is, a new group will be generated when a certain condition is satisfied. This method also does not need to think of a derived column arduously and the calculation efficiency is excellent. But the sampling function is not provided in SPL, we have to write the sampling action manually, which can be easily completed using the rand() function in SPL.

 

In addition, @o and @i options are valid for both groups and group functions with the same effects.

 

As for processing logs, these three order-related grouping operations introduced in the article all work very well.

 

1. The log with fixed number of lines

 

The form of the log is:

 

IP, TIME, GET, URL, and BROWER are in the first line;

 

MODULE is in the seconds line;

 

USERID, UNAME, and LOCATION are in the third line.

 

In this case, use group((#-1)\3) and then process the log of each group.

 

2. The log with unfixed number of lines but each line has a mark

 

The form of such kind of log is as follows:

 

The number of lines of each user is different but each line is started with the ID of users.

 

In this case, use group@o(~.split(“\t”)(1)) and then process the log of each group.

 

3. The log with unfixed number of lines but there is a starting mark

 

The form of the log is:

 

The number of lines of each user is different but each user has a staring mark “userid”.

 

In this case, use group@i(~.split(“:”)(1)==”userid”), and then process the log of each group.

 

Summary

Python is not able to use the ordered data to improve efficiency because it adopts the same method for ordered grouping and unordered grouping. It is always necessary to derive a column and make it satisfy the grouping conditions, wasting a lot of useful information.

 

While SPL can make full use of the ordered data to make the grouping operation even faster. As long as the grouping conditions are well written, we don’t have to derive a column by ourselves, and @n, @o or @i options can be added to change the form of functions, which is very easy and practical.