python - 将新数据列添加到 python 中的 csv 文件

Question

我正在尝试做两件事...

1. 向文件添加一些“元数据”列
2. 创建一个字典，其中文件头是键控值

这是文件的示例...

date,id,type,ccy,mtm,base,20151015,20151016,20151019,20151020,20151021,20151022
20161209,118,book,cad,-2493980,0,3845,1902,-1130,6052,-5594,-1419
20161209,118A188,bond,cad,-407954,0,5,11,5,23,3,-7
20161209,118A193072,bond,cad,111227,0,-296,-155,73,-429,410,95
20161209,118A217,bond,cad,4058,0,0,0,0,-2,1,0

对于 1) 我可以将列添加到标题，但对它们的追加似乎是在标题中而不是在标题下附加...我正在向标题中添加...

导入操作系统导入 csv

data = '/home/usr/local/bin/dev/remote/xx-yyy-zzz-118-sample.csv'

file_path = (os.path.dirname(data))
portfolio = ((os.path.basename(data)).strip().split('-')[3])

with open(data, 'r') as f:
    data = []

    r = csv.reader(f, delimiter = ',')

    header = next(r)
    header.append('portfolio')
    portfolio_col = next(r)
    header.append('file_path')
    file_path_col = next(r)
    print(header)

旧标题...

['date', 'id', 'type', 'ccy', 'mtm', 'base', '20151015', '20151016', '20151019', '20151020', '20151021', '20151022']

新标题...

['date', 'id', 'type', 'ccy', 'mtm', 'base', '20151015', '20151016', '20151019', '20151020', '20151021', '20151022', 'portfolio', 'file_path']

我正尝试像这样追加列，但它没有按我想要的那样工作...

for row in f:
      portfolio_col.append(portfolio)
     file_path_col.append(file_path)
     data.append(portfolio_col)
print(data)

它正在(某种程度上)遍历行，但不是我想要的方式......

[['20161209', '118', 'book', 'cad', '-2493980', '0', '3845', '1902', '-1130', '6052', '-5594', '-1419', '118', '118'], ['20161209', '118', 'book', 'cad', '-2493980', '0', '3845', '1902', '-1130', '6052', '-5594', '-1419', '118', '118']]

我也在努力在标题键上创建一个字典，但这个问题已经足够长了，我可能会问另一个单独的问题......

另外，我试图这样做，但我在将 header 作为列表时遇到问题...

with open(filename,'r') as f:
    header=f.readline().strip().split(',')    
    data = []
    for line in f:

        values=line.strip().split(',')
        row=dict()
        for i,h in enumerate(header):
            row[h]=values[i]

        data.append(row)

Answer 1

您可以使用 pandas 解决问题。这样向文件添加一列并追加就变成了微不足道的任务。

import os 
import pandas as pd
data = '../remote/xx-yyy-zzz-118-sample.csv'

df = pd.read_csv(data)
file_path = (os.path.dirname(data))
portfolio = ((os.path.basename(data)).strip().split('-')[3])

df['file_path'] = file_path
df['portfolio'] = portfolio
df.to_csv(data)
print(df)

       date          id  type  ccy      mtm  base  20151015  20151016  \
0  20161209         118  book  cad -2493980     0      3845      1902   
1  20161209     118A188  bond  cad  -407954     0         5        11   
2  20161209  118A193072  bond  cad   111227     0      -296      -155   
3  20161209     118A217  bond  cad     4058     0         0         0   

   20151019  20151020  20151021  20151022  file_path portfolio  
0     -1130      6052     -5594     -1419  ../remote       118  
1         5        23         3        -7  ../remote       118  
2        73      -429       410        95  ../remote       118  
3         0        -2         1         0  ../remote       118