0

I use API, got many data but need to write only one to Pandas dataframe and then to csv file how can i do this? i need currency and rate columns only


import requests
import pandas as pd

url = 'https://api.apilayer.com/exchangerates_data/latest?base=EUR'

get_response = requests.get(url)

print(get_response.content)

reponse is


b'{\n    "success": true,\n    "timestamp": 1653291723,\n    "base": "EUR",\n    "date": "2022-05-23",\n    "rates": {\n        "AED": 3.891874,\n        "AFN": 96.332724,\n        "ALL": 120.12076,\n        "AMD": 486.326147,\n        "ANG": 1.910798,\n        "AOA": 440.146399,\n        "ARS": 125.559742,\n        "AUD": 1.49136,\n        "AWG": 1.907774,\n        "AZN": 1.796984,\n        "BAM": 1.958501,\n        "BBD": 2.140687,\n        "BDT": 92.747171,\n        "BGN": 1.955884,\n        "BHD": 0.399459,\n        "BIF": 2179.202426,\n        "BMD": 1.05958,\n        "BND": 1.461113,\n        "BOB": 7.300355,\n        "BRL": 5.170217,\n        "BSD": 1.060231,\n        "BTC": 3.4686139e-05,\n        "BTN": 82.245346,\n        "BWP": 12.83561,\n        "BYN": 3.578179,\n        "BYR": 20767.765076,\n        "BZD": 2.137314,\n        "CAD": 1.355568,\n        "CDF": 2124.457448,\n        "CHF": 1.030337,\n        "CLF": 0.032122,\n        "CLP": 886.340415,\n        "CNY": 7.067924,\n        "COP": 4208.651167,\n        "CRC": 711.74061,\n        "CUC": 1.05958,\n        "CUP": 28.078866,\n        "CVE": 110.417129,\n        "CZK": 24.584356,\n        "DJF": 188.746891,\n        "DKK": 7.440804,\n        "DOP": 58.552672,\n        "DZD": 154.438592,\n        "EGP": 19.350685,\n        "ERN": 15.893699,\n        "ETB": 55.044138,\n        "EUR": 1,\n        "FJD": 2.284134,\n        "FKP": 0.867087,\n        "GBP": 0.843796,\n        "GEL": 3.078048,\n        "GGP": 0.867087,\n        "GHS": 8.244159,\n        "GIP": 0.867087,\n        "GMD": 57.376242,\n        "GNF": 9372.913662,\n        "GTQ": 8.136894,\n        "GYD": 221.832492,\n        "HKD": 8.316976,\n        "HNL": 26.044688,\n        "HRK": 7.531387,\n        "HTG": 118.758621,\n        "HUF": 382.210602,\n        "IDR": 15541.387462,\n        "ILS": 3.544575,\n        "IMP": 0.867087,\n        "INR": 82.241939,\n        "IQD": 1547.570278,\n        "IRR": 44820.228002,\n        "ISK": 138.696976,\n        "JEP": 0.867087,\n        "JMD": 163.866665,\n        "JOD": 0.751235,\n        "JPY": 135.279211,\n        "KES": 123.494294,\n        "KGS": 84.628222,\n        "KHR": 4306.032696,\n        "KMF": 494.770756,\n        "KPW": 953.622101,\n        "KRW": 1339.844028,\n        "KWD": 0.324317,\n        "KYD": 0.883605,\n        "KZT": 451.435581,\n        "LAK": 14057.368731,\n        "LBP": 1603.386389,\n        "LKR": 376.408573,\n        "LRD": 161.585115,\n        "LSL": 16.867967,\n        "LTL": 3.128664,\n        "LVL": 0.640929,\n        "LYD": 5.0991,\n        "MAD": 10.616176,\n        "MDL": 20.305389,\n        "MGA": 4285.919145,\n        "MKD": 61.538618,\n        "MMK": 1962.989296,\n        "MNT": 3259.024764,\n        "MOP": 8.570086,\n        "MRO": 378.269824,\n        "MUR": 45.988019,\n        "MVR": 16.344051,\n        "MWK": 866.149081,\n        "MXN": 21.058086,\n        "MYR": 4.645208,\n        "MZN": 67.632728,\n        "NAD": 16.868837,\n        "NGN": 439.852629,\n        "NIO": 37.997351,\n        "NOK": 10.249848,\n        "NPR": 131.573277,\n        "NZD": 1.638153,\n        "OMR": 0.40741,\n        "PAB": 1.060346,\n        "PEN": 3.9603,\n        "PGK": 3.736248,\n        "PHP": 55.384769,\n        "PKR": 213.235927,\n        "PLN": 4.617629,\n        "PYG": 7251.300917,\n        "QAR": 3.857965,\n        "RON": 4.946967,\n        "RSD": 117.499439,\n        "RUB": 62.329807,\n        "RWF": 1088.946708,\n        "SAR": 3.974436,\n        "SBD": 8.607563,\n        "SCR": 14.438411,\n        "SDG": 473.497309,\n        "SEK": 10.486142,\n        "SGD": 1.457722,\n        "SHP": 1.459462,\n        "SLL": 13581.160767,\n        "SOS": 618.272012,\n        "SRD": 22.260709,\n        "STD": 21931.163629,\n        "SVC": 9.277651,\n        "SYP": 2662.141945,\n        "SZL": 16.788471,\n        "THB": 36.311502,\n        "TJS": 13.261298,\n        "TMT": 3.708529,\n        "TND": 3.243904,\n        "TOP": 2.459973,\n        "TRY": 16.843815,\n        "TTD": 7.198816,\n        "TWD": 31.379439,\n        "TZS": 2464.582977,\n        "UAH": 31.322712,\n        "UGX": 3864.924954,\n        "USD": 1.05958,\n        "UYU": 42.945451,\n        "UZS": 11764.462107,\n        "VEF": 226570195087.7927,\n        "VND": 24545.167244,\n        "VUV": 121.073594,\n        "WST": 2.73302,\n        "XAF": 656.781309,\n        "XAG": 0.048294,\n        "XAU": 0.000571,\n        "XCD": 2.863568,\n        "XDR": 0.790993,\n        "XOF": 656.781309,\n        "XPF": 120.315233,\n        "YER": 265.159952,\n        "ZAR": 16.77781,\n        "ZMK": 9537.495082,\n        "ZMW": 18.060768,\n        "ZWL": 341.18428\n    }\n}\n'
Improve this question
1
  • Please be aware that you shared your api key in this question. An example of the response would have been enough to understand your requirements. Commented May 23, 2022 at 7:52

