<jupyter_start><jupyter_text>Loading the Raw Data<jupyter_code>pd.read_excel?
macro_df = pd.read_excel("../data/Residential_data_for_trainer.xlsx",
                         sheet_name="Macro",
                         na_values=[0])
macro_df.info()
macro_df.head()
macro_df.shape
households_df = pd.read_excel("../data/Residential_data_for_trainer.xlsx",
                              sheet_name="Households")
households_df.info()
households_df.head()
households_df.shape
_economies = (macro_df.loc[:, "Economies"]
                      .unique())

_economy_dfs = []
for _economy in _economies:
    _df = pd.read_excel("../data/Residential_data_for_trainer.xlsx",
                        sheet_name=_economy,
                        converters={"Activity": lambda s: s.strip(), "Technology": lambda s: s.strip()},
                        na_values=[0])
    
    # filter out the rows with actual data (don't need to use the egeda data)
    _filter = (_df.loc[:, ["Activity", "Technology"]]
                  .notna()
                  .any(axis=1))
    _clean_df = _df.loc[_filter, :]
    
    _economy_dfs.append(_clean_df)
    
economy_df = pd.concat(_economy_dfs, ignore_index=True)
economy_df.info()
economy_df.head()
economy_df.tail()<jupyter_output><empty_output><jupyter_text>Make the data "tidy"<jupyter_code>macro_df.head()
_wide_df.head()
_long_df.head()
_wide_df = (macro_df.drop(["Parameter", "Unit", "Unnamed: 75"], axis=1)
                    .rename(columns={"Code_Name": "variable", "Economies": "economy"}))

_long_df = pd.melt(_wide_df, id_vars=["economy", "variable"], var_name="year")

tidy_macro_df = _long_df.pivot_table(index=["economy", "year"], columns="variable", values="value")
tidy_macro_df.info()
tidy_macro_df.head()
tidy_macro_df.tail()
tidy_macro_df.to_csv("../data/interim/tidy-macro.csv")
_wide_df = (households_df.drop(["Parameter"], axis=1)
                         .rename(columns={"Code_Name": "variable", "Economies": "economy"}))

_long_df = pd.melt(_wide_df,
                   id_vars=["economy", "variable"],
                   var_name="year")

tidy_households_df = _long_df.pivot_table(index=["economy", "year"],
                                          columns="variable",
                                          values="value")


tidy_households_df.info()
tidy_households_df.head()
tidy_households_df.tail()
tidy_households_df.to_csv("../data/interim/tidy-households.csv")
_wide_df = (economy_df.drop(["Unit"], axis=1)
           .rename(columns={"Economy": "economy", "Fuel": "fuel", "Activity": "activity", "Technology": "technology"})
           .groupby(["economy", "activity"])
           .sum()
           .replace(0.0, np.nan)
           .reset_index())

_long_df = pd.melt(_wide_df, 
                   id_vars=["economy", "activity"],
                   var_name="year")

tidy_economy_df = _long_df.pivot_table(index=["economy", "year"],
                                       columns=["activity"],
                                       values="value")
tidy_economy_df.info()
tidy_economy_df.head()
tidy_economy_df.tail()
tidy_economy_df.to_csv("../data/interim/tidy-economies.csv")<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup