我有一些公司的樣本，我想比較這些公司的財務數據。我的數據如下所示： Cusip9 Issuer IPO Year Total Assets Long-Term Debt Sales SIC-Code1 783755101 Ryerson Tull Inc 1996 9322000.0 2632000.0 633000.0 36612 826170102 Siebel Sys Inc 1996 995010.0 0.0 50250.0 24563 894363100 Travis Boats & Motors Inc 1996 313500.0 43340.0 23830.0 36614 159186105 Channell Commercial Corp 1996 426580.0 3380.0 111100.0 74835 742580103 Printware Inc 1996 145750.0 0.0 23830.0 8473我想為每家公司計算一個“相似度分數”。這個分數應該表明與其他公司的可比性。因此，我想用不同的財務數據來比較它們。可比性應表示為歐幾里德距離，即財務數據與“最接近的公司”之間的平方差之和的平方根。所以我需要計算到符合這些條件的每家公司的距離，但只需要最接近的分數。公司 1 的資產減去公司 2 的資產加上債務公司 1 減去債務公司 2....√((x_1-y_1 )^2+(x_2-y_2 )^2) 這應該只針對具有相同 SIC 代碼的公司進行計算，并且可比公司的 IPO 年份應該小于為其計算“相似度分數”的公司。我只想將這些公司與已經上市的公司進行比較。希望我的觀點得到明確。有人知道我可以從哪里開始嗎？我剛開始編程，完全迷失了。提前致謝。
3 回答
慕的地8271018
TA貢獻1796條經驗 獲得超4個贊
我復制了您提供的數據，然后計算了距離。對于每個發行人，我找到最近的發行人及其距離。請參閱下面的修改代碼。如果您需要更多詳細信息，請告訴我。
issuers = ["Ryerson Tull Inc", "Siebel Sys Inc", "Travis Boats & Motors Inc", "Channell Commercial Corp", "Printware Inc",
"AAA", "BBB", "ZZZ"]
ttl_assets = [9322000.0, 995010.0, 313500.0, 426580.0, 145750.0, 299999.0, 399999.0, 123456.0]
long_term_debt = [2632000.0, 0.0, 43340.0, 3380.0, 0.0, 11111.0, 22222.0, 87500.0]
sic_code = [3661, 2456, 3661, 7483, 8473, 3661, 7483, 3661]
ipo_year = [1996, 1996, 1996, 1996, 1996, 1996, 1996, 1997]
data = pd.DataFrame({"issuer": issuers,
"total assets": ttl_assets,
"long term debt": long_term_debt,
"SIC-Code": sic_code,
"IPO Year": ipo_year
})
def get_distance(x1, x2):
""" computes euclidean distance between two points """
d = math.sqrt((x1[0] - x2[0])**2 + (x1[1] - x2[1])**2)
return round(d, 3)
distMatrix = np.ndarray(shape=(len(data), len(data))) # creating an array to fill up the distances
distMatrix[:, :] = np.inf
for i in range(len(data)):
for j in range(len(data)):
if data.loc[i, "SIC-Code"] == data.loc[j, "SIC-Code"] and data.loc[i, "IPO Year"] == data.loc[j, "IPO Year"] and i != j:
issuer1 = data.loc[i, ["total assets", "long term debt"]].values
issuer2 = data.loc[j, ["total assets", "long term debt"]].values
distance = get_distance(issuer1, issuer2)
distMatrix[i, j] = distance
listIssuers = data["issuer"].tolist()
arrMinDist = distMatrix.argmin(axis=0)
dictMinDistIssuer = {} # dictionary that maps each issuer to its closest issuer
dictMinDist = {} # maps each each issuer to the closest issuers distance
dfDist = pd.DataFrame(distMatrix.tolist())
dfDist.columns = listIssuers
dfDist.insert(0, "issuer", listIssuers)
dfDist.insert(1, "IPO Year", ipo_year)
dfDist.insert(2, "SIC-Code", sic_code)
for issuer_idx, min_idx in enumerate(arrMinDist):
distance_value_counts = np.where(distMatrix==np.inf, 0, 1).sum(axis=0) # this checks if there are any matches for each issuer
if distance_value_counts[issuer_idx] == 0:
dictMinDistIssuer[listIssuers[issuer_idx]] = np.nan
dictMinDist[listIssuers[issuer_idx]] = np.nan
else:
dictMinDistIssuer[listIssuers[issuer_idx]] = listIssuers[min_idx]
dictMinDist[listIssuers[issuer_idx]] = distMatrix[issuer_idx][min_idx]
dfDist["closest issuer"] = dfDist["issuer"].map(dictMinDistIssuer)
dfDist["closest issuer dist"] = dfDist["issuer"].map(dictMinDist)
dfDist.replace(to_replace=np.inf, value=np.nan, inplace=True)
紅糖糍粑
TA貢獻1815條經驗 獲得超6個贊
SIC-Code考慮通過和 lesser對所有可能的配對將數據框與自身進行自連接IPO-Year。一定要避免反向重復。然后使用Numpy 數學和系列運算跨列運行簡單的矢量化算術計算。不需要循環。
慕妹3242003
TA貢獻1824條經驗 獲得超6個贊
import numpy as np
import pandas as pd
...
# REMOVE SPACES AND HYPHENS IN COLUMNS
df.columns = df.columns.str.replace(r'[ -]', '_')
# SELF JOIN AND AVOID REVERSE DUPLICATES WITH LESSER YEAR
df = (df.merge(df, on=['SIC-Code'])
.query("(Cusip9_x < Cusip9_y) & (Issuer_x < Issuer_y) & (IPO_Year_x < IPO_Year_y)")
)
# SIMILARITY SCORE CALCULATION
df['similarity_score'] = np.sqrt((df['Total_Assets_x'] - df['Total_Assets_y']).pow(2) +
(df['Long_Term_Debt_x'] - df['Long_Term_Debt_y']).pow(2))
添加回答
舉報
0/150
提交
取消