Python конвертирует json и сохраняет в csv ⇐ Python
-
Anonymous
Python конвертирует json и сохраняет в csv
I am using Python Pandas in Jupyter Notebooks and new to the use of json files in Python.
I have a nested json file to convert to csv in Python.
I can drilldown and get the nested dataframes.
There is the top level and a first nest of columns with another subnest of columns in first nest.
I am unable to assemble in to a single dataframe with all columns and parsed rows present.
How can I generate an output dataframe containing all the top level and drilldown columns (matched to the top level columns) with all rows parsed.
This is example data with the correct structure
# Example usage json_data = { "name": "John", "questions": [ { "question": "Q1", "fields": [ {"key": "q1_key1", "title": "Title 1", "type": "Type 1", "answer": "Answer 1"}, {"key": "q1_key2", "title": "Title 2", "type": "Type 2", "answer": "Answer 2"} ], "category": "Category 1", "index": 1 }, { "question": "Q2", "fields": [ {"key": "q2_key1", "title": "Title 1", "type": "Type 1", "answer": "Answer 1"} ], "category": "Category 2", "index": 2 } ], "metadata": {"meta_key": "meta_value"}, "formId": "form123", "id": "id123", "user": "user123", "_rid": "_rid123", "_self": "_self123", "_etag": "_etag123", "_attachments": "_attachments123", "_ts": "_ts123" } The following is my attempt
# Normalize the top level columns df_top_level = json_normalize(d) df_dat = json_normalize(d['questions']) df_dat['question'] = df_dat['question'].apply(lambda x: x.strip('[]').split(', ') if isinstance(x, str) else x) df_dat = df_dat.explode('question') df_dat['category'] = df_dat['category'].apply(lambda x: x.strip('[]').split(', ') if isinstance(x, str) else x) df_dat = df_dat.explode('category') df_dat['fields'] = df_dat['fields'].apply(lambda x: x.strip('[]').split(', ') if isinstance(x, str) else x) df_dat = df_dat.explode('fields') # Attempt to generate a new column and merge on the 'key' value in the drilldown dfs df_dat['extracted_key'] = df_dat['fields'].str.extract(r"'key':\s+'([^']+)'") df_dat.head() df_fields = json_normalize(data = d['questions'], record_path = 'fields', meta = ['key', 'title', 'type', 'answer'], meta_prefix = 'meta_', errors = 'ignore') df_fields = df_fields.filter(regex='^(?!meta_)') df_fields['key'] = df_fields['key'].apply(lambda x: x.strip('[]').split(', ') if isinstance(x, str) else x) df_fields = df_fields.explode('key') df_fields['type'] = df_fields['type'].apply(lambda x: x.strip('[]').split(', ') if isinstance(x, str) else x) df_fields = df_fields.explode('type') df_fields['answer'] = df_fields['answer'].apply(lambda x: x.strip('[]').split(', ') if isinstance(x, str) else x) df_fields = df_fields.explode('answer') df_fields.head() df_questions = json_normalize(d['questions'], record_prefix='question_', meta=['name', 'metadata', 'formId', 'id', 'user', '_rid', '_self', '_etag', '_attachments', '_ts']) # Merge all DataFrames final_df = pd.concat([df_top_level, df_questions.drop(columns=['fields']), df_fields.drop(columns = ['question', 'category', 'index'])], axis = 1) final_df.head()
Источник: https://stackoverflow.com/questions/780 ... ave-to-csv
I am using Python Pandas in Jupyter Notebooks and new to the use of json files in Python.
I have a nested json file to convert to csv in Python.
I can drilldown and get the nested dataframes.
There is the top level and a first nest of columns with another subnest of columns in first nest.
I am unable to assemble in to a single dataframe with all columns and parsed rows present.
How can I generate an output dataframe containing all the top level and drilldown columns (matched to the top level columns) with all rows parsed.
This is example data with the correct structure
# Example usage json_data = { "name": "John", "questions": [ { "question": "Q1", "fields": [ {"key": "q1_key1", "title": "Title 1", "type": "Type 1", "answer": "Answer 1"}, {"key": "q1_key2", "title": "Title 2", "type": "Type 2", "answer": "Answer 2"} ], "category": "Category 1", "index": 1 }, { "question": "Q2", "fields": [ {"key": "q2_key1", "title": "Title 1", "type": "Type 1", "answer": "Answer 1"} ], "category": "Category 2", "index": 2 } ], "metadata": {"meta_key": "meta_value"}, "formId": "form123", "id": "id123", "user": "user123", "_rid": "_rid123", "_self": "_self123", "_etag": "_etag123", "_attachments": "_attachments123", "_ts": "_ts123" } The following is my attempt
# Normalize the top level columns df_top_level = json_normalize(d) df_dat = json_normalize(d['questions']) df_dat['question'] = df_dat['question'].apply(lambda x: x.strip('[]').split(', ') if isinstance(x, str) else x) df_dat = df_dat.explode('question') df_dat['category'] = df_dat['category'].apply(lambda x: x.strip('[]').split(', ') if isinstance(x, str) else x) df_dat = df_dat.explode('category') df_dat['fields'] = df_dat['fields'].apply(lambda x: x.strip('[]').split(', ') if isinstance(x, str) else x) df_dat = df_dat.explode('fields') # Attempt to generate a new column and merge on the 'key' value in the drilldown dfs df_dat['extracted_key'] = df_dat['fields'].str.extract(r"'key':\s+'([^']+)'") df_dat.head() df_fields = json_normalize(data = d['questions'], record_path = 'fields', meta = ['key', 'title', 'type', 'answer'], meta_prefix = 'meta_', errors = 'ignore') df_fields = df_fields.filter(regex='^(?!meta_)') df_fields['key'] = df_fields['key'].apply(lambda x: x.strip('[]').split(', ') if isinstance(x, str) else x) df_fields = df_fields.explode('key') df_fields['type'] = df_fields['type'].apply(lambda x: x.strip('[]').split(', ') if isinstance(x, str) else x) df_fields = df_fields.explode('type') df_fields['answer'] = df_fields['answer'].apply(lambda x: x.strip('[]').split(', ') if isinstance(x, str) else x) df_fields = df_fields.explode('answer') df_fields.head() df_questions = json_normalize(d['questions'], record_prefix='question_', meta=['name', 'metadata', 'formId', 'id', 'user', '_rid', '_self', '_etag', '_attachments', '_ts']) # Merge all DataFrames final_df = pd.concat([df_top_level, df_questions.drop(columns=['fields']), df_fields.drop(columns = ['question', 'category', 'index'])], axis = 1) final_df.head()
Источник: https://stackoverflow.com/questions/780 ... ave-to-csv
