logo标签列表

将pandas数据框列转换为带有源和目标的networkx图

pythondataframegraphnetworkxadjacency-matrix
3

我在pandas中有一个数据框，其中包含有关人员位置和时间的信息。它有超过3亿行。

以下是示例，其中每个名称都通过group.by分配给唯一的index，并根据Name和Year进行排序：

import pandas as pd
inp = [{'Name': 'John', 'Year':2018, 'Address':'Beverly hills'}, {'Name': 'John', 'Year':2018, 'Address':'Beverly hills'}, {'Name': 'John', 'Year':2019, 'Address':'Beverly hills'}, {'Name': 'John', 'Year':2019, 'Address':'Orange county'}, {'Name': 'John', 'Year':2019, 'Address':'New York'}, {'Name': 'Steve', 'Year':2018, 'Address':'Canada'}, {'Name': 'Steve', 'Year':2019, 'Address':'Canada'}, {'Name': 'Steve', 'Year':2019, 'Address':'Canada'}, {'Name': 'Steve', 'Year':2020, 'Address':'California'}, {'Name': 'Steve', 'Year':2020, 'Address':'Canada'}, {'Name': 'John', 'Year':2020, 'Address':'Canada'}, {'Name': 'John', 'Year':2021, 'Address':'Canada'}, {'Name': 'John', 'Year':2021, 'Address':'Beverly hills'}, {'Name': 'Steve', 'Year':2021, 'Address':'California'}, {'Name': 'Steve', 'Year':2022, 'Address':'California'}, {'Name': 'Steve', 'Year':2018, 'Address':'NewYork'}, {'Name': 'Steve', 'Year':2018, 'Address':'California'}, {'Name': 'Steve', 'Year':2022, 'Address':'NewYork'}]
df = pd.DataFrame(inp)
df['Author_Grouped_Index'] = df.groupby(['Name']).ngroup()
df.sort_values(['Name', 'Year'], ascending=[False, True])

输出：

+-------+-------+------+---------------+----------------------+
| Index | Name  | Year | Address       | Name_Grouped_Index   |
+-------+-------+------+---------------+----------------------+
| 5     | Steve | 2018 | Canada        | 1                    |
+-------+-------+------+---------------+----------------------+
| 15    | Steve | 2018 | NewYork       | 1                    |
+-------+-------+------+---------------+----------------------+
| 16    | Steve | 2018 | California    | 1                    |
+-------+-------+------+---------------+----------------------+
| 6     | Steve | 2019 | Canada        | 1                    |
+-------+-------+------+---------------+----------------------+
| 7     | Steve | 2019 | Canada        | 1                    |
+-------+-------+------+---------------+----------------------+
| 8     | Steve | 2020 | California    | 1                    |
+-------+-------+------+---------------+----------------------+
| 9     | Steve | 2020 | Canada        | 1                    |
+-------+-------+------+---------------+----------------------+
| 13    | Steve | 2021 | California    | 1                    |
+-------+-------+------+---------------+----------------------+
| 14    | Steve | 2022 | California    | 1                    |
+-------+-------+------+---------------+----------------------+
| 17    | Steve | 2022 | NewYork       | 1                    |
+-------+-------+------+---------------+----------------------+
| 0     | John  | 2018 | Beverly hills | 0                    |
+-------+-------+------+---------------+----------------------+
| 1     | John  | 2018 | Beverly hills | 0                    |
+-------+-------+------+---------------+----------------------+
| 2     | John  | 2019 | Beverly hills | 0                    |
+-------+-------+------+---------------+----------------------+
| 3     | John  | 2019 | Orange county | 0                    |
+-------+-------+------+---------------+----------------------+
| 4     | John  | 2019 | New York      | 0                    |
+-------+-------+------+---------------+----------------------+
| 10    | John  | 2020 | Canada        | 0                    |
+-------+-------+------+---------------+----------------------+
| 11    | John  | 2021 | Canada        | 0                    |
+-------+-------+------+---------------+----------------------+
| 12    | John  | 2021 | Beverly hills | 0                    |
+-------+-------+------+---------------+----------------------+

我想获取网络图矩阵（邻接矩阵），以便查看地址之间的变化总数。换句话说，例如，2018年有多少人从“加拿大”搬到了“加利福尼亚”。

理想输出：

1）直接从地址列生成一个有向图。技术上将"地址"列转换为两列 "源" 和 "目标"，其中 "目标" 值是下一行的 "源"。最好在另一列 "权重" 中计算成对数，而不是成对重复。

+------------+------------+------+--------+
| Source     | Target     | Year | Weight |
+------------+------------+------+--------+
| Canada     | NewYork    | 2018 |        |
+------------+------------+------+--------+
| NewYork    | California | 2018 |        |
+------------+------------+------+--------+
| California | Canada     | 2019 |        |
+------------+------------+------+--------+
| Canada     | Canada     | 2019 |        |
+------------+------------+------+--------+
| Canada     | California | 2020 |        |
+------------+------------+------+--------+
| California | Canada     | 2020 |        |
+------------+------------+------+--------+
| Canada     | California | 2021 |        |
+------------+------------+------+--------+
| California | California | 2022 |        |
+------------+------------+------+--------+
| California | NewYork    | 2022 |        |
+------------+------------+------+--------+

