Sort Index by list - Python Pandas

Question

I have a dataframe that I have pivoted:

FinancialYear   2014/2015   2015/2016   2016/2017   2017/2018
Month               
April             42           32          29          27
August            34           28          32           0
December          45           51          28           0
February          28           20          28           0
January           32           28          33           0
July              40           66          31          30
June              32           67          37          35
March             43           36          39           0
May               34           30          24          29
November          39           32          31           0
October           38           39          28           0
September         29           19          34           0

This is the code that I used:

new_hm01 = hmdf[['FinancialYear','Month','FirstReceivedDate']]

hm05 = new_hm01.pivot_table(index=['FinancialYear','Month'], aggfunc='count')

df_hm = new_hm01.groupby(['Month', 'FinancialYear']).size().unstack(fill_value=0).rename(columns=lambda x: '{}'.format(x))

The Months are not in the order I want, so I used the following code to reindex it according to a list:

vals = ['April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December', 'January', 'February', 'March']

df_hm = df_hm.reindex(vals)

This worked, but the values in my table are now mostly showing NaN values.

FinancialYear   2014/2015   2015/2016   2016/2017   2017/2018
Month               
April              nan          nan         nan         nan
May                nan          nan         nan         nan
June               nan          nan         nan         nan
July               nan          nan         nan         nan
August             nan          nan         nan         nan
September           29           19          34           0
October            nan          nan         nan         nan
November           nan          nan         nan         nan
December           nan          nan         nan         nan
January            nan          nan         nan         nan
February           nan          nan         nan         nan
March              nan          nan         nan         nan

Any idea on what is happening? How to fix it? and if there is a better alternative method?

Please post df_hm.index.tolist() before calling reindex. (Most likely, the labels in the original index are not exactly the same as the labels used in the reindexing. Maybe there are whitespace differences...). — unutbu
– unutbu, Commented Jul 29, 2017 at 12:15
I did what you said, and the index is showing: [u'April ', u'August ', u'December ', u'February ', u'January ', u'July ', u'June ', u'March ', u'May ', u'November ', u'October ', u'September'] — Nicholas
– Nicholas, Commented Jul 29, 2017 at 12:21
Damn, changed it to match the output of 'df_hm.index.tolist()' and still showing all the 'NaNs' :/ — Nicholas
– Nicholas, Commented Jul 29, 2017 at 12:28
You could use hmdf['Month'] = hmdf['Month].str.strip() to remove the whitespaces from the Month column. — unutbu
– unutbu, Commented Jul 29, 2017 at 12:36

unutbu · Accepted Answer · 2017-07-29 12:44:11Z

32

Unexpected NaNs after reindexing are often due to the new index labels not exactly matching the old index labels. For example, if the original index labels contains whitespaces, but the new labels don't, then you'll get NaNs:

import numpy as np
import pandas as pd

df = pd.DataFrame({'col':[1,2,3]}, index=['April ', 'June ', 'May ', ])
print(df)
#         col
# April     1
# June      2
# May       3

df2 = df.reindex(['April', 'May', 'June'])
print(df2)
#        col
# April  NaN
# May    NaN
# June   NaN

This can be fixed by removing the whitespace to make the labels match:

df.index = df.index.str.strip()
df3 = df.reindex(['April', 'May', 'June'])
print(df3)
#        col
# April    1
# May      3
# June     2

answered Jul 29, 2017 at 12:44

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

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

Collectives™ on Stack Overflow

Sort Index by list - Python Pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related