Detect consecutive monthly repetition in Pandas DataFrame column without iterating

user_2160637 0
Viewed 63

I am trying to detect if a value is repeated for two months in a row, on the same day, but I am not able to find a way without iterating through all the rows. The problem with iterating is that it makes it impossibly slow.

My Table:

          date country    cur    amount
0   2019-01-07     LTU    EUR    150.00
1   2019-01-08     LTU    EUR     50.00
2   2019-01-02     LTU    EUR    561.60
3   2019-01-02     LTU    EUR    990.14
4   2019-01-02     LTU    EUR    700.00
5   2019-02-07     LTU    EUR    150.00

What I am trying to achieve:

          date country    cur    amount  monthly
0   2019-01-07     LTU    EUR    150.00     True
1   2019-01-08     LTU    EUR     50.00    False
2   2019-01-02     LTU    EUR    561.60    False
3   2019-01-02     LTU    EUR    990.14    False
4   2019-01-02     LTU    EUR    700.00    False
5   2019-02-07     LTU    EUR    150.00     True

The value 150.00 in the EUR currency is repeated on the same day of the next month.

Thanks!

pandas python
3 Answers

You can try groupby with transform nunique

df['new'] = df.date.dt.strftime('%Y-%m').groupby(df['amount']).transform('nunique')>1
Out[512]: 
0     True
1    False
2    False
3    False
4    False
5     True
Name: date, dtype: bool
user_79645270

One option is to pull the month values, sort the columns, groupby, do a transform on the month column and get the boolean:

result = (df
          .assign(month = df.date.dt.month)
          .sort_values(['country', 'amount', 'month'])
          .groupby(['country', 'amount'])
          .month
          .transform(lambda x: x.iloc[-1] - x.iloc[0])
          .eq(1)
)

df.assign(monthly = result)

        date country  cur  amount  monthly
0 2019-01-07     LTU  EUR  150.00     True
1 2019-01-08     LTU  EUR   50.00    False
2 2019-01-02     LTU  EUR  561.60    False
3 2019-01-02     LTU  EUR  990.14    False
4 2019-01-02     LTU  EUR  700.00    False
5 2019-02-07     LTU  EUR  150.00     True

This works with the assumption that there is only one entry per month.

user_71757130

I ended up finding a solution inspired by sammywemmy's answer above:

mcycle = 1 # 1 for monthly cycle; 3 for quarterly, etc.
df['monthly'] = df
    .assign(year = df.date.dt.year)
    .assign(month = df.date.dt.month)
    .assign(day = df.date.dt.day)
    .sort_values(['year', 'currency', 'amount', 'day', 'month'])
    .groupby([df.date.dt.year, df['currency'], df['amount'], df.data.dt.day])
    .month
    .transform(lambda x: 
        [m+mcycle in x.values or m-mcycle in x.values for m in x]
    )
.eq(True)

Let me know if you have any suggestions for improvements - performance or pythonic style. I am just a beginner.

user_21606370
Related