<jupyter_start><jupyter_text>Make sure to upload the following files:- PC_Export_2014_2015.csv  - PC_Export_2017_2018.csv- PC_Import_2016_2017.csv- PC_Export_2015_2016.csv- PC_Import_2014_2015.csv - PC_Import_2017_2018.csv- PC_Export_2016_2017.csv - PC_Import_2015_2016.csv<jupyter_code>#Make sure to upload the files
!ls
years=['2014_2015','2015_2016','2016_2017','2017_2018']
df_imp_exp={'export':'PC_Export_','import':'PC_Import_'}
df_dict=dict()
for key,value in df_imp_exp.items():
  df_dict[key]=dict()
  for year in years:
    df_dict[key][year]=pd.read_csv('{}{}.csv'.format(value,year))  
df_dict['import']['2017_2018'].head(),df_dict['export']['2017_2018'].head()
df_dict['import']['2016_2017'].columns,df_dict['export']['2016_2017'].columns,df_dict['import']['2017_2018'].columns,df_dict['export']['2017_2018'].columns
cols_rename_2017_2018={'export':{'COMMODITY':'pc_description','UNIT_CODE':'unit','COUNTRY':'country_name',
               'QTY':'quantity','VALUES (US$ million)':'value'},'import':{'PC_DESC':'pc_description',
              'UNIT OF QTY':'unit','COUNTRY':'country_name','QTY':'quantity','VAL (MILL US $)':'value'}}
for key,col_rename in cols_rename_2017_2018.items():
  df_dict[key]['2017_2018']=df_dict[key]['2017_2018'].rename(columns=col_rename)
df_dict['import']['2017_2018'].columns,df_dict['export']['2017_2018'].columns
df_dict['import']['2016_2017'].columns,df_dict['export']['2016_2017'].columns,df_dict['import']['2017_2018'].columns,df_dict['export']['2017_2018'].columns
#adding type and year as columns
for key,value in df_imp_exp.items():
  for year in years:
    df_dict[key][year]['type']=key
    df_dict[key][year]['year']=year
    df_dict[key][year]=df_dict[key][year].applymap(lambda x:x.lower() if type(x) is str else x)
#Checking whether the pc_description of 2017_2018 matches with those of other years
df_dict['import']['2017_2018'].pc_description.isin(df_dict['import']['2016_2017'].pc_description).sum()/df_dict['import']['2017_2018'].shape[0],df_dict['export']['2017_2018'].pc_description.isin(df_dict['export']['2016_2017'].pc_description).sum()/df_dict['export']['2017_2018'].shape[0]
#Deleting these 2 columns since they are not present in 2017_2018 df and they both dont add any extra info anyways
cols_to_remove=['pc_code','country_code']
mod_years=[year for year in years if year!='2017_2018']
for key in df_imp_exp.keys():
  for year in mod_years:
    df_dict[key][year]=df_dict[key][year].drop(cols_to_remove,axis=1)
df_dict['import']['2016_2017'].columns
#Sanity Check...checking whether the columns for all the individual dfs are the same
cols=list(df_dict['import']['2016_2017'].columns)
for key in df_imp_exp.keys():
  for year in years:
    assert list(df_dict[key][year].columns)==cols,"Check df_dict[{}][{}]".format(key,year)
dfs_list=[]
for key in df_imp_exp.keys():
  for year in years:
    dfs_list.append(df_dict[key][year])
joined_df=pd.concat(dfs_list,ignore_index=True)
joined_df.shape,joined_df.head()
(joined_df.loc[(joined_df.country_name=='unspecified')&(joined_df.type=='export'),'value'].sum(),
 joined_df.loc[(joined_df.country_name=='unspecified')&(joined_df.type=='import'),'value'].sum(),
 joined_df.loc[(joined_df.country_name=='unspecified')&(joined_df.type=='export'),'value'].sum()/joined_df.loc[joined_df.type=='export','value'].sum(),
 joined_df.loc[(joined_df.country_name=='unspecified')&(joined_df.type=='import'),'value'].sum()/joined_df.loc[joined_df.type=='import','value'].sum())
print(joined_df.shape[0])
joined_df=joined_df.dropna(subset=['country_name'])
joined_df=joined_df.loc[joined_df.country_name!='unspecified',:] #This is because as shown above the percentage of transactions
#where country is unspecified is very small and because we need to show it on a map its better to remove it for demo purposes
print(joined_df.shape[0])
joined_df.describe(include='all')
joined_df.groupby(['pc_description','country_name','year','type']).agg('count')['value'].value_counts()
joined_df_2=joined_df
#Since pivot method is not accepting multiple values for index
joined_df_2['index_2']=joined_df_2.apply(lambda x:"{}_{}_{}".format(x['pc_description'],x['country_name'],x['year']),axis=1)
joined_df_2.columns,joined_df_2.head()
joined_df_imp_exp_pivot=joined_df_2.pivot(index='index_2',columns='type',values='value')
joined_df_imp_exp_pivot=joined_df_imp_exp_pivot.fillna(0)
joined_df_imp_exp_pivot.head(),joined_df_imp_exp_pivot.shape
print(joined_df_2.shape[0])
joined_df_3=joined_df_2.groupby('index_2').first()
joined_df_3.head(),joined_df_3.shape
joined_df_4=joined_df_3.join(joined_df_imp_exp_pivot,how='inner')
joined_df_4.shape,joined_df_4.head(),joined_df_4.columns,joined_df_4.isnull().sum()
joined_df_4.unit=joined_df_4.unit.fillna('NA')
joined_df_4.quantity=joined_df_4.quantity.fillna('NA')
joined_df_4=joined_df_4.drop(columns=['value']).rename(columns={'import':'import_amt','export':'export_amt'})
joined_df_4.columns,joined_df_4.shape,joined_df_4.head()
joined_df_4.describe(include='all')
joined_df_4.to_csv('processed_data_out_pd.csv',index=False)
from google.colab import files

files.download('processed_data_out_pd.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17740375314374154, 'fraction_numerical': 0.06809827819694332, 'mean_word_length': 10.2882096069869, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '636403', 'license': 'MIT', 'n_tokens_mistral': 2159, 'n_tokens_neox': 1885, 'n_words': 244, 'path': 'Preprocess_Import_and_Export_By_India_DS.ipynb', 'repo_name': 'rwikdutta/india-import-export-tableau-viz', 'size': '5169'}