0
$\begingroup$

I have the following json data file which I have converted to pandas dataframe. The columns are as follows

Index(['id', 'title', 'abstract', 'content', 'metadata'], dtype='object')

I am particularly interested in the column 'metadata' an element of the column looks like

df_json.loc[78, 'metadata']
"{'classification': {'name': 'Manufacturing, Transport & Logistics'}, 'subClassification': {'name': 'Warehousing, Storage & Distribution'}, 'area': {'name': 'Southern Suburbs & Logan'}, 'location': {'name': 'Brisbane'}, 'suburb': {'name': 'Milton'}, 'workType': {'name': 'Casual/Vacation'}}"

So I want to make columns extracting the information out of 'metadata' columns for example location. I am not sure how to extract it and put it beside the same json file with added columns such as location etc.

    id  title   abstract    content metadata    clean_content
0   38915469    Recruitment Consultant  We are looking for someone to focus purely on ...   <HTML><p>Are you looking to join a thriving bu...   {'standout': {'bullet1': 'Join a Sector that i...   Are you looking to join a thriving business th...
1   38934839    Computers Salesperson - Coburg  Passionate about exceptional customer service?...   <HTML><p>&middot;&nbsp;&nbsp;Casual hours as r...   {'additionalSalaryText': 'Attractive Commissio...   middotnbspnbspCasual hours as required transit...
2   38946054    Senior Developer | SA   Readifarians are known for discovering the lat...   <HTML><p>Readify helps organizations
Improve this question
$\endgroup$

1 Answer 1

Reset to default
0
$\begingroup$

If the column contains actual dictionaries you can simply use the .str accessor to access the data in the dictionary by simply using the keys. For example, if you want to get the location from the dictionary you would use df_json["metadata"].str["area"].str["name"] to get the value 'Southern Suburbs & Logan'.

Improve this answer
$\endgroup$
2
  • $\begingroup$ I want to collect all the text in each row when I use your command say "df_json['text]= df_json['content] + df_json["metadata"].str["area"].str["name"] " df_json['text][0] returns nan. Also is it make a dataframe out of the dictionary metadata and put in on the same with title location etc... and put in the same dataframe $\endgroup$
    – GGT
    Dec 30, 2021 at 22:50
  • $\begingroup$ If you run into issues please add some sample data in your OP with the issue you're having. Using the code provided simply returns a pandas.Series object with the values, which you can either add as a column to the existing dataframe with the rest of the values or use in a new dataframe. $\endgroup$
    – Oxbowerce
    Dec 31, 2021 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.