如何将API响应格式转换为Pandas数据框？

huangapple
go评论101阅读模式
英文:

How to convert the API response format into Pandas Dataframe?

问题

  1. import requests
  2. import json
  3. import pandas as pd
  5. url = "https://statdb.luke.fi:443/PxWeb/api/v1/en/LUKE/02 Maatalous/04 Tuotanto/06 Lihantuotanto/02 Kuukausitilastot/02_Lihantuotanto_teurastamoissa_kk.px"
  7. payload = json.dumps({
  8.   "query": [
  9.     {
  10.       "code": "Muuttuja",
  11.       "selection": {
  12.         "filter": "item",
  13.         "values": [
  14.           "Lihantuotanto"
  15.         ]
  16.       }
  17.     },
  18.     {
  19.       "code": "Laji",
  20.       "selection": {
  21.         "filter": "item",
  22.         "values": [
  23.           "Lehmät"
  24.         ]
  25.       }
  26.     }
  27.   ],
  28.   "response": {
  29.     "format": "csv"
  30.   }
  31. })
  32. headers = {
  33.   'Content-Type': 'application/json',
  34.   'Cookie': 'rxid=710a361a-7044-494f-95b7-15261822712c'
  35. }
  37. response = requests.request("POST", url, headers=headers, data=payload)
  39. # Convert the text format data to a pandas data frame
  40. df = pd.read_csv(pd.compat.StringIO(response.text))
  42. # Set the column headers
  43. df.columns = ["Month", "Variable", "Cows 7) 8)"]
  45. # Now you have the data in a pandas data frame with the specified column headers
英文:

I have this code using which I am able to download data from API. But I don't know how to convert this to pandas data frame.

  1. import requests
  2. import json
  4. url = "https://statdb.luke.fi:443/PxWeb/api/v1/en/LUKE/02 Maatalous/04 Tuotanto/06 Lihantuotanto/02 Kuukausitilastot/02_Lihantuotanto_teurastamoissa_kk.px"
  6. payload = json.dumps({
  7.   "query": [
  8.     {
  9.       "code": "Muuttuja",
  10.       "selection": {
  11.         "filter": "item",
  12.         "values": [
  13.           "Lihantuotanto"
  14.         ]
  15.       }
  16.     },
  17.     {
  18.       "code": "Laji",
  19.       "selection": {
  20.         "filter": "item",
  21.         "values": [
  22.           "Lehmät"
  23.         ]
  24.       }
  25.     }
  26.   ],
  27.   "response": {
  28.     "format": "csv"
  29.   }
  30. })
  31. headers = {
  32.   'Content-Type': 'application/json',
  33.   'Cookie': 'rxid=710a361a-7044-494f-95b7-15261822712c'
  34. }
  36. response = requests.request("POST", url, headers=headers, data=payload)
  38. print(response.text)

The code returns a text format data. I need guidance on how to make a pandas data frame with this output.

For this particular data frame, the Column headers would be

"Month",

"Variable",

"Cows 7) 8)"

So on and So forth.

答案1

得分: 1

您可以使用StringIO来模仿文件样的对象，然后从中创建一个pd.DataFrame。此外，第一列中有一些奇怪的符号（），我认为您不需要它们。因此，请确保将第一列重命名。

  1. import requests
  2. import json
  3. import pandas as pd
  4. from io import StringIO
  6. url = "https://statdb.luke.fi:443/PxWeb/api/v1/en/LUKE/02 Maatalous/04 Tuotanto/06 Lihantuotanto/02 Kuukausitilastot/02_Lihantuotanto_teurastamoissa_kk.px"
  8. payload = json.dumps({
  9.     "query": [
  10.         {
  11.             "code": "Muuttuja",
  12.             "selection": {
  13.                 "filter": "item",
  14.                 "values": ["Lihantuotanto"]
  15.             }
  16.         },
  17.         {
  18.             "code": "Laji",
  19.             "selection": {
  20.                 "filter": "item",
  21.                 "values": ["Lehmät"]
  22.             }
  23.         }
  24.     ],
  25.     "response": {
  26.         "format": "csv"
  27.     }
  28. })
  30. headers = {'Content-Type': 'application/json', 'Cookie': 'rxid=710a361a-7044-494f-95b7-15261822712c'}
  32. response = requests.request("POST", url, headers=headers, data=payload)
  34. # 创建StringIO对象来模拟文件样的对象
  35. content_file = StringIO(response.text)
  37. # 创建pd.DataFrame
  38. df = pd.read_csv(content_file)
  40. # 清理列名
  41. df.rename(columns={'"Month"': 'Month'}, inplace=True)
  43. print(df.head())
英文:

You can use StringIO to mimic a file-like object and then create a pd.DataFrame from it. Additionally, there are some weird symbols in the first column (), which I expect you don't need. Therefore, make sure to rename the first column as well.

  1. import requests
  2. import json
  3. import pandas as pd
  4. from io import StringIO
  6. url = "https://statdb.luke.fi:443/PxWeb/api/v1/en/LUKE/02 Maatalous/04 Tuotanto/06 Lihantuotanto/02 Kuukausitilastot/02_Lihantuotanto_teurastamoissa_kk.px"
  8. payload = json.dumps({   "query": [
  9.     {
  10.       "code": "Muuttuja",
  11.       "selection": {
  12.         "filter": "item",
  13.         "values": [
  14.           "Lihantuotanto"
  15.         ]
  16.       }
  17.     },
  18.     {
  19.       "code": "Laji",
  20.       "selection": {
  21.         "filter": "item",
  22.         "values": [
  23.           "Lehmät"
  24.         ]
  25.       }
  26.     }   ],   "response": {
  27.     "format": "csv"   } })
  29. headers = {   'Content-Type': 'application/json',   'Cookie': 'rxid=710a361a-7044-494f-95b7-15261822712c' }
  31. response = requests.request("POST", url, headers=headers, data = payload)
  33. # Create StringIO object to mimic file-like object
  34. content_file = StringIO(response.text)
  36. # Create pd.DataFrame
  37. df = pd.read_csv(content_file)
  39. # Clean column name
  40. df.rename(columns = {'"Month"': 'Month'}, inplace = True)
  42. print(df.head())

huangapple
  • 本文由 发表于 2023年5月17日 21:26:05
  • 转载请务必保留本文链接：https://go.coder-hub.com/76272632.html
  • api
  • json
  • pandas
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定