title: How to data clean in groups
body: <p>I have an extremely long dataframe with a lot of data which I have to clean so that I can proceed with data visualization. There are several things I have in mind that needs to be done and I can do each of them to a certain extent but I don't know how to, or if it's even possible, to do them together. </p>
  
  <p>This is what I have to do:</p>
  
  <ol>
  <li>Find the highest arrival count every year and see if the mode of transport is by air, sea or land.</li>
  </ol>
  
  <pre><code>    period  arv_count Mode of arrival
  0   2013-01  984350         Air
  1   2013-01  129074         Sea
  2   2013-01  178294         Land
  3   2013-02  916372         Air
  4   2013-02  125634         Sea
  5   2013-02  179359         Land
  6   2013-03  1026312    Air
  7   2013-03  143194         Sea
  8   2013-03  199385         Land
  ...   ...      ...          ...
  78  2015-03  940077     Air
  79  2015-03  133632         Sea
  80  2015-03  127939     Land
  81  2015-04  939370     Air
  82  2015-04  118120     Sea
  83  2015-04  151134     Land
  84  2015-05  945080     Air
  85  2015-05  123136     Sea
  86  2015-05  154620     Land
  87  2015-06  930642     Air
  88  2015-06  115631     Sea
  89  2015-06  138474     Land
  </code></pre>
  
  <p>This is an example of what the data looks like. I don't know if it's necessary but I have created another column just for year like so:</p>
  
  <pre><code>def year_extract(year):
      return year.split('-')[0].strip()
  
  df1 = pd.DataFrame(df['period'])
  
  df1 = df1.rename(columns={'period':'Year'})
  
  df1 = df1['Year'].apply(year_extract)
  df1 = pd.DataFrame(df1)
  
  df = pd.merge(df, df1, left_index= True, right_index= True)
  </code></pre>
  
  <p>I know how to use groupby and I know how to find maximum but I don't know if it is possible to find maximum in a group like finding the highest arrival count in 2013, 2014, 2015 etc</p>
  
  <p>The data above is the total arrival count for all countries based on the mode of transport and period but the original data also had hundreds of additional rows of which region and country stated are stated but I dropped because I don't know how to use or clean them. It looks like this:</p>
  
  <pre><code>period     region     country     moa     arv_count
  2013-01     Total      Total      Air      984350
  2013-01     Total      Total      Sea      129074
  2013-01     Total      Total      Land     178294
  2013-02     Total      Total      Air      916372
  ...      ...    ...       ...       ...
  2015-12    AMERICAS     USA       Land      2698
  2015-12    AMERICAS    Canada     Land       924
  2013-01     ASIA        China     Air      136643
  2013-01     ASIA        India     Air       55369
  2013-01     ASIA        Japan     Air       51178
  </code></pre>
  
  <p>I would also like to make use of the region data if it is possible. Hoping to create a clustered column chart with the 7 regions as the x axis and arrival count as y axis and each region showing the arrival count via land, sea and air but I feel like there are too much excess data that I don't know how to deal with right now.</p>
  
  <p>For example, I don't know how to deal with the period and the country because all I need is the total arrival count of land, sea and air based on region and year regardless of country and months.</p>