python - 将 CSV 转换为 JSON。如何保持具有相同索引的值？

Question

我正在使用这个数据库:https://cpj.org/data/killed/?status=Killed&motiveConfirmed%5B%5D=Confirmed&type%5B%5D=Journalist&localOrForeign%5B%5D=Foreign&start_year=1992&end_year=2019&group_by=year

我已经将它预处理成这个 csv(只显示 2 行，每行 159):

year,combinedStatus,fullName,sortName,primaryNationality,secondaryNationality,tertiaryNationality,gender,photoUrl,photoCredit,type,lastStatus,typeOfDeath,status,employedAs,organizations,jobs,coverage,mediums,country,location,region,state,locality,province,localOrForeign,sourcesOfFire,motiveConfirmed,accountabilityCrossfire,accountabilityAssignment,impunityMurder,tortured,captive,threatened,charges,motive,lengthOfSentence,healthProblems,impCountry,entry,sentenceDate,sentence,locationImprisoned
1994,Confirmed,Abdelkader Hireche,,,,,Male,,,Journalist,,Murder,Killed,Staff,Algerian Television (ENTV),Broadcast Reporter,Politics,Television,Algeria,Algiers,,,Algiers,,Foreign,,Confirmed,,,Partial Impunity,No,No,No,,,,,,,,,
2014,Confirmed,Ahmed Hasan Ahmed,,,,,Male,,,Journalist,,Dangerous Assignment,Killed,Staff,Xinhua News Agency,"Camera Operator,Photographer","Human Rights,Politics,War",Internet,Syria,Damascus,,,Damascus,,Foreign,,Confirmed,,,,,,,,,,,,,,,

我想用它来制作这种类型的 JSON:

"Afghanistan": {"year": 2001, "fullName": "Volker Handloik", "gender": "Male", "typeOfDeath": "Crossfire", "employedAs": "Freelance", "organizations": "freelance reporter", "jobs": "Print Reporter", "coverage": "War", "mediums": "Print", "photoUrl": NaN}, "Somalia": {"year": 1994, "fullName": "Pierre Anceaux", "gender": "Male", "typeOfDeath": "Murder", "employedAs": "Freelance", "organizations": "freelance", "jobs": "Broadcast Reporter", "coverage": "Human Rights", "mediums": "Television", "photoUrl": NaN}

问题是阿富汗(正如您在链接中看到的那样)有许多记者死亡。我想在“阿富汗”索引下列出所有这些杀戮。但是，正如我目前所做的那样，只显示 csv 文件中的最后一个案例 (Volker Handloik)。我怎样才能得到它以便显示每个案例？

这是我的密码

import pandas as pd
import pprint as pp
import json

# list with stand-ins for empty cells
missing_values = ["n/a", "na", "unknown", "-", ""]

# set missing values to NaN
df = pd.read_csv("data_journalists.csv", na_values = missing_values, skipinitialspace = True, error_bad_lines=False)

# columns
columns_keep = ['year', 'fullName', 'gender', 'typeOfDeath', 'employedAs', 'organizations', 'jobs', 'coverage', 'mediums', 'country', 'photoUrl']

small_df = df[columns_keep]

with pd.option_context('display.max_rows', None, 'display.max_columns', None):  # more options can be specified also
    print(small_df)

# create dict with country-column as index
df_dict = small_df.set_index('country').T.to_dict('dict')

print(df_dict)

# make json file from the dict
with open('result.json', 'w') as fp:
    json.dump(df_dict, fp)

    # use pretty print to see if dict matches the json example in the exercise
 pp.pprint(df_dict)

我想在索引阿富汗下的 JSON 中包含所有这些名称(以及更多名称)

我想我需要一个附加到国家索引的对象列表，这样每个国家都可以显示所有记者死亡案例，而不是只有 1 个(每次都被 csv 中的下一个替换)我希望这足够清楚

Answer 1

我会保留你的代码直到 small_df 的定义。

之后，我们对“country”列执行 groupby 并在其上使用 pd.to_json:

country_series = small_df.groupby('country').apply(lambda r : r.drop(['country'], axis=1).to_json())

country_series 是一个以国家为索引的 pd.Series。之后，我们创建一个嵌套字典，这样我们就有了一个有效的 json 对象:

fullDict = {}
for ind, a in country_series.iteritems():
    b = json.loads(a)
    c = b['fullName']
    smallDict = {}
    for index, journalist in c.items():
        smallDict[journalist] = {}
        for i in b.keys():
            smallDict[journalist][i] = b[i][index]
    fullDict[ind] = (smallDict)

我这部分代码中的术语很糟糕，但我试图明确地写下所有步骤，这样事情就应该清楚了。

最后，我们将结果写入文件:

with open('result.json', 'w') as f:
    json.dump(fullDict, f)