CODEWITHSUNDEEP
pythonpandas
Shantanu
Shantanu

Reputation: 867

Pandas: How to apply a value to a group of rows based on a specific row value?

I have the following dataframe, where I want to recreate the column To Show?. The idea is if the Product group has a value of Red in the Color column of any row then I want the To Show? column with the value Yes for all the rows of Product A. For any Product group that does not have the value as Red, I want the value to be No for that column.

   Year  Week Product   Color To Show?
0  2019    21       A   Green      Yes
1  2019    22       A   Green      Yes
2  2019    23       A     Red      Yes
3  2019    24       A   Green      Yes
4  2019    25       C   Green       No
5  2019    26       C  Yellow       No
6  2019    27       C   Green       No
7  2019    28       B   Green      Yes
8  2019    29       B     Red      Yes
9  2019    30       B  Yellow      Yes

import pandas as pd

data = {
    'Year': [2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019],
    'Week': [21, 22, 23, 24, 25, 26, 27, 28, 29, 30],
    'Product': ['A', 'A', 'A', 'A', 'C', 'C', 'C', 'B', 'B', 'B'],
    'Color': ['Green', 'Green', 'Red', 'Green', 'Green', 'Yellow', 'Green', 'Green', 'Red', 'Yellow'],
    'To Show?': ['Yes', 'Yes', 'Yes', 'Yes', 'No', 'No', 'No', 'Yes', 'Yes', 'Yes']
}

df = pd.DataFrame(data)

I have tried using unique, but not sure how to parse the Series and assign value based on that?

df1 = df.groupby('Product')['Color'].unique()

Upvotes: 1

Views: 571

Answers (3)

Andy L.
Andy L.

Reputation: 25239

Use where to flip any non-'Red' of df.Color to NaN and groupby df.product; then, transform with first and replace

df['To Show?'] =  (df.Color.where(df.Color.eq('Red'))
                           .groupby(df.Product)
                           .transform('first') 
                           .replace(['Red', np.nan], ['Yes', 'No']))

Out[364]:
   Year  Week Product   Color To Show?
0  2019    21       A   Green      Yes
1  2019    22       A   Green      Yes
2  2019    23       A     Red      Yes
3  2019    24       A   Green      Yes
4  2019    25       C   Green       No
5  2019    26       C  Yellow       No
6  2019    27       C   Green       No
7  2019    28       B   Green      Yes
8  2019    29       B     Red      Yes
9  2019    30       B  Yellow      Yes

Or you may use map with the dictionary create from the mask of Product and Color and map this dict to Product and replace

d = dict(df.loc[df.Color.eq('Red'), ['Product', 'Color']].values)

Out[375]: {'A': 'Red', 'B': 'Red'}

df['To Show?'] = df.Product.map(d).replace(['Red', np.nan], ['Yes', 'No'])

Out[379]:
   Year  Week Product   Color To Show?
0  2019    21       A   Green      Yes
1  2019    22       A   Green      Yes
2  2019    23       A     Red      Yes
3  2019    24       A   Green      Yes
4  2019    25       C   Green       No
5  2019    26       C  Yellow       No
6  2019    27       C   Green       No
7  2019    28       B   Green      Yes
8  2019    29       B     Red      Yes
9  2019    30       B  Yellow      Yes

Upvotes: 1

Erfan
Erfan

Reputation: 42886

We can Groupby.transform with any (if any of the rows in the group is Red) and np.where for conditional creation of our new column:

m = df.groupby('Product')['Color'].transform(lambda x: x.eq('Red').any())

df['To Show?'] = np.where(m, 'Yes', 'No')

Output

   Year  Week Product   Color To Show?
0  2019    21       A   Green      Yes
1  2019    22       A   Green      Yes
2  2019    23       A     Red      Yes
3  2019    24       A   Green      Yes
4  2019    25       C   Green       No
5  2019    26       C  Yellow       No
6  2019    27       C   Green       No
7  2019    28       B   Green      Yes
8  2019    29       B     Red      Yes
9  2019    30       B  Yellow      Yes

Upvotes: 3

rafaelc
rafaelc

Reputation: 59274

Use transform to broadcast results and map to make them Yes/No

df['To Show?'] = df.groupby('Product').Color\
                                      .transform(lambda x: x.eq('Red').any())\
                                      .map({True: 'Yes', False: 'No'})

Upvotes: 2

Related Questions