<jupyter_start><jupyter_text>Wrangle War Participant Data Input Datasets- `Inter-StateWarData_v4.0.csv`- `INTRA-STATE WARS v5.1 CSV.csv`- `Extra-StateWarData_v4.0.csv`- `Non-StateWarData_v4.0.csv`- `polity.csv` Output Datasets- `war_participants.csv`<jupyter_code>import pandas as pd
import numpy as np
raw_data_path = "../data/raw/"
processed_data_path = "../data/processed/"
dfInterStateWar = pd.read_csv(raw_data_path+"Inter-StateWarData_v4.0.csv", encoding='utf-8', na_values=[-7, -8, -9], dtype={"WarNum": str, "TransFrom": str, "TransTo": str})
dfIntraStateWar = pd.read_csv(raw_data_path+"INTRA-STATE_State_participants v5.1 CSV.csv", encoding='latin-1', na_values=[-7, -8, -9], dtype={"WarNum": str, "CcodeA": "Int64", "CcodeB": "Int64"})
dfExtraStateWar = pd.read_csv(raw_data_path+"Extra-StateWarData_v4.0.csv", encoding='latin-1', na_values=[-7, -8, -9], dtype={"WarNum": str, "Interven": bool, "TransFrom": str, "TransTo": str})
dfNonStateWar = pd.read_csv(raw_data_path+"Non-StateWarData_v4.0.csv", encoding='utf-8', na_values=[-7, -8, -9], dtype={"WarNum": str, "TransFrom": str, "TransTo": str})
dfPolities = pd.read_csv(processed_data_path+"polity.csv", encoding='utf-8')<jupyter_output><empty_output><jupyter_text>create "war_participants" tabletable creation statement```class War_Participants(Base):    __tablename__ = "war_participants"    war = Column(String(5), primary_key=True)    polity = Column(Integer(5), primary_key=True)    start_date = Column(Date, primary_key=True)    start_date_prec = Column(Text)    end_date = Column(Date)    end_date_prec = Column(Text)    side = Column(String(1))    is_initiator = Column(Boolean)    outcome = Column(Text)    deaths = Column(Integer)    __table_args__ = (        ForeignKeyConstraint(["war"], ["war.id"]),        ForeignKeyConstraint(["polity"], ["polity.id"]),    )``` To Dos- [x] Convert all sets of date-component columns to one date column + precision column- [ ] Only one column for polity ids- [ ] Fill in polity IDs for non-state actors- [ ] Standardize outcome codes- [ ] Standardize initiator codes- [ ] Standardize side codes NotesUnits of observation- Interstate War: WarNum + ccode + Side- Intrastate War: WarNum + CcodeA + CcodeB- Extrastate War: WarNum + ccode1 + ccode2- Nonstate War: WarNum Deaths- need to figure out for each dataset what unit of measurement deaths are recorded as- for 2 datasets, deaths recorded for the side - may be more than 1 state- for 2 datasets, deaths recorded for the state- need to aggregate up and report deaths for each side- so, new table with unit of measurement: war + side- can also include this extra military force info per side for intrastate warInterstate- BatDeaths = deaths for state participantIntrastate- DeathsA = deaths for side A- DeathsB = deaths for side BNonstate- SideADeaths = deaths for side a- SideBBeaths = deaths for side b- TotalCombatDeaths = deaths for side a + side b    - may have data even if side a or b is unknownExtrastate- BatDeaths = deaths for state participant- NonState Deaths = deaths for nonstate participant Outcome CodesIntegrated:- 1 = "won"- 2 = "lost"- 3 = "compromise"- 4 = "transitioned"- 5 = "war ongoing as of (last updated)"- 6 = "stalemate"- 7 = "conflict continues at below war level"- 8 = "changed sides"Interstate:- 1 = Winner- 2 = Loser- 3 = Compromise/Tied- 4 = The war was transformed into another type of war - 5 = The war is ongoing as of 12/31/2007- 6 = Stalemate- 7 = Conflict continues at below war level- 8 = changed sidesIntrastate:- 1 = Side A wins; - 2 = Side B wins; - 3 = Compromise; - 4 = The war was transformed into another type of war; - 5 = The war is ongoing as of 12/31/2014; - 6 = Stalemate; - 7 = Conflict continues at below war levelNonstate:- 1 - Side A wins- 2 - Side B wins- 3 - Compromise- 4 - The war was transformed into another type of war - 5 - The war is ongoing as of 12/31/2007- 6 - Stalemate- 7 - Conflict continues at below war levelExtrastate:- 1 – Side A wins- 2 - Side B wins- 3 - Compromise- 4 - The war was transformed into another type of war - 5 - The war is ongoing as of 12/31/2007- 6 - Stalemate- 7 - Conflict continues at below war level  common functions<jupyter_code>def process_dates(df, start_year, start_month, start_day, end_year, end_month, end_day):
    """
    For a given dataframe, with the column names provided for the start/end year/month/day,
    create columns for the date precision and the actual date
    """
    df["start_date_prec"] = "day"
    df["start_date_prec"] = df["start_date_prec"]\
        .where(df[start_day].notna(), "month")\
        .where(df[start_month].notna(), "year")

    df["end_date_prec"] = "day"
    df["end_date_prec"] = df["end_date_prec"]\
        .where(df[end_day].notna(), "month")\
        .where(df[end_month].notna(), "year")

    df[start_day] = df[start_day].fillna(1).astype(int)
    df[start_month] = df[start_month].fillna(1).astype(int)
    df[start_year] = df[start_year].fillna(1).astype(int)

    df[end_day] = df[end_day].fillna(1).astype(int)
    df[end_month] = df[end_month].fillna(1).astype(int)
    df[end_year] = df[end_year].fillna(1).astype(int)

    df["start_date"] = pd.to_datetime(df[[start_year, start_month, start_day]].rename(columns={start_year:"year", start_month:"month", start_day:"day"}), errors="coerce")
    df["end_date"] = pd.to_datetime(df[[end_year, end_month, end_day]].rename(columns={end_year:"year", end_month:"month", end_day:"day"}), errors="coerce")
    
    df = df.drop(columns = [start_year, start_month, start_day, end_year, end_month, end_day])
    return df<jupyter_output><empty_output><jupyter_text>Interstate War<jupyter_code>dfInterStateWar.head()
