Dane PIT po kodach TERC

%matplotlib inline
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import geopandas as gpd
from pysal.lib import weights
from pysal.explore import esda
from splot.esda import plot_moran

import os

# Project directory
pDir = "~/tcsrv/projects/data/pit_terc/"
mapDir = "~/tcsrv/data/mapy/prg_2023/"

pit_2023 = pd.read_excel(
    os.path.join(pDir, "dane", "pit_2023.xlsx"),
    dtype={'Kod województwa':str, 'Kod powiatu': str, 'Kod gminy': str}
)

# Dodaj 0 do kodów terc - przyda się przy tworzeniu kodów do merge
pit_2023['Kod województwa'] = pit_2023['Kod województwa'].str.pad(width=2,side='left',fillchar='0')
pit_2023['Kod powiatu'] = pit_2023['Kod powiatu'].str.pad(width=2,side='left',fillchar='0')
pit_2023['Kod gminy'] = pit_2023['Kod gminy'].str.pad(width=3,side='left',fillchar='0')


pit_2023.head()

pit_2023.iloc[:, 8:13].sum() / 1e6

po_formach_2023 = pit_2023.iloc[:, 7:13].groupby("Nazwa formularza").sum() / 1e6
po_formach_2023

# Ładuj dane za 2022 r.
pit_2022 = pd.read_excel(
    os.path.join(pDir, "dane", "pit_2022.xlsx")
)
po_formach_2022 = pit_2022.iloc[:, 7:13].groupby("Nazwa formularza").sum() / 1e6

# Licz średnie
sredni_dochod_23 = po_formach_2023.iloc[:, 2] / po_formach_2023.iloc[:, 0] * 1000
sredni_dochod_23.iloc[0] = \
    po_formach_2023.iloc[po_formach_2023.index == 'PIT-28', 1] / po_formach_2023.iloc[po_formach_2023.index == 'PIT-28', 0] * 1000

sredni_dochod_22 = po_formach_2022.iloc[:, 2] / po_formach_2022.iloc[:, 0] * 1000
sredni_dochod_22.iloc[0] = \
    po_formach_2022.iloc[po_formach_2022.index == 'PIT-28', 1] / po_formach_2022.iloc[po_formach_2022.index == 'PIT-28', 0] * 1000

# Do tabeli
po_formach_22_23 = pd.concat([sredni_dochod_22, sredni_dochod_23], axis=1)
po_formach_22_23.columns = ['Średnia 2022', 'Średnia 2023']
po_formach_22_23['Zmiana r/r %'] = po_formach_22_23['Średnia 2023'] / po_formach_22_23['Średnia 2022'] * 100 - 100

po_formach_22_23.style \
  .format(precision=1, thousands=" ", decimal=",") 

try:
    pl_gminy = gpd.read_file(os.path.join(mapDir, 'A03_Granice_gmin.shp'))
    woj = gpd.read_file(os.path.join(mapDir, 'A01_Granice_wojewodztw.shp'))
    print(pl_gminy.columns)
except ValueError as e:
    print(e)

pl_gminy.head()

fig, ax = plt.subplots(1, figsize=(14, 14))
pl_gminy.plot(ax=ax, linewidth=0.5, edgecolor='0.4', color='0.8')
ax.axis('off')

# Dodaj kod TERC
pit_2023['terc'] = pit_2023['Kod województwa'] + pit_2023['Kod powiatu'] + pit_2023['Kod gminy']
pit_2023_mapa = pl_gminy.merge(pit_2023, left_on='JPT_KOD_JE', right_on='terc', how='left')

pit_2023_mapa['sredni_doch'] = pit_2023_mapa['Kwota dochodu w tys. złotych'] / pit_2023_mapa['Liczba podatników']
pit_2023_mapa['sredni_przychod'] = pit_2023_mapa['Kwota przychodu                   w tys. złotych '] / pit_2023_mapa['Liczba podatników']

pit_2023_mapa.head()

fig, ax = plt.subplots(1, figsize=(12, 12))
woj.plot(ax=ax, edgecolor='0', color='0')
pit_2023_mapa[pit_2023_mapa['Nazwa formularza'] == 'PIT-37'].plot(
    ax=ax, 
    column='sredni_doch', 
    cmap='Spectral_r', 
    legend=True, 
    scheme='quantiles', 
    k=10,
    legend_kwds={'loc': 'lower left'}  # This line positions the legend in the bottom left
)
ax.set_title('Średni dochód na podatnika: PIT-37', fontdict={'fontsize': 20}, loc='left')
ax.axis('off')

fig, ax = plt.subplots(1, figsize=(12, 12))
woj.plot(ax=ax, edgecolor='0', color='0')
pit_2023_mapa[pit_2023_mapa['Nazwa formularza'] == 'PIT-36'].plot(
    ax=ax, 
    column='sredni_doch', 
    cmap='Spectral_r', 
    legend=True, 
    scheme='quantiles', 
    k=10,
    legend_kwds={'loc': 'lower left'}  # This line positions the legend in the bottom left
)
ax.set_title('Średni dochód na podatnika: PIT-36', fontdict={'fontsize': 20}, loc='left')
ax.axis('off')

fig, ax = plt.subplots(1, figsize=(12, 12))
woj.plot(ax=ax, edgecolor='0', color='0')
pit_2023_mapa[pit_2023_mapa['Nazwa formularza'] == 'PIT36L'].plot(
    ax=ax, 
    column='sredni_doch', 
    cmap='Spectral_r', 
    legend=True, 
    scheme='quantiles', 
    k=10,
    legend_kwds={'loc': 'lower left'}  # This line positions the legend in the bottom left
)
ax.set_title('Średni dochód na podatnika: PIT-36L', fontdict={'fontsize': 20}, loc='left')
ax.axis('off')

fig, ax = plt.subplots(1, figsize=(12, 12))
woj.plot(ax=ax, edgecolor='0', color='0')
pit_2023_mapa[pit_2023_mapa['Nazwa formularza'] == 'PIT-28'].plot(
    ax=ax, 
    column='sredni_przychod', 
    cmap='Spectral_r', 
    legend=True, 
    scheme='quantiles', 
    k=10,
    legend_kwds={'loc': 'lower left'}  # This line positions the legend in the bottom left
)
ax.set_title('Średni dochód na podatnika: PIT-28', fontdict={'fontsize': 20}, loc='left')
ax.axis('off')

# Formularze
forms = ['PIT-37', 'PIT-36', 'PIT36L', 'PIT-28']

# Stwórz wagi i policz Moran I
moranI = {}
for form in forms:
    df = pit_2023_mapa[pit_2023_mapa['Nazwa formularza'] == form]
    
    # Wagi
    w = weights.KNN.from_dataframe(df, k=8)
    w.transform = "R"  # Row-standardize the weights
    
    # Moran's I
    if form == 'PIT-28':
        moranI[form] = esda.moran.Moran(df['sredni_przychod'], w)
    else:
        moranI[form] = esda.moran.Moran(df['sredni_doch'], w)

# Print results
for key, value in moranI.items():
    print(f"{key}: {value.I:.3f}, pSim: {value.p_sim:.3f}")

plot_moran(moranI['PIT-36'])

plot_moran(moranI['PIT-36L'])

plot_moran(moranI['PIT-28'])