Python来遍历表和删除列

I need to read one excel file and perform some computations on each sheet. Basically, it needs to drop rows if the column date is not "today".

我需要阅读一个excel文件，并对每个表执行一些计算。基本上，如果列日期不是“today”，它需要删除行。

I got this code so far:

到目前为止，我得到了这个代码:

import datetime import pandas as pd

导入datetime作为pd导入熊猫

'''
Parsing main excel sheet to save transactions != today's date
'''

mainSource = pd.ExcelFile('path/to/file.xlsx')
dfs = {sheet_name: mainSource.parse(sheet_name)
        for sheet_name in mainSource.sheet_names }

for i in dfs:
    now = datetime.date.today();
    dfs = dfs.drop(dfs.columns[6].dt.year != now, axis = 1);    # It is the 6th column
    if datetime.time()<datetime.time(11,0,0,0):
        dfs.to_excel(r'path\to\outpt\test\'+str(i)+now+'H12.xlsx', index=False); #Save as sheetname+timestamp+textstring
    else:
        dfs.to_excel(r'path\to\output\'+str(i)+now+'H16.xlsx', index=False)

When running the script, I am getting the following error:

运行脚本时，我得到以下错误:

dfs = dfs.drop(...):
AttributeError: 'dict' object has no attribute 'drop'

Any suggestions?

有什么建议吗?

Thanks!

谢谢!

1 个解决方案

#1

I think you need replace i to dfs[i], because dfs is dict of DataFrames:

我认为需要将I替换为dfs[I]，因为dfs是DataFrames的dict类型:

df1 = pd.DataFrame({'A':[1,2,3],
                   'B':[4,5,6],
                   'C':['10-05-2011','10-05-2012','10-10-2016']})

df1.C = pd.to_datetime(df1.C)
print (df1)
   A  B          C
0  1  4 2011-10-05
1  2  5 2012-10-05
2  3  6 2016-10-10

df2 = pd.DataFrame({'A':[3,5,7],
                   'B':[9,3,4],
                   'C':['08-05-2013','08-05-2012','10-10-2016']})

df2.C = pd.to_datetime(df2.C)
print (df2)
   A  B          C
0  3  9 2013-08-05
1  5  3 2012-08-05
2  7  4 2016-10-10

names = ['a','b']

dfs = {names[i]:x for i, x in enumerate([df1,df2])}
print (dfs)
{'a':    A  B          C
0  1  4 2011-10-05
1  2  5 2012-10-05
2  3  6 2016-10-10, 'b':    A  B          C
0  3  9 2013-08-05
1  5  3 2012-08-05
2  7  4 2016-10-10}

Remove all rows by boolean indexing:

通过布尔索引删除所有行:

for i in dfs:
    now = pd.datetime.today().date();
    print (now)
    #select 3.column, in real data replace to 5
    mask = dfs[i].iloc[:,2].dt.date == now
    print (mask)
    df = dfs[i][mask]
    print (df)

2016-10-10
0    False
1    False
2     True
Name: C, dtype: bool
   A  B          C
2  3  6 2016-10-10
2016-10-10
0    False
1    False
2     True
Name: C, dtype: bool
   A  B          C
2  7  4 2016-10-10    

    if datetime.time()<datetime.time(11,0,0,0):
        df.to_excel(r'path\to\outpt\test\'+str(i)+now+'H12.xlsx', index=False); 
    else:
        df.to_excel(r'path\to\output\'+str(i)+now+'H16.xlsx', index=False)

#1