hellmoore hellmoore - 6 months ago 52
Python Question

Count value to some id in table using python

I have a data like

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


I need to count
id
in url that include
audios
.
Desire output:

d684cd5f0189ab49c391c2b7bcbac0cb: 291781172 - 4, 168693045 - 1, etc


I don't know how can I get
id
after
audio
and count that.

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
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
Comments