I did ESG Bulk extraction. However, when I am trying to flatten the JSON to CSV, few columns still has the json tags. It doesn't seem to flatten the JSON file completely. Appreciate you support to investigate and help on this.
For instance, I tried converting the RFT-ESG-Scores-Full-Init-2021-04-25.jsonl.gz file from JSON to CSV using the following code,
#convert specific json to csv filedestinationpath = 'C:\\$files\\$ESG\\RDP_BULK\\Results\\' filename = filedestinationpath + 'RFT-ESG-Scores-Full-Init-2021-04-25' + '.jsonl.gz' f=gzip.open(filename,'rb') file_content=f.read() lines = file_content.splitlines() df_inter = pd.DataFrame(lines) df_inter.columns = ['json_element'] df_resolve = df_inter['json_element'].apply(json.loads) df_final = pd.json_normalize(df_resolve) resultspth = filedestinationpath + 'RFT-ESG-Scores-Full-Init-2021-04-25' + '.csv' df_final.to_csv(resultspth, index = False)
It seem to convert, but not all the column. For example, OrganizationName doesn't seem to flatten out completely, it still carries the json tags.
Similarly, when I tried "RFT-ESG-Symbology-SEDOL-Init-2021-04-29.jsonl"; few columns as shown in the screengrab below seems to be the issue.
Appreciate if you can review and support on this.