Home / Questions / 384667
Accepted
Goerman
Asked: 2020-08-26 06:26:13 +0800 CST 2020-08-26 06:26:13 +0800 CST

Is there a complement (opposite) method to groupby in pandas?

  • 772

In the following code it shows a grouping, adding the total of occurrences, assuming that you only have df2, what is the best way to obtain df?

>>> import pandas as pd
>>> d = {
    "var_1":['a','a','a','c','c','c','c'],
    "var_2":['b','b','b','d','d','d','d'],
    "total":[1,1,1,1,1,1,1],
    "days":['4','4','4','2','2','2','2']    
    }
>>> # Create a dataframe since a dictionary 
>>> df = pd.DataFrame(d)
>>> df
  var_1 var_2  total days
0     a     b      1    4
1     a     b      1    4
2     a     b      1    4
3     c     d      1    2
4     c     d      1    2
5     c     d      1    2
6     c     d      1    2
>>> df2 = df.groupby(['var_1','var_2','days']).sum('total').reset_index()
>>> df2
  var_1 var_2 days  total
0     a     b    4      3
1     c     d    2      4
python

2 Answers

  1. Best Answer

    Step 1: Create a column where each row contains a list of the same number of elements as repetitions of the row needed.

    df2.assign(x=df2.total.map(lambda x:[1]*x))

    Step 2: Use explodeto split each list into rows.

    df2.assign(x=df2.total.map(lambda x:[1]*x)).explode('x')

    Step 3: Drop the column.

    df2.assign(x=df2.total.map(lambda x:[1]*x)).explode('x').drop(columns='i')

    Step 4: Set the total column to 1

    df2.assign(x=df2.total.map(lambda x:[1]*x)).explode('x').drop(columns='i').assign(total=1)

    Step 5: Reset the index.

    df2.assign(x=df2.total.map(lambda x:[1]*x)).explode('x').drop(columns='x').assign(total=1).reset_index(drop=True)

    >>> import pandas as pd
>>> df2 = pd.DataFrame({'var_1':list('ac'),'var_2':list('bd'),'days':[4,2],'total':[3,4]})
>>> df2
  var_1 var_2  days  total
0     a     b     4      3
1     c     d     2      4
>>> df2.assign(x=df2.total.map(lambda x:[1]*x)).explode('x').drop(columns='x').assign(total=1).reset_index(drop=True)
  var_1 var_2  days  total
0     a     b     4      1
1     a     b     4      1
2     a     b     4      1
3     c     d     2      1
4     c     d     2      1
5     c     d     2      1
6     c     d     2      1
    • 1

  2. One way to do it:

    # Copiar DataFrame para eliminar referencia a df2.
>>> df3 = df2.copy()
# Siguiendo consejo de expandir columna sumada 'total'.
>>> df3['expand_total'] = df3.total.apply(lambda x: [1 for i in range(x)])
>>> df3
  var_1 var_2 days  total  expand_total
0     a     b    4      3     [1, 1, 1]
1     c     d    2      4  [1, 1, 1, 1]
# extraer lista transpuesta.
>>> s = df3.expand_total.apply(pd.Series, 1).stack()
>>> s
0  0    1.0
   1    1.0
   2    1.0
1  0    1.0
   1    1.0
   2    1.0
   3    1.0
dtype: float64
# Eliminar sub indice ya que no se necesita para el cruce
>>> s.index = s.index.droplevel(-1)
>>> s
0    1.0
0    1.0
0    1.0
1    1.0
1    1.0
1    1.0
1    1.0
dtype: float64
>>> s.name = 'total'
# Eliminar 'total' ya que sera remplazada en siguiente paso.
>>> del df3['total']
# Eliminar columnas temporal 'expand_total' 
>>> del df3['expand_total']
# Join by index
>>> df3.join(s)
  var_1 var_2 days  total
0     a     b    4    1.0
0     a     b    4    1.0
0     a     b    4    1.0
1     c     d    2    1.0
1     c     d    2    1.0
1     c     d    2    1.0
1     c     d    2    1.0

    Reference

    • 0