I'm working on a dataframe where I need to add a new column which is based on other existing rows in the dataframe. Here is a simplified version of what I'm trying to do. Basically there is a dataframe with the purchase and sale record of a particular stock:

data = {'Name' :['Al', 'John', 'Jack', 'Jack', 'Al', 'John', 'Jack', 'Jack'],
'TradeType' : ['Purch', 'Sold', 'Sold', 'Purch', 'Sold', 'Sold', 'Purch', 'Sold'],
'Date' : [ 2019, 2020, 2021, 2022, 2019, 2020, 2021, 2022]}

df = pd.DataFrame(data)

I want to add a new column called 'nTrans2Year' that tells me how many transaction that person has done in the previous two years. The way I am currently doing it is such: I create e temporary dataframe with the right filters and than calculate its lenght.

df['nTrans2Year'] = 0
df = df.reset_index(drop=True)
for index, row in df.iterrows():
  insName = row['Name']
  date = row['Date']
  tradeType = row['TradeType']
  t_df = df[(df['Name'] == insName) & (df['TradeType'] == tradeType) & (df['Date'] < date) & (df['Date'] > (date - dt.timedelta(days = 365*2)))]
  df.loc[index ,'nTrans2Year'] = len(t_df)

the only issue with this approach is that it is very computationally intensive, and given that my original dataframe is 300k rows long it's not really an option. Does anybody have a more efficient way of achieving the same result? Cheers!

  • $\begingroup$ The example code you provided does not work with the dataframe you provided since you are referring to a column called FilingDate which is not present in the dataframe. $\endgroup$
    – Oxbowerce
    2 days ago
  • $\begingroup$ Add picture of resulting DB and test your code. Can't really you under these conditions. $\endgroup$ 2 days ago
  • $\begingroup$ Maybe something like this: df.groupby(['Name','TradeType'])['Date'].rolling(3).count().reset_index().rename(columns={'level_2':'time_delta','Date':'count'}) $\endgroup$ 2 days ago


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.