2023年7月20日 20:14:02go评论99阅读模式

英文:

convert an excel file to JSON

问题

我正在尝试将Excel文件转换为特定的JSON格式。我的Excel文件如下：

我已经使用pandas库进行了如下操作：

import pandas as pd
df = pd.read_excel(r'C:\Users\xcdfr\Desktop\VMtag\vmtag.xlsx', header=1, index_col=None) 
for ind in df.index:
    vmname = df['Name'][ind]
    scop1 = df['Scope1'][ind]
    tag1 = df['Tag1'][ind]
    scop2 = df['Scope2'][ind]
    tag2 = df['Tag2'][ind]
    scop3 = df['Scope3'][ind]
    tag3 = df['Tag3'][ind]
    tagdata = {"external_id": "500cbe10-e7d6-189d-15ae-e60b85326cd3", "tags": [{"scope": scop1, "tag": tag1},{"scope": scop2, "tag": tag2},{"scope": scop3, "tag": tag3}]}
    print(tagdata)

是否有更好的方法，使JSON格式如下：

{'external_id': '500cbe10-e7d6-189d-15ae-e60b85326cd3', 'tags': [{'scope': 'Environment', 'tag': 'Production'}, {'scope': 'Application', 'tag': 'App1'}, {'scope': 'Tier', 'tag': 'Web'}]}
{'external_id': '500cbe10-e7d6-189d-15ae-e60b85326cd3', 'tags': [{'scope': 'Environment', 'tag': 'Production'}, {'scope': 'Application', 'tag': 'App1'}, {'scope': 'Tier', 'tag': 'App'}]}
{'external_id': '500cbe10-e7d6-189d-15ae-e60b85326cd3', 'tags': [{'scope': 'Environment', 'tag': 'Production'}, {'scope': 'Application', 'tag': 'App1'}, {'scope': 'Tier', 'tag': 'DB'}]}
{'external_id': '500cbe10-e7d6-189d-15ae-e60b85326cd3', 'tags': [{'scope': 'Environment', 'tag': 'UAT'}, {'scope': 'Application', 'tag': 'App1'}, {'scope': 'Tier', 'tag': 'Web'}]}
{'external_id': '500cbe10-e7d6-189d-15ae-e60b85326cd3', 'tags': [{'scope': 'Environment', 'tag': 'UAT'}, {'scope': 'Application', 'tag': 'App1'}, {'scope': 'Tier', 'tag': 'App'}]}
{'external_id': '500cbe10-e7d6-189d-15ae-e60b85326cd3', 'tags': [{'scope': 'Environment', 'tag': 'UAT'}, {'scope': 'Application', 'tag': 'App1'}]}

我想要删除Excel文件中没有值的额外“scope”条目。

英文:

I am trying to covert an excel file to specific Jason format.
My Excel file looks like below:
Excel File

I have used pandas library as follows
import pandas as pd

