使用Python根据文件中的列数从文件生成JSON结构

Question

我有一个问题，我需要生成一个json负载，将其作为数据传递给api，问题是我需要基于来自文件的id数量生成json结构。 例如：如果一个人有5个ID，那么我需要生成5行数据；如果有4个ID，那么需要生成4行数据，依此类推。。。你知道吗

我的数据文件如下所示：

Member_ID,User_ID,Proxy_ID,A_ID,Login_ID,First_Name,Last_Name
M1000,U1000,P1000,A1000,Jim1,Jim,Kong
M2000,U2000,P2000,A2000,OlilaJ,Olila,Jayavarman
M3000,U3000,P3000,A3000,LisaKop,Lisa,Kopkingg
M4000,U4000,P4000,A4000,KishoreP,Kishore,Pindhar
M5000,U5000,P5000,A5000,Gobi123,Gobi,Nadar

数据还可以如下所示：

Member_ID,User_ID,A_ID,Login_ID,First_Name,Last_Name
M1000,U1000,A1000,Jim1,Jim,Kong
M2000,U2000,A2000,OlilaJ,Olila,Jayavarman
M3000,U3000,A3000,LisaKop,Lisa,Kopkingg
M4000,U4000,A4000,KishoreP,Kishore,Pindhar
M5000,U5000,A5000,Gobi123,Gobi,Nadar

我无法找到一种方法来为每种类型的输入文件动态生成行数。你知道吗

from datetime import datetime
import json
import requests

start_time = datetime.now()

delim = "," # Just in case we switch to tsv or something
headers = {'content-type': 'application/json'}

with open('Documents/Onboarding_sample.csv', 'r') as file:
    i = next(file)
    listcolumns = i.split(",")
    sub = "ID"
    IDcolumns = [s for s in listcolumns if sub.lower() in s.lower()]
    print len(IDcolumns)
    for line in file:
        line_list = line.split(delim)
        Member_ID = line_list[0]
        User_ID = line_list[1]
        Proxy_ID = line_list[2]
        A_ID = line_list[3]
        payload = { 
            "IndividualInfo":
            [{
            "Member_ID": Member_ID,
            "Identifiertype":"001",
            "EType:01"
            }
            {
            "User_ID": User_ID,
            "Identifiertype":"001",
            "EType:01"
            }
            {
            "Proxy_ID": Proxy_ID,
            "Identifiertype":"001",
            "EType:01"
            }
            {
            "A_ID": A_ID,
            "Identifiertype":"001",
            "EType:01"
            }
            ]
        }
        try:
            r = requests.post("http://www.google.com/blahblah", data=json.dumps(payload), timeout=(1,20), headers=headers)
            r.raise_for_status()
        except requests.exceptions.HTTPError as errh:
            print "HTTP Error:%s" %errh
        except requests.exceptions.ConnectionError as errc:
            print "Error Connecting:%s" %errc
        except requests.exceptions.Timeout as errt:
            print "Timeout error:%s" %errt

        print "This is a JSON object."
        print payload

end_time = datetime.now()

print('Duration: {}'.format(end_time - start_time))

有人请让我知道如何正确地和动态地做，无论我在文件中得到什么ID。你知道吗

Answer 1

正如其他人所建议的，使用csv模块可能更容易，但使用约定方法也可以实现：

delim = "," # Just in case we switch to tsv or something

with open('test.txt', 'r') as file:
    # Create a list of valid headers in comma seperated values and their respective index
    header = [(i, col) for i, col in enumerate(next(file).rstrip().split(delim)) if col.endswith('_ID')]

    # Create a list of data in comma seperated values
    data = [l.rstrip().split(delim) for l in file.readlines()]

    # Go through each record to create a payload
    for record in data:

        # Here we use the header index to retrieve the respective data to create the dictionary with list comprehension
        payload = {'IndividualInfo': [{key: record[i], 'Identifiertype': '001', 'EType':'01'} for i, key in header]}

        # Do whatever you need with json.dumps(payload)

结果如下：

# the index/header pairs
# [(0, 'Member_ID'), (1, 'User_ID'), (2, 'Proxy_ID'), (3, 'A_ID'), (4, 'Login_ID')]

