如何将pandas行转换为自定义json格式并发出POST请求

Question

My DataFrame包含以下列和行

hosp  doc   dep  p_t    ins   tpa       p_n  i_c  t_date       cat    c_amt

ALZ   Dr.M  Onc  SAICO  SAISA AZBRONZE  AZS  11   2020-08-11   Cons   341.25
ALZ   Dr.K  Card Mitra  Mit   ASGOLD    ASG  8265 2020-08-15   Cons   1123.45

我想将每一行转换为以下json格式，并向API发出post请求。（请注意，“id”始终为1）

{
    "hosp": "ALZ",
    "doc": "Dr.M",
    "dep": "Onc",
    "p_t": "SAICO",
    "ins": "SAISA",
    "tpa": "AZBRONZE",
    "p_n": "AZS",
    "activities": [
        {
            "id": "1",
            "i_c": "11",
            "t_d": "2020-08-11",
            "cat": "Cons",
            "c_amt": "341.25"
        }
    ]
}

最后，将响应作为新列添加到数据帧中

hosp  doc   dep  p_t    ins   tpa       p_n  i_c  t_date       cat    c_amt    response

ALZ   Dr.M  Onc  SAICO  SAISA AZBRONZE  AZS  11   2020-08-11   Cons   341.25   Yes
ALZ   Dr.K  Card Mitra  Mit   ASGOLD    ASG  8265 2020-08-15   Cons   1123.45  No

Answer 1

这是解决办法

由于您希望对数据帧中存在的所有行运行此操作，请执行以下操作

# Number of rows
all_rows = len(df)
    
for i in range(all_rows):
    # choose the first half of cols before "activities"
    x = dict(df.iloc[i, 0:7])    
    # add the "activites" with the rest of the cols
    x["activities"] = [dict(df.iloc[i, 7:])]
    # dict has single quotes which throws an error if sent to an API, so use json.dumps
    x = json.dumps(x)

向API发送POST请求的步骤

url = 'http://your_api/api/v1/response'
headers = {
    'content-type': "application/json"
}
response = requests.request("POST", url, data=x, headers=headers)
res = json.loads(response.content)

Answer 2

您可以使用这个实现

1. 选择要放在“活动”下的列
2. 从原始数据帧中删除这些列
3. 根据需要设置Id
4. 将活动数据框架转换为json
5. 将活动json作为活动列设置为原始数据帧

columns = ['i_c', 't_date', 'cat', 'c_amt']
activities = df[columns]
df.drop(columns=columns, inplace=True)
activities['id'] = '1'
activities_json = eval(activities.to_json(orient='records'))
df['activities'] = activities_json

Answer 3

您可以将pandas函数https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_json.html与df.to_json()一起应用，然后访问每一行以获得json表示