pythonのpandasでのピボット風データ集約（サイズ大）について

Question

レコード数が300万ぐらいのログデータがあります。
以下の集計したい項目のほかに、urlや時間などの項目もあります。。

id item
aaa 002
aaa 005
bbb 006
ccc 003

これをpandasのread_csvで読み込み、indexを「id」、columnsを「item」として、
データ数をカウントしたいと思っております。

下記2つの方法を試しましたが、うまくいきませんでした。

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
・1通り目:pivotで集約
pivot_tableで集約しようとすると、次のようなエラーが出ます。

df.pivot_table('url',index='id',columns='item',aggfunc='count')

「ValueError: negative dimensions are not allowed」

ネットで検索した結果、「サイズの大きく、かつ、スパースなndarrayを操作すると発生する」ようです。
なので、pivotでは集計が難しいでしょうか…？

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
・2通り目:for文で地道に計算
下記のようなに集計しようとすると、PCがフリーズしてしまいます。

df=pd.read_csv('logdata.csv')
df1 = DataFrame(index=df['id'].unique(),columns=df['item'].unique())
for i in range(len(df['id'])):
 df1.ix[df.iloc[i,0],df.iloc[i,1]]=1

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

いずれの方法でも、データ量が1万程度なら問題なく動くことを確認しています。

1通り目に関してのpivotでのエラーの回避方法、もしくは別の方法などのお知恵をいただければ幸いです。

Comments

[lightson]

次の点を教えてもらえますか。
・pivot_tableをどのように書いたか
・二つ目の方法のf2とはなんですか

[uraco]

pivot_tableは、他の使用していないnullのないカラムをカウント用に指定して集計しました。
二つ目のコードに間違いがあり、修正しました。失礼しました。。
よろしくお願いします！

Answer 1

一千万個のダミーデータを作って動かしてみたところ、Macbook air 4Gで50秒程度で答えがでました。
実際のデータはどのようなものかはわかりませんが、集計に不必要なものを除いたデータフレームを作って試してみてはいかがでしょうか。

import random
import pandas as pd

ids=['aaa','bbb','ccc','ddd','eee','fff']
items = ['001','002','003','004','005']

lst=[]
for i in xrange(10000000):
    lst.append([random.choice(ids),random.choice(items),i])
    
df = pd.DataFrame(lst)
df.columns=['id','item','cnt']
df_cnt = df.pivot_table(values='cnt',index='id',columns='item',aggfunc='count')
print df_cnt


結果
item     001     002     003     004     005
id                                          
aaa   333172  332853  332715  332805  333969
bbb   333405  332291  333239  333484  333567
ccc   332545  333176  333521  333197  332859
ddd   333978  333837  333153  333673  333523
eee   332649  333473  332910  332809  334210
fff   333865  332900  333881  334287  334054

Comments

[uraco]

ご回答ありがとうございます。
また、ダミーデータでのご確認もありがとうございます。参考になりました。

私の説明不足となり恐縮ですが、実際のデータは、idの数がユニークにすると47万件、itemの数は同様に19万件あります。。。
これが、「ValueError: negative dimensions are not allowed」というエラーになってしまう原因なのでしょうか・・・？

もしそうなのであれば、pivotでの処理ではなく、他のコードでも結構です。
よい方法はありませんでしょうか？

[lightson]

実際のデータがないのではっきりとは分かりませんが、IDやitemの数が多いせいではないと思います。エラーが「サイズの大きく、かつ、スパースなndarrayを操作すると発生する」が正しいなら、読み込んだデータからIDとitemだけのデータフレームを作り、それに対して処理を行えば、うまくいくのではないでしょうか。

[uraco]

アドバイスありがとうございました。
必要な項目だけに絞って実行もしてみましたが、pivotにしようとすると、カウント「0」が多くなってしまうので、それがエラーの原因のような気がしています。

＞読み込んだデータからIDとitemだけのデータフレームを作り、それに対して処理
こちらは質問に記載のようなfor文で地道にやりましたが、動きませんでした。。

Answer 2

今回は、pandasデータフレーム上での操作がうまくいかなかったので、フローを変え、csvデータから直接辞書型のデータを作成する方法で対処することにいたしました。