Soit l'exemple df (df1),

sample data

Nous pouvons obtenir df2 ou trame de données finale en manipulant les données de df1 de la manière suivante,

Étape 1 : supprimez tous les nombres positifs, y compris les zéros

Après l'étape 1, les exemples de données devraient ressembler à ceci:

sample data after Step1

Étape 2 : si column4 Une ligne est un nombre négatif et column4 B est vide, supprimez le nombre -ve de column4 A ligne

Étape 3 : si column4 Une ligne est vide et column4 B est un nombre négatif, conservez le nombre -ve de column4 ligne B

sample data after step2 and 3

Une fois les étapes 1, 2 et 3 terminées,

Étape 4 : si A et B de column4 sont négatifs,

Pour chaque ligne A et B de column4, vérifiez la valeur du côté gauche (LHS) (pour un mois donné) des mêmes lignes A et B de column4

Étape 4.1 : si l'une des valeurs LHS de A ou B est un nombre -ve, supprimez la valeur de ligne actuelle de B column4 et conservez la valeur de ligne actuelle de A {{X1 }}

Après l'étape 4.1, les exemples de données devraient ressembler à ceci,

sample data after step 4.1

Étape 4.2 :

Si la valeur LHS de A et B column4 est vide, conservez la valeur de ligne actuelle de B column4 et supprimez la valeur de ligne actuelle de A column4

Les exemples de données après l'étape 4.2 devraient ressembler à ceci:

sample data after step4.2

Puisque nous voyons encore deux nombres négatifs, nous effectuons à nouveau l'étape 4.1, puis la trame de données finale ou df2 ressemblera à,

sample data after Step4.1

Comment puis-je réaliser ce qui précède en utilisant des pandas? J'ai pu atteindre l'étape 1, mais je n'ai aucune idée de la façon de procéder plus loin. Toute aide serait grandement appréciée.

C'est l'approche que j'ai adoptée,

import pandas as pd
df = pd.read_excel('df1.xlsx', engine='openpyxl')
df.to_pickle('./df1.pkl')
unpickled_df = pd.read_pickle('./df1.pkl')
rem_cols = ['column2', 'column3', 'column5', 'column6', 'column7']
unpickled_df['g'] = unpickled_df.groupby(['column1', 'column4'] ).cumcount()
df1 = unpickled_df.drop(rem_cols, axis=1)
df1 = df1.set_index(['column1','g', 'column4'])
df1.columns = pd.to_datetime(df1.columns, format='%b-%y').strftime('%b-%y')
first_date = df1.columns[0]
df1 = df1.unstack(-1)
df1 = df1.mask(df1.ge(0))
m1 = (df1.xs('A', level=1, axis=1, drop_level=False).notna() & 
      df1.xs('B', level=1, axis=1, drop_level=False).rename(columns={'B':'A'}, level=1).isna())
m2 = (df1.xs('B', level=1, axis=1, drop_level=False).notna() &
      df1.xs('A', level=1, axis=1, drop_level=False).rename(columns={'A':'B'}, level=1).isna())

m = m1.join(m2)
df1 = df1.mask(m)
df2 = df1.groupby(level=1, axis=1).shift(1, axis=1)
mask1 = df1.notna() & df2.isna() & (df1.columns.get_level_values(1) == 'A')[ None, :]
mask1[first_date] = False
mask2 = df1.notna() & df2.notna() & (df1.columns.get_level_values(1) == 'B')[ None, :]
df1 = df1.mask(mask1).mask(mask2).stack(dropna=False)
unpickled_df = unpickled_df[rem_cols + ['column1','g', 'column4']].join(df1, on=['column1','g', 'column4'])
#print(unpickled_df)

Petites données de test: df1,

