Lesson 14 - Merging datasets

import pandas as pd url = "https://raw.githubusercontent.com/austinlasseter/hosting_some_files/main/pandas_files/eu-govt-bonds.tsv" df = pd.read_csv(url, sep='\t')

Run to view results

df.head()

Run to view results

"2020M01" in df.columns

Run to view results

"2020M01 " in df.columns

Run to view results

df.columns

Run to view results

df.columns = [c.strip() for c in df.columns]

Run to view results

print(df.columns)

Run to view results

df["2020M01"].head()

Run to view results

df["country_code"] = df["int_rt,geo\\time"].str[-2:] df.drop("int_rt,geo\\time", axis=1, inplace=True) df.head()

Run to view results

df.columns

Run to view results

df.dtypes

Run to view results

df["2019M02"].sort_values().head()

Run to view results

df["2019M02"].sort_values().tail()

Run to view results

url2 = "https://raw.githubusercontent.com/austinlasseter/hosting_some_files/main/pandas_files/country-codes.csv"

Run to view results

df_codes = pd.read_csv(url2, encoding="latin-1") df_codes.head()

Run to view results

print(len(df_codes)) df_merged = df.merge(df_codes, left_on=["country_code"], right_on=["Alpha-2 code"], how="left") print(len(df_merged)) df_merged.drop(["Alpha-2 code", "Alpha-3 code"], axis=1, inplace=True) df_merged.head()

Run to view results

7. Check for missing data Is there any missing data in any of the columns? Which country codes were you not able to match to a row in the reference file?

df_merged.isnull().sum()

Run to view results

df_merged[df_merged["Country"].isnull()]

Run to view results

df_merged.loc[df_merged["country_code"] == "UK", "Country"] = "United Kingdom"

Run to view results

df_merged["country_code"].head()

Run to view results

jan_rates = df_merged[["Country", "2020M01"]].sort_values("2020M01")

Run to view results

print(jan_rates.head())

Run to view results

print(jan_rates.tail())

Run to view results

jan_rates.set_index("Country").agg(["idxmin", "idxmax"])

Run to view results

df_merged['2020M01'] = pd.to_numeric(df_merged['2020M01'], errors='coerce') df_merged['2019M02'] = pd.to_numeric(df_merged['2019M02'], errors='coerce')

Run to view results

df_merged['diff'] = abs(df_merged['2020M01'] - df_merged['2019M02']) df_merged.sort_values('diff').head(1)

Run to view results

df_merged.to_csv("eu-govt-bonds-cleanes.csv", index=False)

Run to view results

import pandas as pd # Supongamos que df_merged es tu DataFrame # Primero, crea la columna "Country" combinando las columnas "country_code" y "Country" df_merged["Country"] = df_merged["country_code"] + " - " + df_merged["Country"] # A continuación, establece "Country" como el índice y calcula la media y la mediana result = df_merged.set_index("Country").agg(["mean", "median"], axis=1) # Imprime el resultado print(result)

Run to view results

df_merged.columns

Run to view results

df_merged.set_index("Country",inplace=True)

Run to view results

jan_rates["Country"].head()

Run to view results

df_merged.set_index(["country_code", "Country"]).mean(axis=1)

Run to view results

df_merged['Country'] = pd.to_numeric(df['Country'], errors='coerce')

Run to view results

df_staked= ( df_merged .set_index(["country_code", "Country"]) .stake() .reset_index() .rename(columns={"level_2": "month", 0:"rate"}) ) df_staked.head()

Run to view results

df.groupby("Country")["rate"].agg(["mean", "median"])

Run to view results