# the separated data
# [['M1000', 'U1000', 'P1000', 'A1000', 'Jim1', 'Jim', 'Kong'], ['M2000', 'U2000', 'P2000', 'A2000', 'OlilaJ', 'Olila', 'Jayavarman'], ['M3000', 'U3000', 'P3000', 'A3000', 'LisaKop', 'Lisa', 'Kopkingg'], ['M4000', 'U4000', 'P4000', 'A4000', 'KishoreP', 'Kishore', 'Pindhar'], ['M5000', 'U5000', 'P5000', 'A5000', 'Gobi123', 'Gobi', 'Nadar']]

# The payloads
# {'IndividualInfo': [{'Member_ID': 'M1000', 'Identifiertype': '001', 'EType': '01'}, {'User_ID': 'U1000', 'Identifiertype': '001', 'EType': '01'}, {'Proxy_ID': 'P1000', 'Identifiertype': '001', 'EType': '01'}, {'A_ID': 'A1000', 'Identifiertype': '001', 'EType': '01'}, {'Login_ID': 'Jim1', 'Identifiertype': '001', 'EType': '01'}]}
# {'IndividualInfo': [{'Member_ID': 'M2000', 'Identifiertype': '001', 'EType': '01'}, {'User_ID': 'U2000', 'Identifiertype': '001', 'EType': '01'}, {'Proxy_ID': 'P2000', 'Identifiertype': '001', 'EType': '01'}, {'A_ID': 'A2000', 'Identifiertype': '001', 'EType': '01'}, {'Login_ID': 'OlilaJ', 'Identifiertype': '001', 'EType': '01'}]}
# {'IndividualInfo': [{'Member_ID': 'M3000', 'Identifiertype': '001', 'EType': '01'}, {'User_ID': 'U3000', 'Identifiertype': '001', 'EType': '01'}, {'Proxy_ID': 'P3000', 'Identifiertype': '001', 'EType': '01'}, {'A_ID': 'A3000', 'Identifiertype': '001', 'EType': '01'}, {'Login_ID': 'LisaKop', 'Identifiertype': '001', 'EType': '01'}]}
# {'IndividualInfo': [{'Member_ID': 'M4000', 'Identifiertype': '001', 'EType': '01'}, {'User_ID': 'U4000', 'Identifiertype': '001', 'EType': '01'}, {'Proxy_ID': 'P4000', 'Identifiertype': '001', 'EType': '01'}, {'A_ID': 'A4000', 'Identifiertype': '001', 'EType': '01'}, {'Login_ID': 'KishoreP', 'Identifiertype': '001', 'EType': '01'}]}
# {'IndividualInfo': [{'Member_ID': 'M5000', 'Identifiertype': '001', 'EType': '01'}, {'User_ID': 'U5000', 'Identifiertype': '001', 'EType': '01'}, {'Proxy_ID': 'P5000', 'Identifiertype': '001', 'EType': '01'}, {'A_ID': 'A5000', 'Identifiertype': '001', 'EType': '01'}, {'Login_ID': 'Gobi123', 'Identifiertype': '001', 'EType': '01'}]}

注意：我使用enumerate()创建了index/header组合，因为如果在_ID列之间有其他列，它将为您提供一个精确的方法来定位相应的数据。你知道吗

编辑：

对于Python2.7，请改用以下命令（sample on repl.it）：

delim = "," # Just in case we switch to tsv or something

with open('test.txt', 'r') as file:
    # Create a list of valid headers in comma seperated values and their respective index
    header = [(i, col) for i, col in enumerate(next(file).rstrip().split(delim)) if col.endswith('_ID')]
    # Create a list of data in comma seperated values
    data = []
    for f in file:
        data.append(f.rstrip().split(delim))

# We're done with reading the file,
# We can proceed outside the `with` context manager from this point

# Go through each record to create a payload
for record in data:

    # Here we use the header index to retrieve the respective data to create the dictionary with list comprehension
    payload = {'IndividualInfo': [{key: record[i], 'Identifiertype': '001', 'EType':'01'} for i, key in header]}

    # Do whatever you need with json.dumps(payload)