pandas dataframe reshape / pivot

Question

I'd like to convert the following pandas dataframe

to

    0   1   2
a           
1   2   5   3  
2   4   1   NaN
3   7   NaN NaN

Do you know an easy way?

I'm sorry but I can't see the pattern here. How exactly are the elements of the resulting matrix related to the original? — John Haberstroh
– John Haberstroh, Commented Nov 23, 2016 at 23:21
Suppose 'b' column shows blood pressure readings and 'a' column shows the patient id. I'd like to have all the readings from each patient in one line. Each patient may have from 1 to a maximum number of readings, say 10. So the final table will be of shape number_of_patients x 10. — user2725109
– user2725109, Commented Nov 23, 2016 at 23:26

Andy Hayden · Accepted Answer · 2016-11-23 23:28:57Z

2

I would do this as follows:

In [11]: df.groupby("a")["b"].apply(lambda x: pd.Series(x.values))
Out[11]:
a
1  0    2
   1    5
   2    3
2  0    4
   1    1
3  0    7
Name: b, dtype: int64

to get the form you wanted you then unstack (though probably above better):

In [22]: df.groupby('a')["b"].apply(lambda x: pd.Series(x.values)).unstack(1)
Out[22]:
     0    1    2
a
1  2.0  5.0  3.0
2  4.0  1.0  NaN
3  7.0  NaN  NaN

answered Nov 23, 2016 at 23:28

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2725109 Over a year ago

Great solution. Thanks.

Collectives™ on Stack Overflow

pandas dataframe reshape / pivot

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related