Python PANDAS get averages across multiple slices of data -


consider following dataset:

import pandas pd  data = {"store_name":{"0":"storename","1":"storename","2":"storename","3":"storename","4":"storename",                       "5":"storename","6":"storename","7":"storename","8":"storename","9":"storename",                       "10":"storename","11":"storename","12":"storename","13":"storename","14":"storename",                       "15":"storename","16":"storename","17":"storename","18":"storename","19":"storename",                       "20":"storename","21":"storename","22":"storename","23":"storename","24":"storename",                       "25":"storename","26":"storename","27":"storename","28":"storename","29":"storename",                       "30":"storename","31":"storename","32":"storename","33":"storename","34":"storename",                       "35":"storename","36":"storename","37":"storename","38":"storename","39":"storename",                       "40":"storename","41":"storename","42":"storename","43":"storename","44":"storename",                       "45":"storename","46":"storename","47":"storename","48":"storename","49":"storename"},         "category":{"0":"facial care","1":"food","2":"food","3":"food","4":"food","5":"food",                     "6":"soap & bath","7":"soap & bath","8":"facial care","9":"condiments & sauces",                     "10":"yoga & home fitness","11":"yoga & home fitness","12":"honey & sweeteners",                     "13":"honey & sweeteners","14":"honey & sweeteners","15":"honey & sweeteners",                     "16":"honey & sweeteners","17":"honey & sweeteners","18":"honey & sweeteners",                     "19":"honey & sweeteners","20":"honey & sweeteners","21":"honey & sweeteners",                     "22":"candies desserts & toppings","23":"honey & sweeteners","24":"honey & sweeteners",                     "25":"honey & sweeteners","26":"dog","27":"dog","28":"cat","29":"cat",                     "30":"cooking & meal ingredients","31":"snacks","32":"snacks","33":"cooking & meal ingredients",                     "34":"cooking & meal ingredients","35":"cooking & meal ingredients","36":"bars, cereals & granolas",                     "37":"bars, cereals & granolas","38":"candies desserts & toppings","39":"cooking & meal ingredients",                     "40":"cooking & meal ingredients","41":"snacks","42":"snacks","43":"cooking & meal ingredients",                     "44":"cooking & meal ingredients","45":"cooking & meal ingredients","46":"cooking & meal ingredients",                     "47":"snacks","48":"cooking & meal ingredients","49":"sun & bug"},         "brand":{"0":"brand1","1":"brand2","2":"brand2","3":"brand2","4":"brand2","5":"brand2",                  "6":"brand3","7":"brand3","8":"brand3","9":"brand4","10":"brand5","11":"brand5",                  "12":"ƒbrand6","13":"brand6","14":"brand6","15":"brand6","16":"brand6","17":"brand6",                  "18":"brand6","19":"brand6","20":"brand6","21":"brand6","22":"brand6","23":"brand6",                  "24":"brand6","25":"brand6","26":"zuke\'s","27":"zuke\'s","28":"zuke\'s","29":"zuke\'s",                  "30":"brand8","31":"brand8","32":"brand8","33":"brand8","34":"brand8","35":"brand8",                  "36":"brand8","37":"brand8","38":"brand8","39":"brand8","40":"brand8","41":"brand8",                  "42":"brand8","43":"brand8","44":"brand8","45":"brand8","46":"brand8","47":"brand8",                  "48":"brand8","49":"brand7"},         "store_price":{"0":4.49,"1":14.45,"2":13.49,"3":14.29,"4":13.99,"5":13.99,"6":2.65,"7":3.45,                        "8":3.95,"9":3.75,"10":3.65,"11":6.95,"12":10.75,"13":10.75,"14":4.65,                        "15":5.69,"16":3.95,"17":6.45,"18":3.45,"19":4.95,"20":4.45,"21":4.45,                        "22":3.79,"23":4.95,"24":7.45,"25":7.49,"26":4.99,"27":4.99,"28":2.29,                        "29":2.95,"30":1.89,"31":3.25,"32":3.25,"33":2.99,"34":2.99,"35":2.99,                        "36":5.25,"37":5.25,"38":3.25,"39":2.25,"40":2.89,"41":4.25,"42":4.25,                        "43":2.25,"44":2.05,"45":1.89,"46":2.49,"47":4.25,"48":2.49,"49":4.95},          "comp1":{"0": none,"1":15.9,"2":13.7,"3":15.9,"4":14.59,"5":13.99,"6": none,"7": none,"8": none,"9": none,                   "10":10.0,"11":20.0,"12":19.69,"13":20.8,"14": none,"15": none,"16": none,"17":6.18,"18": none,"19": none,                   "20": none,"21": none,"22":5.99,"23": none,"24": none,"25":18.99,"26":6.39,"27":6.39,"28": none,"29": none,                   "30": none,"31": none,"32": none,"33": none,"34": none,"35": none,"36": none,"37": none,"38": none,"39": none,"40": none,                   "41": none,"42": none,"43": none,"44": none,"45": none,"46": none,"47": none,"48": none,"49":7.19},          "comp5":{"0":6.72,"1": none,"2": none,"3": none,"4": none,"5": none,"6": none,"7": none,"8":5.79,"9": none,                   "10": none,"11": none,"12":10.55,"13":11.4,"14": none,"15":8.27,"16":5.01,"17": none,"18": none,                   "19":4.71,"20": none,"21":5.49,"22": none,"23": none,"24": none,"25": none,"26": none,"27":6.46,                   "28": none,"29": none,"30": none,"31":3.94,"32": none,"33": none,"34": none,"35": none,"36":5.0,"37":5.0,                   "38": none,"39": none,"40":4.31,"41":4.7,"42": none,"43": none,"44": none,"45": none,"46": none,"47":4.64,                   "48": none,"49": none},          "comp4":{"0":4.49,"1": none,"2": none,"3": none,"4": none,"5": none,"6":3.09,"7": none,"8":4.39,"9":4.59,                   "10": none,"11": none,"12":10.79,"13":11.09,"14":5.13,"15":6.89,"16":4.39,"17":6.67,"18":3.59,                   "19":5.21,"20":4.29,"21":4.99,"22":3.89,"23":5.39,"24": none,"25": none,"26":6.09,"27":6.09,                   "28":2.89,"29": none,"30":2.19,"31":3.79,"32":3.32,"33":3.39,"34":3.39,"35":3.39,"36":6.29,                   "37":6.29,"38":3.99,"39":2.89,"40":3.59,"41":4.99,"42":4.69,"43":2.89,"44":2.59,"45":2.19,                   "46":2.99,"47":4.99,"48":2.99,"49": none},          "comp2":{"0":4.77,"1":13.66,"2": none,"3":11.38,"4":14.59,"5": none,"6": none,"7":4.99,"8":4.99,"9": none,"10": none,                   "11": none,"12":9.845,"13":13.12,"14": none,"15":11.23,"16":4.67,"17": none,"18":3.82,"19":3.88,"20":3.48,                   "21":5.7,"22": none, "23":7.69,"24":8.18,"25": none,"26":8.99,"27": none,"28":1.95,"29":4.87,"30":1.72,                   "31":2.69,"32":2.82,"33": none,"34": none,"35":2.43,"36":3.98,"37":3.98,"38":3.28,"39": none,"40": none,                   "41":4.39,"42":3.99,"43":1.97,"44": none,"45":1.72,"46":2.49,"47": none,"48":2.48,"49":6.39},          "comp3":{"0":6.6833333333,"1": none,"2": none,"3": none,"4": none,"5": none,"6":3.79,"7":4.12,"8":5.46,                   "9": none,"10": none,"11": none,"12":13.57,"13":14.1233333333,"14":4.52,"15":10.035,"16":5.2066666667,                   "17":6.23,"18":4.095,"19":6.4733333333,"20":5.3866666667,"21":5.025,"22":4.41,"23":7.525,                   "24":8.38,"25": none,"26":7.42,"27":6.85,"28":3.21,"29": none,"30":2.3733333333,"31": none,"32":4.315,                   "33": none,"34": none,"35": none,"36": none,"37": none,"38":4.7133333333,"39":3.495,"40":3.8833333333,                   "41":5.73,"42":5.73,"43":3.495,"44": none,"45":2.575,"46":3.495,"47":5.79,"48":3.47,"49":8.21}}  df = pd.dataframe(data)

