Pandas Write Variable Number Of New Rows From List In Series

I'm using Pandas as a way to write data from Selenium. Two example results from a search box ac_results on a webpage: #Search for product_id = '01' ac_results = 'Orange (10)' #Sea

Solution 1:

# initializing here for reproducibility
pids = ['01','02']
prices = [10, [u'10', u'20', u'30']]
names = ['Orange','Banana']
df = pd.DataFrame({"product_id": pids, "prices": prices, "item_name": names})

The following snippet should work after your apply(crawl).

# convert all of the prices to lists (even if they only have one element)df.prices = df.prices.apply(lambda x: x if isinstance(x, list) else [x])

# Create a new dataframe which splits the lists into separate columns.# Then flatten using stack. The explicit MultiIndex allows us to keep# the item_name and product_id associated with each price.idx = pd.MultiIndex.from_tuples(zip(*[df['item_name'],df['product_id']]), 
                                names = ['item_name', 'product_id'])
df2 = pd.DataFrame(df.prices.tolist(), index=idx).stack()

# drop the hierarchical index and select columns of interestdf2 = df2.reset_index()[['product_id', 0, 'item_name']]
# rename back to pricesdf2.columns = ['product_id', 'prices', 'item_name']

Solution 2:

I was not able to run your code (probably missing inputs) but you can probably transform your prices list in a list of dict and then build a DataFrame from there:

d = [{"price":10, "product_id":2, "item_name":"banana"}, 
      {"price":20, "product_id":2, "item_name":"banana"}, 
      {"price":10, "product_id":1, "item_name":"orange"}]
df = pd.DataFrame(d)

Then df is:

  item_name  price  product_id
0    banana     10           2
1    banana     20           2
2    orange     10           1