我有一个.csv文件，有四个表格，每个表格都是2001-1986年西南航空公司的不同财务报表。我知道我可以将每个表分成单独的文件，但它们最初是作为一个下载的。
我想将每个表读取到其自己的熊猫数据帧进行分析。以下是数据的子集：
Balance Sheet
Report Date 12/31/2001 12/31/2000 12/31/1999 12/31/1998
Cash & cash equivalents 2279861 522995 418819 378511
Short-term investments - - - -
Accounts & other receivables 71283 138070 73448 88799
Inventories of parts... 70561 80564 65152 50035
Income Statement
Report Date 12/31/2001 12/31/2000 12/31/1999 12/31/1998
Passenger revenues 5378702 5467965 4499360 3963781
Freight revenues 91270 110742 102990 98500
Charter & other - - - -
Special revenue adjustment - - - -
Statement of Retained Earnings
Report Date 12/31/2001 12/31/2000 12/31/1999 12/31/1998
Previous ret earn... 2902007 2385854 2044975 1632115
Cumulative effect of.. - - - -
Three-for-two stock split 117885 - 78076 -
Issuance of common.. 52753 75952 45134 10184
每个表格有 17 列，第一列是行项目描述，但行数不同，即资产负债表为 100 行，而现金流量表为 65 行
我做了什么
import pandas as pd
import numpy as np
# Lines that separate the various financial statements
lines_to_skip = [0, 102, 103, 158, 159, 169, 170]
with open('LUV.csv', 'r') as file:
fin_statements = pd.read_csv(file, skiprows=lines_to_skip)
balance_sheet = fin_statements[0:100]
我见过具有类似目标的帖子，指出要利用 nrows 和 skiprows。我使用跳绳来读取整个文件，然后通过索引创建单个财务报表。
我正在寻找评论和建设性的批评，以更好的 Pythonic 风格和最佳实践为每个相应的表创建数据帧。
如果远远超出read_csv所能做的事情，你想做什么。如果您输入文件结构可以建模为：
REPEAT:
Dataframe name
Header line
REPEAT:
Data line
BLANK LINE OR END OF FILE
恕我直言，最简单的方法是逐行手动解析，为每个数据帧提供一个临时 csv 文件，然后加载数据帧。代码可以是：
df = {} # dictionary of dataframes
def process(tmp, df_name):
'''Process the temporary file corresponding to one dataframe'''
# print("Process", df_name, tmp.name) # uncomment for debugging
if tmp is not None:
tmp.close()
df[df_name] = pd.read_csv(tmp.name)
os.remove(tmp.name) # do not forget to remove the temp file
with open('LUV.csv') as file:
df_name = "NONAME" # should never be in resulting dict...
tmp = None
for line in file:
# print(line) # uncomment for debugging
if len(line.strip()) == 0: # close temp file on empty line
process(tmp, df_name) # and process it
tmp = None
elif tmp is None: # a new part: store the name
df_name = line.strip()
state = 1
tmp = tempfile.NamedTemporaryFile("w", delete=False)
else:
tmp.write(line) # just feed the temp file
# process the last part if no empty line was present...
process(tmp, df_name)
这并不真正有效，因为每一行都写入一个临时文件，然后再次读取，但它简单而健壮。
一个可能的改进是最初使用 csv 模块解析部分(可以在 pandas 需要文件时解析流(。缺点是csv模块只能解析为字符串，并且您将失去对熊猫数量的自动转换。我的观点是，只有当文件很大并且必须重复完整操作时，才值得。
这是我的解决方案： 我的假设是，每个报表都以一个指标("资产负债表"、"损益表"、"留存收益表"(开头，我们可以根据该指标拆分表格以获得单个数据帧。这是以下代码所基于的前提。让我知道这是否是一个有缺陷的假设。
import pandas as pd
import numpy as np
#i copied your data above and created a csv with it
df = pd.read_csv('csvtable_stackoverflow',header=None)
0
0 Balance Sheet
1 Report Date 12/31/2001 12/31/...
2 Cash & cash equivalents 2279861 522995...
3 Short-term investments - - ...
4 Accounts & other receivables 71283 138070...
5 Inventories of parts... 70561 80564...
6 Income Statement
7 Report Date 12/31/2001 12/31/...
8 Passenger revenues 5378702 546796...
9 Freight revenues 91270 110742...
10 Charter & other - - ...
11 Special revenue adjustment - - ...
12 Statement of Retained Earnings
13 Report Date 12/31/2001 12/31/2...
14 Previous ret earn... 2902007 2385854...
15 Cumulative effect of.. - - ...
16 Three-for-two stock split 117885 - 78076 -
17 Issuance of common.. 52753 75952...
下面的代码只是使用 numpy select 来过滤掉哪些行包含 资产负债表或损益表或现金流
https://docs.scipy.org/doc/numpy/reference/generated/numpy.select.html
bal_sheet = df[0].str.strip()=='Balance Sheet'
income_stmt = df[0].str.strip()=='Income Statement'
cash_flow_sheet = df[0].str.strip()=='Statement of Retained Earnings'
condlist = [bal_sheet, income_stmt, cash_flow_sheet]
choicelist = ['Balance Sheet', 'Income Statement', 'Statement of
Retained Earnings']
下面的下一个代码创建一个指示工作表类型的列，将"0"转换为 null，然后向下填充
df = (df.assign(sheet_type = np.select(condlist,choicelist))
.assign(sheet_type = lambda x: x.sheet_type.replace('0',np.nan))
.fillna(method='ffill')
)
最后一步是拉出单个数据帧
df_bal_sheet = df.copy().query('sheet_type=="Balance Sheet"')
df_income_sheet = df.copy().query('sheet_type=="Income Statement"')
df_cash_flow = df.copy().query('sheet_type=="Statement of Retained Earnings"')
df_bal_sheet :
0 sheet_type
0 Balance Sheet Balance Sheet
1 Report Date 12/31/2001 12/31/... Balance Sheet
2 Cash & cash equivalents 2279861 522995... Balance Sheet
3 Short-term investments - - ... Balance Sheet
4 Accounts & other receivables 71283 138070... Balance Sheet
5 Inventories of parts... 70561 80564... Balance Sheet
df_income_sheet :
0 sheet_type
6 Income Statement Income Statement
7 Report Date 12/31/2001 12/31/... Income Statement
8 Passenger revenues 5378702 546796... Income Statement
9 Freight revenues 91270 110742... Income Statement
10 Charter & other - - ... Income Statement
11 Special revenue adjustment - - ... Income Statement
df_cash_flow:
0 sheet_type
12 Statement of Retained Earnings Statement of Retained Earnings
13 Report Date 12/31/2001 12/31/2... Statement of Retained Earnings
14 Previous ret earn... 2902007 2385854... Statement of Retained Earnings
15 Cumulative effect of.. - - ... Statement of Retained Earnings
16 Three-for-two stock split 117885 - 78076 - Statement of Retained Earnings
17 Issuance of common.. 52753 75952... Statement of Retained Earnings
您可以通过修复列名并删除不需要的行来执行进一步的操作。