或者

2) 一个矩阵来说明地址之间的总变化。

+---------------+--------+---------+------------+---------------+---------------+
| From \ To     | Canada | NewYork | California | Beverly hills | Orange county |
+---------------+--------+---------+------------+---------------+---------------+
| Canada        | 2      | 2       | 2          | 2             | 0             |
+---------------+--------+---------+------------+---------------+---------------+
| NewYork       | 1      | 0       | 1          | 0             | 0             |
+---------------+--------+---------+------------+---------------+---------------+
| California    | 2      | 1       | 1          | 0             | 0             |
+---------------+--------+---------+------------+---------------+---------------+
| Beverly hills | 0      | 0       | 0          | 2             | 1             |
+---------------+--------+---------+------------+---------------+---------------+
| Orange county | 0      | 1       | 0          | 0             | 0             |
+---------------+--------+---------+------------+---------------+---------------+
1个回答
1
这不是最漂亮的代码，但至少您可以按照每个步骤进行操作。我选择了第二个选项，因为您可以从此连接矩阵轻松制作图形。您需要帮助制作networkx图吗？ 矩阵的行和列是：['比佛利山', '橙县', '纽约', '加拿大', '加利福尼亚州', '纽约'] 您为每个人拼写newyork的方式不同，因此它出现了两次。
import pandas as pd
inp = [{'Name': 'John', 'Year':2018, 'Address':'Beverly hills'}, {'Name': 'John', 'Year':2018, 'Address':'Beverly hills'}, {'Name': 'John', 'Year':2019, 'Address':'Beverly hills'}, {'Name': 'John', 'Year':2019, 'Address':'Orange county'}, {'Name': 'John', 'Year':2019, 'Address':'New York'}, {'Name': 'Steve', 'Year':2018, 'Address':'Canada'}, {'Name': 'Steve', 'Year':2019, 'Address':'Canada'}, {'Name': 'Steve', 'Year':2019, 'Address':'Canada'}, {'Name': 'Steve', 'Year':2020, 'Address':'California'}, {'Name': 'Steve', 'Year':2020, 'Address':'Canada'}, {'Name': 'John', 'Year':2020, 'Address':'Canada'}, {'Name': 'John', 'Year':2021, 'Address':'Canada'}, {'Name': 'John', 'Year':2021, 'Address':'Beverly hills'}, {'Name': 'Steve', 'Year':2021, 'Address':'California'}, {'Name': 'Steve', 'Year':2022, 'Address':'California'}, {'Name': 'Steve', 'Year':2018, 'Address':'NewYork'}, {'Name': 'Steve', 'Year':2018, 'Address':'California'}, {'Name': 'Steve', 'Year':2022, 'Address':'NewYork'}]
df = pd.DataFrame(inp)
df['Author_Grouped_Index'] = df.groupby(['Name']).ngroup()
df.sort_values(['Name', 'Year'], ascending=[False, True])

print (df)
dictionary_ = {} # where each person went
places = [] # all of the places
for index, row in df.iterrows():
    if row['Author_Grouped_Index'] not in dictionary_:
        dictionary_[row['Author_Grouped_Index']] = []
        dictionary_[row['Author_Grouped_Index']].append(row["Address"])
    else:
        dictionary_[row['Author_Grouped_Index']].append(row["Address"])
    if row["Address"] not in places:
        places.append(row["Address"])


print (dictionary_)

new_dictionary = {} #number of times each place visited
for key, value in dictionary_.items():
    for x in range(len(value)-1):
        move = value[x] + "-" + value[x+1]
        if not move in new_dictionary:
            new_dictionary[move] = 1
        else:
            new_dictionary[move] += 1

print (new_dictionary)
print (places)
import numpy as np
array = np.zeros((len(places),len(places)), dtype=int)
for x, place in enumerate(places):
    for y, place_2 in enumerate(places):

        move_2 = (place + "-" + place_2)
        try:
            array[x,y] = (new_dictionary[move_2])
        except:
            array[x,y] = 0

print (array)
从“array = np.zero”开始的代码的最后一部分，我收到“SyntaxError: invalid syntax”的错误。该错误将箭头（^）指向此行中引号（：）部分的“for x, place in enumerate(places):”部分。非常感谢您的建议，以便我可以运行该代码。 - AHK
1
已解决。您需要帮助从NumPy数组中绘制networkx图吗？ - MarcusRenshaw
谢谢，它起作用了！如果您也分享在networkx中绘制图形的方法，那就太完美了！ - AHK
请用您要实现的内容提出一个新问题。 - MarcusRenshaw
如果这段代码能为每个源和目标组合提供关联的“年份”，那么它将是完美无瑕的，如果如此，我可以接受它作为答案。这些配对是从代码的第二部分“new_dictionary”生成的，但是否有可能为它们提供“年份”？ - AHK
我已经发布了一个新的问题，关于如何在这个解决方案中使用networkx绘制结果NumPy数组。请从以下链接找到该问题：https://stackoverflow.com/q/61325124/2739921 - AHK
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接