python - 使用python计算表中某些id的值

Question

我有这样的数据

ID,"url","used_at","active_seconds"
d684cd5f0189ab49c391c2b7bcbac0cb,"vk.com/videos168693045?section=all",2016-03-01 10:18:45,4
d684cd5f0189ab49c391c2b7bcbac0cb,"vk.com",2016-03-01 10:18:49,2
d684cd5f0189ab49c391c2b7bcbac0cb,"vk.com/feed",2016-03-01 10:18:51,2
d684cd5f0189ab49c391c2b7bcbac0cb,"vk.com/audios291781172",2016-03-01 10:18:53,2
d684cd5f0189ab49c391c2b7bcbac0cb,"vk.com/audios291781172?q=Тимур%20Гатиятуллин%20%7C%20Честный%20-%20Улетай%20полная%20версия",2016-03-01 10:18:55,6
d684cd5f0189ab49c391c2b7bcbac0cb,"vk.com/audios291781172?q=Тимур%20ГатиятуллинЧестный%20-%20Улетай%20полная%20версия",2016-03-01 10:19:01,2
d684cd5f0189ab49c391c2b7bcbac0cb,"vk.com/audios291781172?q=Тимур%20Гатиятуллин%20Честный%20-%20Улетай%20полная%20версия",2016-03-01 10:19:03,4
d684cd5f0189ab49c391c2b7bcbac0cb,"vk.com/audios168693045?section=all",2016-03-01 10:19:07,2

我需要计算 url 中包含 audios 的 id。期望输出:

d684cd5f0189ab49c391c2b7bcbac0cb: 291781172 - 4, 168693045 - 1, etc

我不知道如何在 audio 之后获取 id 并计算它。

data = pd.read_csv("get_id.csv")
data_name = pd.read_excel("name.xlsx")
names_panel = data_name['Names']
urls = data['url']
ids = data['ID']
for url in urls:
    if 'audios' in url:
        print url

Answer 1

print pd.concat([df['ID'], df['url'].str.extract('(?P<count>audios)(?P<digit>\d+)')], axis=1).groupby(['ID', 'digit']).count()

                                            count
ID                               digit           
d684cd5f0189ab49c391c2b7bcbac0cb 168693045      1
                                 291781172      4