Sums cumulatifs conditionnels dans Pandas

Question

Je suis un ancien utilisateur expert d'Excel repentant pour ses péchés. J'ai besoin d'aide pour recréer un calcul courant pour moi.

J'essaie de calculer la performance d'un portefeuille de prêts. Dans le numérateur, je calcule le total cumulé des pertes. Dans le dénominateur, j'ai besoin du solde initial des prêts inclus dans le total cumulé.

Je n'arrive pas à faire un groupby conditionnel dans Pandas pour y parvenir. C'est très simple dans Excel, donc j'espère que je me prends trop la tête.

Je n'ai pas trouvé grand chose sur le problème sur StackOverflow, mais c'était le plus proche : python pandas conditional cumulative sum

La chose que je n'arrive pas à comprendre, c'est que mes conditions sont basées sur des valeurs dans l'index et contenues dans des colonnes.

Voici mes données :

| Prêt   | Origination | Solde | Date NCO | NCO | Date de référence | Âge (mois) | Âge du NCO (mois) |
|--------|-------------|-------|----------|----|-------------------|-------------|-------------------|
| Prêt 1 | 1/31/2011   | 1000  | 1/31/2018 | 25 | 5/31/2019        | 100         | 84                |
| Prêt 2 | 3/31/2011   | 2500  |          | 0  | 5/31/2019        | 98          |                   |
| Prêt 3 | 5/31/2011   | 3000  | 1/31/2019 | 15 | 5/31/2019        | 96          | 92                |
| Prêt 4 | 7/31/2011   | 2500  |          | 0  | 5/31/2019        | 94          |                   |
| Prêt 5 | 9/30/2011   | 1500  | 3/31/2019 | 35 | 5/31/2019        | 92          | 90                |
| Prêt 6 | 11/30/2011  | 2500  |          | 0  | 5/31/2019        | 90          |                   |
| Prêt 7 | 1/31/2012   | 1000  | 5/31/2019 | 5  | 5/31/2019        | 88          | 88                |
| Prêt 8 | 3/31/2012   | 2500  |          | 0  | 5/31/2019        | 86          |                   |
| Prêt 9 | 5/31/2012   | 1000  |          | 0  | 5/31/2019        | 84          |                   |
| Prêt 10| 7/31/2012   | 1250  |          | 0  | 5/31/2019        | 82          |                   |

Dans Excel, je calculerais ce total en utilisant les formules suivantes :

Ligne de solde impayé : =SUMIFS(Solde,Âge (mois),Âge de référence)

NCO Cumulé : =SUMIFS(NCO,Âge (mois),>=Âge de référence,Âge du NCO (mois),<=Âge de référence)

Données :

| Âge de référence   | 85    | 90    | 95    | 100  
|---------------------|-------|-------|-------|------
| Solde impayé        | 16500 | 13000 | 6500  | 1000 
| NCO Cumulé          | 25    | 60    | 40    | 25   

L'objectif ici est d'inclure les éléments dans le solde impayé qui sont assez anciens pour avoir une observation pour NCO. Et les NCO sont le montant total qui s'est produit jusqu'à ce point pour ces prêts impayés.

MODIFICATION :

J'ai obtenu un calcul de cette manière. Mais est-ce le plus efficace ?

age_bins = list(np.arange(85, 101, 5))
final_df = pd.DataFrame()
df.fillna(value=0, inplace=True)
df["Âge du NCO (mois)"] = df["Âge du NCO (mois)"].astype(int)

for x in age_bins:

    age = x

    nco = df.loc[(df["Âge (mois)"] >= x) & (df["Âge du NCO (mois)"] <= x), "NCO"].sum()

    bal = df.loc[(df["Âge (mois)"] >= x), "Solde"].sum()

    temp_df = pd.DataFrame(
        data=[[age, nco, bal]],
        columns=["Âge", "NCO Cumulé", "Solde impayé"],
        index=[age],
    )

    final_df = final_df.append(temp_df, sort=True)

Answer 1

Vous utilisez des conditions complexes en fonction des variables. Il est facile de trouver une méthode vectorisée pour les sommes cumulatives simples, mais je ne peux pas imaginer une méthode élégante pour le Cumulative NCO.

Je reviendrais donc aux compréhensions Python :

data = [
    { 'Âge de référence': ref,
      'Solde impayé': df.loc[df.iloc[:,6]>=ref,'Balance'].sum(),
      'NCO cumulatif': df.loc[(df.iloc[:,6]>=ref)&(df.iloc[:,7]<=ref),
                   'NCO'].sum() }
    for ref in [85, 90, 95, 100]]

result = pd.DataFrame(data).set_index('Âge de référence').T

Il produit :

Âge de référence          85     90    95    100
NCO cumulatif          25     60    40    25
Solde impayé  16500  13000  6500  1000

Answer 2

Vous pourriez essayer de construire des groupes de prêts dans une plage d'âge donnée en utilisant pd.cut et utiliser groupby par la suite. Quelque chose comme cela :

import pandas as pd

df = pd.DataFrame([[1, 2, 3, 4, 5], [7, 8, 9, 10, 11]], index=['age', 'valeur']).T
df['groupes'] = pd.cut(df.age, [0, 1, 3, 5]) # définir les intervalles (0,1], (1,3], (3,5]
df.groupby('groupes')['valeur'].sum()

Answer 3

Je ne suis pas sûr de bien suivre la logique exacte que vous avez en tête, mais vous pouvez accomplir un sumifs avec la combinaison de pandas query et groupby.

Exemple

import pandas as pd
import numpy as np

age = np.random.randint(85, 100, 50)
balance = np.random.randint(1000, 2500, 50)
nco = np.random.randint(85, 100, 50)

df = pd.DataFrame({'age': age, 'balance': balance, 'nco':nco})

df['reference_age'] = df['age'].apply(lambda x: 5 * round(float(x)/5))

outstanding_balance = (
   df
   .query('age >= reference_age')
   .groupby('reference_age')
   [['balance']]
   .sum()
   .rename(columns={'balance': 'Outstanding Balance'}
   )

cumulative_nco = (
   df
   .query('age < reference_age')
   .groupby('reference_age')
   [['nco']]
   .sum()
   .rename(columns={'nco': 'cumulative nco'})
   .cumsum()
   )

result = outstanding_balance.join(cumulative_sum).T

résultat

reference_age            85       90       95
Outstanding Balance  2423.0  16350.0  13348.0
cumulative nco          NaN    645.0   1107.0