Delete rows from dataframe with multi-level header

user_14564287 0
Viewed 117

I have an excel file with 2 levels of headers and read it into python like this:

data = pd.read_excel('file.xlsx', header = [0,1])

Next, I would like to select only the rows where the column 'type' is not 2. This should be straightforward, and I used

data2 = data[data['type'] != 2]

Yesterday, this worked. Today, I get a dataframe filled with NaN which still contains rows with type =2. It does not give me an error. Does this problem arise because it is a multi-level header? 'type' is on the top row, and it does not have a level at the second row. Example:

name  type   x
             a    b  c
A      1     4    3  7
B      2     2    6  1

Any suggestions?

print (data.columns)

gives me:

MultiIndex(levels=[[1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12, 'TBvol', 'breastvol', 'clipno', 'cliptype', 'interval', 'lat', 'markerno', 'nCT', 'type', 'xTB', 'yTB', 'zTB'], ['Unnamed: 0_level_1', 'Unnamed: 10_level_1', 'Unnamed: 11_level_1', 'Unnamed: 1_level_1', 'Unnamed: 2_level_1', 'Unnamed: 3_level_1', 'Unnamed: 4_level_1', 'Unnamed: 5_level_1', 'Unnamed: 6_level_1', 'Unnamed: 7_level_1', 'Unnamed: 8_level_1', 'Unnamed: 9_level_1', 'fx', 'fy', 'fz', 'px', 'py', 'pz']],
           labels=[[15, 14, 13, 17, 16, 12, 11, 20, 21, 22, 18, 19, 0, 0, 0, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 5, 5, 5, 6, 6, 6, 7, 7, 7, 8, 8, 8, 9, 9, 9, 10, 10, 10, 0, 0, 0, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 5, 5, 5, 6, 6, 6, 7, 7, 7, 8, 8, 8, 9, 9, 9, 10, 10, 10], [0, 3, 4, 5, 6, 7, 8, 9, 10, 11, 1, 2, 15, 16, 17, 15, 16, 17, 15, 16, 17, 15, 16, 17, 15, 16, 17, 15, 16, 17, 15, 16, 17, 15, 16, 17, 15, 16, 17, 15, 16, 17, 15, 16, 17, 12, 13, 14, 12, 13, 14, 12, 13, 14, 12, 13, 14, 12, 13, 14, 12, 13, 14, 12, 13, 14, 12, 13, 14, 12, 13, 14, 12, 13, 14, 12, 13, 14]],
           names=[None, 'PID'])
multi-index pandas python
1 Answers

Problem is if select type column, get not one column, but multiple - DataFrame.

You can see it e.g. if test with sample data if select x - it is value from first level and return all columns like:

print (data)
  name type  x      
             a  b  c
0    A    1  4  3  7
1    B    2  2  6  1


print (data['x'])
   a  b  c
0  4  3  7
1  2  6  1

So possible solution is select by tuple like:

print (data[('x', 'a')])
0    4
1    2
Name: (x, a), dtype: int64

So you need:

data2 = data[data[('type', 'second_level_value')] != 2]
user_29010020
Related