pandas groupby output convert to DataFrame

안녕하세요 코봉이입니다!

python 에서 groupby 함수를 써서 원하는 결과를 얻은 다음 data를 저장하고, 다시 불러왔을 때 당황하셨던 경험이 있으신가요?

 

import pandas as pd
path = 'C:/Users/dataset/'
data = pd.read_csv(path + 'data4.csv')
print(data)

      name  age         job       hobby          city
0    brian   25      farmer    baseball         tokyo
1    baker   20     teacher  basketball         seoul
2    henna   18     soldier      soccer  sanfransisco
3    harry   19      driver         ski          roma
4      tom   24     painter    pingpong         paris
5      bob   23    athletic    billiard        berlin
6    amily   20    designer      coding         tokyo
7     json   30  programmer        sing         seoul
8     mago   21    engineer      soccer       toronto
9      tim   40     student    baseball         seoul
10   ostin   20      driver         ski       toronto
11    beck   25     teacher    baseball         paris
12    jack   35      farmer      soccer        berlin
13  dustin   43    engineer      coding         seoul
14   jolly   58   president    baseball       toronto

 

데이터가 이렇게 있고, 직업별로 취미가 얼마나 다양한지를 확인하려고 groupby 를 이용해보겠습니다.

 

group = data.groupby(['job', 'hobby'])['name'].count()
print(group)

job         hobby     
athletic    billiard      1
designer    coding        1
driver      ski           2
engineer    coding        1
            soccer        1
farmer      baseball      1
            soccer        1
painter     pingpong      1
president   baseball      1
programmer  sing          1
soldier     soccer        1
student     baseball      1
teacher     baseball      1
            basketball    1
Name: name, dtype: int64

 

오 그래, 직업별로 취미도 다양하게 있고, driver는 두 명이 ski가 취미구나!
원하는 결과를 얻어냈으니, 저장하고 나중에 써먹어야지.

 

group.to_csv(path + 'groupby_output.csv', index = False)
print(pd.read_csv(path + 'groupby_output.csv'))

    name
0      1
1      1
2      2
3      1
4      1
5      1
6      1
7      1
8      1
9      1
10     1
11     1
12     1
13     1

 

헉!! 이게 뭐야 !!

 

데이터를 저장하고 불러왔는데, grouping 요소는 온데간데 없고 count 결과만 남아있네요.

 

이런 현상은 groupby할 때 group 요소들이 index로 들어가있어서 발생하기 때문에, output에 .reset_index() 만 붙여주시면 깔끔하게 해결됩니다^^

 

group = data.groupby(['job', 'hobby'])['name'].count().reset_index() ## reset_index() 추가 ##

group.to_csv(path + 'groupby_output.csv', index = False)
print(pd.read_csv(path + 'groupby_output.csv'))

           job       hobby  name
0     athletic    billiard     1
1     designer      coding     1
2       driver         ski     2
3     engineer      coding     1
4     engineer      soccer     1
5       farmer    baseball     1
6       farmer      soccer     1
7      painter    pingpong     1
8    president    baseball     1
9   programmer        sing     1
10     soldier      soccer     1
11     student    baseball     1
12     teacher    baseball     1
13     teacher  basketball     1

 

오늘도 많은 도움 되었으면 좋겠네요!

 

화이팅입니다.