<jupyter_start><jupyter_text>Battles only<jupyter_code>battles = data[data.level == 100]
battles.shape
columns = ['Location', 'name', 'Date', 'Result', 'Belligerents.allies',
           'Belligerents.axis', 'Casualties and losses.allies', 'Casualties and losses.axis']
battles['Location'].iloc[15]
battles[columns].head()<jupyter_output><empty_output><jupyter_text>Empty Cells<jupyter_code>msno.matrix(battles, labels=True, sparkline=False)
mask = battles[['Date', 'Location']].isnull().all(1)
print(battles.loc[mask, ['name', 'url']].to_string())
battles.loc[mask, ['name', 'url']].iloc[3, 1]
battles=battles.dropna(subset=['Date', 'Location'])<jupyter_output><empty_output><jupyter_text>Location<jupyter_code>pattern = r'/ ([\d|\.]+); ([\d|\.]+)'
# battles.iloc[9]['Location']
battles[['Lattitude', 'Longitude']] = battles.Location.str.extract(pattern)
for col in  'Lattitude', 'Longitude':
    battles[col] =  battles[col].astype(float)
f"{(battles['Lattitude'].isnull().sum() / len(battles)):.1%}"<jupyter_output><empty_output><jupyter_text>Geocode So, we actually don't have coordinates for 78% of our battles, which is too hight. As an alternative, let's use the geocoding function we wrote in chapter 6! Of course, some addesses are obsolete, but most will, probably, get us to the coordinates<jupyter_code>from geocode import nominatim_geocode # copied the file from folder "Chapter06"
from tqdm import tqdm, tqdm_notebook
tqdm().pandas()
geo_mask = battles['Lattitude'].isnull()
battles.loc[geo_mask, 'Location'].sample(15, random_state=2019)
location = battles['Location'].str.lower().str.replace('near ', '')
replacements = {
    'Ukrainian SSR, Soviet Union': 'Ukraine',
    'Russian SFSR, Soviet Union': 'Russia',
    'Russian SFSR': 'Russia',
    'Belorussian SSR': 'Belorus',
    'Soviet Union': '',
    'USSR': '',
    ', Poland (now Ukraine)': 'Ukraine',
    'east prussia (now kaliningrad oblast)': 'Kaliningrad Oblast, Russia',
    ', czechoslovakia': ', czech republic',
    'königsberg, germany (now: kaliningrad, russia)': 'Kaliningrad Oblast, Russia',
    'lwów, lwów voivodeship, poland': 'Lvov, Ukraine',
    'leningrad region, ; narva, estonia': 'Narva, Estonia',
    'Kingdom of Hungary': 'Hungary',
    'odessa region, ukraine': 'Odessa, Ukraine'
}
for k, v in replacements.items():
    location = location.str.replace(k.lower(), v.lower(), regex=False)
def vectorized_geocode(x):
    result = nominatim_geocode(x)
    if len(result) == 0:
        return dict()
    return {k:result[0][k] for k in ('lat', 'lon', 'importance', 'display_name')}
# nominatim_geocode('realm commissariat ostland\ngeneral district of latvia\nkreisgebiete liepaja\nkreise aizpute\nkreise kuldiga\nkreise liepaja\nkreise liepaja (city)\nkreise talsi\nkreise ventspils\nkreisgebiete jelgava\nkreise jelgava\nkreise tukums\ngeneral district of lithuania\nkreisgebiete siauliai\nkreise kretinga\nkreise mazeikiai'.replace('\n', ' '))
location[location.fillna('').str.contains('kreisgebiete')] = 'Courland Peninsula'
response = location[geo_mask].str.replace('\n', ' ').progress_apply(vectorized_geocode)
response.iloc[0]
battles.loc[geo_mask, 'Location'].iloc[0]
geo_df = pd.DataFrame(response.tolist(), index = response.index)
geo_df.rename(columns={'lat': 'Lattitude', 'lon': ' Longitude'}, inplace=True)
rmask = geo_df['importance'].isnull()  # still no location
f'{rmask.sum() / len(battles):.1%}'
location[geo_mask].loc[rmask]<jupyter_output><empty_output><jupyter_text>Time<jupyter_code># pd.to_datetime('September 1939')
battles.loc[94, 'Date']
d = ('January', 'February', 'March', 'April', 'May', 
     'June', "July",' August', 'September', 'October', 'November', 'December')

