我正在為一個正在創建航線的項目完善代碼。我目前擁有的是由 c_match 的索引值組合在一起的數據幀。很酷，很棒，乍一看一切看起來都是正確的。航線是一組具有相同折扣和最低費用的州。我的代碼返回具有相同折扣的州。大多數具有相同折扣的州也具有相同的最低費用。然而，異常值是具有相同折扣和不同最低費用的州。目標：創建具有相同最低費用和相同折扣百分比的航線。我的想法：創建一個邏輯運算，將具有相同費率和成本的州名稱連接起來，并返回其費率和成本。仍然需要考慮到相同費率下具有不同成本的州。期望的輸出：Shipping Lane Rate Cost20_21_RDWY_Purple_AL_AR_KY_LA_MS_SC_TN_PE 50.80% 12020_21_RDWY_Purple_AZ 50.80% 15520_21_RDWY_Purple_CA 62.40% 14520_21_RDWY_Purple_CO_ND_WY_MB_NF_PQ 62.40% 15520_21_RDWY_Purple_CT_DE_MN_NE 50.00% 14520_21_RDWY_Purple_DC_IA_KS_MD_MI_OH_OK_WI 49.00% 12520_21_RDWY_Purple_FL 48.30% 125當前代碼def remove_dups(input, output): input.sort() n_list = list(input for input, _ in itertools.groupby(input)) output.append(n_list)def get_matches_discount(state): state_groups = [] state_rates = [] state_cost = [] final_format = [] match = [] c_match = [] for i, x in enumerate(df_d[state]): #checks within the column for identical values then maps where the identical values are match1 = [j for j, y in enumerate(df_d[state].isin([x])) if y is True] match.append(match1) remove_dups(match, c_match) for list in c_match: for elements in list: r = elements[0] state_g = df_d.index[elements] state_groups.append(state_g) state_r = df_d[state][r] state_rates.append(state_r) print(state_rates) match_cost = df_m[state][r] state_cost.append(match_cost) for i in state_groups: delimiter = "_" join_str = delimiter.join(i) j_str = "20_21_RDWY_Purple_" + join_str final_format.append(j_str) master_frame = pd.DataFrame( {'Shipping Lane': final_format, 'Rate': state_rates, 'Cost': state_cost, } ) print(master_frame) return master_frame輸入示例：最低收費表
2 回答
慕雪6442864
TA貢獻1812條經驗 獲得超5個贊
您有多個狀態行，但它們也位于列上?？磥砟皇秋@示了該AL列的示例輸出？您可以在State和 然后.groupby Rate和上合并兩個數據框Cost。.apply(lambda x: '_'.join(x))然后，返回具有相同費率和成本的州的連接字符串（帶有）（因為您按它們分組，所以它們將具有相同的費率和成本）：
master_dataframe0 = (pd.merge(df_d[['State', 'AL']], df_m[['State', 'AL']], how='inner', on='State')
.rename({'AL_x' : 'Rate', 'AL_y' : 'Cost'}, axis=1)
.groupby(['Rate', 'Cost'])['State'].apply(lambda x: '_'.join(x)).reset_index()
.sort_values('State'))
master_dataframe0 = master_dataframe0[['State', 'Rate', 'Cost']].assign(State='20_21_RDWY_Purple_' + master_dataframe0['State'])
master_dataframe0
Out[1]:
State Rate Cost
7 20_21_RDWY_Purple_AL_AR_KY_LA_MS_SC_TN 50.80% 120.0
11 20_21_RDWY_Purple_AZ 56.70% 155.0
15 20_21_RDWY_Purple_CA 62.40% 145.0
9 20_21_RDWY_Purple_CO_ND 54.30% 155.0
5 20_21_RDWY_Purple_CT_DE_MN_NE 50.00% 145.0
4 20_21_RDWY_Purple_DC_IA_KS_MD_MI_OH_OK 49.00% 125.0
3 20_21_RDWY_Purple_FL 48.30% 125.0
18 20_21_RDWY_Purple_GA 67.90% 120.0
14 20_21_RDWY_Purple_ID_MT_NV 61.80% 145.0
0 20_21_RDWY_Purple_IL_IN_MO_NC 44.10% 120.0
16 20_21_RDWY_Purple_MA 63.50% 155.0
8 20_21_RDWY_Purple_ME_NH_NM_RI_SD 53.90% 155.0
6 20_21_RDWY_Purple_NJ 50.50% 145.0
13 20_21_RDWY_Purple_NY 61.10% 145.0
17 20_21_RDWY_Purple_OR 64.40% 185.0
2 20_21_RDWY_Purple_PA 47.20% 145.0
10 20_21_RDWY_Purple_TX 56.60% 125.0
1 20_21_RDWY_Purple_UT 45.00% 170.0
12 20_21_RDWY_Purple_VA 57.90% 120.0
翻過高山走不出你
TA貢獻1875條經驗 獲得超3個贊
借助 Erickson 的 和 lambda 函數幫助.groupby，我們得出了正確的解決方案：
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
pd.set_option('display.width', None)
pd.set_option('display.max_colwidth', None)
df_d = pd.read_excel(path,
sheet_name=0,
header=0,
index_col=False,
keep_default_na=True)
df_m = pd.read_excel(path2,
sheet_name=0,
header=0,
index_col=False,
keep_default_na=True)
m_col_names = ['AL', 'AR', 'AZ', 'CA', 'CO', 'CT', 'DC', 'DE', 'FL', 'GA', 'IA', 'ID', 'IL', 'IN', 'KS', 'KY', 'LA',
'MA', 'MD', 'ME', 'MI', 'MN', 'MO', 'MS', 'MT', 'NC', 'ND', 'NE', 'NH', 'NJ', 'NM', 'NV', 'NY', 'OH',
'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VA', 'VT', 'WA', 'WI', 'WV', 'WY', 'AB', 'BC',
'MB', 'NB', 'NF', 'NS', 'ON', 'PE', 'PQ', 'SK']
final_frame = pd.DataFrame()
for state in m_col_names:
master_dataframe0 = (pd.merge(df_d[['State', state]], df_m[['State', state]], how='inner', on='State')
.rename({state + '_x': 'Rate', state + '_y': 'Cost'}, axis=1)
.groupby(['Rate', 'Cost'])['State'].apply(lambda x: '_'.join(x)).reset_index()
.sort_values('State'))
master_dataframe0['Origin'] = state
master_dataframe0 = master_dataframe0[['State', 'Rate', 'Cost', 'Origin']].assign(
State='20_21_RDWY_Purple_' + master_dataframe0['State'])
final_frame = final_frame.append(master_dataframe0)
print(final_frame)
final_frame.to_excel("w3llshipmeright.xlsx")
正確輸出：
添加回答
舉報
0/150
提交
取消