CODEWITHSUNDEEP
pythonpython-3.xpandasdataframegroup-by
turong
turong

Reputation: 1622

Pandas DataFrame add new column values based on group by multiple conditions

I have a DataFrame as below

       Color  Month  Quantity
index                        
0          1      1     34047
1          1      2     36654
2          2      3     37291
3          2      4     35270
4          3      5     35407
5          1     12      9300

I want to add a more extra column PrevoiousMonthQty to this Dataframe with the filled values in the Qty column by the logic that we will group by (Color, Month) and the Month is the Previous Month

The target DataFrame I expected looks like this

enter image description here

Some logic explanation can be seen as

enter image description here

Any helps would be very much appreciated.

Thank you very much.

Upvotes: 1

Views: 1042

Answers (3)

anky
anky

Reputation: 75080

Here is a way using Multindex and map after finding the previous month:

prev_month = pd.to_datetime(df['Month'],format='%m').sub(pd.Timedelta(1,unit='m')).dt.month

m = df.set_index(['Color','Month'])['Quantity']

final = (df.assign(Prev_Month_Value=pd.MultiIndex.from_arrays([df['Color'],prev_month])
                                                          .map(m).fillna(0)))

#To assign into the existing df,use below code instead of df.assign() which returns a copy
#df['Previous Month Value'] = (pd.MultiIndex.from_arrays([df['Color'],prev_month])
#                                                              .map(m).fillna(0)

Output:

       Color  Month  Quantity  Prev_Month_Value
index                                          
0          1      1     34047            9300.0
1          1      2     36654           34047.0
2          2      3     37291               0.0
3          2      4     35270           37291.0
4          3      5     35407               0.0
5          1     12      9300               0.0

Details:

Step1 : Find previous month by converting Month column to datetime and subtract 1 month using pd.Timedelta.

Step2: Create a multiindex series with Quantity as value and Color and Month as index.

Step3: Create a MultiIndex using Color and prev_month series and map it back as new column (also fill nan with 0)

Upvotes: 2

jezrael
jezrael

Reputation: 862641

Use DataFrame.pivot for reshape DataFrame and add full months by DataFrame.reindex:

df1 = df.pivot('Color','Month','Oty').reindex(columns=range(1,13))
print (df1)
Month        1        2        3        4        5   6   7   8   9  10  11  \
Color                                                                        
1      34047.0  36654.0      NaN      NaN      NaN NaN NaN NaN NaN NaN NaN   
2          NaN      NaN  37291.0  35270.0      NaN NaN NaN NaN NaN NaN NaN   
3          NaN      NaN      NaN      NaN  35407.0 NaN NaN NaN NaN NaN NaN   

Month      12  
Color          
1      9300.0  
2         NaN  
3         NaN

Then use numpy.roll with DataFrame.join:

s = pd.DataFrame(np.roll(df1.to_numpy(), 1, axis=1), 
                 index=df1.index, 
                 columns=df1.columns).stack().rename('Previous Month')

df = df.join(s, on=['Color','Month']).fillna({'Previous Month':0})
print (df)
   Index  Color  Month    Oty  Previous Month
0      0      1      1  34047          9300.0
1      1      1      2  36654         34047.0
2      2      2      3  37291             0.0
3      3      2      4  35270         37291.0
4      4      3      5  35407             0.0
5      5      1     12   9300             0.0

Upvotes: 1

Chris Adams
Chris Adams

Reputation: 18647

Here is another approach using merge - we'll "merge" on a prv_month key which we'll assign inline:

df['PreviousQty'] = (df.assign(prv_month=df['Month'].sub(1).where(lambda x: x!=0, 12))
                     .merge(df,
                            how='left',
                            left_on=['Color', 'prv_month'],
                            right_on=['Color', 'Month'])['Qty_y'].fillna(0))

[out]

   Color  Month    Qty  PreviousQty
0      1      1  34047       9300.0
1      1      2  36654      34047.0
2      2      3  37291          0.0
3      2      4  35270      37291.0
4      3      5  35407          0.0
5      1     12   9300          0.0

Upvotes: 3

Related Questions