Python按绑定到字符串的整数对分组中的数据帧列进行排序

Question

我有一个具有以下列和值的数据框：

product 1_vendor    2_vendor    3_vendor    price_shop1 price_shop2 price_shop3 url_shop1   url_shop2   url_shop3
blue    shop1       shop3       shop2       500         600         550         1.com/blue  2.com/blue  3.com/blue
pink    shop3       shop2       shop1       700         650         600         1.com/pink  2.com/pink  3.com/pink
cyan    shop1       shop2       shop3       0           200         300         1.com/cyan  2.com/cyan  3.com/cyan

“1_供应商”是最便宜供应商的名称，“3_供应商”是最昂贵供应商的名称

从这些信息中，我想以以下列结束：产品、1个供应商、1个价格、1个url、2个供应商、2个价格、2个url等等，按1是最便宜的和3是最贵的顺序排列。像这样：

product 1_vendor 1_price 1_url      2_vendor 2_price 2_url
blue    shop3    555     3.com/blue shop1    700     1.com/blue

我想我可以使用.replace for each column将“shop”字符串更改为price和url，但是下面的代码给出了一个错误

df['1_url'] = df['1_vendor'].replace('shop1', df['url_shop1'])
df['1_url'] = df['1_vendor'].replace('shop2', df['url_shop2'])

ValueError: Series.replace cannot use dict-value and non-None to_replace

如果我在它前面加上str（df['url\u shop1']），它会运行，但会用整个列的值填充单元格

如何以这种方式对数据帧进行排序？我将最终导出到CSV

Answer 1

我希望我正确理解了你的问题。
我有点醉了，所以可能会有错误，强制家庭办公的几周比预期的要难xD。

无论如何，有一个解决方案：

# Import pandasand numpy
import pandas as pd
import numpy as np

# Sample df
product = ['blue', 'pink', 'cyan']
v1_vendor = ['shop1', 'shop3', 'shop1']
v2_vendor = ['shop3', 'shop2', 'shop2']
v3_vendor = ['shop2', 'shop1', 'shop3']
price_shop1 = [500, 700, 0]
price_shop2 = [600, 650, 200]
price_shop3 = [550, 600, 300]
url_shop1 = ['1.com/blue', '1.com/pink', '1.com/cyan']
url_shop2 = ['2.com/blue', '2.com/pink', '2.com/cyan']
url_shop3 = ['3.com/blue', '3.com/pink', '3.com/cyan']

df = pd.DataFrame({'product':product, '1_vendor' : v1_vendor, '2_vendor' : v2_vendor, '3_vendor' : v3_vendor, 'price_shop1' : price_shop1, 'price_shop2' : price_shop2, 'price_shop3' : price_shop3,'url_shop1' : url_shop1,'url_shop2' : url_shop2,'url_shop3' : url_shop3})

# Create second dataframe that we will fill with final data
df_f = pd.DataFrame({'product':product})
df_f['1_vendor'] = np.nan
df_f['1_price'] = np.nan
df_f['1_url'] = np.nan
df_f['2_vendor'] = np.nan
df_f['2_price'] = np.nan
df_f['2_url'] = np.nan
df_f['3_vendor'] = np.nan
df_f['3_price'] = np.nan
df_f['3_url'] = np.nan

现在我们可以使用simple for函数循环原始df并提取结果。

# For loop to fill in the final dataframe
for i in list(df.index.values):
    df_f.loc[i, '1_vendor'] = df.loc[i,'1_vendor']
    df_f.loc[i, '2_vendor'] = df.loc[i,'2_vendor']
    df_f.loc[i, '3_vendor'] = df.loc[i,'3_vendor']
    df_f.loc[i, '1_price'] = df.loc[i, 'price_'+df_f.loc[i,'1_vendor']]
    df_f.loc[i, '2_price'] = df.loc[i, 'price_'+df_f.loc[i,'2_vendor']]
    df_f.loc[i, '3_price'] = df.loc[i, 'price_'+df_f.loc[i,'3_vendor']]
    df_f.loc[i, '1_url'] = df.loc[i, 'url_'+df_f.loc[i,'1_vendor']]
    df_f.loc[i, '2_url'] = df.loc[i, 'url_'+df_f.loc[i,'2_vendor']]
    df_f.loc[i, '3_url'] = df.loc[i, 'url_'+df_f.loc[i,'3_vendor']]

编辑：对于导出，只需使用to_csv命令，如果有问题，请告诉我

好的，应该是这样。
如果我没有收到问题或您有任何问题，请告诉我。
祝你好运&书信电报；br/

（如果答案正确，请标记，谢谢）