notebook.community

Edit and run



In [1]:

    
import io
import pandas as pd

from pathlib import Path

import stardog

conn = stardog.Connection('bs',
                          endpoint='https://localhost:443/stardog',
                          username='admin', password='admin')



In [2]:

    
query = """
prefix ledger: <https://github.com/jbalint/ledger-ontology#>

select ?category ?year ?month (sum(?amt) as ?spent)
from <http://banshee-sympatico/ledger>
{
{
  ?tx a ledger:Payment ; ledger:payee ?payee .
  ?tx ledger:time ?time ; ledger:post ?p.
  ?p ledger:amount ?pay_amt ; ledger:category ?category
  optional { { ?tx ledger:note ?note } union { ?p ledger:note ?note } }
  bind(year(?time) as ?year)
  bind(month(?time) as ?month)
  bind(?pay_amt * -1 as ?amt)
}
UNION
{
  ?tx a ledger:Income .
  ?tx ledger:time ?time ; ledger:post ?p.
  ?p ledger:amount ?amt ; ledger:category ?category
  optional { { ?tx ledger:note ?note } union { ?p ledger:note ?note } }
  bind(year(?time) as ?year)
  bind(month(?time) as ?month)
}
}
group by ?category ?year ?month
order by ?category
"""



In [3]:

    
# TODO: this query takes 2+ minutes. why?
csv_results = conn.select(query, content_type='text/csv')
df = pd.read_csv(io.BytesIO(csv_results))



In [4]:

    
df.head()









    Out[4]:







  
    
      
      category
      year
      month
      spent
    
  
  
    
      0
      https://github.com/jbalint/ledger-ontology#Exp...
      2005
      7
      -30.00
    
    
      1
      https://github.com/jbalint/ledger-ontology#Exp...
      2012
      4
      -36.71
    
    
      2
      https://github.com/jbalint/ledger-ontology#Exp...
      2015
      12
      -78.74
    
    
      3
      https://github.com/jbalint/ledger-ontology#Exp...
      2011
      1
      -109.60
    
    
      4
      https://github.com/jbalint/ledger-ontology#Exp...
      2014
      1
      -105.04



In [33]:

    
curr_year = df[df['year'] == 2019]

#curr_year["year"] = curr_year["year"].astype("str")
#curr_year["month2"] = curr_year["year"] + curr_year["month"].astype("str")
curr_year["month"] = curr_year[["year", "month"]].apply(lambda x : f"{x[0]}-{x[1]:02d}", axis=1)
curr_year["category"] = curr_year["category"].str.replace("https://github.com/jbalint/ledger-ontology#Expenses", "")
curr_year["category"] = curr_year["category"].str.replace("https://github.com/jbalint/ledger-ontology#Income", "")

curr_year.head()









    



