Mean grouped by two columns with window by 3 months and NaN for less than 3 months

user_5681578 0
Viewed 56

I have to apply the mean calculation in this dataset by customer, account but this mean needs to be applied to each 3 months in these groups. For the customer A1200 that don't has 3 months, the result need to be NaN.

customer    account    month        invoice
C1000       A1100      2019-10-01   34000
                       2019-11-01   55000
                       2019-12-01   80000
            A1200      2019-10-01   90000
                       2019-11-01   55000
            A1300      2019-10-01   10000
                       2019-11-01   10000
                       2019-12-01   20000
C2000       A2100      2019-10-01   78000
                       2019-11-01   55000
                       2019-12-01   80000

I tried to use this command, but the average looks incorrect.

df_3m.groupby(['customer','account']).mean()

Are there some ideias in pandas or pyspark?

pandas-groupby pyspark pandas python-3.x python
2 Answers

Data

+----------+---------+----------+----------+
| customer | account |  month   |  invoice |
+----------+---------+----------+----------+
| C1000    | A1100   | 01-10-19 |    34000 |
| C1000    | A1100   | 01-11-19 |    55000 |
| C1000    | A1100   | 01-12-19 |    80000 |
| C1000    | A1200   | 01-10-19 |    90000 |
| C1000    | A1200   | 01-11-19 |    55000 |
| C1000    | A1300   | 01-10-19 |    10000 |
| C1000    | A1300   | 01-11-19 |    10000 |
| C1000    | A1300   | 01-12-19 |    20000 |
| C2000    | A2100   | 01-10-19 |    78000 |
| C2000    | A2100   | 01-11-19 |    55000 |
| C2000    | A2100   | 01-12-19 |    80000 |
+----------+---------+----------+----------+

Your Query 

res = df_3m.groupby(['customer','account']).mean()

Query to filter accounts with less than 3 months

lt_3 = df.groupby(['account']).count() >2

Final Result 

res[lt_3]

Output

+----------+---------+--------------+
| customer | account |   invoice    |
+----------+---------+--------------+
| C1000    | A1100   | 56333.333333 |
|          | A1200   | NaN          |
|          | A1300   | 13333.333333 |
| C2000    | A2100   | 71000.000000 |
+----------+---------+--------------+
user_40462740

You can try this:

df['month'] = pd.to_datetime(df['month'])
df = df.groupby(by=['customer', 'account']).mean()[df.groupby(by=['customer', 'account']).count() > 2].reset_index()
print(df)

Output:

  customer account       invoice
0    C1000   A1100  56333.333333
1    C1000   A1200           NaN
2    C1000   A1300  13333.333333
3    C2000   A2100  71000.000000
user_61683230
Related