How to modify column values in a data frame based on previous years value in another column of the same dataframe for same company

user_11161687 0
Viewed 81

Please find below the input and sample output:

input and output image If the count is null , then the next years weight becomes zero. We need to group by company and year. Please note that the starting and ending years may be different for different companies. Also if a year is missing then automatically the next available year should have zero.For e.g. def has data upto 2016 and then 2018(2017 is missing). Since 2017 is missing 2018 weight should be zero as we are assuming missing years have null values.

I have also added an image of sample input and output

pandas dataframe python-3.x python
3 Answers

df has columns company, year, weight, count

flag = False
for index, row in df.iterrows():
    if flag:
        row['weight'] = 0
        flag = False
    if row['count'] is None:
        flag = True
user_121272120

If I understand your question correctly, what you need is pandas.DataFrame.shift:

Assume your pandas.DataFrame is named df:

import numpy as np

df.sort_values(['company', 'year'], inplace=True)
is_previous_null = df.loc[:, 'count'].shift(1).isnull()  # Is the previous 'count' value null?
is_same_company = (df.loc[:, 'company'] == df.loc[:, 'company'].shift(1))  # Check if the previous row's 'company' value is the same as the current one
df.loc[is_previous_null & is_same_company, 'value'] = 0
user_108654880

Solution if consecutive years per company - first replace missing values by helper values - e.g. tmp, then use DataFrameGroupBy.shift and compare tmp.

Last set 0 by DataFrame.loc:

df = df.sort_values(['company', 'year'])

mask = df.assign(count=df['count'].fillna('tmp')).groupby('company')['count'].shift().eq('tmp')

df.loc[mask, 'weight'] = 0
print (df)
  company  year  weight  count
0     abc  2016     0.7    1.0
1     abc  2017     0.3    NaN
2     abc  2018     0.0    3.0
3     def  2015     0.6    6.0
4     def  2016     0.6    NaN
5     def  2017     0.0    7.0
6     def  2018     0.7    5.0

EDIT:

First add new years by reindex per groups with minimal and maximal years:

s = (df.set_index('year')
      .groupby('company')['count']
      .apply(lambda x: x.reindex(np.arange(x.index.min(), x.index.max() + 1)).fillna('tmp')))
print (s)
company  year
abc      2016      1
         2017    tmp
         2018      3
def      2015      6
         2016      8
         2017    tmp
         2018      5
Name: count, dtype: object

Then shift like in original solution per company, here by first level company and compare by tmp:

m = s.groupby(level=0).shift().eq('tmp').rename('m')
print (m)
company  year
abc      2016    False
         2017    False
         2018     True
def      2015    False
         2016    False
         2017    False
         2018     True
Name: m, dtype: bool

Create mask with same index like original DataFrame with join:

mask = df.join(m, on=['company','year'])['m']
print (mask)
0    False
1    False
2     True
3    False
4    False
5     True
Name: m, dtype: bool

Set 0 values:

df.loc[mask, 'weight'] = 0
print (df)
  company  year  weight  count
0     abc  2016     0.7    1.0
1     abc  2017     0.3    NaN
2     abc  2018     0.0    3.0
3     def  2015     0.6    6.0
4     def  2016     0.6    8.0
5     def  2018     0.0    5.0
user_29010020
Related