month_pattern = r'(' + "|".join(d) + ')'
year_pattern = r'(19\d\d)'
year_extracted = battles['Date'].str.extractall(year_pattern).unstack()
# len(d.iloc[:, -1].notnull())
year_extracted[year_extracted.iloc[:, -1].notnull()]
# d[d.iloc[:, -1].notnull()]
battles.loc[94, 'Date']
year_extracted = year_extracted.iloc[:, :2]
year_extracted.head(10)
year_extracted.iloc[:, 1] = year_extracted.iloc[:, 1].fillna(year_extracted.iloc[:, 0])
month_extracted = battles['Date'].str.extractall(month_pattern).unstack()
for i in range(2, month_extracted.shape[1]+1):
    month_extracted.iloc[:, -1].fillna(month_extracted.iloc[:, -i], inplace=True)
month_extracted = month_extracted.iloc[:, [0, -1]]
year_extracted.columns = month_extracted.columns = ['start', 'end']
I = battles.index
cols = 'start', 'end'

for col in cols:
    battles[col] = pd.to_datetime(month_extracted.loc[I, col] + ' ' + year_extracted.loc[I, col])<jupyter_output>/Users/philippk/anaconda3/envs/py36/lib/python3.6/site-packages/pandas/core/indexing.py:1017: FutureWarning: 
Passing list-likes to .loc or [] with any missing label will raise
KeyError in the future, you can use .reindex() as an alternative.

See the documentation here:
https://pandas.pydata.org/pandas-docs/stable/indexing.html#deprecate-loc-reindex-listlike
  return getattr(section, self.name)[new_key]<jupyter_text>Sides Swap<jupyter_code>words = ['Germany', 'Italy', 'Estonian conscripts']
for word in words:
    mask = battles['Belligerents.allies'].fillna('').str.contains(word)
    axis_party = battles.loc[mask, ['Belligerents.allies', 'Casualties and losses.allies']].copy()
    battles.loc[mask, ['Belligerents.allies', 'Casualties and losses.allies']] = battles.loc[mask, ['Belligerents.axis', 'Casualties and losses.axis']].values
    battles.loc[mask, ['Belligerents.axis', 'Casualties and losses.axis']] = axis_party.values<jupyter_output><empty_output><jupyter_text>Casualties<jupyter_code>battles['Casualties and losses.allies'].iloc[0]
digit_pattern = '([\d|\,|\.]+)(?:\[\d+\])?\+?\s*(?:{words})'

keywords = { 'killed': ['men', 'dead', 'killed', 'casualties', 'kia'], 
             'wounded': ['wounded', 'sick', 'injured'], 
             'captured': ['captured', 'prisoners'],
             'tanks': ['tank', 'panzer'],
             'airplane': ['airplane', 'aircraft'],
             'guns': ['artillery', 'gun', 'self propelled guns', 'field-guns', 'anti-tank guns'],
             'ships': ['warships', 'boats', 'destroyer', 'minelayer'],
             'submarines': ['submarines']
}

only_digits = '([\d|\,|\.]+)\Z'
# column[column.fillna('').str.match(only_digits)].str.extract(only_digits)
def _shy_convert_numeric(v):
    if pd.isnull(v) or v in (',', '.'):
        return pd.np.nan
    
    return int(v.replace(',', '').replace('.', ''))
    
results = {
'allies' : pd.DataFrame(index=battles.index, columns=keywords.keys()),  # empty dataframes with the same index
'axis' : pd.DataFrame(index=battles.index, columns=keywords.keys())
}

