python - Pandas 从系列列表中写入可变数量的新行

Question

我正在使用 Pandas 作为从 Selenium 写入数据的方式。

网页上搜索框 ac_results 的两个示例结果:

#Search for product_id = "01"
ac_results = "Orange (10)"

#Search for product_id = "02"
ac_result = ["Banana (10)", "Banana (20)", "Banana (30)"]

Orange 仅返回一个价格(10 美元)，而 Banana 返回来自不同供应商的可变数量的价格，在本例中为三个价格(10 美元)、20 美元、30 美元。

该代码通过 re.findall 使用正则表达式获取每个价格并将它们放入列表中。只要 re.findall 只找到一个列表项，代码就可以正常工作，就像 Oranges 一样。问题是当价格数量可变时，例如在搜索 Bananas 时。我想为每个规定的价格创建一个新行，这些行还应包括 product_id 和 item_name。

当前输出:

product_id      prices                  item_name
01              10                      Orange
02              [u'10', u'20', u'30']   Banana

期望的输出:

product_id      prices                  item_name
01              10                      Orange
02              10                      Banana
02              20                      Banana
02              30                      Banana

当前代码:

df = pd.read_csv("product_id.csv")
def crawl(product_id):
    #Enter search input here, omitted
    #Getting results:
    search_result = driver.find_element_by_class_name("ac_results")
    item_name = re.match("^.*(?=(\())", search_result.text).group().encode("utf-8")
    prices = re.findall("((?<=\()[0-9]*)", search_reply.text)
    return pd.Series([prices, item_name])

df[["prices", "item_name"]] = df["product_id"].apply(crawl)
df.to_csv("write.csv", index=False)

仅供引用:使用 csv 模块的可行解决方案，但我想使用 Pandas。

with open("write.csv", "a") as data_write:
    wr_data = csv.writer(data_write, delimiter = ",")
    for price in prices: #<-- This is the important part!
        wr_insref.writerow([product_id, price, item_name])

Answer 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})

以下代码段应该在您的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 interest
df2 = df2.reset_index()[['product_id', 0, 'item_name']]
# rename back to prices
df2.columns = ['product_id', 'prices', 'item_name']