MaxCompute:Aggregation

• PyODPS DataFrames provide the groupby function to group data. After the data is grouped, call the agg or aggregate function to aggregate the data. The result columns include the grouped column and the aggregated column.

  >>> iris.groupby('name').agg(iris.sepallength.max(), smin=iris.sepallength.min())

  The following result is returned:

                name  sepallength_max  smin
  0      Iris-setosa              5.8   4.3
  1  Iris-versicolor              7.0   4.9
  2   Iris-virginica              7.9   4.9

• PyODPS DataFrames provide the value_counts function. After the data is grouped based on a specified column, you can sort the groups in descending order based on the number of distinct values in each group.
  • Call the groupby function.

    >>> iris.groupby('name').agg(count=iris.name.count()).sort('count', ascending=False).head(5)

    The following result is returned:

                  name  count
    0   Iris-virginica     50
    1  Iris-versicolor     50
    2      Iris-setosa     50

  • Call the value_counts function.

    >>> iris['name'].value_counts().head(5)

    The following result is returned:

                  name  count
    0   Iris-virginica     50
    1  Iris-versicolor     50
    2      Iris-setosa     50

• You can retrieve the column name of a single aggregated column. However, this operation restricts you to use only aggregate functions to manage the values in the aggregated column.

  >>> iris.groupby('name').petallength.sum()

  The following result is returned:

     petallength_sum
  0             73.2
  1            213.0
  2            277.6

  >>> iris.groupby('name').agg(iris.petallength.notnull().sum())

  The following result is returned:

                name  petallength_sum
  0      Iris-setosa               50
  1  Iris-versicolor               50
  2   Iris-virginica               50

• You can also group data by constant value. This operation requires Scalar initialization.

  >>> from odps.df import Scalar
  >>> iris.groupby(Scalar(1)).petallength.sum()

  The following result is returned:

     petallength_sum
  0            563.8

class Agg(object):

    def buffer(self):
        return [0.0, 0]

    def __call__(self, buffer, val):
        buffer[0] += val
        buffer[1] += 1

    def merge(self, buffer, pbuffer):
        buffer[0] += pbuffer[0]
        buffer[1] += pbuffer[1]

    def getvalue(self, buffer):
        if buffer[1] == 0:
            return 0.0
        return buffer[0] / buffer[1]

>>> iris.sepalwidth.agg(Agg)

3.0540000000000007

• If the data type of the output is different from the data type of the input, you must specify the data type for the output.

  >>> iris.sepalwidth.agg(Agg, 'float')

• You can use custom aggregations to group and aggregate data.

  >>> iris.groupby('name').sepalwidth.agg(Agg)

  The following result is returned:

     petallength_aggregation
  0                    3.418
  1                    2.770
  2                    2.974

• You can use the agg function to call custom aggregations on multiple columns.

  class Agg(object):
  
      def buffer(self):
          return [0.0, 0.0]
  
      def __call__(self, buffer, val1, val2):
          buffer[0] += val1
          buffer[1] += val2
  
      def merge(self, buffer, pbuffer):
          buffer[0] += pbuffer[0]
          buffer[1] += pbuffer[1]
  
      def getvalue(self, buffer):
          if buffer[1] == 0:
              return 0.0
          return buffer[0] / buffer[1]

  >>> from odps.df import agg
  >>> to_agg = agg([iris.sepalwidth, iris.sepallength], Agg, rtype='float')  # Call custom aggregations on two columns. 
  >>> iris.groupby('name').agg(val=to_agg)

  The following result is returned:

                name       val
  0      Iris-setosa  0.682781
  1  Iris-versicolor  0.466644
  2   Iris-virginica  0.451427

• To call an existing user-defined aggregate function (UDAF) in MaxCompute, you need to only specify the name of the UDAF.

  >>> iris.groupby('name').agg(iris.sepalwidth.agg('your_func'))  # Aggregate the values in a single column. 
  >>> to_agg = agg([iris.sepalwidth, iris.sepallength], 'your_func', rtype='float')
  >>> iris.groupby('name').agg(to_agg.rename('val'))  # Aggregate the values in multiple columns. 

  Note Due to limits of Python user-defined functions (UDFs), you cannot specify the LIST or DICT type as the input or output data type for custom aggregations.

>>> df = DataFrame(pd.DataFrame({'a': np.random.randint(100000, size=100000)}))
>>> df.a.hll_count()

63270

>>> df.a.nunique()

63250