python - JSON object inside Pandas DataFrame -


i have json object inside pandas dataframe column, want pull apart , put other columns. in dataframe, json object looks string containing array of dictionaries. array can of variable length, including zero, or column can null. i've written code, shown below, want. column names built 2 components, first being keys in dictionaries, , second being substring key value in dictionary.

this code works okay, slow when running on big dataframe. can offer faster (and simpler) way this? also, feel free pick holes in have done if see not sensible/efficient/pythonic. i'm still relative beginner. heaps.

# import libraries  import pandas pd ipython.display import display # used display df's nicely in jupyter notebook. import json  # set display options pd.set_option('max_colwidth',150)  # create example dataframe print("original df:") df = pd.dataframe.from_dict({'cola': {0: 123, 1: 234, 2: 345, 3: 456, 4: 567},\  'colb': {0: '[{"key":"keyvalue=1","vala":"8","valb":"18"},{"key":"keyvalue=2","vala":"9","valb":"19"}]',\   1: '[{"key":"keyvalue=2","vala":"28","valb":"38"},{"key":"keyvalue=3","vala":"29","valb":"39"}]',\   2: '[{"key":"keyvalue=4","vala":"48","valc":"58"}]',\   3: '[]',\   4: none}}) display(df)  # create temporary dataframe append results to, record record dftemp = pd.dataframe()  # step through rows in dataframe in range(df.shape[0]):     # check whether record null, or doesn't contain real data     if pd.notnull(df.iloc[i,df.columns.get_loc("colb")]) , len(df.iloc[i,df.columns.get_loc("colb")]) > 2:         # convert json structure dataframe, 1 cell @ time in relevant column         x = pd.read_json(df.iloc[i,df.columns.get_loc("colb")])         # last bit of string (after last =) used key column labels         x['key'] = x['key'].apply(lambda x: x.split("=")[-1])         # set new key index         y = x.set_index('key')         # stack rows via multi-level column index         y = y.stack().to_frame().t         # flatten out multi-level column index         y.columns = ['{1}_{0}'.format(*c) c in y.columns]         # give single record same index number parent dataframe (for merge work)         y.index = [df.index[i]]         # append dataframe on sequentially each row go through loop         dftemp = dftemp.append(y)  # merge new dataframe onto original 1 columns, index mataching original dataframe df = pd.merge(df,dftemp, how = 'left', left_index = true, right_index = true)  print("processed df:") display(df)

first, general piece of advice pandas. if find iterating on rows of dataframe, doing wrong.

with in mind, can re-write current procedure using pandas 'apply' method (this speed begin with, means far fewer index lookups on df):

# check whether record null, or doesn't contain real data def do_the_thing(row):     if pd.notnull(row) , len(row) > 2:         # convert json structure dataframe, 1 cell @ time in relevant column         x = pd.read_json(row)         # last bit of string (after last =) used key column labels         x['key'] = x['key'].apply(lambda x: x.split("=")[-1])         # set new key index         y = x.set_index('key')         # stack rows via multi-level column index         y = y.stack().to_frame().t         # flatten out multi-level column index         y.columns = ['{1}_{0}'.format(*c) c in y.columns]          #we don't need re-index             # give single record same index number parent dataframe (for merge work)             #y.index = [df.index[i]]         #we don't need add temp df         # append dataframe on sequentially each row go through loop         return y.iloc[0]     else:         return pd.series() df2 = df.merge(df.colb.apply(do_the_thing), how = 'left', left_index = true, right_index = true)

notice returns same result before, haven't changed logic. apply method sorts out indexes, can merge, fine.

i believe answers question in terms of speeding , being bit more idiomatic.

i think should consider however, want data structure, , how might better structure you're doing.

given colb of arbitrary length, end dataframe arbitrary number of columns. when come access these values whatever purpose, cause pain, whatever purpose is.

are entries in colb important? away keeping first one? need know index of vala val?

these questions should ask yourself, decide on structure allow whatever analysis need, without having check bunch of arbitrary things.


Comments

Post a Comment

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m
Read more

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more