How to reassign value based on condition (Python)

Question

This is a sample dataframe, and I'd like to reassign the values from years b/w 2010-2013 from Company A's A1000 to Company A's B2000. What's a good way to get the result?

Code to generate the dataframe:

df = pd.DataFrame({'Year': [2010, 2010,2010,2010,2010,2011,2011,2011,2012,2012,2012,2012,2012,2013,2013,2013,2014], 
                   'Comapny': ['A','A','A','B','B','A','B','C','A','B','B','B','C','A','B','C','D'],
                   'Code': ['A1000','B2000','C3000','A1000','B2000','B2000','B2000','B2000','A1000','A1000',
                   'B2000','C3000','A1000','B2000','C3000','A1000','A1000'], 
                   'values': [1000,2000,3000,500,1000,2000,4000,4000,1500,4000,2000,6000,1000,5000,2000,1500,2000
]}
                  )

Desired Output:

score 1 · Answer 1 · answered Jul 07 '23 at 22:59

1

You can create a mask and the use df.loc:

mask = ((2010 <= df.Year) & (df.Year <= 2013)) & df.Company.eq('A') & df.Code.eq('A1000')

df.loc[mask, 'Code'] = 'B2000'
print(df)

Prints:

    Year Company   Code  values
0   2010       A  B2000    1000
1   2010       A  B2000    2000
2   2010       A  C3000    3000
3   2010       B  A1000     500
4   2010       B  B2000    1000
5   2011       A  B2000    2000
6   2011       B  B2000    4000
7   2011       C  B2000    4000
8   2012       A  B2000    1500
9   2012       B  A1000    4000
10  2012       B  B2000    2000
11  2012       B  C3000    6000
12  2012       C  A1000    1000
13  2013       A  B2000    5000
14  2013       B  C3000    2000
15  2013       C  A1000    1500
16  2014       D  A1000    2000

answered Jul 07 '23 at 22:59

Andrej Kesely

168,389
15
48
91

Thanks, Is there anyway to add those values up when reassigning? I know I can groupby again to get the aggregated data, but just wondering if there's any simpler way. For example, for 2010, Company A's value for Code B2000 would be 3000 (just a single line). – Jiamei Jul 07 '23 at 23:15
@Jiamei Do you mean `df = df.groupby(['Year', 'Company', 'Code']).sum().reset_index()` ? – Andrej Kesely Jul 07 '23 at 23:22
Right, that's the way that I can think of. I was wondering if there's anyway I could aggregate those values while doing the reassignment? Like if years b/w 2010-2013, company == 'A', Code == 'A1000', add the values to B200 of the same time period for Company A. – Jiamei Jul 07 '23 at 23:31
@Jiamei Can you please [edit] your question and put the desired result there? – Andrej Kesely Jul 07 '23 at 23:32
I just edited my question with the desired result. – Jiamei Jul 08 '23 at 05:14

How to reassign value based on condition (Python)

1 Answers1