Json2df

json2df is a library batch processing a lists of json data (multiple instances of same structured json data) into Pandas DataFrame

installation

$ pip install json2df

usage example

https://github.com/shichaoji/json2df

e.g. when you scrape some users info data from a website, usually some fields contains json data format

import pandas as pd
df = pd.read_csv('https://raw.githubusercontent.com/shichaoji/json2df/master/sample.csv')
df.shape

(100, 5)

df.head(3)

	mock_id	location	primary_currency	status	timezone
0	0	{u'administrative_area': None, u'city': u'Sing...	{u'code': u'USD', u'name': u'US Dollar', u'cou...	{u'payment_verified': False, u'identity_verifi...	{u'country': u'SG', u'offset': 8, u'id': 210, ...
1	1	{u'administrative_area': None, u'city': u'Bris...	{u'code': u'USD', u'name': u'US Dollar', u'cou...	{u'payment_verified': False, u'identity_verifi...	{u'country': u'UK', u'offset': 1, u'id': 262, ...
2	2	{u'administrative_area': None, u'city': u'Ambo...	{u'code': u'USD', u'name': u'US Dollar', u'cou...	{u'payment_verified': False, u'identity_verifi...	{u'country': u'MG', u'offset': 3, u'id': 295, ...

e.g. we want to extract and expend all columns from json/dict format to key-value DataFrame ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

use json2df to convert all columns into a DataFrame, the example exlude col "mock_id" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

from json2df import df2df

df_new = df2df(df, split_sign='||', exclude_cols=['mock_id'])
df_new.head(3)

(100, 39)

.dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; }

	location||country||highres_flag_url	location||country||code	location||country||name	location||country||seo_url	location||country||flag_url_cdn	location||country||highres_flag_url_cdn	location||country||phone_code	location||country||language_code	location||country||demonym	location||country||language_id	...	status||identity_verified	status||email_verified	status||deposit_made	status||phone_verified	status||facebook_connected	status||profile_complete	timezone||country	timezone||offset	timezone||id	timezone||timezone
0	/img/flags/highres_png/singapore.png	sg	Singapore	None	//cdn2.f-cdn.com/img/flags/png/sg.png	//cdn6.f-cdn.com/img/flags/highres_png/singapo...	None	None	None	None	...	False	True	False	False	False	False	SG	8.0	210	Asia/Singapore
1	/img/flags/highres_png/united-kingdom.png	gb	United Kingdom	None	//cdn6.f-cdn.com/img/flags/png/gb.png	//cdn5.f-cdn.com/img/flags/highres_png/united-...	None	None	None	None	...	False	True	True	False	False	True	UK	1.0	262	Europe/London
2	/img/flags/highres_png/madagascar.png	mg	Madagascar	None	//cdn3.f-cdn.com/img/flags/png/mg.png	//cdn3.f-cdn.com/img/flags/highres_png/madagas...	None	None	None	None	...	False	True	False	False	False	True	MG	3.0	295	Indian/Antananarivo

3 rows × 39 columns

df_new.head(3).transpose()

.dataframe tbody tr th:only-of-type { vertical-align: middle; } .dataframe tbody tr th { vertical-align: top; } .dataframe thead th { text-align: right; }

	0	1	2
location||country||highres_flag_url	/img/flags/highres_png/singapore.png	/img/flags/highres_png/united-kingdom.png	/img/flags/highres_png/madagascar.png
location||country||code	sg	gb	mg
location||country||name	Singapore	United Kingdom	Madagascar
location||country||seo_url	None	None	None
location||country||flag_url_cdn	//cdn2.f-cdn.com/img/flags/png/sg.png	//cdn6.f-cdn.com/img/flags/png/gb.png	//cdn3.f-cdn.com/img/flags/png/mg.png
location||country||highres_flag_url_cdn	//cdn6.f-cdn.com/img/flags/highres_png/singapo...	//cdn5.f-cdn.com/img/flags/highres_png/united-...	//cdn3.f-cdn.com/img/flags/highres_png/madagas...
location||country||phone_code	None	None	None
location||country||language_code	None	None	None
location||country||demonym	None	None	None
location||country||language_id	None	None	None
location||country||person	None	None	None
location||country||iso3	None	None	None
location||country||sanction	None	None	None
location||country||flag_url	/img/flags/png/sg.png	/img/flags/png/gb.png	/img/flags/png/mg.png
location||country||flag_class	singapore	united-kingdom	madagascar
location||country||region_id	None	None	None
location||administrative_area	None	None	None
location||city	Singapore	Bristol	Ambohidratrimo
location||vicinity	None	None	None
location||longitude	None	None	None
location||full_address	None	None	None
location||latitude	None	None	None
primary_currency||code	USD	USD	USD
primary_currency||name	US Dollar	US Dollar	US Dollar
primary_currency||country	US	US	US
primary_currency||sign	$	$	$
primary_currency||exchange_rate	1.0	1.0	1.0
primary_currency||id	1	1	1
status||payment_verified	False	False	False
status||identity_verified	False	False	False
status||email_verified	True	True	True
status||deposit_made	False	True	False
status||phone_verified	False	False	False
status||facebook_connected	False	False	False
status||profile_complete	False	True	True
timezone||country	SG	UK	MG
timezone||offset	8.0	1.0	3.0
timezone||id	210	262	295
timezone||timezone	Asia/Singapore	Europe/London	Indian/Antananarivo