for each brand , each comp[n], i'm looking average store price on comp[n] price there price both comp[n] , store price. had tried like: 

brand = df.groupby('brand')['store_price','comp1', 'comp2', 'comp3', 'comp4', 'comp5'].mean()  comp in ['comp1', 'comp2', 'comp3', 'comp4', 'comp5']:     brand[comp] = 1.0 * (brand['store_price']/brand[comp])

this didn't work because each comp[n]'s average being compared average across entire brand storename. strictly needs dollar dollar ratio of items have price both comp[n] , storename reflected store_price.

i thinking might have run like:

for in df.index:     comp in ['comp1', 'comp2', 'comp3', 'comp4', 'comp5']:         df.loc[i, comp] = 1.0*(df.loc[i, 'store_price']/df.loc[i,comp])   brand = df.groupby('brand')['comp1', 'comp2', 'comp3', 'comp4', 'comp5'].mean().reset_index()

however, feel there has more intelligent way slice data, run computations, , paste slices on dataframe.

to avoid nested loops, consider update across columns anonymous lambda function in apply. 

df.update(df[['comp1', 'comp2', 'comp3', 'comp4', 'comp5']].\      apply(lambda col: df['store_price'] / col, axis=0))  brand = df.groupby('brand')['comp1', 'comp2', 'comp3', 'comp4', 'comp5'].mean().reset_index()

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