Pandas: pivot table

Question

I have df:

ID,url,used_at,active_seconds,domain,search_engine,diff_time,period,code, category
08cd0141663315ce71e0121e3cd8d91f,market.yandex.ru/product/12858630?hid=91491&track=fr_same,2016-03-20 23:19:49,6,yandex.ru,None,78.0,515,100.0, Search system
08cd0141663315ce71e0121e3cd8d91f,market.yandex.ru/product/12858630?hid=91491&track=fr_same,2016-03-20 23:20:01,26,yandex.ru,None,6.0,515,100.0, Social network
08cd0141663315ce71e0121e3cd8d91f,market.yandex.ru/catalog/54726/list?hid=91491&track=pieces&gfilter=1801946%3A1871375&exc=1&regprice=9&how=dpop,2016-03-20 23:20:33,14,yandex.ru,None,6.0,515,100.0, Social network
08cd0141663315ce71e0121e3cd8d91f,market.yandex.ru/product/12858630/offers?hid=91491&grhow=shop,2016-03-20 23:20:47,2,yandex.ru,None,14.0,515,100.0, Internet shop
08cd0141663315ce71e0121e3cd8d91f,market.yandex.ru/product/12858630/offers?hid=91491&grhow=shop,2016-03-20 23:24:05,8,yandex.ru,None,196.0,515,100.0, Internet shop
08cd0141663315ce71e0121e3cd8d91f,market.yandex.ru/catalogmodels.xml?hid=91491&CAT_ID=160043&nid=54726&track=pieces,2016-03-20 23:24:13,32,yandex.ru,None,8.0,515,100.0, Search system
08cd0141663315ce71e0121e3cd8d91f,market.yandex.ru/catalog/54726/list?hid=91491&track=fr_cm_shwall&exc=1&how=dpop,2016-03-20 23:24:45,16,yandex.ru,None,32.0,515,100.0, Internet shop
08cd0141663315ce71e0121e3cd8d91f,market.yandex.ru/catalogmodels.xml?hid=91491&CAT_ID=160043&nid=54726&track=pieces,2016-03-20 23:25:01,4,yandex.ru,None,16.0,515,100.0, Search system
08cd0141663315ce71e0121e3cd8d91f,market.yandex.ru/catalog/54726/list?hid=91491&track=fr_cm_pop&exc=1&how=dpop,2016-03-20 23:25:05,10,yandex.ru,None,4.0,515,100.0, Social network
08cd0141663315ce71e0121e3cd8d91f,market.yandex.ru/product/11153512?hid=91491&track=fr_same,2016-03-21 06:52:44,2,yandex.ru,None,14.0,516,100.0, Internet shop
08cd0141663315ce71e0121e3cd8d91f,market.yandex.ru/catalog/54726/list?hid=91491&track=pieces&gfilter=1801946%3A1871375&exc=1&regprice=9&how=dpop,2016-04-04 21:08:41,24,yandex.ru,None,20.0,562,100.0, Internet shop
0bc0898d3fe2e46158621c674effb458,market.yandex.ru/product/12259780?hid=91491&show-uid=56508001849064882783001,2016-02-26 20:34:20,28,yandex.ru,yandex,10.0,1217,100.0, Social network
0bc0898d3fe2e46158621c674effb458,market.yandex.ru/product/12259780?hid=91491&show-uid=56508001849064882783001,2016-02-26 20:34:50,1,yandex.ru,None,2.0,1217,100.0, Internet shop

I need to build pivot_table. I use

table = pd.pivot_table(df, values='domain', index=['ID'], columns=['category'], aggfunc=np.sum)

But problems in that it concatenates domain, but I want to count quanity of unique domains. How can I do that?

Answer 1

It looks like need:

table = pd.pivot_table(df, values='domain', 
                           index=['ID'], 
                           columns=['category'], 
                           aggfunc=lambda x: x.nunique())
print (table)

category                           Internet shop   Search system  \
ID                                                                 
08cd0141663315ce71e0121e3cd8d91f             1.0             1.0   
0bc0898d3fe2e46158621c674effb458             1.0             NaN   

category                           Social network  
ID                                                 
08cd0141663315ce71e0121e3cd8d91f              1.0  
0bc0898d3fe2e46158621c674effb458              1.0 

Another faster solution:

print (df.groupby(['ID','category'])['domain'].nunique().unstack())
category                           Internet shop   Search system  \
ID                                                                 
08cd0141663315ce71e0121e3cd8d91f             1.0             1.0   
0bc0898d3fe2e46158621c674effb458             1.0             NaN   

category                           Social network  
ID                                                 
08cd0141663315ce71e0121e3cd8d91f              1.0  
0bc0898d3fe2e46158621c674effb458              1.0