将非数据框附加到

Question

我试图找出是否有一个更简单的方法来附加一个日期列和一个额外的信息列到我现有的csv文件。我之所以添加这些列，是因为这些信息不在restapi调用的JSON字符串中。你知道吗

import requests
import json
import http.client
import datetime
import pandas as pd
from pandas.io.json import json_normalize

url = api.getinfo()
r = requests.get(url, headers=headers, verify=False)
if r.status_code != http.client.OK:
    raise requests.HTTPError(r)

jsonstring = json.dumps(r.json()["data"])
load = json.loads(jsonstring)
df = json_normalize(load)
col = ["poolId", "totalPoolCapacity", "totalLocatedCapacity",
       "availableVolumeCapacity", "usedCapacityRate"]
with open('hss.csv', 'a') as f:
    df.to_csv(f, header=False, columns=col)

a = pd.read_csv('hss.csv')
a['date'] = [datetime.date.today()] * len(a)
a.to_csv('hss.csv')
b = pd.read_csv('hss.csv')
b['storage system'] = "ssystem22"
b.to_csv('hss.csv')

每次运行脚本时，我都会在csv文件中获得额外的列Unnamed: 0,Unnamed: 0.1。而且每次我附加它都会覆盖旧的日期。你知道吗

,Unnamed: 0,Unnamed: 0.1,poolId,totalPoolCapacity, totalLocatedCapacity,availableVolumeCapacity,usedCapacityRate,date,storage system
0,155472,223618,565064,51,,2017-04-12,ssystem22
1,943174,819098,262042,58,,2017-04-12,ssystem22
0,764600,966017,046668,71,,2017-04-12,ssystem22
1,764600,335680,487650,76,,2017-04-12,ssystem22
2,373700,459800,304446,67,,2017-04-12,ssystem22

Answer 1

我不断研究，找到了解决办法。我应该使用pd.Series函数。下面是更正的代码：

import requests
import json
import http.client
import datetime
import pandas as pd
from pandas.io.json import json_normalize

url = api.getinfo()
r = requests.get(url, headers=headers, verify=False)
if r.status_code != http.client.OK:
    raise requests.HTTPError(r)

jsonstring = json.dumps(r.json()["data"])
load = json.loads(jsonstring)
df = json_normalize(load)
df['storage system'] = pd.Series('ssystem22', index=df.index)
df['date'] = pd.Series(datetime.date.today().strftime('%m-%d-%Y'),
                       index=df.index)
col = ["poolId", "totalPoolCapacity", "totalLocatedCapacity",
       "availableVolumeCapacity", "usedCapacityRate", "storage system",
       "date"]
with open(csvfile, 'a') as f:
    df.to_csv(f, header=False, columns=col)