Merging Data from WRDS with Local Data

[timhdesilva]

Hi,

I'm working with the wrds package in Python in order to download data from wrds. I have a local dataset called df that consists of identifiers and dates. I'm looking to merge this dataset with data on the WRDS server. If I was working with a SAS-WRDS connection, I would write the following code (after establishing a WRDS connection):

rsubmit;

proc upload data = df;
run;

proc sql;
create table result as select *
from df as a inner join crsp.dsf as b
on a.permno = b.permno and a.start <= b.date and a.end >= b.date
;
quit;

proc download data = result;
run;

endrsubmit;

Is there any way to accomplish something similar in python using the wrds package? I've tried to find a solution using params argument in the .raw_sql() function, but this doesn't allow me to implement the range join that I'd like to accomplish above. An alternative solution would be to write a loop where I do the merge for each individual row of df and then stack the datasets, but this takes much longer to run than I'd hoped.

Thanks in advance for any comments or suggestions!

[upenn-hughmac]

Hi Tim,

It's been a long time since you asked this question, and you've probably figured it out by now. But for others who may be interested, I'd suggest using f-strings (available in Python 3.6+) to create your raw SQL string between from df.date.max() and df.date.min(), and then use the Pandas DataFrame.merge() function to do the inner join.

Assuming you already have a df with at least permno (float) and date (datetime), try something like this:

df2 = df.merge(
    conn.raw_sql(
        f"select * from crsp.dsf where date between '{df.date.min()}' and '{df.date.max()}'",
        date_cols=["date"],
    ),
    on=["permno", "date"],
)

I hope that helps!

-Hugh

[timhdesilva]

Hi @upenn-hughmac,

Thanks for the response! Much appreciated. Your answer is great and is actually how I’ve been handling this now. One question - the problem with this technique is that it requires me to download a larger fraction of data from WRDS than I really need. When using CRSP data files, this isn’t a big issue. However, if I’m working with a much larger database (e.g. OptionMetrics) and only want price data from a specific option day, I have to download the all the OptionMetrics data and then merge.

Is there a way to get around this problem?

Thanks again very much for your help!

[Layla2001]

Hi Tim,

Also, a long time has passed. I have come up with the following way to solve this issue – converting the dataframe to SQL query. My context is in TRACE instead of OptionMetrics but I think they share similar logics to process. I am trying to get TRACE daily bond transactions for 7 days after a bond is offered.

def df_to_sql_query(df, table_alias, column_names):
    """
    Convert a DataFrame into a SQL query using VALUES clause.

    Parameters:
    df (pd.DataFrame): The DataFrame to be converted.
    table_alias (str): The alias for the virtual table in the SQL query.
    column_names (list of str): The column names for the virtual table.

    Returns:
    str: The SQL query string.
    """
    # Ensure the DataFrame column order matches the specified column names
    df = df[column_names]

    # Convert DataFrame rows to SQL VALUES format
    values = ", ".join(
        [f"({', '.join(map(repr, row))})" for row in df.itertuples(index=False, name=None)]
    )

    # Construct the SQL query
    query = f"""
    FROM (
        VALUES {values}
    ) AS {table_alias} ({', '.join(column_names)})
    """

    return query

offer_dt = issue1[["complete_cusip", "offering_date"]].rename(columns={"complete_cusip": "cusip_id"})
offer_dt["offering_date"] = offer_dt["offering_date"].dt.strftime("%Y-%m-%d")
offer_dt = offer_dt.dropna(subset=["offering_date"])

offer_dt_query = df_to_sql_query(offer_dt, "offer_dt", ["cusip_id", "offering_date"])

trace_window = db.raw_sql(
    f"""
    SELECT trace_clean.*, offer_dt.offering_date
    {offer_dt_query}
    JOIN wrdsapps.trace_enhanced_clean AS trace_clean
    ON offer_dt.cusip_id = trace_clean.cusip_id
    WHERE trace_clean.trd_exctn_dt <= TO_DATE(offer_dt.offering_date, 'YYYY-MM-DD') + interval '7' day
    """
)