我有一个数据框，用于显示加州火车中 2 个车站的所有可能组合以及它们之间旅行的票价。我想制作一个值矩阵，但不明白如何。数据如下所示：
+=========================+=========================+========+
| SOURCE_STN | DEST_STN | RC1($) |
+=========================+=========================+========+
| 22nd Street | Atherton | 8.25 |
+-------------------------+-------------------------+--------+
| 22nd Street | Redwood City | 6 |
+-------------------------+-------------------------+--------+
| 22nd Street | Millbrae Transit Center | 6 |
+-------------------------+-------------------------+--------+
| 22nd Street | Stanford | 8.25 |
+-------------------------+-------------------------+--------+
| 22nd Street | Santa Clara | 10.5 |
+-------------------------+-------------------------+--------+
| 22nd Street | California Ave. | 8.25 |
+-------------------------+-------------------------+--------+
| 22nd Street | Tamien | 10.5 |
+-------------------------+-------------------------+--------+
| 22nd Street | Lawrence | 10.5 |
+-------------------------+-------------------------+--------+
| 22nd Street | San Antonio | 8.25 |
+-------------------------+-------------------------+--------+
| 22nd Street | Burlingame | 6 |
+-------------------------+-------------------------+--------+
| 22nd Street | San Mateo | 6 |
+-------------------------+-------------------------+--------+
| 22nd Street | Hayward Park | 6 |
+-------------------------+-------------------------+--------+
| 22nd Street | Blossom Hill | 12.75 |
+-------------------------+-------------------------+--------+
| 22nd Street | South San Francisco | 3.75 |
+-------------------------+-------------------------+--------+
| 22nd Street | San Carlos | 6 |
+-------------------------+-------------------------+--------+
| 22nd Street | San Jose Diridon | 10.5 |
+-------------------------+-------------------------+--------+
| 22nd Street | Gilroy | 15 |
+-------------------------+-------------------------+--------+
| 22nd Street | College Park | 10.5 |
+-------------------------+-------------------------+--------+
| 22nd Street | Menlo Park | 8.25 |
+-------------------------+-------------------------+--------+
| 22nd Street | Palo Alto | 8.25 |
+-------------------------+-------------------------+--------+
| 22nd Street | Belmont | 6 |
+-------------------------+-------------------------+--------+
| 22nd Street | Menlo Park | 8.25 |
+-------------------------+-------------------------+--------+
| 22nd Street | Broadway | 6 |
+-------------------------+-------------------------+--------+
| 22nd Street | Sunnyvale | 8.25 |
+-------------------------+-------------------------+--------+
| 22nd Street | San Martin | 15 |
+-------------------------+-------------------------+--------+
| 22nd Street | Morgan Hill | 15 |
+-------------------------+-------------------------+--------+
| 22nd Street | Hillsdale | 6 |
+-------------------------+-------------------------+--------+
| 22nd Street | Capitol | 12.75 |
+-------------------------+-------------------------+--------+
| 22nd Street | Bayshore | 3.75 |
+-------------------------+-------------------------+--------+
| 22nd Street | Mountain View | 8.25 |
+-------------------------+-------------------------+--------+
| 22nd Street | San Francisco | 3.75 |
+-------------------------+-------------------------+--------+
| Atherton | Redwood City | 6 |
+-------------------------+-------------------------+--------+
| Atherton | Millbrae Transit Center | 6 |
+-------------------------+-------------------------+--------+
| Atherton | Stanford | 8.25 |
+-------------------------+-------------------------+--------+
| Atherton | Santa Clara | 10.5 |
+-------------------------+-------------------------+--------+
| Atherton | California Ave. | 8.25 |
+-------------------------+-------------------------+--------+
| Atherton | Tamien | 10.5 |
+-------------------------+-------------------------+--------+
| Atherton | Lawrence | 10.5 |
+-------------------------+-------------------------+--------+
| Atherton | San Antonio | 8.25 |
+-------------------------+-------------------------+--------+
| Atherton | Burlingame | 6 |
+-------------------------+-------------------------+--------+
| Atherton | San Mateo | 6 |
+-------------------------+-------------------------+--------+
| Atherton | Hayward Park | 6 |
+-------------------------+-------------------------+--------+
| Atherton | Blossom Hill | 12.75 |
+-------------------------+-------------------------+--------+
| Atherton | South San Francisco | 3.75 |
+-------------------------+-------------------------+--------+
| Atherton | San Carlos | 6 |
+-------------------------+-------------------------+--------+
| Atherton | San Jose Diridon | 10.5 |
+-------------------------+-------------------------+--------+
| Atherton | Gilroy | 15 |
+-------------------------+-------------------------+--------+
| Atherton | College Park | 10.5 |
+-------------------------+-------------------------+--------+
| Atherton | Menlo Park | 8.25 |
+-------------------------+-------------------------+--------+
| Atherton | Palo Alto | 8.25 |
+-------------------------+-------------------------+--------+
| Atherton | Menlo Park | 8.25 |
+-------------------------+-------------------------+--------+
| Atherton | 22nd Street | 3.75 |
+-------------------------+-------------------------+--------+
| Atherton | Broadway | 6 |
+-------------------------+-------------------------+--------+
| Atherton | Sunnyvale | 8.25 |
+-------------------------+-------------------------+--------+
| Atherton | San Martin | 15 |
+-------------------------+-------------------------+--------+
| Atherton | Belmont | 6 |
+-------------------------+-------------------------+--------+
| Atherton | Morgan Hill | 15 |
+-------------------------+-------------------------+--------+
| Atherton | Hillsdale | 6 |
+-------------------------+-------------------------+--------+
| Atherton | Capitol | 12.75 |
+-------------------------+-------------------------+--------+
| Atherton | Bayshore | 3.75 |
+-------------------------+-------------------------+--------+
| Atherton | Mountain View | 8.25 |
+-------------------------+-------------------------+--------+
| Atherton | San Francisco | 3.75 |
+-------------------------+-------------------------+--------+
| Bayshore | Atherton | 8.25 |
+-------------------------+-------------------------+--------+
| Bayshore | Redwood City | 6 |
+-------------------------+-------------------------+--------+
| Bayshore | Millbrae Transit Center | 6 |
+-------------------------+-------------------------+--------+
| Bayshore | Stanford | 8.25 |
+-------------------------+-------------------------+--------+
| Bayshore | Santa Clara | 10.5 |
+-------------------------+-------------------------+--------+
| Bayshore | Menlo Park | 8.25 |
+-------------------------+-------------------------+--------+
| Bayshore | 22nd Street | 3.75 |
+-------------------------+-------------------------+--------+
| Bayshore | Broadway | 6 |
+-------------------------+-------------------------+--------+
| Bayshore | Sunnyvale | 8.25 |
+-------------------------+-------------------------+--------+
| Bayshore | San Martin | 15 |
+-------------------------+-------------------------+--------+
| Bayshore | California Ave. | 8.25 |
+-------------------------+-------------------------+--------+
| Bayshore | Tamien | 10.5 |
+-------------------------+-------------------------+--------+
| Bayshore | Lawrence | 10.5 |
+-------------------------+-------------------------+--------+
| Bayshore | San Antonio | 8.25 |
+-------------------------+-------------------------+--------+
| Bayshore | Burlingame | 6 |
+-------------------------+-------------------------+--------+
| Bayshore | San Mateo | 6 |
+-------------------------+-------------------------+--------+
| Bayshore | Hayward Park | 6 |
+-------------------------+-------------------------+--------+
| Bayshore | Blossom Hill | 12.75 |
+-------------------------+-------------------------+--------+
| Bayshore | South San Francisco | 3.75 |
+-------------------------+-------------------------+--------+
| Bayshore | San Carlos | 6 |
+-------------------------+-------------------------+--------+
| Bayshore | San Jose Diridon | 10.5 |
+-------------------------+-------------------------+--------+
| Bayshore | Gilroy | 15 |
+-------------------------+-------------------------+--------+
| Bayshore | College Park | 10.5 |
+-------------------------+-------------------------+--------+
| Bayshore | Menlo Park | 8.25 |
+-------------------------+-------------------------+--------+
| Bayshore | Palo Alto | 8.25 |
+-------------------------+-------------------------+--------+
| Bayshore | Belmont | 6 |
+-------------------------+-------------------------+--------+
| Bayshore | Morgan Hill | 15 |
+-------------------------+-------------------------+--------+
| Bayshore | Hillsdale | 6 |
+-------------------------+-------------------------+--------+
| Bayshore | Capitol | 12.75 |
+-------------------------+-------------------------+--------+
| Bayshore | Mountain View | 8.25 |
+-------------------------+-------------------------+--------+
| Bayshore | San Francisco | 3.75 |
+-------------------------+-------------------------+--------+
| Belmont | Atherton | 8.25 |
+-------------------------+-------------------------+--------+
| Belmont | Redwood City | 6 |
+-------------------------+-------------------------+--------+
| Belmont | Millbrae Transit Center | 6 |
+-------------------------+-------------------------+--------+
| Belmont | Stanford | 8.25 |
+-------------------------+-------------------------+--------+
| Belmont | Santa Clara | 10.5 |
+-------------------------+-------------------------+--------+
| Belmont | Menlo Park | 8.25 |
+-------------------------+-------------------------+--------+
| Belmont | 22nd Street | 3.75 |
+-------------------------+-------------------------+--------+
| Belmont | Broadway | 6 |
+-------------------------+-------------------------+--------+
| Belmont | Sunnyvale | 8.25 |
+-------------------------+-------------------------+--------+
| Belmont | San Martin | 15 |
+-------------------------+-------------------------+--------+
| Belmont | California Ave. | 8.25 |
+-------------------------+-------------------------+--------+
| Belmont | Tamien | 10.5 |
+-------------------------+-------------------------+--------+
| Belmont | Lawrence | 10.5 |
+-------------------------+-------------------------+--------+
| Belmont | San Antonio | 8.25 |
+-------------------------+-------------------------+--------+
| Belmont | Burlingame | 6 |
+-------------------------+-------------------------+--------+
| Belmont | San Mateo | 6 |
+-------------------------+-------------------------+--------+
| Belmont | Hayward Park | 6 |
+-------------------------+-------------------------+--------+
| Belmont | Blossom Hill | 12.75 |
+-------------------------+-------------------------+--------+
| Belmont | South San Francisco | 3.75 |
+-------------------------+-------------------------+--------+
| Belmont | San Carlos | 6 |
+-------------------------+-------------------------+--------+
| Belmont | San Jose Diridon | 10.5 |
+-------------------------+-------------------------+--------+
| Belmont | Gilroy | 15 |
+-------------------------+-------------------------+--------+
| Belmont | College Park | 10.5 |
+-------------------------+-------------------------+--------+
| Belmont | Menlo Park | 8.25 |
+-------------------------+-------------------------+--------+
| Belmont | Palo Alto | 8.25 |
+-------------------------+-------------------------+--------+
| Belmont | Morgan Hill | 15 |
+-------------------------+-------------------------+--------+
| Belmont | Hillsdale | 6 |
+-------------------------+-------------------------+--------+
| Belmont | Capitol | 12.75 |
+-------------------------+-------------------------+--------+
| Belmont | Bayshore | 3.75 |
+-------------------------+-------------------------+--------+
| Belmont | Mountain View | 8.25 |
+-------------------------+-------------------------+--------+
| Belmont | San Francisco | 3.75 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Atherton | 8.25 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Redwood City | 6 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Millbrae Transit Center | 6 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Stanford | 8.25 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Santa Clara | 10.5 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Menlo Park | 8.25 |
+-------------------------+-------------------------+--------+
| Blossom Hill | 22nd Street | 3.75 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Broadway | 6 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Sunnyvale | 8.25 |
+-------------------------+-------------------------+--------+
| Blossom Hill | San Martin | 15 |
+-------------------------+-------------------------+--------+
| Blossom Hill | California Ave. | 8.25 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Tamien | 10.5 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Lawrence | 10.5 |
+-------------------------+-------------------------+--------+
| Blossom Hill | San Antonio | 8.25 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Burlingame | 6 |
+-------------------------+-------------------------+--------+
| Blossom Hill | San Mateo | 6 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Hayward Park | 6 |
+-------------------------+-------------------------+--------+
| Blossom Hill | South San Francisco | 3.75 |
+-------------------------+-------------------------+--------+
| Blossom Hill | San Carlos | 6 |
+-------------------------+-------------------------+--------+
| Blossom Hill | San Jose Diridon | 10.5 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Gilroy | 15 |
+-------------------------+-------------------------+--------+
| Blossom Hill | College Park | 10.5 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Menlo Park | 8.25 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Palo Alto | 8.25 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Belmont | 6 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Morgan Hill | 15 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Hillsdale | 6 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Capitol | 12.75 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Bayshore | 3.75 |
+-------------------------+-------------------------+--------+
| Blossom Hill | Mountain View | 8.25 |
+-------------------------+-------------------------+--------+
| Blossom Hill | San Francisco | 3.75 |
+-------------------------+-------------------------+--------+
| Broadway | Atherton | 8.25 |
+-------------------------+-------------------------+--------+
| Broadway | Redwood City | 6 |
+-------------------------+-------------------------+--------+
| Broadway | Millbrae Transit Center | 6 |
+-------------------------+-------------------------+--------+
| Broadway | Stanford | 8.25 |
+-------------------------+-------------------------+--------+
| Broadway | Santa Clara | 10.5 |
+-------------------------+-------------------------+--------+
| Broadway | Menlo Park | 8.25 |
+-------------------------+-------------------------+--------+
| Broadway | 22nd Street | 3.75 |
+-------------------------+-------------------------+--------+
| Broadway | Sunnyvale | 8.25 |
+-------------------------+-------------------------+--------+
| Broadway | San Martin | 15 |
+-------------------------+-------------------------+--------+
| Broadway | California Ave. | 8.25 |
+-------------------------+-------------------------+--------+
| Broadway | Tamien | 10.5 |
+-------------------------+-------------------------+--------+
| Broadway | Lawrence | 10.5 |
+-------------------------+-------------------------+--------+
| Broadway | San Antonio | 8.25 |
+-------------------------+-------------------------+--------+
| Broadway | Burlingame | 6 |
+-------------------------+-------------------------+--------+
| Broadway | San Mateo | 6 |
+-------------------------+-------------------------+--------+
| Broadway | Hayward Park | 6 |
+-------------------------+-------------------------+--------+
| Broadway | Blossom Hill | 12.75 |
+-------------------------+-------------------------+--------+
| Broadway | South San Francisco | 3.75 |
+-------------------------+-------------------------+--------+
| Broadway | San Carlos | 6 |
+-------------------------+-------------------------+--------+
| Broadway | San Jose Diridon | 10.5 |
+-------------------------+-------------------------+--------+
| Broadway | Gilroy | 15 |
+-------------------------+-------------------------+--------+
| Broadway | College Park | 10.5 |
+-------------------------+-------------------------+--------+
| Broadway | Menlo Park | 8.25 |
+-------------------------+-------------------------+--------+
| Broadway | Palo Alto | 8.25 |
+-------------------------+-------------------------+--------+
| Broadway | Morgan Hill | 15 |
+-------------------------+-------------------------+--------+
| Broadway | Belmont | 6 |
+-------------------------+-------------------------+--------+
| Broadway | Hillsdale | 6 |
+-------------------------+-------------------------+--------+
| Broadway | Capitol | 12.75 |
+-------------------------+-------------------------+--------+
| Broadway | Bayshore | 3.75 |
+-------------------------+-------------------------+--------+
| Broadway | Mountain View | 8.25 |
+-------------------------+-------------------------+--------+
| Broadway | San Francisco | 3.75 |
+-------------------------+-------------------------+--------+
我试图用pd做点什么。数据帧(索引 = ，列 =(，但它不起作用。 它必须是这样的：
+---------------+----------------+-----------+
| station | San Francisco | Broadway |
+---------------+----------------+-----------+
| San Francisco | 0 | value |
| Broadway | value | 0 |
+---------------+----------------+-----------+
谢谢！
您需要使用df.pivot().
df.pivot(index='SOURCE_STN', columns='DEST_STN', values='RC1($)').fillna(0)
编辑：
对于具有重复项的数据帧，您需要改用df.pivot_table()，并在aggfunc参数中指定聚合函数。
import numpy as np
df.pivot_table(index='SOURCE_STN', columns='DEST_STN', values='RC1($)', aggfunc=np.sum).fillna(0)
您可以通过在熊猫中使用pivot_table来实现此目的。
data_frame.pivot_table('RC1($)', 'SOURCE_STN', 'DEST_STN').fillna(0)
解释：
您期望的矩阵可以通过以下代码实现，因为排列仍保留，同时还通过获取重复的平均值来管理重复项。
import pandas as pd
import numpy as np
route_table = pd.read_csv("path_to_file.csv")
route_matrix = route_table.pivot_table('RC1($)', 'SOURCE_STN', 'DEST_STN', aggfunc=np.mean).fillna(0)
print(route_matrix)
输出：
DEST_STN 22nd Street Atherton ... Sunnyvale Tamien
SOURCE_STN ...
22nd Street 0.00 8.25 ... 8.25 10.5
Atherton 3.75 0.00 ... 8.25 10.5
Bayshore 3.75 8.25 ... 8.25 10.5
Belmont 3.75 8.25 ... 8.25 10.5
Blossom Hill 3.75 8.25 ... 8.25 10.5
Broadway 3.75 8.25 ... 8.25 10.5
Burlingame 3.75 8.25 ... 8.25 10.5
California Ave. 3.75 8.25 ... 8.25 10.5
Capitol 3.75 8.25 ... 8.25 10.5
College Park 3.75 8.25 ... 8.25 10.5
Gilroy 3.75 8.25 ... 8.25 10.5
Hillsdale 3.75 8.25 ... 8.25 10.5
有关数据透视表的不同视角，以便更好地理解：
备用代码
route_matrix = route_table.pivot_table('RC1($)', 'DEST_STN', 'SOURCE_STN', aggfunc=np.mean).fillna(0)
print(route_matrix)
输出
SOURCE_STN 22nd Street Atherton ... Sunnyvale Tamien
DEST_STN ...
22nd Street 0.00 3.75 ... 3.75 3.75
Atherton 8.25 0.00 ... 8.25 8.25
Bayshore 3.75 3.75 ... 3.75 3.75
Belmont 6.00 6.00 ... 6.00 6.00
Blossom Hill 12.75 12.75 ... 12.75 12.75
Broadway 6.00 6.00 ... 6.00 6.00
Burlingame 6.00 6.00 ... 6.00 6.00
California Ave. 8.25 8.25 ... 8.25 8.25
Capitol 12.75 12.75 ... 12.75 12.75
College Park 10.50 10.50 ... 10.50 10.50
Gilroy 15.00 15.00 ... 15.00 15.00