for name, edf in results.items():
    column = battles[f'Casualties and losses.{name}']
    for tp, keys in keywords.items():
        pattern = digit_pattern.format(words="|".join(keys))
        print(pattern)
        extracted = column.str.lower().str.extractall(pattern).unstack()
        values = extracted.applymap(_shy_convert_numeric)
#         if tp == 'killed':
#             mask values.iloc[:, 0].notnull()
        edf[tp] = values.min(1)
    results[name] = edf.fillna(0).astype(int)
    

    b = column.fillna('').str.extract(only_digits).applymap(_shy_convert_numeric).iloc[:, 0]
    mask = b.notnull()
    results[name].loc[mask, 'killed'] = b[mask]
pattern = '([\d|\,]+)(?:\[\d+\])?\+?\s*(?:men|dead|killed|casualties|kia)'
battles[f'Casualties and losses.axis'].str.extractall(pattern).unstack().head(5)
battles['Casualties and losses.axis'][battles.name == 'Battle of Stalingrad'].iloc[0]
# battles[battles.name == 'Battle of Stalingrad']
results['axis'][battles.name == 'Battle of Stalingrad']
battles.loc[[27], 'Casualties and losses.axis'].iloc[0] #.str.extractall('([\d|\,]+)\s[tank]')
results['axis'].loc[27]
results['axis'][battles.name == 'Battle for Velikiye Luki (1943)']
battles.loc[battles.name=='Battle for Velikiye Luki (1943)', 'Casualties and losses.axis'].iloc[0]<jupyter_output><empty_output><jupyter_text>Combine<jupyter_code>results['old_metrics'] = battles
new_dataset = pd.concat(results, axis=1)<jupyter_output><empty_output><jupyter_text>Quality Assurance<jupyter_code>idx = pd.IndexSlice
assumptions = {
    'killed': [0, 2_000_000],
    'wounded': [0, 1_000_000],
    'tanks': [0, 5_000],
    'airplane': [0, 3_000],
    'guns': [0, 30_000],
    ('start', 'end'): [pd.to_datetime(el) for el in ('1939-01-01', '1945-12-31')]
}
def _check_assumptions(data, assumptions):
    for k, (min_, max_) in assumptions.items():
        df = data.loc[:, idx[:, k]]
        for i in range(df.shape[1]):
            assert df.iloc[:, i].between(min_, max_).all(), (df.iloc[:, i].name, df.iloc[:, i].describe())
d = new_dataset.loc[new_dataset.loc[:, idx['allies', 'tanks']] > 1_000 , idx['old_metrics', ['name', 'url']]] 
d
d.iloc[0, 1]
_check_assumptions(new_dataset, assumptions)
new_dataset.loc[~new_dataset.loc[:, idx['old_metrics', 'start']].between(*[pd.to_datetime(el) for el in ('1939-01-01', '1945-12-31')]), idx['old_metrics', ['start']]]
new_dataset.loc[new_dataset.loc[:, idx['old_metrics', 'start']].isnull(), idx['old_metrics', ['name','url']]]
new_dataset.loc[135, idx['old_metrics', 'start']] = pd.to_datetime('1944-08-09')
new_dataset.loc[135, idx['old_metrics', 'end']] = pd.to_datetime('1944-08-16') # August 9–16, 1944
_check_assumptions(new_dataset, assumptions)<jupyter_output><empty_output><jupyter_text>Store<jupyter_code>new_dataset.to_csv('./data/EF_battles.csv', index=None)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.19175927867477457, 'fraction_numerical': 0.019081568462990146, 'mean_word_length': 5.5335616438356166, 'pattern_counts': {'":': 0, '<': 40, '<?xml version=': 0, '>': 41, 'https://': 1, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '154011', 'license': 'MIT', 'n_tokens_mistral': 3692, 'n_tokens_neox': 3556, 'n_words': 637, 'path': 'Chapter11/2_data_cleaning.ipynb', 'repo_name': 'Drtaylor1701/Learn-Python-by-Building-Data-Science-Applications', 'size': '9538'}