I was doing something very basic like this -
data = np.arange(1,13).reshape(4,3)
table = pd.DataFrame(data, index = list('abcd'), columns =['foo','bar','baz'])
table
foo bar baz
a 1 2 3
b 4 5 6
c 7 8 9
d 10 11 12
And then I ran this -
table['bar':'foo']
#output
foo bar baz
c 7 8 9
d 10 11 12
I don't get why I am getting this result. Note that I am not asking for any other solution or workaround. I am just looking for explanation/rules behind this behavior.
I'm not entirly sure, but it looks like you can't use slicing for column names, the slicing only works on the rows, so only c and d are (lexicography) between bar and foo
You can instead use loc:
table.loc[:, 'foo':'bar']
Note that I changed the order of foo and bar, this is because they are ordered as you defined them, foo -> baz -> bar and not lexicographically. 'bar':'foo' will return an empty dataframe.
It's basically outputting row slices by comparing bar and foo lexicographically with the existing column names. The output includes column c and d as they're only two columns that fall between bar and foo: a < b < bar < c < d < ... < foo
First, you have to know that the notation df[x:y] try to slice your dataframe by index labels and not columns. This is different than the notation df[x] which try to select a column. A generic way to filter your dataframe is to use .loc (or .iloc). You should the documentation about Indexing and selecting data
>>> table['bar':'foo']
foo bar baz
c 7 8 9 # 'bar' >= 'c' (a b bar c d )
d 10 11 12 # 'd' <= 'foo' (a b c d foo)
When you use your code you raise an exception because we often use a RangeIndex or IntIndex index label:
>>> table.reset_index(drop=True)['bar':'foo']
...
TypeError: cannot do slice indexing on RangeIndex with these indexers [bar] of type str
Immediately, we understand the problem and use .loc:
>>> table.loc[:, 'foo':'bar']
foo bar
a 1 2
b 4 5
c 7 8
d 10 11