python中有没有一种方法可以删除csv文件中的几行

Question

我目前正在从sec.gov下载2016年第一季度的form.idx文件。由于我只对10 Ks感兴趣，所以我想将该文件下载为.csv文件，并删除无用的行。我试着按表单类型筛选，但没有成功。

到目前为止，我的代码如下：

import requests
import os
years = [2016]
quarters = ['QTR1']
base_path = '/Users/xyz/Desktop'
current_dirs = os.listdir(path=base_path)
for yr in years:
if str(yr) not in current_dirs:
os.mkdir('/'.join([base_path, str(yr)]))

current_files = os.listdir('/'.join([base_path, str(yr)]))

for qtr in quarters:
local_filename =  f'{yr}-{qtr}.csv'


local_file_path = '/'.join([base_path, str(yr), local_filename])

if local_filename in current_files:
print(f'Skipping file for {yr}, {qtr} because it is already saved.')
continue

url = f'https://www.sec.gov/Archives/edgar/full-index/{yr}/{qtr}/form.idx'

r = requests.get(url, stream=True)
with open(local_file_path, 'wb') as f:
for chunk in r.iter_content(chunk_size=128):
f.write(chunk)
r2 = pd.read_csv('/Users/xyz/Desktop/2016-QTR1.csv', sep=";", encoding="utf-8")
r2.head()
filt = (r2 ['Form Type'] == '10-K')
r2_10K = r2.loc[filt]
r2_10K.head()
r2_10K.to_csv('/Users/xyz/Desktop/modified.csv')
The Error message I get is:
Traceback (most recent call last):
File "<ipython-input-5-f84e3f81f3d1>", line 61, in <module>
filt = (r2 ['Form Type'] == '10-K')
File "/Users/xyz/opt/anaconda3/envs/spyder-4.1.5_1/lib/python3.8/site-packages/pandas/core/frame.py", line 2906, in __getitem__
indexer = self.columns.get_loc(key)
File "/Users/xyz/opt/anaconda3/envs/spyder-4.1.5_1/lib/python3.8/site-packages/pandas/core/indexes/base.py", line 2897, in get_loc
raise KeyError(key) from err
KeyError: 'Form Type'

也许有一种方法可以删除文件中不需要的行？除此之外，我也很感谢在这个问题上得到的任何帮助。

非常感谢。

谨致问候，Elena

Answer 1

这是您的完整工作代码，主要问题是您从网上获得的csv格式，完整代码：https://rextester.com/QUGF24653

我做了什么：

1. 我确实跳过了前10行
2. 使用3空格分隔符后设置列名
3. 将最后一列拆分为两列
4. 过滤器形式类型与"；10-K">

import requests
import os
import pandas as pd
years = [2016]
quarters = ['QTR1']
base_path = '/Users/xyz/Desktop'
current_dirs = os.listdir(path=base_path)
for yr in years:
if str(yr) not in current_dirs:
os.mkdir('/'.join([base_path, str(yr)]))
current_files = os.listdir('/'.join([base_path, str(yr)]))
for qtr in quarters:
local_filename = f'{yr}-{qtr}.csv'
local_file_path = '/'.join([base_path, str(yr), local_filename])
if local_filename in current_files:
print(f'Skipping file for {yr}, {qtr} because it is already saved.')
continue
url = f'https://www.sec.gov/Archives/edgar/full-index/{yr}/{qtr}/form.idx'
r = requests.get(url, stream=True)
with open(local_file_path, 'wb') as f:
for chunk in r.iter_content(chunk_size=128):
f.write(chunk)
colnames=['Form Type', 'Company Name', 'CIK', 'Date Filed','File Name']
r2 = pd.read_csv('/Users/xyz/Desktop/2016-QTR1.csv', sep=r's{3,}', skiprows=10, encoding="utf-8", names=colnames,header=None)
r2[['Date Filed','File Name']] = r2['Date Filed'].str.split(expand=True)
filtered = (r2['Form Type'] == '10-K')
r2_10K = r2.loc[filtered]
print(r2_10K.head())

输出：

Form Type                            Company Name      CIK  Date Filed                                    File Name
2181      10-K                       1347 Capital Corp  1606163  2016-03-21  edgar/data/1606163/0001144204-16-089184.txt
2182      10-K  1347 Property Insurance Holdings, Inc.  1591890  2016-03-17  edgar/data/1591890/0001387131-16-004603.txt
2183      10-K                1ST CONSTITUTION BANCORP  1141807  2016-03-22  edgar/data/1141807/0001141807-16-000010.txt
2184      10-K                         1ST SOURCE CORP    34782  2016-02-19    edgar/data/34782/0000034782-16-000102.txt
2185      10-K            1st Century Bancshares, Inc.  1420525  2016-03-04  edgar/data/1420525/0001437749-16-026765.txt

Answer 2

有多种方法可以从csv文件中删除行。Python中的Pandas库具有任意数量的函数，通过这些函数可以更改csv文件中的数据。首先通过以下代码导入Pandas库：

import pandas as pd

通过以下代码读取csv文件：

df = pd.read_csv("filename.csv")

例如，如果您有一个名为df的数据字段，其中包含您的csv文件。您可以通过以下代码按索引删除行：

df1 = df.drop([df.index[1], df.index[2]])

使用Pandas可以通过多种方式从csv中删除行。例如：按行值、按空值、按数据类型等等！