Wsrtjtyk

I'm attempting to use the BEA's API to query income data. API Instructions - https://apps.bea.gov/api/_pdf/bea_web_service_api_user_guide.pdf

My goal is to parse the XML generated and turn it into a dataframe, with columns for the different years.

The issue that I run into is that the way I am parsing the data, it is in a "melted" format, where I want individual columns for the years and the Income data for those years in each of those columns.

How can I accomplish this? Below is the code that I am using. It requires that you sign up for an API key via email and enter it after "UserID" in the URL below.

bea_income = 'https://apps.bea.gov/api/data/?UserID=ENTERYOURAPIKEY&method=GetData&'

'datasetname=RegionalIncome&TableName=RPI2&LineCode=2&Year=2014,2015,2016&GeoFips=MSA&ResultFormat=xml'



bea_inc_request = requests.get(bea_income, headers={'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36',

                                                'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8'})

bea_inc_html = bea_inc_request.content

bea_inc_soup = BeautifulSoup(bea_inc_html, 'xml')



MSA = 

TimePeriod = 

Income = 

GeoFips = 



for i in range(len(bea_inc_soup.Results.find_all('Data'))):

    MSA.append(bea_inc_soup.Results.find_all('Data')[i]['GeoName'])

    GeoFips.append(bea_inc_soup.Results.find_all('Data')[i]['GeoFips'])

    Income.append(bea_inc_soup.Results.find_all('Data')[i]['DataValue'])

    TimePeriod.append(bea_inc_soup.Results.find_all('Data')[i]['TimePeriod'])





income_data = pd.DataFrame({'MSA':MSA, 'FIPS':GeoFips,  'Year':TimePeriod, 'Income':Income})



                                           MSA  FIPS    Year    Income

0   Abilene, TX (Metropolitan Statistical Area) 10180   2014    41818

1   Abilene, TX (Metropolitan Statistical Area) 10180   2015    41651

2   Abilene, TX (Metropolitan Statistical Area) 10180   2016    40409

3   Akron, OH (Metropolitan Statistical Area)   10420   2016    45448

4   Akron, OH (Metropolitan Statistical Area)   10420   2015    45298