如何删除数据透视表数据框的VALUES列 [英] How to drop VALUES column of pivot table dataframe
问题描述
只需要动态地在特定列(月)上删除一个子列
Need to drop a sub-column only at specific columns(month) dynamically
我有一个从数据透视表创建的数据框,需要动态在特定列上放置一个子列...
如果今天的日期小于15 ,我需要在除 Sep-19 (<当前月份)
如果今天的日期大于15 ,则应该删除 Oct-19 (下个月)
I have a dataframe created from pivot table and need to drop a sub-column at specific columns dynamically...
if todays date is less than 15 i need to drop the sub-column Bill1 for all the months except Sep-19(current month)
if todays date is greater than 15, it should drop the sub-column Bill1 for all the months except Oct-19(next month)
data_frame1 = pd.pivot_table(data_frame, index=['PC', 'Geo', 'Comp'], values=['Bill1', 'Bill2'], columns=['Month'], fill_value=0)
data_frame1 = data_frame1.swaplevel(0,1, axis=1).sort_index(axis=1)
tuples = [(a.strftime('%b-%y'), b) if a!= 'All' else (a,b) for a,b in data_frame1.columns]
data_frame1.columns = pd.MultiIndex.from_tuples(tuples)
data_frame1 = pd.pivot_table(data_frame, index=['PC', 'Geo', 'Comp'], values=['Bill1', 'Bill2'], columns=['Month'], fill_value=0)
data_frame1 = data_frame1.swaplevel(0,1, axis=1).sort_index(axis=1)
tuples = [(a.strftime('%b-%y'), b) if a!= 'All' else (a,b) for a,b in data_frame1.columns]
data_frame1.columns = pd.MultiIndex.from_tuples(tuples)
输出:
Sep-19 OCT-19 Nov-19
Bill1 Bill2 Bill1 Bill2 Bill1 Bill2
PC Geo Comp
A Ind OS 1 1.28 1 1.28 1 1.28
所需的输出:
如果今天的日期小于15
Sep-19 OCT-19 Nov-19
Bill1 Bill2 Bill2 Bill2
PC Geo Comp
A Ind OS 1 1.28 1.28 1.28
如果今天的日期大于15
Sep-19 OCT-19 Nov-19
Bill2 Bill1 Bill2 Bill2
PC Geo Comp
A Ind OS 1.28 1 1.28 1.28
推荐答案
使用:
#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 filter
if dat.day < 15:
df = df.loc[:, (level0 == today_per) | (level1 != 'Bill1')]
else:
#test with add 1 month to today period
df = 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
下个月测试:
#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 filter
if dat.day < 15:
df = df.loc[:, (level0 == today_per) | (level1 != 'Bill1')]
else:
#test with add 1 month to today period
df = 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
这篇关于如何删除数据透视表数据框的VALUES列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!