How to modify the following dataframe using groupby/pivot?

Question

I have the following pandas dataframe:

I want to change this dataframe in the following way: I want to have the ID column as index and the month for each index in a column named month, and then all the values as follow:

ID    MONTH  VAL
1     Jan    nan
1     Feb    nan
1     Mar    nan
1     Apr    nan
1     May    nan
1     Jun    nan
1     Jul    nan
1     Aug    94.0000
1     Sep    113.0000
1     Oct    21.0000
1     Nov    nan
2     Jan    107.00000
.
.
.
7     Nov    nan

The order of the month is not important. Is there an easy way to do it?

Instead of posting a picture, can you post output from `df.head(10).to_dict()`. This will help us test solutions. — jpp, Feb 21 '18 at 22:08

Scott Boston · Accepted Answer · 2018-02-21T22:31:54.890

1

Try stack with dropnaparameter set to False:

df.set_index('ID').stack(dropna=False)

@PaulH is correct in his observations from the comments below.

To move that column index to row index converting from a wide dataframe to a long dataframe.

edited Feb 21 '18 at 22:31

answered Feb 21 '18 at 22:09

Scott Boston

147,308
15
139
187

1

I think you'll need to set the index on the `'ID'` column first – Paul H Feb 21 '18 at 22:31
you'll need to "categorize" the remaining column to them to sort properly – Paul H Feb 21 '18 at 22:39
OP states that order is not important. – Scott Boston Feb 21 '18 at 22:39
Ok i understood the first part, but didn't understand the part in which i have to convert from a wide dataframe to a long. Because after the stack i obtain a Series – Marco Feb 21 '18 at 22:40
@MarcoMiglionico that was just the way the dataframe looks. it was just a statement. Not import to the solution. – Scott Boston Feb 21 '18 at 22:41
If order of month is important this see this [SO post](https://stackoverflow.com/a/48916148/6361531) – Scott Boston Feb 21 '18 at 22:41
@ScottBoston oh yes i see that. oops. – Paul H Feb 21 '18 at 22:42
@ScottBoston I used df.melt() after the stack to have the dataset exactly as in the code i posted. Thank you! – Marco Feb 21 '18 at 22:58
Yes, melt is a great method also. Probably faster. You're welcome. Happy coding! – Scott Boston Feb 21 '18 at 22:59

How to modify the following dataframe using groupby/pivot?

1 Answers1