dfInterStateWar.duplicated(['WarNum','ccode','Side']).sum()
dfInterStateWar.columns
dfInterPar_dates1 = dfInterStateWar[['WarNum', 'ccode', 'Side','StartMonth1', 'StartDay1', 'StartYear1', 'EndMonth1', 'EndDay1', 'EndYear1', 'Initiator', 'Outcome', 'BatDeath']]\
.drop_duplicates().dropna(subset=["StartYear1"])
dfInterPar_dates1_proc = process_dates(dfInterPar_dates1, "StartYear1", "StartMonth1", "StartDay1", "EndYear1", "EndMonth1", "EndDay1")
dfInterPar_dates2 = dfInterStateWar[['WarNum', 'ccode', 'Side', 'StartMonth2', 'StartDay2', 'StartYear2', 'EndMonth2', 'EndDay2', 'EndYear2', 'Initiator', 'Outcome', 'BatDeath']]\
.drop_duplicates().dropna(subset=["StartYear2"])
dfInterPar_dates2_proc = process_dates(dfInterPar_dates2, "StartYear2", "StartMonth2", "StartDay2", "EndYear2", "EndMonth2", "EndDay2")
dfInterPar = pd.concat([dfInterPar_dates1_proc, dfInterPar_dates2_proc])
dfInterPar
dfInterPar = dfInterPar.rename(columns={"WarNum": "war", "ccode": "polity"})
inter_outcome_map = {1: "won", 
                     2: "lost", 
                     3: "compromised", 
                     4: "war transitioned", 
                     5: "war ongoing as of war_type.last_updated", 
                     6: "stalemate", 
                     7: "conflict continues at below war level", 
                     8: "changed sides",
                    }
