Drag down formula in python

Question

Is there a way to calculate the (multiplier x value) for the 5 years after a True, for each fruit? Shown the formulae here for clarity

df   

fruit   year     value     Criteria        multiplier        multiplier x value
apple   1950      2
apple   1951      3
apple   1952      3
apple   1953      4
apple   1954      5
apple   1955      4       True           1.4                        (=1.4*4)
apple   1956      7                                                 (=1.4*7)
..
apple   2000      8                                                 (=1.4*8)

banana  1950     333                                                
banana  1951     335

Gerd · Accepted Answer · 2020-09-29T12:18:04.680

1

You can use df[df['Criteria'] == True].index to find the indices of all True rows. Now you can fill the subsequent rows with the corresponding multiplier like this:

for i in df[df['Criteria'] == True].index:
  df.loc[i:i+4, 'multiplier'] = df.loc[i]['multiplier']

and finally write the results to the multiplier x value column like this:

df['multiplier x value'] = df['multiplier'] * df['value']

This assumes that all your emtpy multiplier cells are filled with None or 0.

If you need this for each fruit, use groupby to split the DataFrame and run the loop on each subframe:

result = pd.DataFrame()
for subframe in [x for _, x in df.groupby('fruit')]:
  for i in subframe[subframe['Criteria'] == True].index:
    subframe.loc[i:i+4, 'multiplier'] = subframe.loc[i]['multiplier']
  result = pd.concat([result, subframe])

result['multiplier x value'] = result['multiplier'] * result['value']

edited Sep 29 '20 at 12:18

answered Sep 29 '20 at 12:01

Gerd

2,568
1
7
20

Thank you! Would you mind explaining what ```for subframe in [x for _, x in df.groupby('fruit')``` means? I understand the groupby but not the bit before. – asd Sep 29 '20 at 14:15
@asd: `groupby` returns a tuple of the group name and the group data, so what happens is that the names are assigned to `_` (i.e. are not needed and will be discarded) and the data are assigned to `x` and used as a list, which is iterated through by the outer `for` loop. You can `print(subframe)` to see the content. – Gerd Sep 29 '20 at 18:57
Further reading on [iterating through groups](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html#iterating-through-groups). – Gerd Sep 29 '20 at 19:00
See also [this related question](https://stackoverflow.com/questions/45797633/looping-over-groups-in-a-grouped-dataframe). – Gerd Sep 29 '20 at 19:06

Drag down formula in python

1 Answers1