如何在没有panda的情况下读取、格式化、排序和保存csv文件

Question

• 给定文件test.csv中的以下示例数据

27-Mar-12,8.25,8.35,8.17,8.19,9801989
26-Mar-12,8.16,8.25,8.12,8.24,8694416
23-Mar-12,8.05,8.12,7.95,8.09,8149170

• 如何在不使用pandas的情况下解析此文件？
  1. 打开文件
  2. 将日期列格式化为datetime日期格式的字符串
  3. 按日期列0对所有行进行排序
  4. 保存回同一文件，并带有日期列的标题
• 使用pandas，这可以通过一行(长(代码来实现，不包括导入。
  • 需要注意的是，如果不使用date_parser，则使用parse_date可能非常缓慢

import pandas as pd
(pd.read_csv('test.csv', header=None, parse_dates=[0], date_parser=lambda t: pd.to_datetime(t, format='%d-%b-%y'))
.rename(columns={0: 'date'})
.sort_values('date')
.to_csv('test.csv', index=False))

预期形式

date,1,2,3,4,5
2012-03-23,8.05,8.12,7.95,8.09,8149170
2012-03-26,8.16,8.25,8.12,8.24,8694416
2012-03-27,8.25,8.35,8.17,8.19,9801989

• 编写此问答是为了填补Stack Overflow上的知识内容空白
• 使用pandas执行此任务非常容易
• 令人惊讶的是，在没有pandas的情况下，很难想出所有必要的部分来创建一个完整的解决方案
• 这对任何好奇这项任务的人，以及被禁止使用pandas的学生来说都是有益的
• 我不介意看到numpy的解决方案，但问题的主要点是，只使用标准库中的包来完成这项任务

这三个答案都是这个问题可以接受的答案

Answer 1

尽可能少地使用导入：

from datetime import datetime
def format_date(date: str) -> str:
formatted_date = datetime.strptime(date, "%d-%b-%y").date().isoformat()
return formatted_date

# read in the CSV
with open("test.csv", "r") as file:
lines = file.readlines()
records = [[value for value in line.split(",")] for line in lines]
# reformat the first field in each record
for record in records:
record[0] = format_date(record[0])
# having formatted the dates, sort records by first (date) field:
sorted_records = sorted(records, key = lambda r: r[0])
# join values with commas once more, removing newline characters
prepared_lines = [",".join(record).strip("n") for record in sorted_records]
# create a header row
field_names = "date,1,2,3,4,5"
# prepend the header row
prepared_lines.insert(0, field_names)
prepared_data = "n".join(prepared_lines)
# write out the CSV
with open("test.csv", "w") as file:
file.write(prepared_data)

Answer 2

• pandas是一个更容易解析和清理文件的工具
• 什么需要1行pandas，需要11行代码，需要一个for-loop
• 这需要以下软件包和功能
  • csv&datetime
  • 文件对象的方法：.seek&.truncate
  • 排序：如何
• 最初，list()用于解包csv.reader对象，但在迭代reader时，它被删除了，用于更新日期值
• 可以向sorted提供自定义键函数来自定义排序顺序，但我看不到从lambda表达式返回值的方法。
  • 最初使用key=lambda row: datetime.strptime(row[0], '%Y-%m-%d')，但已删除，因为更新的日期列不包含月份名称
  • 如果日期列包含月份名称，那么如果没有自定义排序键，它将无法正确排序

import csv
from datetime import datetime
# open the file for reading and writing
with open('test1.csv', mode='r+', newline='') as f:
# create a reader and writer opbject
reader, writer = csv.reader(f), csv.writer(f)

data = list()
# iterate through the reader and update column 0 to a datetime date string
for row in reader:
# update column 0 to a datetime date string
row[0] = datetime.strptime(row[0], "%d-%b-%y").date().isoformat()

# append the row to data
data.append(row)
# sort all of the rows, based on date, with a lambda expression
data = sorted(data, key=lambda row: row[0])
# change the stream position to the given byte offset
f.seek(0)
# truncate the file size
f.truncate()
# add a header to data
data.insert(0, ['date', 1, 2, 3, 4, 5])
# write data to the file
writer.writerows(data)

更新test.csv

date,1,2,3,4,5
2012-03-23,8.05,8.12,7.95,8.09,8149170
2012-03-26,8.16,8.25,8.12,8.24,8694416
2012-03-27,8.25,8.35,8.17,8.19,9801989

%time试验

import pandas
import pandas_datareader as web
# test data with 1M rows
df = web.DataReader(ticker, data_source='yahoo', start='1980-01-01', end='2020-09-27').drop(columns=['Adj Close']).reset_index().sort_values('High', ascending=False)
df.Date = df.Date.dt.strftime('%d-%b-%y')
df = pd.concat([df]*100)
df.to_csv('test.csv', index=False, header=False)

测试

# pandas test with date_parser
%time pandas_test('test.csv')
[out]:
Wall time: 17.9 s
# pandas test without the date_parser parameter
%time pandas_test('test.csv')
[out]:
Wall time: 1min 17s
# from Paddy Alton
%time paddy('test.csv')
[out]:
Wall time: 15.9 s
# from Trenton
%time trenton('test.csv')
[out]:
Wall time: 17.7 s
# from sammywemmy with functions updated to return the correct date format
%time sammy('test.csv')
[out]:
Wall time: 22.2 s
%time sammy2('test.csv')
[out]:
Wall time: 22.2 s

测试功能

from operator import itemgetter
import csv
import pandas as pd
from datetime import datetime
def pandas_test(file):
(pd.read_csv(file, header=None, parse_dates=[0], date_parser=lambda t: pd.to_datetime(t, format='%d-%b-%y'))
.rename(columns={0: 'date'})
.sort_values('date')
.to_csv(file, index=False))

def trenton(file):
with open(file, mode='r+', newline='') as f:
reader, writer = csv.reader(f), csv.writer(f)
data = list()
for row in reader:
row[0] = datetime.strptime(row[0], "%d-%b-%y").date().isoformat()
data.append(row)
data = sorted(data, key=lambda row: row[0])
f.seek(0)
f.truncate()
data.insert(0, ['date', 1, 2, 3, 4, 5])
writer.writerows(data)

def paddy(file):
def format_date(date: str) -> str:
formatted_date = datetime.strptime(date, "%d-%b-%y").date().isoformat()
return formatted_date

with open(file, "r") as f:
lines = f.readlines()
records = [[value for value in line.split(",")] for line in lines]
for record in records:
record[0] = format_date(record[0])
sorted_records = sorted(records, key = lambda r: r[0])
prepared_lines = [",".join(record).strip("n") for record in sorted_records]
field_names = "date,1,2,3,4,5"
prepared_lines.insert(0, field_names)
prepared_data = "n".join(prepared_lines)
with open(file, "w") as f:
f.write(prepared_data)

def sammy(file):
# updated with .date().isoformat() to return the correct format
with open(file) as csvfile:
fieldnames = ["date", 1, 2, 3, 4, 5]
reader = csv.DictReader(csvfile, fieldnames=fieldnames)
mapping = list(reader)
mapping = [
{
key: datetime.strptime(value, ("%d-%b-%y")).date().isoformat()
if key == "date" else value
for key, value in entry.items()
}
for entry in mapping
]
mapping = sorted(mapping, key=itemgetter("date"))
with open(file, mode="w", newline="") as csvfile:
fieldnames = mapping[0].keys()
writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
writer.writeheader()
for row in mapping:
writer.writerow(row)

def sammy2(file):
# updated with .date().isoformat() to return the correct format
with open(file) as csvfile:
reader = csv.reader(csvfile, delimiter=",")
mapping = dict(enumerate(reader))
num_of_cols = len(mapping[0])
fieldnames = ["date" if n == 0 else n
for n in range(num_of_cols)]
mapping = [
[ datetime.strptime(val, "%d-%b-%y").date().isoformat()
if ind == 0 else val
for ind, val in enumerate(value)
]
for key, value in mapping.items()
]
mapping = sorted(mapping, key=itemgetter(0))
with open(file, mode="w", newline="") as csvfile:
csvwriter = csv.writer(csvfile, delimiter=",")
csvwriter.writerow(fieldnames)
for row in mapping:
csvwriter.writerow(row)

Answer 3

正如OP所说，Pandas让这一切变得容易；另一种选择是使用DictReader和DictWriter选项；它仍然比使用Pandas更冗长(这里的抽象之美，Pandas为我们做了繁重的工作(。

import csv
from datetime import datetime
from operator import itemgetter
with open("test.csv") as csvfile:
fieldnames = ["date", 1, 2, 3, 4, 5]
reader = csv.DictReader(csvfile, fieldnames=fieldnames)
mapping = list(reader)
mapping = [
{
key: datetime.strptime(value, ("%d-%b-%y"))
if key == "date" else value
for key, value in entry.items()
}
for entry in mapping
]
mapping = sorted(mapping, key=itemgetter("date"))
with open("test.csv", mode="w", newline="") as csvfile:
fieldnames = mapping[0].keys()
writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
writer.writeheader()
for row in mapping:
writer.writerow(row)

由于字段名称事先未知，我们可以使用csvreader和csvwriter选项：

with open("test.csv") as csvfile:
reader = csv.reader(csvfile, delimiter=",")
mapping = dict(enumerate(reader))
num_of_cols = len(mapping[0])
fieldnames = ["date" if n == 0 else n
for n in range(num_of_cols)]
mapping = [
[ datetime.strptime(val, "%d-%b-%y")
if ind == 0 else val
for ind, val in enumerate(value)
]
for key, value in mapping.items()
]
mapping = sorted(mapping, key=itemgetter(0))
with open("test.csv", mode="w", newline="") as csvfile:
csvwriter = csv.writer(csvfile, delimiter=",")
csvwriter.writerow(fieldnames)
for row in mapping:
csvwriter.writerow(row)

Answer 4

这个排序/格式化任务可以在mlr中完成，无需任何编码，有点；(

cat nopanda.csv
27-Mar-12,8.25,8.35,8.17,8.19,9801989
26-Mar-12,8.16,8.25,8.12,8.24,8694416
23-Mar-12,8.05,8.12,7.95,8.09,8149170

brew install miller
mlr --csv --ofs ',' --implicit-csv-header label date,1,2,3,4,5 then put '$date=strftime(strptime($date, "%d-%b-%y"), "%Y-%m-%d");' then sort -f date nopanda.csv
date,1,2,3,4,5
2012-03-23,8.05,8.12,7.95,8.09,8149170
2012-03-26,8.16,8.25,8.12,8.24,8694416
2012-03-27,8.25,8.35,8.17,8.19,9801989

参见文档：

• https://miller.readthedocs.io/en/latest/csv-with-and-without-headers/