How to move for loop away and go pure Pandas?

Question

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']))

    try:
        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,:]
        else:
            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
    except:
        stock_cost_price = None
        stock_cost_real_price = None




    products.append({
        '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 :)

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.