Get market

'''
Link: https://developer.mapquest.com/documentation/traffic-api/markets/get
The Traffic Markets Request returns a structured list of regions, or "markets", for which MapQuest provides Traffic data.
'''

api_key = "..."
market = !curl -X GET "http://www.mapquestapi.com/traffic/v2/markets?key=api_key"

def Market_Json_To_DF(s):
  # s: market data đã GET từ MapQuest ở dạng thô
  # return df: trả về một DataFrame gồm các thuộc tính của từng incidents
  states = {
    'AK': 'Alaska','AL': 'Alabama','AR': 'Arkansas', 'AZ': 'Arizona', 'CA': 'California',
    'CO': 'Colorado', 'CT': 'Connecticut', 'DC': 'District of Columbia', 'DE': 'Delaware',
    'FL': 'Florida', 'GA': 'Georgia', 'HI': 'Hawaii', 'IA': 'Iowa', 'ID': 'Idaho',
    'IL': 'Illinois', 'IN': 'Indiana', 'KS': 'Kansas', 'KY': 'Kentucky', 'LA': 'Louisiana',
    'MA': 'Massachusetts', 'MD': 'Maryland', 'ME': 'Maine', 'MI': 'Michigan',
    'MN': 'Minnesota', 'MO': 'Missouri', 'MS': 'Mississippi', 'MT': 'Montana',
    'NC': 'North Carolina','ND': 'North Dakota','NE': 'Nebraska','NH': 'New Hampshire',
    'NJ': 'New Jersey','NM': 'New Mexico','NV': 'Nevada','NY': 'New York',
    'OH': 'Ohio','OK': 'Oklahoma','OR': 'Oregon','PA': 'Pennsylvania',
    'RI': 'Rhode Island','SC': 'South Carolina','SD': 'South Dakota','TN': 'Tennessee',
    'TX': 'Texas','UT': 'Utah','VA': 'Virginia','VT': 'Vermont',
    'WA': 'Washington','WI': 'Wisconsin','WV': 'West Virginia', 'WY': 'Wyoming'
  }
  market_data = json.loads(s[0])
  df = []
  for i in range(len(market_data['markets'])):
    markets_key = list(market_data['markets'][i].keys())
    markets_key.remove('boundingBox')
    ul_bbox_key = market_data['markets'][i]['boundingBox']['ul'].keys()
    lr_bbox_key = market_data['markets'][i]['boundingBox']['lr'].keys()
    d = dict()
    for key in markets_key:
      d.update({key:market_data['markets'][i][key]})
      if key == 'state':
        try:
          d.update({'fullstate':states[market_data['markets'][i][key]]})
        except:
          pass
    for key in ul_bbox_key:
      d.update({'ul_'+key:market_data['markets'][i]['boundingBox']['ul'][key]})
    for key in lr_bbox_key:
      d.update({'lr_'+key:market_data['markets'][i]['boundingBox']['lr'][key]})
    d.update({'Copyright': market_data['info']['copyright']['text']})
    d.update({'CopyrightImage': market_data['info']['copyright']['imageUrl']})
    d.update({'Status': market_data['info']['statuscode']})
    d.update({'Message': market_data['info']['messages']})
    df.append(d)
  return pd.DataFrame(df)

market_df = Market_Json_To_DF(market)
market_df.to_excel("market_infor.xlsx", index = False)