I'm working with huge data sheet, and start learning Pandas, but i hit this challenge i have a loop and trying to move everything from my loop into Pandas but i not all i can find a way around.

panda_dataframe = pd.read_sql(sql=sql, con=mysql_cnx, index_col='UUID')

logging.debug('__setupProducts() - after mysql query : run time {time}'.format(time=datetime.datetime.now() - start_time))
logging.debug('__setupProducts() - Product found to handle: {count}'.format(count=len(panda_dataframe)))

panda_dataframe['Price'] = panda_dataframe['Price'].apply(lambda x:float(x/100))
panda_dataframe['PriceNext'] = panda_dataframe['PriceNext'].apply(lambda x:float(x/100))
panda_dataframe['CostPrice'] = panda_dataframe['CostPrice'].apply(lambda x:float(x/100))
panda_dataframe['CostPriceReal'] = panda_dataframe['CostPriceReal'].apply(lambda x:float(x/100))
panda_dataframe['InStoreStock'] = panda_dataframe['InStoreStock'].apply(lambda x:int(x))

logging.debug('__setupProducts() - restructer dataframe to right types : run time {time}'.format(time=datetime.datetime.now() - start_time))

for product_uuid, product in panda_dataframe.iterrows():
    logging.info('Product: {title} loading and prepare...'.format(title=product['Title']))

        product_data = store_remote_stock_dataframe.get_group(product_uuid)

        product_data_onstock = product_data.loc[product_data['Stock'] > 0, ['Stock', 'CostPriceReal', 'CostPrice', 'Expected', 'DistributorUUID', 'Country']]
        product_data_outstock = product_data.loc[product_data['Stock'] <= 0, ['Stock', 'CostPriceReal', 'CostPrice', 'Expected', 'DistributorUUID', 'Country']]
        product_data = None

        if len(product_data_onstock) > 0:
            stock_cost_price = product_data_onstock.sort_values(by=['CostPrice'], ascending=True).iloc[0,:]
            stock_cost_real_price = product_data_onstock.sort_values(by=['CostPriceReal'], ascending=True).iloc[0,:]
        elif len(product_data_outstock) > 0:
            stock_cost_price = product_data_outstock.sort_values(by=['CostPrice'], ascending=True).iloc[0,:]
            stock_cost_real_price = product_data_outstock.sort_values(by=['CostPriceReal'], ascending=True).iloc[0,:]
            stock_cost_price = None
            stock_cost_real_price = None

        stock_cost_price = stock_cost_price.drop(['CostPriceReal','CostPrice']) if stock_cost_price is not None else None
        stock_cost_real_price = stock_cost_real_price.drop(['CostPriceReal','CostPrice']) if stock_cost_real_price is not None else None
        stock_cost_price = None
        stock_cost_real_price = None

        'uuid' : product_uuid,
        'title' : product['Title'],
        'price' : product['Price'],
        'price-next' : product['PriceNext'],
        'price-cost' : product['CostPrice'],
        'price-cost-real' : product['CostPriceReal'],
        'overwrites' : product['Overwrites'],
        'distributor-stock' : {
            'cost-price' : {
                'distributor' : stock_cost_price['DistributorUUID'] if stock_cost_price is not None else None,
                'stock' : stock_cost_price['Stock'] if stock_cost_price is not None else 0,
                'expected' : stock_cost_price['Expected']  if stock_cost_price is not None else -1,
                'country' : stock_cost_price['Country']  if stock_cost_price is not None else None,
            'cost-price-real' : {
                'distributor' : stock_cost_real_price['DistributorUUID'] if stock_cost_real_price is not None else None,
                'stock' : stock_cost_real_price['Stock'] if stock_cost_real_price is not None else 0,
                'expected' : stock_cost_real_price['Expected'] if stock_cost_real_price is not None else -1,
                'country' : stock_cost_real_price['Country']  if stock_cost_real_price is not None else None
        'stock' : {
            'store' : int(product['InStoreStock']) if product['InStoreStock'] is not None else 0,
        'manufacturer' : manufacturers[product['ManufacturerUUID']]['_id'] if product['ManufacturerUUID'] in manufacturers else None,
        'category' : categorys[product['CategoryUUID']]['_id'] if product['CategoryUUID'] in categorys else None,

what i wich is move my "try" code shut be before my for loop becures then i can remove the for loop and continue whitout the for loop at all.

Hope sombody can help me to be better to Pandas so i can use the panda power :)


1 Answer 1


I always avoid for loops when operating on pandas rows - it’s slow and inefficient. If possible, try to creat some functions (def func(x): ...) and then apply these functions to the rows of the dataframe: df[‘col1’].apply(func) which will vectorise the operation of applying func to each row, making it much faster and more efficient.

  • $\begingroup$ Thanks, yes i realy try to go away from the for loops, and i try to restrutrer my data go from 1min => 0.5sec now for 10.000 rows, its better but i need a still to fix my try statement, becures i return 2 variables and return multi-collems and i'm not sure i can put the output to 1 colum in panda dataframe? $\endgroup$ Mar 15, 2018 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.