df = pd.read_excel(r&#39;C:\Users\xcdfr\Desktop\VMtag\vmtag.xlsx&#39;, header=1,index_col=None) 
for ind in df.index:
    #print(df[&#39;VMName&#39;][ind],&quot;|&quot;, df[&#39;Scope1&#39;][ind],&quot;|&quot;,df[&#39;Tag1&#39;][ind], &quot;|&quot;,df[&#39;Scope2&#39;][ind],&quot;|&quot;,df[&#39;Tag2&#39;][ind], &quot;|&quot;,df[&#39;Scope3&#39;][ind],&quot;|&quot;,df[&#39;Tag3&#39;][ind])
    vmname = df[&#39;Name&#39;][ind]
    scop1 = df[&#39;Scope1&#39;][ind]
    tag1 = df[&#39;Tag1&#39;][ind]
    scop2 = df[&#39;Scope2&#39;][ind]
    tag2 = df[&#39;Tag2&#39;][ind]
    scop3 = df[&#39;Scope3&#39;][ind]
    tag3 = df[&#39;Tag3&#39;][ind]
    tagdata = {&quot;external_id&quot;: &quot;500cbe10-e7d6-189d-15ae-e60b85326cd3&quot;, &quot;tags&quot;: [{&quot;scope&quot;: scop1, &quot;tag&quot;: tag1},{&quot;scope&quot;: scop2, &quot;tag&quot;: tag2},{&quot;scope&quot;: scop3, &quot;tag&quot;: tag3}]}
    print(tagdata)

Is there a better way so that the JSON format will be as follows:

{&#39;external_id&#39;: &#39;500cbe10-e7d6-189d-15ae-e60b85326cd3&#39;, &#39;tags&#39;: [{&#39;scope&#39;: &#39;Environment&#39;, &#39;tag&#39;: &#39;Production&#39;}, {&#39;scope&#39;: &#39;Application&#39;, &#39;tag&#39;: &#39;App1&#39;}, {&#39;scope&#39;: &#39;Tier&#39;, &#39;tag&#39;: &#39;Web&#39;}]}
{&#39;external_id&#39;: &#39;500cbe10-e7d6-189d-15ae-e60b85326cd3&#39;, &#39;tags&#39;: [{&#39;scope&#39;: &#39;Environment&#39;, &#39;tag&#39;: &#39;Production&#39;}, {&#39;scope&#39;: &#39;Application&#39;, &#39;tag&#39;: &#39;App1&#39;}, {&#39;scope&#39;: &#39;Tier&#39;, &#39;tag&#39;: &#39;App&#39;}]}
{&#39;external_id&#39;: &#39;500cbe10-e7d6-189d-15ae-e60b85326cd3&#39;, &#39;tags&#39;: [{&#39;scope&#39;: &#39;Environment&#39;, &#39;tag&#39;: &#39;Production&#39;}, {&#39;scope&#39;: &#39;Application&#39;, &#39;tag&#39;: &#39;App1&#39;}, {&#39;scope&#39;: &#39;Tier&#39;, &#39;tag&#39;: &#39;DB&#39;}]}
{&#39;external_id&#39;: &#39;500cbe10-e7d6-189d-15ae-e60b85326cd3&#39;, &#39;tags&#39;: [{&#39;scope&#39;: &#39;Environment&#39;, &#39;tag&#39;: &#39;UAT&#39;}, {&#39;scope&#39;: &#39;Application&#39;, &#39;tag&#39;: &#39;App1&#39;}, {&#39;scope&#39;: &#39;Tier&#39;, &#39;tag&#39;: &#39;Web&#39;}]}
{&#39;external_id&#39;: &#39;500cbe10-e7d6-189d-15ae-e60b85326cd3&#39;, &#39;tags&#39;: [{&#39;scope&#39;: &#39;Environment&#39;, &#39;tag&#39;: &#39;UAT&#39;}, {&#39;scope&#39;: &#39;Application&#39;, &#39;tag&#39;: &#39;App1&#39;}, {&#39;scope&#39;: &#39;Tier&#39;, &#39;tag&#39;: &#39;App&#39;}]}
{&#39;external_id&#39;: &#39;500cbe10-e7d6-189d-15ae-e60b85326cd3&#39;, &#39;tags&#39;: [{&#39;scope&#39;: &#39;Environment&#39;, &#39;tag&#39;: &#39;UAT&#39;}, {&#39;scope&#39;: &#39;Application&#39;, &#39;tag&#39;: &#39;App1&#39;}{&#39;scope&#39;: nan, &#39;tag&#39;: nan}]}

I woul like to remove additional "scope" entry if no values in the excel files

答案1

得分: 0

尝试这个
    import pandas as pd
    
    df = pd.read_excel(r'C:\Users\xcdfr\Desktop\VMtag\vmtag.xlsx', header=1, index_col=None)
    tagdata_list = []
    
    for _, row in df.iterrows():
        tagdata = {"external_id": "500cbe10-e7d6-189d-15ae-e60b85326cd3", "tags": []}
        
        for i in range(1, 4):
            scope = row[f"Scope{i}"]
            tag = row[f"Tag{i}"]
            
            if pd.notna(scope) and pd.notna(tag):
                tagdata["tags"].append({"scope": scope, "tag": tag})
        
        tagdata_list.append(tagdata)
    
    for tagdata in tagdata_list:
        print(tagdata)

英文:

Try this
import pandas as pd

df = pd.read_excel(r&#39;C:\Users\xcdfr\Desktop\VMtag\vmtag.xlsx&#39;, header=1, index_col=None)
tagdata_list = []
for _, row in df.iterrows():
    tagdata = {&quot;external_id&quot;: &quot;500cbe10-e7d6-189d-15ae-e60b85326cd3&quot;, &quot;tags&quot;: []}
    
    for i in range(1, 4):
        scope = row[f&quot;Scope{i}&quot;]
        tag = row[f&quot;Tag{i}&quot;]
        
        if pd.notna(scope) and pd.notna(tag):
            tagdata[&quot;tags&quot;].append({&quot;scope&quot;: scope, &quot;tag&quot;: tag})
    
    tagdata_list.append(tagdata)
for tagdata in tagdata_list:
    print(tagdata)

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

将Excel文件转换为JSON

问题

答案1

将byte[]转换为JSON，反之亦然，不使用jackson或gson。

读取一个文本文件，根据分隔符将其拆分为多行。

从列中拆分值并生成序列号。

连接/根据列值将字符串重复连接X次，以字符”|”（管道）分隔。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。