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