python - Sum amount associated with item in a column of lists -


i have dataframe follows:

country   letter             keywords                 amount    c         y     ['fruits', 'apples', "banana"]       700    c         y     ["music", "dance", "banana"]         150    c         y     ['loud', "dance", "apples"]          350

i'd sum amount associated each keyword. note: country , letters not same, in contrived data above. also, list of keywords vary in size.

i've tried several solutions. i've attached fastest 1 below. i've tried solutions apply , defaultdicts...

keywords_list = [] in zip(*[df[c] c in df.columns]):     data = list(i[0:2])     k in i[2]:         row = [k] + data + [i[-1]]         keywords_list.append(row)  df_expanded = pd.dataframe(keywords_list) df_expanded.groupby(list(range(3)))[3].sum().reset_index()

goal

  country letter keywords  amount 0       c      y   apples    1050 1       c      y   banana     850 2       c      y    dance     500 3       c      y   fruits     700 4       c      y     loud     350 5       c      y    music     150

edit: corrected error in example of goal

data

country = list("ccc") letters = list("yyy") keywords = [['fruits', 'apples', "banana"], ["music", "dance", "banana"], ['loud', "dance", "apples"]] amount = [700, 150, 350]  df = pd.dataframe({"country" : country, "keywords": keywords, "letter" : letters, "amount" : amount}) df = df[['country', 'letter', 'keywords', 'amount']]

you can use:

df1 = pd.dataframe(df.keywords.values.tolist())        .stack()        .reset_index(level=1, drop=true)        .rename('keywords') print (df1) 0    fruits 0    apples 0    banana 1     music 1     dance 1    banana 2      loud 2     dance 2    apples name: keywords, dtype: object  print (df.drop('keywords', axis=1).join(df1).reset_index(drop=true))   country letter  amount keywords 0       c      y     700   fruits 1       c      y     700   apples 2       c      y     700   banana 3       c      y     150    music 4       c      y     150    dance 5       c      y     150   banana 6       c      y     350     loud 7       c      y     350    dance 8       c      y     350   apples

another solution:

df = df.set_index(['country','letter','amount']) df1 = pd.dataframe(df.keywords.values.tolist(), index = df.index) \         .stack() \         .reset_index(name='keywords') \         .drop('level_3',axis=1) print (df1)   country letter  amount keywords 0       c      y     700   fruits 1       c      y     700   apples 2       c      y     700   banana 3       c      y     150    music 4       c      y     150    dance 5       c      y     150   banana 6       c      y     350     loud 7       c      y     350    dance 8       c      y     350   apples

then need groupby aggregating sum:

print (df.groupby(['country','letter','keywords'], as_index=false)['amount'].sum())   country letter keywords  amount 0       c      y   apples    1050 1       c      y   banana     850 2       c      y    dance     500 3       c      y   fruits     700 4       c      y     loud     350 5       c      y    music     150

timings:

in [47]: %timeit (df.set_index(['country','letter','amount']).keywords.apply(pd.series).stack().reset_index().drop('level_3',1)) 1 loop, best of 3: 4.55 s per loop  in [48]: %timeit (jez1(df3)) 10 loops, best of 3: 24.8 ms per loop  in [49]: %timeit (jez2(df3)) 10 loops, best of 3: 29.7 ms per loop

code timings:

df = pd.concat([df]*10000).reset_index(drop=true) df3 = df.copy() df4 = df.copy()                  def jez1(df):     df1 = pd.dataframe(df.keywords.values.tolist()).stack().reset_index(level=1, drop=true).rename('keywords')     return df.drop('keywords', axis=1).join(df1).reset_index(drop=true)  def jez2(df):     df = df.set_index(['country','letter','amount'])     df1 = pd.dataframe(df.keywords.values.tolist(), index = df.index).stack().reset_index(name='keywords').drop('level_3',axis=1)     return df1

thank maxu improvement pop - drop not necessary. unfortunately timing failed (keyerror: 'keywords'), cannot compare it.


Comments

Post a Comment

Popular posts from this blog

asynchronous - C# WinSCP .NET assembly: How to upload multiple files asynchronously -

i using winscp .net assembly uploading files. want upload multiple files asynchronous way. have created method works single upload. public class uploadingdata { private sessionoptions _sessionoptions; private session _session; //connection etc private void onconnected() { foreach (var fileinfo in localfileslist) { var task = task.factory.startnew(() => uploadfilesasync(fileinfo)); } } private async task uploadfilesasync(string file) { string remotefilepath = _session.translatelocalpathtoremote(file, @"/", "/test_data_upload"); var uploading = _session.putfiles(file, remotefilepath, false); //when done await task.run(() => thread.sleep(1000)); } } please suggest me correct way. thanks you can use backgroundworker class this: string[] images = new string[50]; foreach (var path in images) { backgroundworker
Read more

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more