如何在python中向现有csv文件添加日期列

8dtrkrch 于 2022-12-06 发布在 Python

关注(0)|答案(3)|浏览(263)

我有一个Json文件，我正在转换并附加到csv，我还试图添加一个月/日/年格式的日期列。
最近一次尝试：

import json
from csv import DictWriter
import pandas as pd
import datetime
with open('output.json', 'r') as inp, open('output.csv', 'a') as outp:
    writer = DictWriter(outp, fieldnames=[
        'name', 'price'])
    for line in inp:
        row = json.loads(line)
        writer.writerow(row)

a = pd.read_csv('output.csv')
a['Date'] = [datetime.date.today()]*len(a)
a.to_csv('adate.csv')

我接近了我正在尝试做的事情。输出：

Lattice Top 6'W x 6'H King Cedar Aluminum/Composite Horizontal Fence-Starter Section-SURFACE MOUNT,"4,300 .",Date
"FENCESMART4U XCEL - Black Steel Anti-Rust Fence Gate - Sharp End Pickets - 4ft W x 5ft H - Easy Installation Kit, for Residential, Outdoor, Yard, Patio, Entry Way, 3-Rail Metal Gate",199 .,2022-10-18
"XCEL - Black Steel Anti-Rust Fence Gate - 4ft W x 5ft H - Easy Installation Kit, for Residential, Outdoor, Yard, Patio, Entry Way, 3-Rail Metal Gate",206 .,2022-10-18
"LZMZMQ Courtyard Baluster Garden Fencing Villa Outdoor Guardrail, Wide 100 120 140 160 180 cm Aluminum Fence Panels Deckorators, Grey Panels Decor Free Standing for Weatherproof","1,771 .",2022-10-18

我试过添加列标题，如headerList = ['name', 'price', 'date']，但在第一列下效果不佳，因为它删除了产品名称。
这是json文件阅读的方式：

{"name": "Lattice Top 6'W x 6'H King Cedar Aluminum/Composite Horizontal Fence-Starter Section-SURFACE MOUNT", "price": "4,300 ."}
{"name": "FENCESMART4U XCEL - Black Steel Anti-Rust Fence Gate - Sharp End Pickets - 4ft W x 5ft H - Easy Installation Kit, for Residential, Outdoor, Yard, Patio, Entry Way, 3-Rail Metal Gate", "price": "199 ."}
{"name": "XCEL - Black Steel Anti-Rust Fence Gate - 4ft W x 5ft H - Easy Installation Kit, for Residential, Outdoor, Yard, Patio, Entry Way, 3-Rail Metal Gate", "price": "206 ."}
{"name": "LZMZMQ Courtyard Baluster Garden Fencing Villa Outdoor Guardrail, Wide 100 120 140 160 180 cm Aluminum Fence Panels Deckorators, Grey Panels Decor Free Standing for Weatherproof", "price": "1,771 ."}

csv

来源：https://stackoverflow.com/questions/74117295/how-to-add-a-date-column-to-existing-csv-file-in-python

3条答案

按热度按时间

djp7away1#

您可以直接在pandas中完成这些操作：

import pandas as pd

df = pd.read_json('output.json', orient='records', lines=True)
df['Date'] = pd.to_datetime('today').strftime('%m/%d/%Y')
df.to_csv('adate.csv', index=False)

输出（针对示例数据）：

name,price,Date
Lattice Top 6'W x 6'H King Cedar Aluminum/Composite Horizontal Fence-Starter Section-SURFACE MOUNT,"4,300 .",10/22/2022
"FENCESMART4U XCEL - Black Steel Anti-Rust Fence Gate - Sharp End Pickets - 4ft W x 5ft H - Easy Installation Kit, for Residential, Outdoor, Yard, Patio, Entry Way, 3-Rail Metal Gate",199 .,10/22/2022
"XCEL - Black Steel Anti-Rust Fence Gate - 4ft W x 5ft H - Easy Installation Kit, for Residential, Outdoor, Yard, Patio, Entry Way, 3-Rail Metal Gate",206 .,10/22/2022
"LZMZMQ Courtyard Baluster Garden Fencing Villa Outdoor Guardrail, Wide 100 120 140 160 180 cm Aluminum Fence Panels Deckorators, Grey Panels Decor Free Standing for Weatherproof","1,771 .",10/22/2022

赞(0）回复(0）举报 2022-12-06

ryevplcw2#

如果需要的话，你可以不使用pandas直接执行此操作。确保在使用csv模块的文件上使用newline=''。（为什么）

import json
from csv import DictWriter
from datetime import datetime

with open('output.json', 'r', newline='') as inp, open('output.csv', 'w', newline='') as outp:
    writer = DictWriter(outp, fieldnames=['name', 'price', 'date'])
    writer.writeheader()
    for line in inp:
        row = json.loads(line)
        row['date'] = datetime.now().strftime('%m/%d/%Y')
        writer.writerow(row)

输出量：

name,price,date
Lattice Top 6'W x 6'H King Cedar Aluminum/Composite Horizontal Fence-Starter Section-SURFACE MOUNT,4300.00,10/21/2022
"FENCESMART4U XCEL - Black Steel Anti-Rust Fence Gate - Sharp End Pickets - 4ft W x 5ft H - Easy Installation Kit, for Residential, Outdoor, Yard, Patio, Entry Way, 3-Rail Metal Gate",199.00,10/21/2022
"XCEL - Black Steel Anti-Rust Fence Gate - 4ft W x 5ft H - Easy Installation Kit, for Residential, Outdoor, Yard, Patio, Entry Way, 3-Rail Metal Gate",206.00,10/21/2022
"LZMZMQ Courtyard Baluster Garden Fencing Villa Outdoor Guardrail, Wide 100 120 140 160 180 cm Aluminum Fence Panels Deckorators, Grey Panels Decor Free Standing for Weatherproof",1771.00,10/21/2022

赞(0）回复(0）举报 2022-12-06

cbeh67ev3#

这个解决方案将利用参数restval，这意味着如果一个值丢失，DictWriter可以用这个值来填充。

import csv
import json
import datetime

today = datetime.date.today()
formatted_date = today.strftime("%Y/%m/%d")

with open("output.json") as instream, open("output.csv", "w") as outstream:
    writer = csv.DictWriter(
        outstream,
        fieldnames=["name", "price", "date"],
        restval=formatted_date
    )

    writer.writeheader()
    for line in instream:
        row = json.loads(line)
        writer.writerow(row)

所以我这里的代码和原始的海报代码几乎一样，但是使用了restval。

赞(0）回复(0）举报 2022-12-06

我来回答

如何在python中向现有csv文件添加日期列

3条答案

相关问题

热门标签

最新问答