converting json to csv using python pandas

Question

I have referred this: Nested Json to pandas DataFrame with specific format

and this: json_normalize produces confusing KeyError

to try and normalize my json snippet using json_normalize in pandas. However, the output isn't getting normalized fully. Here's a snippet of my code

x =[{'fb_metrics': [{'period': 'lifetime', 'values': [{'value': {'share': 2, 'like': 10}}], 'title': 'Lifetime Post Stories by action type', 'name': 'post_stories_by_action_type', '_id': '222530618111374_403476513350116/insights/post_stories_by_action_type/lifetime', 'description': 'Lifetime: The number of stories created about your Page post, by action type. (Total Count)'}]}]

df = pd.io.json.json_normalize(x[0]['fb_metrics'])

The output for values column is

values
[{'value': {'share': 2, 'like': 10}}] 

I would've liked to have two column outputs instead like

value.share   value.like
2                10

How should I achieve this?

Answer 1

You might apply json_normalize to the values column one more time to flatten it:

pd.concat([
    df.drop('values', 1), 
    df['values'].apply(lambda x: pd.io.json.json_normalize(x).iloc[0])
], axis=1)

Answer 2

For your dataframe,

You can create a new DataFrame from the nested dictionary within values using df.from_dcit() do:

df2 = pd.DataFrame.from_dict(df['values'].values[0][0], orient = 'index').reset_index().drop(['index'], axis=1)

to get:

df2:

   share  like
0      2    10

Then add this to your existing dataframe to get the format you need using pd.concat:

result = pd.concat([df, df2], axis=1, join='inner')

result[['values', 'share', 'like']]
Out[74]: 
                                     values  share  like
0  [{u'value': {u'share': 2, u'like': 10}}]      2    10

If needed can rename:

result.rename(columns={'share': 'values.share', 'like':'values.like'}, inplace=True)

result[['values', 'share', 'like']]
Out[74]: 
                                     values  values.share  values.like
0  [{u'value': {u'share': 2, u'like': 10}}]             2           10

Answer 3

import pandas as pd
df = pd.read_json('data.json')
df.to_csv('data.csv', index=False, columns=['title', 'subtitle', 'date', 
'description'])

import pandas as pd
df = pd.read_csv("data.csv")
df = df[df.columns[:4]]
df.dropna(how='all')
df.to_json('data.json', orient='records')