{'column1': ['ABC', 'ABC', 'CDF', 'CDF'], 'column4': ['A', 'B', 'A', 'B'], 'Feb-21': [0, 10, 0, 0], 'Mar-21': [0, 0, 70, 70], 'Apr-21': [-10, -10, -8, 60], 'May-21': [-30, -60, -10, 40], 'Jun-21': [-20, 9, -40, -20], 'Jul-21': [30, -10, 0, -20], 'Aug-21': [-30, -20, 0, -20], 'Sep-21': [0, -15, 0, -20], 'Oct-21': [0, -15, 0, -20]}

Df2 (sortie attendue),

{'column1': ['ABC', 'ABC', 'CDF', 'CDF'], 'column4': ['A', 'B', 'A', 'B'], 'Feb-21': [nan, nan, nan, nan], 'Mar-21': [nan, nan, nan, nan], 'Apr-21': [nan, -10.0, nan, nan], 'May-21': [-30.0, nan, nan, nan], 'Jun-21': [nan, nan, nan, -20.0], 'Jul-21': [nan, -10.0, nan, -20.0], 'Aug-21': [-30.0, nan, nan, -20.0], 'Sep-21': [nan, -15.0, nan, -20.0], 'Oct-21': [nan, -15.0, nan, -20.0]}

Données de test:

Df1

