Analysing Singapore's Gross Monthly Income Changes From 2010 to 2020

1
import pandas as pd 
2
df = pd.read_csv('gross monthly income.csv', index_col=0)
3
df

1
# 1. get rid of 'total' row
2
# 2. convert cells to percentage change from previous row using pct_change()
3
change_df_by_year = df.drop('Total').pct_change(axis='columns')
4
change_df_by_year.multiply(100) # express as percentage out of 100

1
import seaborn as sns
2
sns.set_theme()
3
import matplotlib.pyplot as plt
4

5
grid_kws = {"height_ratios": (.9, .05), "hspace": .1}
6
f, (ax, cbar_ax) = plt.subplots(2, gridspec_kw=grid_kws, figsize=(25,10))
7

8
ax = sns.heatmap(change_df, ax=ax, annot=True, fmt=".2f", linewidths=.5, cbar_ax=cbar_ax,
9
                  cbar_kws={"orientation": "horizontal"}, center=0)
10

11
ax.xaxis.set_ticks_position("top")

1
year_range = range(2010, 2021)
2

3
def get_percentile_value(series, p):
4
    total = series.sum()
5
    return total/100*p
6

7
def find_bucket(series, value):
8
    sum = 0
9
    idx = 0
10
    for val in series:
11
        sum += val
12
        if sum >= value:
13
            return idx
14
        idx += 1
15

16
def bucket_for_year(df, year, p):
17
    series = df[year]
18
    return df.index[find_bucket(series, get_percentile_value(series, p))]
19

20
def format_percentile(p):
21
    if p > 50:
22
        return "Top {}%".format(100 - p)
23
    elif p < 50:
24
        return "Bottom {}%".format(p)
25
    else:
26
        return "Median (50%)"
27

28
def construct_median_pds(df, percentiles):
29
    values = {}
30
    for p in percentiles:
31
        data = []
32
        for y in year_range:
33
            bucket = bucket_for_year(df, str(y), p)
34
            data.append(bucket)
35
        values[format_percentile(p)] = data
36
    return pd.DataFrame(data=values, index=year_range)
37

38
percentile_df = construct_median_pds(data.drop('Total'), [5, 10, 25, 50, 75, 90, 95])
39
percentile_df

Calculating Income Ranges of Different Percentiles using CPI

1
import re 
2

3
def parse_value(str):
4
  str = str.replace(',', '', -1)
5
  regex_search = re.findall(r'$(.*) - $(.*)', str)[0]
6
  return list(map(lambda x: int(x), regex_search))
7

8
def get_cpi_by_year(year):
9
  return round(cpi_df.loc[cpi_df['year'] == year]['cpi'].values[0], 3)
10

11
def convert_value_to_2020_base(value, year):
12
  return round(value / get_cpi_by_year(year) * 100, 2)
13

14
def format_currency(value):
15
  return "{:,.2f}".format(value)
16

17
def format_values(values):
18
  return "${} - ${}".format(format_currency(values[0]), format_currency(values[1]))
19

20
cpi_2020_df = percentile_df.copy()
21

22
for col in percentile_df:
23
  for year in percentile_df.index:
24
    values = format_values(
25
      list(
26
        map(lambda x: convert_value_to_2020_base(x, year), parse_value(percentile_df[col][year]))
27
      )
28
    )
29

30
    cpi_2020_df[col][year] = values
31

32
cpi_2020_df