[Python] merge rows together into one cell (into a tuple) for specifc columns

say i have a table like below: where under id 2 for exampple this row just shows the history of status and date for that id.

*id*, *status*, *date*, *Type*
2 dissolved 2016/03/19 T1
nan active NaT nan
3 dissolved 2016/03/19, T3
nan active 2012/03/16 nan
4 in liquidation, 2017/03/19 T2
nan dissolved, NaT nan


what i want to do is combine the rows by id so that for instance for first id = 2 i get:

*id*, *status*, *date*, *Type*
2 [dissolved,active] [2016/03/19,None] T1



i have tried:

data.groupby(['id']).agg(lambda x: tuple(x)).applymap(list).reset_index()


but this puts id column as [2,nan] when all i want is 2. how do i go about this? i only want list format for my status and date values not to tuple all of them up!

Input df:

df = pd.DataFrame([[2,"dissolved","2016/03/19","T1" ],
[float("nan"),"active","NaT",float("nan")],
[3,"dissolved","2016/03/19","T3" ],
[float("nan"),"active","2012/03/16",float("nan")],
[4,"in liquidation","2017/03/19","T2" ],
[float("nan"),"dissolved","NaT",float("nan")]],columns = ["id","status","date","Type"])

Continue reading...