Free Python

How to Combine Two Timeseries Together Into Single Pandas DataFrame in Python

Aug 22, 2022 · by CryptoDataDownload

Combining Timeseries DataFrames

From time to time, you may run into situations where you are going to want to combine timeseries from separate files into the same file. We will use data from FTX to combine hourly spot BTC/USDT timeseries with hourly BTC Perpetual futures data in the below code. The ability to combine timeseries on a common index (the timestamp) will open doors to other analysis and the creation of all new timeseries. The most key step is ensuring that the index is set properly. Data is loaded from our respective FTX page. We use Pandas Dataframes to 1) change the index 2) drop unimportant columns 3) rename our remaining column 4) join our timeseries together 5) add a human-readable date time to the dataframe

Live codebase Python

# CryptoDataDownload
# No Express Warranty
import pandas as pd
import ssl

# change the ssl state so we do not see an error for https:// content
ssl._create_default_https_context = ssl._create_unverified_context

def main():
    """Call one function to load and combine into one final dataframe
    """
    df = load_data_sets()

def load_data_sets():
    """Function will load the FTX spot timeseries and the FTX futures timeseries and combine into one pandas dataframe,
    we will join values using the same timestamps
    """

spot = pd.read_csv("https://www.cryptodatadownload.com/cdd/FTX_BTCUSDT_1h.csv", skiprows=1)
    futures = pd.read_csv("https://www.cryptodatadownload.com/cdd/FTX_Futures_BTCPERP_1h.csv", skiprows=1)

# change the index of each spot/futures dataframe to be the unix column
    spot.set_index('unix', inplace=True)
    futures.set_index("unix", inplace=True)

# drop every other column except the close price, unix is the index
    spot.drop(columns=['date', 'symbol', 'open', 'high', 'low', 'Volume BTC', 'Volume USDT'], inplace=True)
    futures.drop(columns=['date', 'symbol', 'open', 'high', 'low', 'Volume BTC', 'Volume USDT'], inplace=True)

# rename our "close" column in each to what it represents
    spot.rename(columns={'close': 'BTC Spot'}, inplace=True)
    futures.rename(columns={'close': 'BTC Perp'}, inplace=True)

# join where there is a unix timestamp for spot, reset the index back to int ID afterwards
    df = spot.join(futures, how='inner').reset_index()

# add a column for the date in human readable format
    df['date'] = pd.to_datetime(df['unix'], unit='ms')
    return df

if __name__ == "__main__":
    main()

Notice: Information contained herein is not and should not be construed as an offer, solicitation, or recommendation to buy or sell securities. The information has been obtained from sources we believe to be reliable; however no guarantee is made or implied with respect to its accuracy, timeliness, or completeness. Author does not own the any crypto currency discussed. The information and content are subject to change without notice. CryptoDataDownload and its affiliates do not provide investment, tax, legal or accounting advice.

This material has been prepared for informational purposes only and is the opinion of the author, and is not intended to provide, and should not be relied on for, investment, tax, legal, accounting advice. You should consult your own investment, tax, legal and accounting advisors before engaging in any transaction. All content published by CryptoDataDownload is not an endorsement whatsoever. CryptoDataDownload was not compensated to submit this article. Please also visit our Privacy policy; disclaimer; and terms and conditions page for further information.

THE PERFORMANCE OF TRADING SYSTEMS IS BASED ON THE USE OF COMPUTERIZED SYSTEM LOGIC. IT IS HYPOTHETICAL. PLEASE NOTE THE FOLLOWING DISCLAIMER. CFTC RULE 4.41: HYPOTHETICAL OR SIMULATED PERFORMANCE RESULTS HAVE CERTAIN LIMITATIONS. UNLIKE AN ACTUAL PERFORMANCE RECORD, SIMULATED RESULTS DO NOT REPRESENT ACTUAL TRADING. ALSO, SINCE THE TRADES HAVE NOT BEEN EXECUTED, THE RESULTS MAY HAVE UNDER-OR-OVER COMPENSATED FOR THE IMPACT, IF ANY, OF CERTAIN MARKET FACTORS, SUCH AS LACK OF LIQUIDITY. SIMULATED TRADING PROGRAMS IN GENERAL ARE ALSO SUBJECT TO THE FACT THAT THEY ARE DESIGNED WITH THE BENEFIT OF HINDSIGHT. NO REPRESENTATION IS BEING MADE THAT ANY ACCOUNT WILL OR IS LIKELY TO ACHIEVE PROFIT OR LOSSES SIMILAR TO THOSE SHOWN. U.S. GOVERNMENT REQUIRED DISCLAIMER: COMMODITY FUTURES TRADING COMMISSION. FUTURES AND OPTIONS TRADING HAS LARGE POTENTIAL REWARDS, BUT ALSO LARGE POTENTIAL RISK. YOU MUST BE AWARE OF THE RISKS AND BE WILLING TO ACCEPT THEM IN ORDER TO INVEST IN THE FUTURES AND OPTIONS MARKETS. DON’T TRADE WITH MONEY YOU CAN’T AFFORD TO LOSE. THIS IS NEITHER A SOLICITATION NOR AN OFFER TO BUY/SELL FUTURES OR OPTIONS. NO REPRESENTATION IS BEING MADE THAT ANY ACCOUNT WILL OR IS LIKELY TO ACHIEVE PROFITS OR LOSSES SIMILAR TO THOSE DISCUSSED ON THIS WEBSITE. THE PAST PERFORMANCE OF ANY TRADING SYSTEM OR METHODOLOGY IS NOT NECESSARILY INDICATIVE OF FUTURE RESULTS.