0

I am trying to generate a report using python for Azure billing API. i am getting the response from WEBAPI in csv format. with follwoing code:

import requests
import pandas as pd
url=  
"https://consumption.azure.com/v2/enrollments/"+xxxxx+"/usagedetails/download? 
startTime="+startTime+"&endTime="+endTime
headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) 
AppleWebKit/537.36 (KHTML, like Gecko) Chrome/46.0.2490.80 Safari/537.36',
      'Authorization': 'Bearer {0}'.format(key)}
response=requests.get(url,headers=headers)
response_format=response.data.decode('utf-8-sig')
TESTDATA1=StringIO(response_format)
df1=pd.DataFrame([sub.split(",") for sub in TESTDATA1])

Now I have a question. where is my data what has column values but the column values are coming as 0,1,2,3 like . The actual column values are going into the 2nd row of the data-frame. how to fix this ?any leads will be highly appreciated.

Saige Zhang
  • 737
  • 1
  • 7
  • 18
vin123
  • 3
  • 6

1 Answers1

0

This is due to how you are initializing your DataFrame.

You can added the column names by doing 

df1.columns = df1.values[the row with column names]

then drop that row

VegardKT
  • 1,226
  • 10
  • 21
  • Hi @VegardKT, It Worked, Thanks for the quick response. But there is problem for me here. Some of the column has values { "ImageType": null, "ServiceType": null, "VMName": null, "VMProperties": null, "UsageType": "DataTrIn"} ( This is the entire column value) when i am doing the split(',') it is converting this single column into multiple . that is causing my data columns wrong. How can i get this through can you please help me on this. – vin123 Aug 07 '18 at 10:54
  • Then you need to manuall clean this line before you assign it as column values. seeing as your column names are quite specific it may be easier to just assign them manually? Or will they be different depending on the task of your program? – VegardKT Aug 07 '18 at 11:06
  • Hi @VegardKT, I am not sure how to clean this line as the data i am getting in csv format from the WebAPI. that i am trying to manipulate and sum it up. But due to this column spliting it is messing up. We can do it manually as they are same all the time. Could you please help me to get clean of it. As i am the beginner of the python – vin123 Aug 07 '18 at 11:12
  • if you want to do it manually, just do like this: df1.columns = ['a', 'b', 'c'] etc – VegardKT Aug 07 '18 at 11:13
  • So this problem is not with the header but the values themselves? – VegardKT Aug 07 '18 at 11:19
  • Yes, While i am doing a step in the code df1=pd.DataFrame([sub.split(",") for sub in TESTDATA1]) This is spliting the values based on the ',' but some of the column values have the , so , the column values are changing. Here is the format of my data from API https://imgur.com/a/5AVhkYc – vin123 Aug 07 '18 at 11:21
  • okay, I understand. Seeing as this is a different question you should first try to solve it yourself and if you are unable you should create a new question. (Wer are not suppsoed to use this comment field for debugging like this on other problems) If you feel like my answer solved your initial problem you can also choose to accept it if you wish. – VegardKT Aug 07 '18 at 11:23