{'column1': ['CT', 'CT', 'NBB', 'NBB', 'CT', 'CT', 'NBB', 'NBB', 'HHH', 'HHH', 'TP1', 'TP1', 'TPR', 'TPR', 'PP1', 'PP1', 'PP1', 'PP1'], 'column2': ['POUPOU', 'POUPOU', 'PRPRP', 'PRPRP', 'STDD', 'STDD', 'STDD', 'STDD', 'STEVT', 'STEVT', 'SYSYS', 'SYSYS', 'SYSYS', 'SYSYS', 'SHW', 'SHW', 'JV', 'JV'], 'column3': ['V', 'CV', 'V', 'CV', 'V', 'CV', 'V', 'CV', 'V', 'CV', 'V', 'CV', 'V', 'CV', 'V', 'CV', 'V', 'CV'], 'column4': ['A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'A', 'B'], 'column5': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'column6': ['BBB', 'BBB', 'CCC', 'CCC', 'BBB', 'BBB', 'BBB', 'BBB', 'VVV', 'VVV', 'CHCH', 'CHCH', 'CHCH', 'CHCH', 'CCC', 'CCC', 'CHCH', 'CHCH'], 'column7': ['Apr-21', 'Apr-21', 'Apr-21', 'Apr-21', 'Apr-21', 'Apr-21', 'Apr-21', 'Apr-21', 'Mar-21', 'Mar-21', 'Mar-21', 'Mar-21', 'Mar-21', 'Mar-21', 'Apr-21', 'Apr-21', 'Mar-21', 'Mar-21'], 'Feb-21': [11655, 0, 0, 0, 121117, 0, 14948, 0, 0, 0, 0, 0, 0, 0, 1838, 0, 0, 0], 'Mar-21': [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, -16474.0, -16474.0, 7000.0, 7000.0, -19946.0, -19946.0, 16084.44444444444, 0.0, 0.0, 0.0], 'Apr-21': [104815.0, 104815.0, 17949.0, 17949.0, 96132.0, 96132.0, 0.0, 0.0, -17001.0, -33475.0, -878.0, 6122.0, 8398.0, -11548.0, -5297.073170731703, -5297.073170731703, -282.0, -282.0], 'May-21': [78260.0, 183075.0, 42557.0, 60506.0, -15265.0, 80867.0, -18.0, -18.0, 21084.0, -12391.0, -1831.0, 4291.0, 2862.0, -8686.0, 5261.25, -35.8231707317027, -369.0, -651.0], 'Jun-21': [-52480.0, 130595.0, -13258.0, 47248.0, -35577.0, 45290.0, 2434.0, 2416.0, 31147.0, 18756.0, -4310.0, -19.0, -4750.0, -13436.0, -92.0, -127.8231707317027, -280.0, -931.0], 'Jul-21': [-174544.0, -43949.0, -38127.0, 9121.0, -124986.0, -79696.0, -9707.0, -7291.0, 13577.0, 32333.0, 0.0, -19.0, -15746.0, -29182.0, 93.0, -34.8231707317027, -319.0, -1250.0], 'Aug-21': [35498.0, -8451.0, -37094.0, -27973.0, 79021.0, -675.0, -1423.0, -8714.0, 32168.0, 64501.0, 0.0, -19.0, 18702.0, -10480.0, 4347.634146341465, 4312.810975609762, -341.0, -1591.0], 'Sep-21': [44195.0, 35744.0, 2039.0, -25934.0, 70959.0, 70284.0, 2816.0, -5898.0, 38359.0, 102860.0, 0.0, -19.0, 18119.0, 7639.0, 5302.222222222219, 9615.033197831981, 0.0, -1591.0], 'Oct-21': [-13163.0, 22581.0, -4773.0, -30707.0, 205080.0, 275364.0, -709.0, -6607.0, -1397.0, 101463.0, 0.0, -19.0, 0.0, 7639.0, -34.0, 9581.033197831981, 0.0, -1591.0]}

Df2 (sortie attendue),

{'column1': ['CT', 'CT', 'NBB', 'NBB', 'CT', 'CT', 'NBB', 'NBB', 'HHH', 'HHH', 'TP1', 'TP1', 'TPR', 'TPR', 'PP1', 'PP1', 'PP1', 'PP1'], 'column2': ['POUPOU', 'POUPOU', 'PRPRP', 'PRPRP', 'STDD', 'STDD', 'STDD', 'STDD', 'STEVT', 'STEVT', 'SYSYS', 'SYSYS', 'SYSYS', 'SYSYS', 'SHW', 'SHW', 'JV', 'JV'], 'column3': ['V', 'CV', 'V', 'CV', 'V', 'CV', 'V', 'CV', 'V', 'CV', 'V', 'CV', 'V', 'CV', 'V', 'CV', 'V', 'CV'], 'column4': ['A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'A', 'B', 'A', 'B'], 'column5': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'column6': ['BBB', 'BBB', 'CCC', 'CCC', 'BBB', 'BBB', 'BBB', 'BBB', 'VVV', 'VVV', 'CHCH', 'CHCH', 'CHCH', 'CHCH', 'CCC', 'CCC', 'CHCH', 'CHCH'], 'column7': ['Apr-21', 'Apr-21', 'Apr-21', 'Apr-21', 'Apr-21', 'Apr-21', 'Apr-21', 'Apr-21', 'Mar-21', 'Mar-21', 'Mar-21', 'Mar-21', 'Mar-21', 'Mar-21', 'Apr-21', 'Apr-21', 'Mar-21', 'Mar-21'], 'Feb-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan], 'Mar-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, -16474.0, nan, nan, nan, -19946.0, nan, nan, nan, nan], 'Apr-21': [nan, nan, nan, nan, nan, nan, nan, nan, -17001.0, nan, nan, nan, nan, -11548.0, nan, -5297.073170731703, nan, -282.0], 'May-21': [nan, nan, nan, nan, nan, nan, nan, -18.0, nan, -12391.0, nan, nan, nan, -8686.0, nan, -35.8231707317027, -369.0, nan], 'Jun-21': [nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, nan, -19.0, -4750.0, nan, -92.0, nan, -280.0, nan], 'Jul-21': [nan, -43949.0, nan, nan, nan, -79696.0, nan, -7291.0, nan, nan, nan, -19.0, -15746.0, nan, nan, -34.8231707317027, -319.0, nan], 'Aug-21': [nan, -8451.0, nan, -27973.0, nan, -675.0, -1423.0, nan, nan, nan, nan, -19.0, nan, -10480.0, nan, nan, -341.0, nan], 'Sep-21': [nan, nan, nan, -25934.0, nan, nan, nan, -5898.0, nan, nan, nan, -19.0, nan, nan, nan, nan, nan, -1591.0], 'Oct-21': [nan, nan, -4773.0, nan, nan, nan, -709.0, nan, nan, nan, nan, -19.0, nan, nan, nan, nan, nan, -1591.0]}

Remarque: j'ai implémenté mon code sur la base des données de test fournies. Les exemples de données sont simplement destinés à se concentrer sur les colonnes censées être manipulées.

4
royalewithcheese 1 mars 2021 à 15:36

1 réponse

Meilleure réponse

Et ça ?

Pour chaque étape, je groupe sur column1, puis je place column4 comme index et je travaille sur la matrice de transposition avec vos critères. Notez que j'ai extrapolé un peu sur vos critères pour correspondre à vos résultats assistés (j'espère que c'est correct mais vous devrez vérifier cela).

Notez également que j'ai gardé chaque étape séparée pour la rendre plus facile à lire. Mais il serait plus efficace de faire le regroupement / indexation / transposition d'un seul coup et de travailler sur votre algorithme à partir de là.

import pandas as pd
import numpy as np
pd.set_option('display.max_columns', None)


df1 = pd.DataFrame(
        {'column1': ['ABC', 'ABC', 'CDF', 'CDF'], 'column4': ['A', 'B', 'A', 'B'], 'Feb-21': [0, 10, 0, 0], 'Mar-21': [0, 0, 70, 70], 'Apr-21': [-10, -10, -8, 60], 'May-21': [-30, -60, -10, 40], 'Jun-21': [-20, 9, -40, -20], 'Jul-21': [30, -10, 0, -20], 'Aug-21': [-30, -20, 0, -20], 'Sep-21': [0, -15, 0, -20], 'Oct-21': [0, -15, 0, -20]}
        )

print(df1)
print('-'*50)

df = df1.copy()

#step1 :
df.iloc[:, 2:] = df.iloc[:, 2:].where(df.iloc[:, 2:] < 0, np.nan)


print(df)
print('-'*50)

#step2 :
def step2(df):
    df = df.set_index("column4").T
    ix = df[(df.A<=0) & (df.B.isnull())].index
    df.loc[ix, "A"] = np.nan
    return df.T
df = df.groupby('column1').apply(step2)
df.reset_index(drop=False, inplace=True)
print(df)
print('-'*50)


#step3 :
def step3(df):
    df = df.set_index("column4").T
    ix = df[(df.A.isnull()) & (df.B>=0)].index
    df.loc[ix, "B"] = df.loc[ix, "A"]
    return df.T
df = df.groupby('column1').apply(step3)
df.reset_index(drop=False, inplace=True)
print(df)
print('-'*50)

#step4 :
def step4(df):
    df = df.set_index("column4").T
    a_pos = df.columns.get_loc('A')
    b_pos = df.columns.get_loc('B')
    #step 4.1
    ix = df[(df.A<0) & (df.B<0)].index
    if len(ix):
        ix = df.index.get_indexer(ix)
        left_pos = ix-1
        condition_left = df.iloc[left_pos].notnull().any(axis=1)
        ix = condition_left[condition_left].index
        ix = df.index.get_indexer(ix)
        df.iloc[ix+1, b_pos] = np.nan
    
    #step 4.2
    ix = df[(df.A<0) & (df.B<0)].index
    if len(ix):
        ix = df.index.get_indexer(ix)
        left_pos = ix-1
        condition_left = df.iloc[left_pos].isnull().all(axis=1)    
        ix = condition_left[condition_left].index
        ix = df.index.get_indexer(ix)
        df.iloc[ix+1, a_pos] = np.nan
    
    #step 4.1 (again)
    ix = df[(df.A<0) & (df.B<0)].index
    if len(ix):
        ix = df.index.get_indexer(ix)
        left_pos = ix-1
        condition_left = df.iloc[left_pos].notnull().any(axis=1)
        ix = condition_left[condition_left].index
        ix = df.index.get_indexer(ix)
        df.iloc[ix+1, b_pos] = np.nan
        
    return df.T

df = df.groupby('column1').apply(step4)
df.reset_index(drop=False, inplace=True)
print(df)
print('-'*50)

MODIFIER

Je suppose ici (sur la base de votre commentaire précédent) que votre dataframe sera toujours composée de lignes A / B en alternance (et que l'ordre dans la dataframe est valide). Nous devrons alors calculer un index artificiel pour identifier chaque paire de lignes.

Notez que j'ai utilisé un fillna sur vos colonnes (principalement column5) car c'est une bonne pratique avec les commandes groupby. En raison de plusieurs niveaux de colonnes, je ne suis pas sûr que cela aura un impact de toute façon ...

L'indexation booléenne commence à être délicate lors de la gestion de plusieurs niveaux de colonnes. Vous verrez que je vais calculer chaque "colonne" en un numpy.array (en utilisant la méthode .values). D'une manière ou d'une autre, les pandas n'effectueront pas la correspondance booléenne sur plusieurs colonnes, je ne sais pas exactement pourquoi.

Alors ça va:

df = pd.DataFrame(  ...  )

#Compute the unique index for each pair of rows
df.reset_index(drop=False, inplace=True)
ix = df.index
ix = ix[ix%2==0]
df.loc[ix, 'index'] = df.shift(-1).loc[ix, 'index']

#step1 :
cols = [x for x in df.columns.tolist() if not x.startswith('column') and x != "index"]
df[cols] = df[cols].where(df[cols] < 0, np.nan)


cols_index = ["column4", "column1", "column2", "column3", "column5", "column6", "column7"]
df[cols_index] = df[cols_index].fillna(-1)

#step2 :
def step2(df):
    df = df.set_index(cols_index).drop('index', axis=1).T
    ix = df[
            (df.A<=0).values
            & df.B.isnull().values
         ].index
    df.loc[ix, "A"] = np.nan
    return df.T
df = df.groupby('index').apply(step2)
print(df)
df.reset_index(drop=False, inplace=True)
print(df)
print('-'*50)


#step3 :
def step3(df):
    df = df.set_index(cols_index).drop('index', axis=1).T
    ix = df[
            df.A.isnull().values 
            & (df.B>=0).values
            ].index
    df.loc[ix, "B"] = df.loc[ix, "A"]
    return df.T
df = df.groupby('index').apply(step3)
df.reset_index(drop=False, inplace=True)
print(df)
print('-'*50)

#step4 :
def step4(df):
    df = df.set_index(cols_index).drop('index', axis=1).T
    a_pos = df.columns.get_loc('A')
    b_pos = df.columns.get_loc('B')
    
    #step 4.1
    ix = df[
            (df.A<0).values
            & (df.B<0).values
            ].index
    if len(ix):
        ix = df.index.get_indexer(ix)
        left_pos = ix-1
        condition_left = df.iloc[left_pos].notnull().any(axis=1)
        ix = condition_left[condition_left].index
        ix = df.index.get_indexer(ix)
        df.iloc[ix+1, b_pos] = np.nan
    
    #step 4.2
    ix = df[
            (df.A<0).values
            & (df.B<0).values
            ].index
    if len(ix):
        ix = df.index.get_indexer(ix)
        left_pos = ix-1
        condition_left = df.iloc[left_pos].isnull().all(axis=1)    
        ix = condition_left[condition_left].index
        ix = df.index.get_indexer(ix)
        df.iloc[ix+1, a_pos] = np.nan
    
    #step 4.1 (again)
    ix = df[
            (df.A<0).values
            & (df.B<0).values
            ].index
    if len(ix):
        ix = df.index.get_indexer(ix)
        left_pos = ix-1
        condition_left = df.iloc[left_pos].notnull().any(axis=1)
        ix = condition_left[condition_left].index
        ix = df.index.get_indexer(ix)
        df.iloc[ix+1, b_pos] = np.nan
        
    return df.T

df = df.groupby('index').apply(step4)
df.reset_index(drop=False, inplace=True)
print(df)
print('-'*50)

Et si vous souhaitez restaurer votre colonne5:

df[cols_index] = df[cols_index].replace(-1, np.nan)
1
tgrandje 7 mars 2021 à 14:23