<jupyter_start><jupyter_text>Putting stores on a mapKaggle competition [Rossmann Store Sales](https://www.kaggle.com/c/rossmann-store-sales) contains sales data for 1115 Rossmann stores across Germany. The data includes information on school and state holidays. In this notebook I use holidays data to determine which state of Germany each store is located in.External sources used:* Public holidays data from http://www.timeanddate.com/holidays/germany/2013* School holidays data from http://www.holidays-info.com/School-Holidays-Germany/2015/school-holidays_2015.html<jupyter_code>train = pd.read_csv(os.path.join('..','Data','train.csv'),parse_dates=['Date'],dtype = {'StateHoliday':object})
test = pd.read_csv(os.path.join('..','Data','test.csv'),parse_dates=['Date'])
# Fix StateHoliday column
train.loc[train.StateHoliday == 0,'StateHoliday'] = '0'<jupyter_output><empty_output><jupyter_text>Public holidays datesLet's look at public holiday dates and numbers of stores celebrating them.<jupyter_code>holid = train.loc[train.StateHoliday=='a']
bydate = holid.groupby('Date')['Store'].count()
bydate
bydate.groupby(bydate.index.year).plot(style='-o')<jupyter_output><empty_output><jupyter_text>We see that different dates refer to public holidays in some stores but not in the others. German public holidays differ state by state. So we can use holidays data to figure out what state a store is located in.   Figuring out the states from holiday datesI'm using public holidays data from http://www.timeanddate.com/holidays/germany/2013.2013-11-20 is Repentance Day in Saxony (SN).<jupyter_code>SN = holid.loc[holid.Date == '2013-11-20','Store'].values
print('{} stores located in Saxony.'.format(SN.shape[0]))<jupyter_output>75 stores located in Saxony.<jupyter_text>2013-01-06 is Epiphany in BW, BY, ST.<jupyter_code>BW_BY_ST = holid.loc[holid.Date == '2013-01-06','Store'].values
print('{} stores located in BW, BY, ST.'.format(BW_BY_ST.shape[0]))<jupyter_output>309 stores located in BW, BY, ST.<jupyter_text>2013-05-30 is Corpus Christi in BW, BY, HE, NW, RP, SL.<jupyter_code>BW_BY_HE_NW_RP_SL = holid.loc[holid.Date == '2013-05-30','Store'].values
print('{} stores located in BW, BY, HE, NW, RP, SL.'.format(BW_BY_HE_NW_RP_SL.shape[0]))<jupyter_output>766 stores located in BW, BY, HE, NW, RP, SL.<jupyter_text>For some reason this list also contains stores from Saxony (SN):<jupyter_code>np.array_equal(np.intersect1d(BW_BY_HE_NW_RP_SL,SN),SN)<jupyter_output><empty_output><jupyter_text>I'll delete Saxony stores from here:<jupyter_code>BW_BY_HE_NW_RP_SL = np.setxor1d(BW_BY_HE_NW_RP_SL, SN)
print('{} stores located in BW, BY, HE, NW, RP, SL.'.format(BW_BY_HE_NW_RP_SL.shape[0]))<jupyter_output>691 stores located in BW, BY, HE, NW, RP, SL.<jupyter_text>2013-08-15 is the Assumption of Mary in BY, SL<jupyter_code>BY_SL = holid.loc[holid.Date =='2013-08-15','Store'].values
print('{} stores located in BY,SL.'.format(BY_SL.shape[0]))<jupyter_output>180 stores located in BY,SL.<jupyter_text>2013-10-31 is Reformation Day in BB, MV, SN, ST, TH<jupyter_code>BB_MV_SN_ST_TH = holid.loc[holid.Date =='2013-10-31','Store'].values
print('{} stores located in BB, MV, SN, ST, TH.'.format(BB_MV_SN_ST_TH.shape[0]))<jupyter_output>167 stores located in BB, MV, SN, ST, TH.<jupyter_text>2013-11-01 is All Saints' Day in BW, BY, NW, RP, SL<jupyter_code>BW_BY_NW_RP_SL = holid.loc[holid.Date =='2013-11-01','Store'].values
print('{} stores located in BW, BY, NW, RP, SL.'.format(BW_BY_NW_RP_SL.shape[0]))<jupyter_output>579 stores located in BW, BY, NW, RP, SL.<jupyter_text>Now let's do some intersecting and setxoring =).<jupyter_code>BW_BY = np.intersect1d(BW_BY_ST, BW_BY_HE_NW_RP_SL)
print('{} stores located in BW, BY.'.format(BW_BY.shape[0]))
ST = np.setxor1d(BW_BY_ST, BW_BY)
print('{} stores located in ST.'.format(ST.shape[0]))
BY = np.intersect1d(BW_BY, BY_SL)
print('{} stores located in BY.'.format(BY.shape[0]))
SL = np.setxor1d(BY, BY_SL)
print('{} stores located in SL.'.format(SL.shape[0]))
BW = np.setxor1d(BW_BY, BY)
print('{} stores located in BW.'.format(BW.shape[0]))
HE = np.setxor1d(BW_BY_HE_NW_RP_SL,BW_BY_NW_RP_SL)
print('{} stores located in HE.'.format(HE.shape[0]))
BB_MV_TH = np.setxor1d(np.setxor1d(BB_MV_SN_ST_TH,SN),ST)
print('{} stores located in BB, MV, TH.'.format(BB_MV_TH.shape[0]))
NW_RP = np.setxor1d(BW_BY_NW_RP_SL,BW_BY) # SL has 0 stores
print('{} stores located in NW, RP.'.format(NW_RP.shape[0]))
allstores = np.unique(train.Store.values)
BE_HB_HH_NI_SH = np.setxor1d(np.setxor1d(allstores,BW_BY_HE_NW_RP_SL),BB_MV_SN_ST_TH)
print('{} stores located in BE, HB, HH, NI, SH.'.format(BE_HB_HH_NI_SH.shape[0]))<jupyter_output>257 stores located in BE, HB, HH, NI, SH.<jupyter_text>Checking resultsWe should now have 1115 stores in 16 states.<jupyter_code>print('{} stores located in BE, HB, HH, NI, SH.'.format(BE_HB_HH_NI_SH.shape[0]))
print('{} stores located in NW, RP.'.format(NW_RP.shape[0]))
print('{} stores located in BB, MV, TH.'.format(BB_MV_TH.shape[0]))
print('{} stores located in HE.'.format(HE.shape[0]))
print('{} stores located in BY.'.format(BY.shape[0]))
print('{} stores located in ST.'.format(ST.shape[0]))
print('{} stores located in SL.'.format(SL.shape[0]))
print('{} stores located in BW.'.format(BW.shape[0]))
print('{} stores located in SN.'.format(SN.shape[0]))
257+326+36+112+180+56+73+75<jupyter_output><empty_output><jupyter_text>We managed to distinguish 6 distinct states and also three groups of 2, 3 and 5 states. Next I'll use public school holidays data to further divide stores. Further division based on public schools holidaysLet's now look at school holidays. I'm using holiday dates from here: http://www.holidays-info.com/School-Holidays-Germany/2015/school-holidays_2015.html.Public school holidays vary state by state. We can use this fact to check that our single-state groups do in fact contain stores with same school holiday dates and maybe to further divide groups that still contain several states.<jupyter_code>train.loc[train.Store.isin(SN)].groupby('Date')['SchoolHoliday'].sum().plot()<jupyter_output><empty_output><jupyter_text>If we sum SchoolHoliday grouping by date for every state we should get two possible values - either no stores have school holidays or all of them do.<jupyter_code>train.loc[train.Store.isin(SN)].groupby('Date')['SchoolHoliday'].sum().value_counts()
train.loc[train.Store.isin(BY)].groupby('Date')['SchoolHoliday'].sum().value_counts()
train.loc[train.Store.isin(HE)].groupby('Date')['SchoolHoliday'].sum().value_counts()
train.loc[train.Store.isin(ST)].groupby('Date')['SchoolHoliday'].sum().value_counts()
train.loc[train.Store.isin(BW)].groupby('Date')['SchoolHoliday'].sum().value_counts()<jupyter_output><empty_output><jupyter_text>So far so good.Now let's look at a group with two states.<jupyter_code>train.loc[train.Store.isin(NW_RP)].groupby('Date')['SchoolHoliday'].sum().value_counts()<jupyter_output><empty_output><jupyter_text>Looks like we have 40 stores in one state and 286 in the other. 2015-03-26 should be a school holiday in RP but not in NW.<jupyter_code>RP = train.loc[train.Date=='2015-03-26'].loc[train.Store.isin(NW_RP)].loc[train.SchoolHoliday==1,'Store'].values
NW = np.setxor1d(NW_RP,RP)
print('{} stores located in RP.'.format(RP.shape[0]))
print('{} stores located in NW.'.format(NW.shape[0]))<jupyter_output>40 stores located in RP.
286 stores located in NW.<jupyter_text>Let's look at a group of three states:<jupyter_code>train.loc[train.Store.isin(BB_MV_TH)].groupby('Date')['SchoolHoliday'].sum().value_counts()<jupyter_output><empty_output><jupyter_text>Ok, it looks like it is in fact one state... Or 2 or 3 with exact same holidays. Let's see the holiday schedule.<jupyter_code>train.loc[train.Store==(BB_MV_TH[0])].loc[train.Date >= '2015-01-31'].loc[train.SchoolHoliday==1,'Date']<jupyter_output><empty_output><jupyter_text>Winter holidays not lasting till Feb 14th rule out MV. Easter holidays starting on Mar 30th suggest TH. Confirmed by summer holidays starting on Jul 13th.<jupyter_code>TH = BB_MV_TH
print('{} stores located in TH.'.format(TH.shape[0]))<jupyter_output>36 stores located in TH.<jupyter_text>Now the final 5-state group.<jupyter_code>train.loc[train.Store.isin(BE_HB_HH_NI_SH)].groupby('Date')['SchoolHoliday'].sum().value_counts()<jupyter_output><empty_output><jupyter_text>What a mess. There is a lot of states here.Only HH has easter holidays on Mar 2nd. And on May 5th.<jupyter_code>HH = train.loc[train.Date=='2015-03-02'].loc[train.Store.isin(BE_HB_HH_NI_SH)].loc[train.SchoolHoliday==1,'Store'].values
print('{} stores located in HH.'.format(HH.shape[0]))
# Just to check
HH1 = train.loc[train.Date=='2015-05-11'].loc[train.Store.isin(BE_HB_HH_NI_SH)].loc[train.SchoolHoliday==1,'Store'].values
np.array_equal(HH,HH1)
BE_HB_NI_SH = np.setxor1d(BE_HB_HH_NI_SH,HH)<jupyter_output><empty_output><jupyter_text>Only SH has holidays on Apr 17th. And on Feb 2nd.<jupyter_code>SH = train.loc[train.Date=='2015-04-17'].loc[train.Store.isin(BE_HB_NI_SH)].loc[train.SchoolHoliday==1,'Store'].values
print('{} stores located in SH.'.format(SH.shape[0]))
SH1 = train.loc[train.Date=='2015-02-02'].loc[train.Store.isin(BE_HB_NI_SH)].loc[train.SchoolHoliday==0,'Store'].values
np.array_equal(SH,SH1)
BE_HB_NI = np.setxor1d(BE_HB_NI_SH,SH)<jupyter_output><empty_output><jupyter_text>Mar 25th is not a holiday in BE.<jupyter_code>BE = train.loc[train.Date=='2015-03-25'].loc[train.Store.isin(BE_HB_NI)].loc[train.SchoolHoliday==0,'Store'].values
print('{} stores located in BE.'.format(BE.shape[0]))
HB_NI = np.setxor1d(BE_HB_NI,BE)
HB_NI.shape<jupyter_output><empty_output><jupyter_text>School holidays in these two states are the same...<jupyter_code>train.loc[train.Store.isin(HB_NI)].groupby('Date')['SchoolHoliday'].sum().value_counts()<jupyter_output><empty_output><jupyter_text>I give up. These two I cannot distinguish. Saving results<jupyter_code>states = pd.Series('',index = allstores,name='State')
states.loc[BW] = 'BW'
states.loc[BY] = 'BY'
states.loc[BE] = 'BE'
states.loc[HB_NI] = 'HB,NI'
states.loc[HH] = 'HH'
states.loc[HE] = 'HE'
states.loc[NW] = 'NW'
states.loc[RP] = 'RP'
states.loc[SN] = 'SN'
states.loc[ST] = 'ST'
states.loc[SH] = 'SH'
states.loc[TH] = 'TH'
states[states!=''].value_counts().sum()
states.to_csv('store_states.csv', header=True, index_label='Store')<jupyter_output><empty_output><jupyter_text>Some further analysisTrain set contains 1115 stores, but test set contains only 865 stores. Let's see the distributions of stores by state in train and test sets.<jupyter_code>df = pd.DataFrame(states)
trainstates = df['State'].value_counts()
teststores = np.unique(test.Store)
teststates = df.loc[df.index.isin(teststores),'State'].value_counts()
statesdf = pd.DataFrame(index=trainstates.index)
statesdf['train'] = trainstates
statesdf['test'] = teststates
ax = statesdf[['train','test']].plot(kind='barh')
ax.set_title('Number of stores per state')<jupyter_output><empty_output><jupyter_text>Looks like test set excludes TH, SN, ST and BE. Some stores have a gap in data in the second half of 2014. Let's see whether they come from a single state or different states.<jupyter_code>train.groupby('Date')['Store'].count().plot()
nogap = train.loc[train.Date == '2014-09-01','Store'].values
gap = np.setxor1d(allstores, nogap)
statesdf['no gap'] = df.loc[df.index.isin(nogap),'State'].value_counts()
statesdf['gap'] = df.loc[df.index.isin(gap),'State'].value_counts()
ax = statesdf[['no gap','gap']].plot(kind='barh')
ax.set_title('Number of stores per state')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15805811847891696, 'fraction_numerical': 0.030352677416573253, 'mean_word_length': 6.857723577235772, 'pattern_counts': {'":': 0, '<': 99, '<?xml version=': 0, '>': 100, 'https://': 1, 'lorem ipsum': 0, 'www.': 5, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '27', 'dir': 'jupyter-structured-clean-dedup', 'id': '624130', 'license': 'MIT', 'n_tokens_mistral': 4644, 'n_tokens_neox': 4203, 'n_words': 934, 'path': '.ipynb_checkpoints/map_ross-checkpoint.ipynb', 'repo_name': 'avinashpasupulate/rossmann_sales', 'size': '11597'}