python 如何解析具有动态记录的 Dataframe 列,并通过维护与表的关系将其写入不同的列

oxcyiej7  于 2023-04-10  发布在  Python
关注(0)|答案(1)|浏览(98)
cur = snow_connection.cursor()
data = snow_connection.cursor().execute("SELECT x,y,z FROM abc.testing_view2").fetchall()
df = pd.DataFrame(data)
df.columns = ['x','y','z']
print(df)

temp_col=df.iloc[:,2]
print(temp_col)
for i in range (len(df.iloc[:,0])):
    for j in range(len(df.iloc[:,1])):
        for k in range(len(temp_col)):
            json_dict = str(json.loads(str(k)))
            print(json_dict)
    # accessing values in the dictionary
            portfolio_name = str(json_dict[0])
            portfolio_id = str(json_dict[1])
            for l in range(len(portfolio_name)):
                for m in range(len(portfolio_id)):
    # created a new dictionary with all three columns
                    new_dict = {'name': portfolio_name, 'portfolio_id': portfolio_id,'x': ['X'], 'y': ['Y']}
                    print(new_dict)
                    new_data = []
                    new_data.append(new_dict)
                    new_df = pd.DataFrame(new_data)

问题出在z列(具有名称和ID的JSON记录)具有动态记录,即只能包含一个具有名称和ID的记录,并且也可以超过1。但它将仅与一个x和yMap。所以我必须将此数据写入多行,即如果列z包含一个记录,则应该将数据写入4列x,y,name和id,如果列z包含2条记录,则它应该写入数据并创建2行。
我正在迭代这个列,并将其写入字典以维护关系,但它没有阅读我为name和id创建的json dict。
样本数据:

{
  "document_portfolio": "[{\"name\": \"MWP FT Income ICP\", \"portfolio_id\": \"31a01afd-1f69-e617-ade3-d7c1895c4461\"}, {\"name\": \"MWP Tactical ETP IMG\", \"portfolio_id\": \"13281ca6-a9a7-d361-1cf2-07e6fa3e283a\"}]",
  "document_uuid": "28d7ccb1-3f9f-fdd0-c757-6b134a74fdd3",
  "user_id": "00u9vj92B0ZPUMU9b5d5"
}

{
"document_portfolio": "[{\"name\": \"tesying\", \"portfolio_id\": \"59d26651-3484-e7ef-9ece-f7194d7639e0\"}]","document_uuid": "1cf1ca8e-f0e9-844b-11d6-0d05302fb777",
"user_id": "00u5flkeths3G668k5d7"
}

预期输出:

portfolio_id    name    portfolio_uuid  user_id

31a01afd-1f69-e617-ade3-d7c1895c4461    MWP FT Income ICP   28d7ccb1-3f9f-fdd0-c757-6b134a74fdd3    00u9vj92B0ZPUMU9b5d5

13281ca6-a9a7-d361-1cf2-07e6fa3e283a    MWP Tactical ETP IMG    28d7ccb1-3f9f-fdd0-c757-6b134a74fdd3    00u9vj92B0ZPUMU9b5d5
huwehgph

huwehgph1#

for i in range (len(df.iloc[:,0])):
    for j in range(len(df.iloc[:,1])):
        for s in range(len(df.iloc[:,2])):
            for index,row in df.iterrows():
                json_dict = row['DOCUMENT_PORTFOLIO']
                data = json.loads(json_dict)
                for i,row in enumerate(data):
                    #for j in range(len(df['DOCUMENT_UUID'])):
                    if len(data) == 1:
                        result_dic = {}
                        portfolio_name = row['name']
                        result_dic[i] = portfolio_name
                        print(portfolio_name)
                        portfolio_id = row['portfolio_id']
                        result_dic[i] = portfolio_id
                        print(portfolio_id)
                        DOCUMENT_UUID = df['DOCUMENT_UUID']
                        result_dic[j] = DOCUMENT_UUID
                        print(DOCUMENT_UUID)
                    else:
                        exit()

output:这是当我不对长度设置条件时的输出Ali Mahbod - $1M Valued Client-Copy 5efbd 6c 7 -2abe-5e 78-b 035 - 1cfffc 18 b 9 cd ['name':'Falip Large Caps 1','portfolio_id':“7 c3 b5 cd 5-b788-8667- 23 b2-d25 fb 110 e525”},{“名称”:'Falip Large Caps 2','portfolio_id':'7 c464968 - 91 e8-a4 d5 -9756- 07 efb 0 d 0 c7 b6'}] Falip大号瓶盖1 7 c3 b5 cd 5-b788-8667- 23 b2-d25 fb 110 e525 Falip大号瓶盖2 7 c464968 -91
然而,在添加长度条件时,我得到了这个错误:
追溯(最近一次调用):File“Document_custom_field.py”,line 102,in for index,row in name.iterrows():属性错误:“str”对象没有属性“iterrows”
我试着再次将其转换为 Dataframe 并再次加载JSON,但没有成功。
现在,如果长度大于1,我将退出循环,但它不会根据长度条件将记录写入一个单一的 Dataframe 。

相关问题