# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Exercise - Data Management

import pandas as pd
import numpy as np

# 1. Load `src/original_data/chs_data.dta` into a pandas DataFrame. Transform it into a new dataframe that differs from the old one in the following aspects:
#     - It has the colum `"mom_id"` which of dtype integer and does not have the original column `"momid"`
#     - It does not contain observations where `"year"` is less than 1986 or above 2010 or where the year number is odd. 
#     - The index is set to `["child_id", "year"]` (both of dtype integer).

chs_data= pd.read_stata("chs_data.dta")
chs_data.head()

#Renaming variables, changing data types and replacing values
chs_data.rename(columns = {'momid':'mom_id','childid':'child_id'}, inplace = True)
chs_data = chs_data.astype({'mom_id':'int', 'child_id':'int'})

#Dropping rows
chs_data = chs_data[(chs_data.year >= 1986) & (chs_data.year <= 2010) & (chs_data.year%2 == 0)]
#Setting index
chs_data.set_index(['child_id','year'], inplace=True)
chs_data.head()

# 2. Load the `bpi` dataset, clean and transform it. This means that you generate a new dataset that differs from the old one by the following aspccts. You can vary the order in which you achieve these tasks as you wish.
#     - Missing values are coded as [pandas missings](https://pandas.pydata.org/pandas-docs/stable/user_guide/missing_data.html) instead of negative numbers. But for now stay away from the new nullable Integer dtypes because that can lead to problems with some file formats. 
#     - The index is the same as in the chs data. `"child_id"` can be generated from `"C0000100"` 
#     - All variables have readable names from `bpi_variable_info.csv`
#     - Only keep variables that are present in `bpi_variable_info.csv`
#     - The data is in long format

bpi = pd.read_stata("BEHAVIOR_PROBLEMS_INDEX.dta")
bpi.head()

bpi_var_info = pd.read_csv("bpi_variable_info.csv")
bpi_var_info.head()

#keep variables that are present in `bpi_variable_info.csv`
old_name = bpi_var_info["nlsy_name"] 
bpi = bpi[old_name] 
bpi.shape

#variables have readable names from `bpi_variable_info.csv`
new_name = bpi_var_info["readable_name"] + bpi_var_info["survey_year"]
bpi.rename(columns=dict(zip(old_name, new_name)), inplace=True)
bpi.head()

#Renaming time invariant variables and changing their data type
bpi.rename(columns = {'momidinvariant':'mom_id','childidinvariant':'child_id','birth_orderinvariant':'birth_order'},inplace = True)
bpi = bpi.astype({'mom_id':'int', 'child_id':'int'})
#Replacing negative values
bpi = bpi.replace(list(range(-10,0)), np.nan)
#Converting from wide to long format with child_id and year as indices
bpi_unique = bpi_var_info["readable_name"].iloc[3:].unique()
bpi = pd.wide_to_long(bpi, bpi_unique, i="child_id", j="year")
bpi

# 3. Merge the datasets from the two previous task. Keep all observations that are in the chs dataset but drop all that are only in the bpi dataset. Before you merge, check if there are any overlaps in column names between the two datasets. If so, solve them by renaming those columns before merging. 

df1 = pd.merge(chs_data, bpi, how="left", on=["child_id", "year"], suffixes=("_chs", "_bpi"))
df1

# 4. The dataset contains surprises such as categorical variables where you need numerical ones, variables that take values you did not expect or category labels that are similar but not identical across variables. Try to find good solutions for each of them. Also, use the following conventions:
#     - The answers 'sometimes true' and 'often true', are counted as 1, 
#     - 'not true' is counted as zero

#Replacing values
df1 = df1.replace(["Not True", "NOT TRUE", "Not true"],0)
df1 = df1.replace(["Sometimes True","SOMETIMES TRUE","Sometimes true",
                   "Often True","OFTEN TRUE","Often true"],1)
df1 = df1.replace(["NEVER ATTENDED SCHOOL","Never Attended School","Child has never attended school"],np.nan)
df1

StarcoderdataJupyterScriptsDedupFiltered