CODEWITHSUNDEEP
pythonpandasindexingdataframegroup-by
Riley Hun
Riley Hun

Reputation: 2785

Python/Pandas: How to Get the Last 3 of Each Group and Put into a List of Lists

I would like to group this DataFrame by "AutoNumber" and get the last 2 Amounts of each, then put into a list of lists 

                        LoanAgreementID   Amount TransactionDate  \
0  252357C2-24C2-E611-8126-06CAB7997043  1667.35      2016-12-14   
1  252357C2-24C2-E611-8126-06CAB7997043  4181.28      2016-12-14   
2  4BF6F3D3-30C2-E611-8126-06CAB7997043  1667.35      2016-12-14   
3  4BF6F3D3-30C2-E611-8126-06CAB7997043  4181.28      2016-12-14   
4  4BF6F3D3-30C2-E611-8126-06CAB7997043   147.51      2017-01-18   
5  4BF6F3D3-30C2-E611-8126-06CAB7997043   147.51      2017-02-01   

                              ContactID  PaymentType  CashLedgerType  \
0  000FF848-42BE-E611-8126-06CAB7997043          NaN               5   
1  000FF848-42BE-E611-8126-06CAB7997043          NaN               5   
2  000FF848-42BE-E611-8126-06CAB7997043          NaN               5   
3  000FF848-42BE-E611-8126-06CAB7997043          NaN               5   
4  000FF848-42BE-E611-8126-06CAB7997043          0.0               3   
5  000FF848-42BE-E611-8126-06CAB7997043          0.0               3   

  KeyValue_String KeyValue_String.1  AutoNumber  IssueDate date_helper  
0          Cheque               NaN       54940 2016-12-14  2016-12-14  
1          Cheque               NaN       54940 2016-12-14  2016-12-14  
2          Cheque               NaN       54945 2016-12-14  2016-12-14  
3          Cheque               NaN       54945 2016-12-14  2016-12-14  
4         Payment               PAP       54945 2016-12-14  2017-01-18  
5         Payment               PAP       54945 2016-12-14  2017-02-01  
0    1667.35
1    4181.28
3    4181.28
4     147.51
5     147.51
Name: Amount, dtype: float64

Using the following code...

Amount_ref = group.groupby('AutoNumber')['Amount'].tail(2)

I got the output...

0    1667.35
1    4181.28
4     147.51
5     147.51
Name: Amount, dtype: float64

But my desired output is...

[[1667.35, 4181.28], [147.51, 147.51]]

Upvotes: 0

Views: 44

Answers (1)

jezrael
jezrael

Reputation: 862801

You can use apply and tolist:

Amount_ref = group.groupby('AutoNumber')['Amount']
                  .apply(lambda x: x.tail(2).tolist()).tolist()
print (Amount_ref)
[[1667.35, 4181.28], [147.51, 147.51]]

Or:

Amount_ref = group.groupby('AutoNumber')['Amount']
                  .apply(lambda x: x.iloc[-2:].tolist()).tolist()
print (Amount_ref)
[[1667.35, 4181.28], [147.51, 147.51]]

Upvotes: 1

Related Questions