CODEWITHSUNDEEP
pandaspandas-groupby
Adil Blanco
Adil Blanco

Reputation: 666

How to group dataframe by id and put the values ​of the same id in a list and the last row as column

My goal is to group my dataframe by id and put the N first rows in a list and the last row of each group in another column.

My dataframe:

| id  | val1|
| 110 |  A  |
| 110 |  B  |
| 110 |  0  |
| 220 |  E  |
| 220 |  F  |
| 220 |  1  |
| 300 |  H  |
| 300 |  2  |

What I m looking for:

| id | val1 | val2|
|110 | [A,B]|  0  |
|220 | [E,F]|  1  |
|300 | [H]  |  2  |

Upvotes: 0

Views: 564

Answers (1)

BENY
BENY

Reputation: 323366

IIUC we need to_numeric for val1

s=pd.to_numeric(df.val1,errors='coerce')
df=df.assign(New=s.bfill())[s.isnull()].groupby('id').agg({'val1':list,'New':"first"})
df#df.reset_index(inplace=True)
       val1  New
id              
110  [A, B]  0.0
220  [E, F]  1.0
300     [H]  2.0

Upvotes: 1

Related Questions