具有不同集合的自定义嵌套json结构

Question

我是python新手，pandas有一个csv文件

.----.---------.-------.-------------------.-------------------.-------------------.-------------------.
| id | country | state | cold_stress_score | cold_stress_level | heat_stress_score | heat_stress_level |
:----+---------+-------+-------------------+-------------------+-------------------+-------------------:
|  1 | USA     | NJ    |             0.003 | low               |             0.673 | moderate          |
:----+---------+-------+-------------------+-------------------+-------------------+-------------------:
|  2 | USA     | NJ    |             0.001 | high              |               0.2 | high              |
:----+---------+-------+-------------------+-------------------+-------------------+-------------------:
|  3 | USA     | NJ    |             0.004 | moderate          |               0.3 | low               |
:----+---------+-------+-------------------+-------------------+-------------------+-------------------:
|  4 | USA     | NY    |             0.005 | moderate          |               0.4 | moderate          |
:----+---------+-------+-------------------+-------------------+-------------------+-------------------:
|  5 | USA     | NY    |             0.006 | high              |               0.5 | high              |
:----+---------+-------+-------------------+-------------------+-------------------+-------------------:
|  6 | USA     | NY    |             0.009 | low               |               0.6 | low               |
'----'---------'-------'-------------------'-------------------'-------------------'-------------------'

我想把它转换成嵌套的json方式。

期望的json

  {
  "id":1,
  "country": "USA",
  "state": "NJ",
  "cold_stress":{
    "cold_stress_score" : 0.003,
    "cold_stress_level": "low",
  },
  "heat_stress":{
    "heat_stress_score" : 0.0673,
    "heat_stress_level": "moderate",

  }

}

我尝试过这个解决方案Convert Pandas Dataframe to nested JSON

j = (df.groupby(['id','country','state'], as_index=False)
             .apply(lambda x: x[['cold_stress_score','cold_stress_level']].to_dict('r'))
             .reset_index()
             .rename(columns={0:'cold_stress'})
             .to_json(orient='records'))

我想在上面返回的代码中添加热应力。

  "id":1,
  "country": "USA",
  "state": "NJ",
  "cold_stress":{
    "cold_stress_score" : 0.003,
    "cold_stress_level": "low",
  }
}

我如何能够添加heat_stress我的csv太大，并且正在寻找上面填充的动态值，如冷应激

Answer 1

如果你很少或根本不像这里那样做处理，那么pandas就是过度杀伤力和太复杂了。我的建议是坚持使用标准库中的csv和json模块。

代码可能是(或多或少)：

with open(inputfile) as fdin, open (outputfile, "w") as fdout:
    rd = csv.DictReader(fdin)
    js = [{'id': int(row['id']), 'country': row['country'], 'state': row['state'],
           'cold_stress': {'cold_stress_code': row['cold_stress_code'],
                           'cold_stress_level': row['cold_stress_level']},  
           'heat_stress': {'heat_stress_code': row['heat_stress_code'],
                           'heat_stress_level': row['heat_stress_level']}, 
           } for row in rd]
    json.dump(js, fdout, indent=2)

Answer 2

你试过像这样的东西吗？

# create first grouping (leave heat columns same)
j = (df.groupby(['id','country','state', 'heat_stress_score', 'heat_stress_level'], as_index=False)
             .apply(lambda x: x[['cold_stress_score','cold_stress_level']].to_dict('r'))
             .reset_index()
             .rename(columns={0:'cold_stress'}))
# care about heat grouping
j = (j.groupby(['id','country','state', 'cold_stress'], as_index=False)
             .apply(lambda x: x[['heat_stress_score','heat_stress_level']].to_dict('r'))
             .reset_index()
             .rename(columns={0:'heat_stress'})
             .to_json(orient='records'))