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
1条答案
按热度按时间huwehgph1#
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 。