/home/jbalint/sw/banshee-sympatico/drachma/venv/lib/python3.7/site-packages/ipykernel_launcher.py:5: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  """
/home/jbalint/sw/banshee-sympatico/drachma/venv/lib/python3.7/site-packages/ipykernel_launcher.py:6: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  
/home/jbalint/sw/banshee-sympatico/drachma/venv/lib/python3.7/site-packages/ipykernel_launcher.py:7: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  import sys






    Out[33]:







  
    
      
      category
      year
      month
      spent
    
  
  
    
      5
      Alcohol
      2019
      2019-03
      -552.54
    
    
      11
      Alcohol
      2019
      2019-11
      -181.86
    
    
      13
      Alcohol
      2019
      2019-02
      -97.79
    
    
      16
      Alcohol
      2019
      2019-08
      -286.05
    
    
      25
      Alcohol
      2019
      2019-06
      -236.90



In [34]:

    
curr_year.pivot_table(values="spent", index=["category"], columns="month", aggfunc=[sum], fill_value=0, margins=True)









    Out[34]:







  
    
      
      sum
    
    
      month
      2019-01
      2019-02
      2019-03
      2019-04
      2019-05
      2019-06
      2019-07
      2019-08
      2019-09
      2019-10
      2019-11
      2019-12
      All
    
    
      category
      
      
      
      
      
      
      
      
      
      
      
      
      
    
  
  
    
      Alcohol
      0.00
      -97.79
      -552.54
      -146.71
      -292.53
      -236.90
      -248.09
      -286.05
      -206.15
      -243.25
      -181.86
      -83.06
      -2574.93
    
    
      Bike
      0.00
      0.00
      0.00
      0.00
      -9.48
      -150.00
      0.00
      0.00
      0.00
      -212.93
      0.00
      0.00
      -372.41
    
    
      Books
      0.00
      0.00
      -13.17
      0.00
      0.00
      -32.36
      0.00
      0.00
      0.00
      -143.61
      -51.70
      -51.70
      -292.54
    
    
      Business
      0.00
      0.00
      -186.60
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      -432.60
      -619.20
    
    
      Camping
      0.00
      0.00
      0.00
      0.00
      -274.00
      100.50
      -199.70
      -31.99
      0.00
      0.00
      0.00
      0.00
      -405.19
    
    
      Car
      153.09
      0.00
      -2.00
      -8.35
      -597.50
      -2.00
      -1.75
      -219.89
      0.00
      -4.90
      -536.21
      0.00
      -1219.51
    
    
      Cash
      -500.00
      -100.75
      -200.00
      -10.00
      -300.00
      -150.00
      -280.00
      0.00
      -370.00
      0.00
      0.00
      -1264.22
      -3174.97
    
    
      Charity
      0.00
      0.00
      -81.41
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      -81.41
    
    
      Children
      -409.30
      -49.38
      -214.58
      -628.41
      -325.01
      -60.00
      -296.63
      -160.00
      -145.00
      -211.86
      -74.83
      -160.00
      -2735.00
    
    
      Clothes
      -351.39
      -373.91
      -386.75
      -313.54
      -178.59
      -508.85
      -494.06
      -105.19
      -364.04
      -406.26
      -477.37
      -186.87
      -4146.82
    
    
      College
      0.00
      -30.00
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      -30.00
    
    
      Computer
      -237.60
      5.33
      -73.06
      0.00
      -80.52
      -67.56
      0.00
      -242.64
      -99.99
      0.00
      0.00
      0.00
      -796.04
    
    
      Donation
      -15.00
      -15.00
      -15.00
      -65.00
      -15.00
      -65.00
      -15.00
      -40.00
      -35.00
      -30.00
      -30.00
      -172.00
      -512.00
    
    
      Drugs
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      -120.00
      0.00
      -130.00
      0.00
      0.00
      0.00
      -250.00
    
    
      Entertainment
      -51.80
      -666.19
      -32.60
      -442.75
      -148.61
      -80.75
      -343.82
      0.00
      0.00
      -384.00
      0.00
      -75.60
      -2226.12
    
    
      Fees
      -315.32
      -208.34
      -547.80
      -300.33
      -261.95
      -267.55
      -374.73
      -366.24
      -312.20
      -348.60
      -375.02
      -374.96
      -4053.04
    
    
      Gas
      -201.52
      -129.64
      -104.59
      -182.85
      -203.96
      -308.12
      -200.22
      -148.52
      -83.86
      -81.85
      -155.43
      -211.27
      -2011.83
    
    
      Gift
      -80.00
      -211.33
      -95.00
      -468.03
      -530.09
      -7467.65
      -20.00
      -383.27
      0.00
      -7.00
      -69.65
      -157.56
      -9489.58
    
    
      Grocery
      -1294.07
      -745.42
      -1357.27
      -764.49
      -1264.71
      -740.48
      -1062.12
      -1102.94
      -1039.75
      -1062.45
      -1140.70
      -639.58
      -12213.98
    
    
      Homeimprovement
      0.00
      0.00
      0.00
      0.00
      -195.78
      0.00
      0.00
      -723.82
      0.00
      -2319.07
      -664.87
      -776.07
      -4679.61
    
    
      Household
      -447.02
      -175.65
      -832.49
      -544.07
      -745.22
      -197.20
      -71.51
      -503.96
      -1551.24
      -948.69
      -460.95
      -635.33
      -7113.33
    
    
      Internet
      -89.76
      -86.61
      -122.95
      -90.82
      -86.61
      -87.65
      -88.61
      -88.61
      -99.15
      -99.15
      -92.83
      -94.82
      -1127.57
    
    
      Medical
      -445.47
      -72.00
      -758.50
      -653.93
      -668.70
      -416.68
      -230.34
      -326.62
      -398.00
      -649.56
      -382.53
      -310.60
      -5312.93
    
    
      None
      0.00
      0.00
      2927.00
      0.00
      0.00
      6200.00
      200.00
      0.00
      0.00
      0.00
      0.00
      0.00
      9327.00
    
    
      Pets
      -28.47
      -62.21
      -143.86
      -964.72
      -934.47
      -103.19
      -128.47
      -93.93
      -67.08
      -198.81
      0.00
      -216.84
      -2942.05
    
    
      Phone
      -122.14
      -157.71
      -126.70
      -120.23
      -156.94
      -141.38
      -145.02
      -152.82
      -139.47
      -147.57
      -142.92
      -138.53
      -1691.43
    
    
      Reimbursement
      0.00
      0.00
      0.00
      1469.59
      0.00
      0.00
      0.00
      0.00
      0.00
      0.00
      179.12
      0.00
      1648.71
    
    
      Rent
      -1724.47
      -1724.47
      -1724.47
      -1701.92
      -1701.92
      -1701.92
      -1701.92
      -1701.92
      -1701.92
      -1701.92
      -1701.92
      -1701.92
      -20490.69
    
    
      Restaurant
      -662.65
      -1067.85
      -700.08
      -1067.67
      -1342.53
      -977.28
      -764.23
      -891.81
      -855.81
      -1001.60
      -766.57
      -992.47
      -11090.55
    
    
      Salary
      8898.89
      8573.37
      8570.36
      10570.36
      8570.37
      8570.36
      10920.36
      8944.16
      8944.15
      8944.16
      9274.13
      9820.22
      110600.89
    
    
      Therapy
      -308.00
      -246.00
      0.00
      -787.50
      -761.00
      -480.00
      -1105.00
      -245.00
      -652.50
      -345.00
      -120.00
      0.00
      -5050.00
    
    
      Transportation
      0.00
      -19.40
      -40.54
      -67.21
      -110.99
      0.00
      0.00
      0.00
      0.00
      -119.18
      0.00
      -140.98
      -498.30
    
    
      Travel
      -85.68
      -1012.77
      -1109.24
      -1489.60
      -20.00
      -2005.25
      0.00
      0.00
      -4228.48
      -2484.46
      -525.00
      -887.40
      -13847.88
    
    
      Utility
      -318.16
      -377.82
      -380.15
      -298.30
      -232.89
      -228.92
      -215.32
      -356.49
      -267.84
      -239.39
      -216.46
      -270.02
      -3401.76
    
    
      All
      1364.16
      948.46
      1696.01
      923.52
      -2868.63
      -1605.83
      3013.82
      772.45
      -3803.33
      -4446.95
      1286.43
      -154.18
      -2874.07



In [37]:

    
curr_year.groupby(["category"])["spent"].sum().sort_values()









    Out[37]:





category
Rent               -20490.69
Travel             -13847.88
Grocery            -12213.98
Restaurant         -11090.55
Gift                -9489.58
Household           -7113.33
Medical             -5312.93
Therapy             -5050.00
Homeimprovement     -4679.61
Clothes             -4146.82
Fees                -4053.04
Utility             -3401.76
Cash                -3174.97
Pets                -2942.05
Children            -2735.00
Alcohol             -2574.93
Entertainment       -2226.12
Gas                 -2011.83
Phone               -1691.43
Car                 -1219.51
Internet            -1127.57
Computer             -796.04
Business             -619.20
Donation             -512.00
Transportation       -498.30
Camping              -405.19
Bike                 -372.41
Books                -292.54
Drugs                -250.00
Charity               -81.41
College               -30.00
Reimbursement        1648.71
None                 9327.00
Salary             110600.89
Name: spent, dtype: float64

	category	year	month	spent
0	https://github.com/jbalint/ledger-ontology#Exp...	2005	7	-30.00
1	https://github.com/jbalint/ledger-ontology#Exp...	2012	4	-36.71
2	https://github.com/jbalint/ledger-ontology#Exp...	2015	12	-78.74
3	https://github.com/jbalint/ledger-ontology#Exp...	2011	1	-109.60
4	https://github.com/jbalint/ledger-ontology#Exp...	2014	1	-105.04

	category	year	month	spent
5	Alcohol	2019	2019-03	-552.54
11	Alcohol	2019	2019-11	-181.86
13	Alcohol	2019	2019-02	-97.79
16	Alcohol	2019	2019-08	-286.05
25	Alcohol	2019	2019-06	-236.90

	sum
month	2019-01	2019-02	2019-03	2019-04	2019-05	2019-06	2019-07	2019-08	2019-09	2019-10	2019-11	2019-12	All
category
Alcohol	0.00	-97.79	-552.54	-146.71	-292.53	-236.90	-248.09	-286.05	-206.15	-243.25	-181.86	-83.06	-2574.93
Bike	0.00	0.00	0.00	0.00	-9.48	-150.00	0.00	0.00	0.00	-212.93	0.00	0.00	-372.41
Books	0.00	0.00	-13.17	0.00	0.00	-32.36	0.00	0.00	0.00	-143.61	-51.70	-51.70	-292.54
Business	0.00	0.00	-186.60	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	-432.60	-619.20
Camping	0.00	0.00	0.00	0.00	-274.00	100.50	-199.70	-31.99	0.00	0.00	0.00	0.00	-405.19
Car	153.09	0.00	-2.00	-8.35	-597.50	-2.00	-1.75	-219.89	0.00	-4.90	-536.21	0.00	-1219.51
Cash	-500.00	-100.75	-200.00	-10.00	-300.00	-150.00	-280.00	0.00	-370.00	0.00	0.00	-1264.22	-3174.97
Charity	0.00	0.00	-81.41	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	-81.41
Children	-409.30	-49.38	-214.58	-628.41	-325.01	-60.00	-296.63	-160.00	-145.00	-211.86	-74.83	-160.00	-2735.00
Clothes	-351.39	-373.91	-386.75	-313.54	-178.59	-508.85	-494.06	-105.19	-364.04	-406.26	-477.37	-186.87	-4146.82
College	0.00	-30.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	-30.00
Computer	-237.60	5.33	-73.06	0.00	-80.52	-67.56	0.00	-242.64	-99.99	0.00	0.00	0.00	-796.04
Donation	-15.00	-15.00	-15.00	-65.00	-15.00	-65.00	-15.00	-40.00	-35.00	-30.00	-30.00	-172.00	-512.00
Drugs	0.00	0.00	0.00	0.00	0.00	0.00	-120.00	0.00	-130.00	0.00	0.00	0.00	-250.00
Entertainment	-51.80	-666.19	-32.60	-442.75	-148.61	-80.75	-343.82	0.00	0.00	-384.00	0.00	-75.60	-2226.12
Fees	-315.32	-208.34	-547.80	-300.33	-261.95	-267.55	-374.73	-366.24	-312.20	-348.60	-375.02	-374.96	-4053.04
Gas	-201.52	-129.64	-104.59	-182.85	-203.96	-308.12	-200.22	-148.52	-83.86	-81.85	-155.43	-211.27	-2011.83
Gift	-80.00	-211.33	-95.00	-468.03	-530.09	-7467.65	-20.00	-383.27	0.00	-7.00	-69.65	-157.56	-9489.58
Grocery	-1294.07	-745.42	-1357.27	-764.49	-1264.71	-740.48	-1062.12	-1102.94	-1039.75	-1062.45	-1140.70	-639.58	-12213.98
Homeimprovement	0.00	0.00	0.00	0.00	-195.78	0.00	0.00	-723.82	0.00	-2319.07	-664.87	-776.07	-4679.61
Household	-447.02	-175.65	-832.49	-544.07	-745.22	-197.20	-71.51	-503.96	-1551.24	-948.69	-460.95	-635.33	-7113.33
Internet	-89.76	-86.61	-122.95	-90.82	-86.61	-87.65	-88.61	-88.61	-99.15	-99.15	-92.83	-94.82	-1127.57
Medical	-445.47	-72.00	-758.50	-653.93	-668.70	-416.68	-230.34	-326.62	-398.00	-649.56	-382.53	-310.60	-5312.93
None	0.00	0.00	2927.00	0.00	0.00	6200.00	200.00	0.00	0.00	0.00	0.00	0.00	9327.00
Pets	-28.47	-62.21	-143.86	-964.72	-934.47	-103.19	-128.47	-93.93	-67.08	-198.81	0.00	-216.84	-2942.05
Phone	-122.14	-157.71	-126.70	-120.23	-156.94	-141.38	-145.02	-152.82	-139.47	-147.57	-142.92	-138.53	-1691.43
Reimbursement	0.00	0.00	0.00	1469.59	0.00	0.00	0.00	0.00	0.00	0.00	179.12	0.00	1648.71
Rent	-1724.47	-1724.47	-1724.47	-1701.92	-1701.92	-1701.92	-1701.92	-1701.92	-1701.92	-1701.92	-1701.92	-1701.92	-20490.69
Restaurant	-662.65	-1067.85	-700.08	-1067.67	-1342.53	-977.28	-764.23	-891.81	-855.81	-1001.60	-766.57	-992.47	-11090.55
Salary	8898.89	8573.37	8570.36	10570.36	8570.37	8570.36	10920.36	8944.16	8944.15	8944.16	9274.13	9820.22	110600.89
Therapy	-308.00	-246.00	0.00	-787.50	-761.00	-480.00	-1105.00	-245.00	-652.50	-345.00	-120.00	0.00	-5050.00
Transportation	0.00	-19.40	-40.54	-67.21	-110.99	0.00	0.00	0.00	0.00	-119.18	0.00	-140.98	-498.30
Travel	-85.68	-1012.77	-1109.24	-1489.60	-20.00	-2005.25	0.00	0.00	-4228.48	-2484.46	-525.00	-887.40	-13847.88
Utility	-318.16	-377.82	-380.15	-298.30	-232.89	-228.92	-215.32	-356.49	-267.84	-239.39	-216.46	-270.02	-3401.76
All	1364.16	948.46	1696.01	923.52	-2868.63	-1605.83	3013.82	772.45	-3803.33	-4446.95	1286.43	-154.18	-2874.07