dfInterPar["is_initiator"] = dfInterPar["Initiator"].map({1:True, 2:False})
dfInterPar["side"] = dfInterPar["Side"].map({1:"A", 2:"B"})
dfInterPar["deaths"] = dfInterPar["BatDeath"].astype("Int64")
dfInterPar["outcome"] = dfInterPar["Outcome"].map(inter_outcome_map)
dfInterPar
dfInterPar_final = dfInterPar[["war", "polity", "start_date", "start_date_prec", "end_date", "end_date_prec", "side", "is_initiator", "outcome", "deaths"]]
dfInterPar_final
dfInterPar_final.duplicated(subset=["war", "polity", "start_date"]).sum()<jupyter_output><empty_output><jupyter_text>Intrastate War<jupyter_code>dfIntraStateWar
dfIntraStateWar.columns
dfIntraStateWar_dates1 = dfIntraStateWar[['WarNum', 'CcodeA', 'SideA', 'CcodeB', 'SideB', 'StartMo1', 'StartDy1', 'StartYr1', 'EndMo1', 'EndDy1', 'EndYr1', 'WDuratDays', 'WDuratMo', 'Initiator', 'Outcome', 'Deaths A', 'Deaths B', 'TotalBDeaths', 'SideAPeakTotForces', 'SideAPeak TheatForces', 'SideBPeakTotForces', 'SideBPeakTheatForces']]\
.drop_duplicates().dropna(subset=["StartYr1"])
dfIntraStateWar_dates1_proc = process_dates(dfIntraStateWar_dates1, "StartYr1", "StartMo1", "StartDy1", "EndYr1", "EndMo1", "EndDy1")
dfIntraStateWar_dates2 = dfIntraStateWar[['WarNum', 'CcodeA', 'SideA', 'CcodeB', 'SideB', 'StartMo2', 'StartDy2', 'StartYr2', 'EndMo2', 'EndDy2', 'EndYr2', 'WDuratDays', 'WDuratMo', 'Initiator', 'Outcome', 'Deaths A', 'Deaths B', 'TotalBDeaths', 'SideAPeakTotForces', 'SideAPeak TheatForces', 'SideBPeakTotForces', 'SideBPeakTheatForces']]\
.drop_duplicates().dropna(subset=["StartYr2"])
dfIntraStateWar_dates2_proc = process_dates(dfIntraStateWar_dates2, "StartYr2", "StartMo2", "StartDy2", "EndYr2", "EndMo2", "EndDy2")
dfIntraStateWar_dates3 = dfIntraStateWar[['WarNum', 'CcodeA', 'SideA', 'CcodeB', 'SideB', 'StartMo3', 'StartDy3', 'StartYr3', 'EndMo3', 'EndDy3', 'EndYr3', 'WDuratDays', 'WDuratMo', 'Initiator', 'Outcome', 'Deaths A', 'Deaths B', 'TotalBDeaths', 'SideAPeakTotForces', 'SideAPeak TheatForces', 'SideBPeakTotForces', 'SideBPeakTheatForces']]\
.drop_duplicates().dropna(subset=["StartYr3"])
dfIntraStateWar_dates3_proc = process_dates(dfIntraStateWar_dates3, "StartYr3", "StartMo3", "StartDy3", "EndYr3", "EndMo3", "EndDy3")
dfIntraStateWar_dates4 = dfIntraStateWar[['WarNum', 'CcodeA', 'SideA', 'CcodeB', 'SideB', 'StartMo4', 'StartDy4', 'StartYr4', 'EndMo4', 'EndDy4', 'EndYr4', 'WDuratDays', 'WDuratMo', 'Initiator', 'Outcome', 'Deaths A', 'Deaths B', 'TotalBDeaths', 'SideAPeakTotForces', 'SideAPeak TheatForces', 'SideBPeakTotForces', 'SideBPeakTheatForces']]\
.drop_duplicates().dropna(subset=["StartYr4"])
dfIntraStateWar_dates4_proc = process_dates(dfIntraStateWar_dates4, "StartYr4", "StartMo4", "StartDy4", "EndYr4", "EndMo4", "EndDy4")
dfIntraPar_datesproc = pd.concat([dfIntraStateWar_dates1_proc, dfIntraStateWar_dates2_proc, dfIntraStateWar_dates3_proc, dfIntraStateWar_dates4_proc])
dfIntraPar_datesproc
dfIntraPar_datesproc.columns
dfIntraPar_datesproc_pol1 = dfIntraPar_datesproc[['WarNum', 'CcodeA', 'SideA', 'WDuratDays', 'WDuratMo', 'Initiator', 'Outcome', 'Deaths A', 'SideAPeakTotForces', 'SideAPeak TheatForces', 'start_date_prec', 'end_date_prec', 'start_date', 'end_date']]
dfIntraPar_datesproc_pol1['side'] = "A"
dfIntraPar_datesproc_pol1 = dfIntraPar_datesproc_pol1.rename(columns={'CcodeA': 'polity', 'SideA': 'polity_name','Deaths A': 'deaths', 'SideAPeakTotForces': 'peak_total_forces', 'SideAPeakTheatForces': 'peak_theater_forces'})
dfIntraPar_datesproc_pol2 = dfIntraPar_datesproc[['WarNum', 'CcodeB', 'SideB', 'WDuratDays', 'WDuratMo', 'Initiator', 'Outcome', 'Deaths B', 'SideBPeakTotForces', 'SideBPeakTheatForces', 'start_date_prec', 'end_date_prec', 'start_date', 'end_date']]
dfIntraPar_datesproc_pol2['side'] = "B"
dfIntraPar_datesproc_pol2 = dfIntraPar_datesproc_pol2.dropna(subset = ['CcodeB', 'SideB'], how='all')
dfIntraPar_datesproc_pol2 = dfIntraPar_datesproc_pol2.rename(columns={'CcodeB': 'polity', 'SideB': 'polity_name','Deaths B': 'deaths', 'SideBPeakTotForces': 'peak_total_forces', 'SideBPeakTheatForces': 'peak_theater_forces'})
dfIntraPar_datesproc_pol = pd.concat([dfIntraPar_datesproc_pol1, dfIntraPar_datesproc_pol2])
dfIntraPar_datesproc_pol
dfPolities<jupyter_output><empty_output><jupyter_text>Extrastate War<jupyter_code>dfExtraStateWar
dfExtraStateWar.columns
dfExtraStateWar_dates1 = dfExtraStateWar[['WarNum', 'ccode1', 'SideA', 'ccode2', 'SideB', 'StartMonth1', 'StartDay1', 'StartYear1', 'EndMonth1', 'EndDay1','EndYear1', 'Initiator', 'Outcome', 'BatDeath', 'NonStateDeaths']]\
.drop_duplicates().dropna(subset=["StartYear1"])
dfExtraStateWar_dates1_proc = process_dates(dfExtraStateWar_dates1, "StartYear1", "StartMonth1", "StartDay1", "EndYear1", "EndMonth1", "EndDay1")
dfExtraStateWar_dates1_proc
dfExtraStateWar_dates2 = dfExtraStateWar[['WarNum', 'ccode1', 'SideA', 'ccode2', 'SideB', 'StartMonth2', 'StartDay2', 'StartYear2', 'EndMonth2', 'EndDay2 ','EndYear2', 'Initiator', 'Outcome', 'BatDeath', 'NonStateDeaths']]\
.drop_duplicates().dropna(subset=["StartYear2"])
dfExtraStateWar_dates2_proc = process_dates(dfExtraStateWar_dates2, "StartYear2", "StartMonth2", "StartDay2", "EndYear2", "EndMonth2", "EndDay2 ")
dfExtraStateWar_dates2_proc
dfExtraPar_datesproc = pd.concat([dfExtraStateWar_dates1_proc, dfExtraStateWar_dates2_proc])
dfExtraPar_datesproc
dfExtraPar_datesproc[dfExtraPar_datesproc.ccode1.isna()]
dfExtraPar_datesproc.columns
dfExtraPar_datesproc_pol1 = dfExtraPar_datesproc[['WarNum', 'ccode1', 'SideA', 'Initiator', 'Outcome', 'BatDeath', 'NonStateDeaths', 'start_date_prec', 'end_date_prec', 'start_date', 'end_date']]
dfExtraPar_datesproc_pol1['side'] = "A"
dfExtraPar_datesproc_pol1 = dfExtraPar_datesproc_pol1.dropna(subset=['ccode1', 'SideA'], how='all')
dfExtraPar_datesproc_pol1 = dfExtraPar_datesproc_pol1.rename(columns={'ccode1': 'polity', 'SideA': 'polity_name'})
dfExtraPar_datesproc_pol2 = dfExtraPar_datesproc[['WarNum', 'ccode2', 'SideB', 'Initiator', 'Outcome', 'BatDeath', 'NonStateDeaths', 'start_date_prec', 'end_date_prec', 'start_date', 'end_date']]
dfExtraPar_datesproc_pol2['side'] = "B"
dfExtraPar_datesproc_pol2 = dfExtraPar_datesproc_pol2.dropna(subset=['ccode2', 'SideB'], how='all')
dfExtraPar_datesproc_pol2 = dfExtraPar_datesproc_pol2.rename(columns={'ccode2': 'polity', 'SideB': 'polity_name'})
dfExtraPar_datesproc_pol = pd.concat([dfExtraPar_datesproc_pol1, dfExtraPar_datesproc_pol2])
dfExtraPar_datesproc_pol<jupyter_output><empty_output><jupyter_text>Nonstate War<jupyter_code>dfNonStateWar
dfNonStateWar.columns
dfNonStateWar_dates = dfNonStateWar[['WarNum', 'SideA1', 'SideA2', 'SideB1', 'SideB2', 'SideB3', 'SideB4', 'SideB5', 'StartYear', 'StartMonth', 'StartDay', 'EndYear', 'EndMonth', 'EndDay', 'Initiator', 'Outcome', 'SideADeaths', 'SideBDeaths', 'TotalCombatDeaths',]]\
.drop_duplicates()
dfNonStateWar_dates_proc = process_dates(dfNonStateWar_dates, "StartYear", "StartMonth", "StartDay", "EndYear", "EndMonth", "EndDay")
dfNonStateWar_dates_proc<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15544152234338252, 'fraction_numerical': 0.022521559404176467, 'mean_word_length': 6.905352112676057, 'pattern_counts': {'":': 15, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '465884', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 5315, 'n_tokens_neox': 5118, 'n_words': 1029, 'path': 'wrangling_code/war_participants.ipynb', 'repo_name': 'jenna-jordan/correlates-of-war', 'size': '14031'}