pandas concatenate Date value along with other values-CodePudding

I am pulling forex data and the resulting data that I get back from the call looks like this:

{"code":"EUR.FOREX","timestamp":1666980480,"gmtoffset":0,"open":1.0034,"high":1.0071,"low":1,"close":1.0054,"volume":0,"previousClose":0.9909,"change":0.0145,"change_p":1.4633}

Since there is no simple date value in the result, I want to use python to get the current date and pass in that value.

I am breaking "code" into two dataframes and no issues there It is only when I am trying to concatenate everything together that I get the following error:

TypeError: cannot concatenate object of type '<class 'datetime.date'>'; only Series and DataFrame objs are valid

Here is the code snippet. It appears that I have to convert "Date" into a series or dataframe. Thank you.

 import pandas as pd
 from datetime import date, timedelta
 from datetime import datetime

 today = date.today()

 currencies = ["USD","EUR","RUB"]

 for ex in currencies:

    df = pd.read_csv(f'https://eodhistoricaldata.com/api/real-time/{ex}.FOREX? 
     api_token=99999.99999')

        if not df.empty:

        combined = df['code'].astype(str)
        df3 = combined.str.split('.').str[0]
        df4 = combined.str.split('.').str[1]
        print(df3)
        print(df4)
        print(today)
        Date = today
        Open = df['open']
        High = df['high']
        Low = df['low']
        Close = df['close']
        Volume = df['volume']

        total_df = pd.concat([df3, df4, Date, Open, High, Low, Close, Volume],
                         axis=1, keys=['Ticker', 'Exchange', 'Date', 'Open', 'High', 
                         'Low', 'Close', 'Volume'])

CodePudding user response：

Yes, your idea is right. You can pass it as [df3, df4, pd.Series(Date), Open, ...]

The second thing I noticed is that you're not concatenating your total_df with the data you already have. So for each iteration you only get one row.

Here is a suggestion of how to change that I've simplified a few other lines:

import pandas as pd
from datetime import date

today = date.today()
currencies = ["USD", "EUR", "RUB"]
total_df = pd.DataFrame()

for ex in currencies:
    df = pd.read_csv(f'https://eodhistoricaldata.com/api/real-time/{ex}.FOREX?api_token=???')
    if not df.empty:
        df[["Ticker", "Exchange"]] = df["code"].str.split(".", expand=True)
        df["Date"] = today
        total_df = pd.concat([df, total_df])

mapping_column_names = {"open": "Open", "high": "High", "low": "Low", "close": "Close", "volume": "Volume"}
total_df.rename(columns=mapping_column_names, inplace=True)

total_df = total_df[['Ticker', 'Exchange', 'Date', 'Open', 'High', 'Low', 'Close', 'Volume']]


total_df
  Ticker Exchange        Date     Open     High    Low    Close  Volume
0    RUB    FOREX  2022-10-29  61.5000  61.5000  61.25  61.5000       0
0    EUR    FOREX  2022-10-29   1.0034   1.0071   1.00   1.0034       0
0    USD    FOREX  2022-10-29   1.0000   1.0000   1.00   1.0000       0

Let me know if you have any questions :)