4 Answers 4

Reset to default
2

first load data as DataFrame

import json
df = json.loads(get_response.content)

second, choose the rates (currency is index) and save to csv

df[["base","rates"]].to_csv("path/to/csv")
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1 
import requests
import pandas as pd
import json
url = 'https://api.apilayer...'

get_response = requests.get(url)
# Parse the response to a dict
response_dict = get_response.json()
# Turn the rates nodes into a dataframe
data_items = response_dict['rates'].items()
data_list = list(data_items)
df = pd.DataFrame(data_list,columns=['currency','rate'])
# Export to csv
df.to_csv('export.csv')
Improve this answer

Comments

1

IIUC, you can use:

import json
df = pd.DataFrame(json.loads(get_response.content.decode('utf-8')))[['base', 'rates']]

# for export to csv
# df.to_csv('filename.csv')

output:

    base        rates
AED  EUR     3.893237
AFN  EUR    96.366461
ALL  EUR   120.162829
AMD  EUR   486.496485
ANG  EUR     1.911467
..   ...          ...
YER  EUR   265.252922
ZAR  EUR    16.796411
ZMK  EUR  9540.830787
ZMW  EUR    18.067093
ZWL  EUR   341.303769

[168 rows x 2 columns]
Improve this answer

6 Comments

@irenavox don't forget to decode the string as your API returns bytes, I used utf-8 here by default but ensure your have the correct encoding ;)
just curious, how to treat this timestamp? "timestamp": 1653291723
@NoobVB what do you mean? To get a datetime? You'd need to use pd.to_datetime
close, :) but it spits out a strange timestamp 1970-01-01 00:00:01.653293823
@NoobVB depends on what the number means! Here I guess seconds (not milliseconds): pd.to_datetime(1653291723, unit='s') -> Timestamp('2022-05-23 07:42:03')
|
0 

get_response = requests.get(url)

# print(get_response.content)

in_json = get_response.json()

# print(in_json)

fd = pd.DataFrame(in_json)

y = fd[['rates']]

print(y)

solved but looks not nice and maybe there a more simple solution i converted to json - then to dataframe - then i will convert to csv

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.