CODEWITHSUNDEEP
python-3.xpandasdataframe
Thanasis
Thanasis

Reputation: 725

How to select columns based on criteria?

I have the following dataframe:

d2 = {('CAR','ALPHA'): pd.Series(['A22', 'A23', 'A24', 'A25'],index=[2, 3, 4, 5]), 
  ('CAR','BETA'): pd.Series(['B22', 'B23', 'B24', 'B25'],index=[2, 3, 4, 5]),
  ('MOTOR','SOLO'): pd.Series(['S22', 'S23', 'S24', 'S25'], index=[2, 3, 4, 5])}
db= pd.DataFrame(data=d2)

enter image description here

I would like in the columns that have 'CAR' in the 0 level multiindex to delete all the values and set them to NA after a row index, ex. 4.

I am trying to use .loc but I would like the results to be saved in the same dataframe.

The second thing I would to do to set the values of columns that their 0 multiindex level is different from 'CAR' to NA after a row index, ex 3.

Upvotes: 1

Views: 44

Answers (1)

jezrael
jezrael

Reputation: 862431

Use slicers for first and for second MultiIndex.get_level_values compare by level value:

idx = pd.IndexSlice
db.loc[4:, idx['CAR', :]] = np.nan

db.loc[3:, db.columns.get_level_values(0) != 'CAR'] = 'AAA'

Or:

mask = db.columns.get_level_values(0) == 'CAR'

db.loc[4:, mask] = np.nan
db.loc[3:, ~mask] = 'AAA'

print(db)
    CAR      MOTOR
  ALPHA BETA  SOLO
2   A22  B22   S22
3   A23  B23   AAA
4   NaN  NaN   AAA
5   NaN  NaN   AAA

Upvotes: 1

Related Questions