Pandas Sort File and group up values-CodePudding

I'm learning pandas,but having some trouble. I import data as DataFrame and want to bin the 2017 population values into four equal-size groups. And count the number of group4

However the system print out:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-52-05d9f2e7ffc8> in <module>
      2 
      3 df=pd.read_excel('C:/Users/Sam/Desktop/商業分析/Python_Jabbia1e/Chapter 2/jaggia_ba_1e_ch02_Data_Files.xlsx',sheet_name='Population')
----> 4 df=df.sort_values('2017',ascending=True)
      5 df['Group'] = pd.qcut(df['2017'], q = 4, labels = range(1, 5))
      6 splitData = [group for _, group in df.groupby('Group')]

C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\frame.py in sort_values(self, by, axis, ascending, inplace, kind, na_position, ignore_index, key)
   5453 
   5454             by = by[0]
-> 5455             k = self._get_label_or_level_values(by, axis=axis)
   5456 
   5457             # need to rewrap column in Series to apply key function

C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\generic.py in _get_label_or_level_values(self, key, axis)
   1682             values = self.axes[axis].get_level_values(key)._values
   1683         else:
-> 1684             raise KeyError(key)
   1685 
   1686         # Check for duplicates

KeyError: '2017'

What's wrong with it? Thanks~

Here's the dataframe:

And I tried:

df=pd.read_excel('C:/Users/Sam/Desktop/商業分析/Python_Jabbia1e/Chapter 2/jaggia_ba_1e_ch02_Data_Files.xlsx',sheet_name='Population')
df=df.sort_values('2017',ascending=True)
df['Group'] = pd.qcut(df['2017'], q = 4, labels = range(1, 5))
splitData = [group for _, group in df.groupby('Group')]
print('The number of group4 is :',splitData[3].shape[0])

CodePudding user response：

Firstly, you have problem in 4 line with the sort, you tell sort function to look for string 2017, but it's integer. Try this then move on on your code:

df=df.sort_values([2017],ascending=True)

CodePudding user response：

You are inserting the key for df.sort_values() as a str. You can either give it as an element in a list or not.

df = df.sort_values(by=['2017'], ascending=True)

df = df.sort_values(by='2017', ascending=True)

This only works if the column value is exactly matching the string you pass. If it is not a string or if that string contains some white spaces it won't work. You can remove any trailing white spaces before sorting by,

df.columns = df.columns.str.strip()

and if it is not a string you should use,

df = df.sort_values(by=[2017], ascending=True)