[Py2.7] API호출을 통한 JSON 처리하기

/*********************************************************************************************************
-- Title : [Py2.7] API호출을 통한 JSON 처리하기
-- Reference : Python for Data Analysis
-- Key word : json pandas dataframe 데이터프레임 urllib2
*********************************************************************************************************/

# -*- coding: utf-8 -*-
 
import os
import sys
import pandas as pd
import json
import urllib2
 
# ********************************************
# -- JSON 읽기
# ********************************************
print("-" * 100 + "{[10]}") # ----- #
 
# -- JSON 만들기(반드시 """를 사용해야)
json_data1 = """{
    "color":"red",
    "value":"#f00"}
"""

result = json.loads(json_data1)
print result

json_data2 = """{
    "id":"0001",
    "type":"donut",
    "name":"Cake",
    "ppu":0.55,
    "batters":{
                "batter": [
                            {"id":1001, "type":"aaaa"},
                            {"id":1002, "type":"bbbb"},
                            {"id":1003, "type":"dddd"}
                          ]
               },
    "topping":"zzzz"}
"""

result2 = json.loads(json_data2)
print result2
print result2["id"]
print result2["batters"]
print result2["batters"]["batter"]
print result2["batters"]["batter"][1]


# ********************************************
# -- API 호출(JSON)
#    urllib2 패키지 필요
# ********************************************
print("-" * 100 + "{[11]}") # ----- #

movieIdListURL = "http://www.kobis.or.kr/kobisopenapi/webservice/rest/movie/searchMovieList.json?key=430156241533f1d058c603178cc3ca0e&openDt=20150101"
moviewIdPage = urllib2.urlopen(movieIdListURL)
movieIdData = json.loads(moviewIdPage.read())

print movieIdData
print movieIdData["movieListResult"]["movieList"]
print movieIdData["movieListResult"]["movieList"][0]["movieCd"]
print movieIdData["movieListResult"]["movieList"][0]["movieNm"]
print movieIdData["movieListResult"]["movieList"][0]["movieNmEn"]
print movieIdData["movieListResult"]["movieList"][0]["openDt"]
print movieIdData["movieListResult"]["movieList"][0]["directors"][0]["peopleNm"]

print("-" * 100 + "{[11.1]}") # ----- #

# -- JSON 저장할 DF 생성
df = pd.DataFrame(columns=("movieCd", "movieNm", "movieNmEn", "openDt", "peopleNm"))
print df

print("-" * 100 + "{[11.2]}") # ----- #

# -- JSON에서 전체 영화 정보 건수 추출
moviecnt =  len(movieIdData["movieListResult"]["movieList"])

for i in range(moviecnt):
    df.loc[i] = [movieIdData["movieListResult"]["movieList"][i]["movieCd"],
                 movieIdData["movieListResult"]["movieList"][i]["movieNm"],
                 movieIdData["movieListResult"]["movieList"][i]["movieNmEn"],
                 movieIdData["movieListResult"]["movieList"][i]["openDt"],
                 movieIdData["movieListResult"]["movieList"][i]["directors"][0]["peopleNm"]
                ]
print df

# 빈 DF 만들기 (다른 버전) : 여긴 잘 안된다. 다른 자료 참조해서 추가 하자.
# df2 = pd.DataFrame()
# df2 = df2.append({"movieCd":"", "movieNm":"", "movieNmEn":"", "openDt":"", "peopleNm":""}, ignore_index=True)
# print df2
# moviecnt =  len(movieIdData["movieListResult"]["movieList"])
# for i in range(0, num)
#
#    df["movieCd"][i] = movieIdData["movieListResult"][i].[movieCd] ...요딴식으로 필드별 추가
#    또는
#    df.ix[i, "movieCd" = movieIdData["movieListResult"][i].[movieCd] ...요딴식으로 필드별 추가


# ********************************************
# -- 컬럼 추가
# ********************************************
print("-" * 100 + "{[12]}") # ----- #

df["year"] = df["openDt"].apply(lambda x:(x)[:4])
df["month"] = df["openDt"].apply(lambda x:(x)[4:6])
df["day"] = df["openDt"].apply(lambda x:(x)[6:])

print df


# ********************************************
# -- 인덱스 생성 및 컬럼 순서 변경, 엑셀 출력
# ********************************************
print("-" * 100 + "{[13]}") # ----- #

# -- 인덱스 생성(기존 컬럼을 인덱스로)
df = df.set_index("movieCd")
print df

# -- 컬럼 순서 변경
df = df[["movieNm","movieNmEn", "openDt", "peopleNm", "day", "month", "year"]]
print df

# -- 엑셀 출력
excelOutPath = "C:\\samples\\movie_new.xlsx"
writer = pd.ExcelWriter(excelOutPath, engine="xlsxwriter")

df.to_excel(writer, sheet_name="Sheet1", index=True)

writer.save()