How To Drop Values Column Of Pivot Table Dataframe

Need to drop a sub-column of multi-index data frame created from pivot table Need to drop a sub-column only at specific columns(month) dynamically I have a dataframe created from p

Solution 1:

Use:

#convert first level for datetimes and to month periods
level0 = pd.to_datetime(df.columns.get_level_values(0), format='%b-%y').to_period('m')
#get second level
level1 = df.columns.get_level_values(1)
print (level0)
PeriodIndex(['2019-09', '2019-09', '2019-10', '2019-10', '2019-11', '2019-11'],
             dtype='period[M]', freq='M')

print (level1)
Index(['Bill1', 'Bill2', 'Bill1', 'Bill2', 'Bill1', 'Bill2'], dtype='object')

#test for next 15 days#dat = pd.to_datetime('2019-09-20')#get today timestamp
dat = pd.to_datetime('now')
print (dat)

#convert timestamp to period
today_per = dat.to_period('m')

#compare day and filterif dat.day < 15:
    df = df.loc[:, (level0 == today_per) | (level1 != 'Bill1')]
else:
    #test with add 1 month to today perioddf = df.loc[:, (level0 == today_per + 1) | (level1 != 'Bill1')]
print (df)
         Sep-19       Oct-19 Nov-19
          Bill1 Bill2  Bill2  Bill2
A Ind OS      1  1.28   1.28   1.28

Test next month:

#convert first level for datetimes and to month periods
level0 = pd.to_datetime(df.columns.get_level_values(0), format='%b-%y').to_period('m')
#get second level
level1 = df.columns.get_level_values(1)
print (level0)
PeriodIndex(['2019-09', '2019-09', '2019-10', '2019-10', '2019-11', '2019-11'],
             dtype='period[M]', freq='M')

print (level1)
Index(['Bill1', 'Bill2', 'Bill1', 'Bill2', 'Bill1', 'Bill2'], dtype='object')

#test for next 15 days
dat = pd.to_datetime('2019-09-20')
#get today timestamp#dat = pd.to_datetime('now')print (dat)

#convert timestamp to period
today_per = dat.to_period('m')

#compare day and filterif dat.day < 15:
    df = df.loc[:, (level0 == today_per) | (level1 != 'Bill1')]
else:
    #test with add 1 month to today perioddf = df.loc[:, (level0 == today_per + 1) | (level1 != 'Bill1')]
print (df)
         Sep-19 Oct-19       Nov-19
          Bill2  Bill1 Bill2  Bill2
A Ind OS   1.28      1  1.28   1.28