CODEWITHSUNDEEP
pythonpandasdataframe
Ahmad
Ahmad

Reputation: 9658

Group by using agg and count

I have the following group by in pandas

   df = df.groupby("pred_text1").agg({
        "rouge_score":"first","pred_text1":"first", 
        "input_text":"first", "target_text":"first", "prefix":"first"})

I want to have another columns which counts the number in each group (repetition of each pred_text)

I know that using transform('count') after groupby can add such a new column, but I still need agg function too.

Upvotes: 0

Views: 361

Answers (2)

quasi-human
quasi-human

Reputation: 1928

You can use pd.NamedAgg function. (Pandas 0.25+)

Code:

import pandas as pd

# A sample dataframe
df = pd.DataFrame({
        'pred_text1': [chr(ord('A')+i%3) for i in range(10)],
        'rouge_score': [chr(ord('A')+i%5) for i in range(10)],
        'input_text': [chr(ord('A')+i%7) for i in range(10)],
        'target_text': [chr(ord('A')+i%9) for i in range(10)],
        'prefix': [chr(ord('A')+i%11) for i in range(10)],
    })

# Aggregation
df = df.groupby("pred_text1").agg(
        rouge_score=pd.NamedAgg("rouge_score", "first"),
        pred_text1=pd.NamedAgg("pred_text1", "first"),
        input_text=pd.NamedAgg("input_text", "first"),
        target_text=pd.NamedAgg("target_text", "first"),
        prefix=pd.NamedAgg("prefix", "first"),
        pred_count=pd.NamedAgg("pred_text1", "count"),
    )

Input:

(index) pred_text1 rouge_score input_text target_text prefix
0 A A A A A
1 B B B B B
2 C C C C C
3 A D D D D
4 B E E E E
5 C A F F F
6 A B G G G
7 B C A H H
8 C D B I I
9 A E C A J

Output:

(index) rouge_score pred_text1 input_text target_text prefix pred_count
A A A A A A 4
B B B B B B 3
C C C C C C 3

Upvotes: 1

MYousefi
MYousefi

Reputation: 1008

import pandas as pd
df = pd.DataFrame({'Group': ['G1', 'G1', 'G2', 'G3', 'G2', 'G1'], 'ValueLabel': [0, 1, 1, 1, 0, 2]})

You can do this in a few steps: First aggregate.

df = df.groupby('Group').agg({'ValueLabel': ['first', 'count']})

The new columns are a pd.MultiIndex type which we will flatten. After that we can create a mapping of the names to the labels we want and rename the columns.

df.columns = df.columns.to_flat_index()
mapper = {label: new_label for label, new_label in zip \
         (pd.MultiIndex.from_product([['ValueLabel'], \
         ['first', 'count']]), ['ValueFirstLabel', 'ValueCountLabel'])}
df.rename(mapper, axis=1, inplace=True)

Upvotes: 1

Related Questions