Using pandas to merge data and output Json

Question

I have several different CSV files like this:

one: 

iq, name 
69, joe
122, james
...

two:

iq_start, iq_end, category
120,500, Very Superior
120,129,    Superior
110,119,    High Average
90,109, Average
80,89,  Low Average
70,79,  Borderline
0,69,   Extremely Low

three: 
iq, career
69 , manual work
122, doctor

I want to pull the data into pandas , and output the data like this

[
"122" = {
    "name" : "james",
    "career" : "doctor"
    "category" : "Superior"
    },

"69" = {
    "name" : "joe",
    "career" : "manual work"
    "category" : "Extremely Low"
    }


]

Can panda pull this off or get me some of the way?

You could probably even do that without pandas, with the csv and json modules. With pandas should work fine as well, but what have you tried already? — user707650
– user707650, Commented Nov 6, 2013 at 9:23
I know I can do it without Pandas, but I'm wondering if I can do it in Pandas. for example can you do a join like iq between iq_start and iq_end in pandas? — macarthy
– macarthy, Commented Nov 6, 2013 at 9:59
Well, Pandas has a csv import, and Series and Dataframes have a to_json method, so this should definitely be possible. — user707650
– user707650, Commented Nov 6, 2013 at 10:49

HYRY · Accepted Answer · 2013-11-06 10:49:05Z

1

Here is the code, but are you sure there are not two person with the same IQ?

import pandas as pd
import io

one="""iq, name
69, joe
120, james"""

two="""iq_start, iq_end, category
130,500, Very Superior
120,129,    Superior
110,119,    High Average
90,109, Average
80,89,  Low Average
70,79,  Borderline
0,69,   Extremely Low"""

three="""iq, career
69 , manual work
120, doctor"""

df1 = pd.read_csv(io.BytesIO(one), skipinitialspace=True)
df2 = pd.read_csv(io.BytesIO(two), skipinitialspace=True)
df3 = pd.read_csv(io.BytesIO(three), skipinitialspace=True)

iqmap = pd.Series(df2.category.values, index=df2.iq_start).sort_index()

df = pd.merge(df1, df3)
df["category"] = iqmap.asof(df.iq).values
df.set_index("iq", inplace=True)
df.T.to_dict()

output:

{69: {'career': 'manual work', 'category': 'Extremely Low', 'name': 'joe'},
 120: {'career': 'doctor', 'category': 'Superior', 'name': 'james'}}

answered Nov 6, 2013 at 10:49

HYRY

97.8k28 gold badges197 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

macarthy Over a year ago

Thanks, that will get me started. The data is only a sample. The real data the "iq" is unique

Collectives™ on Stack Overflow

Using pandas to merge data and output Json

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related