# Use a String For a Variable name and convert Dictionaries to DataFrames
**URL:** https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107
**Category:** General Usage
**Tags:** question, package, dictionary, dataframes
**Created:** [May 31, 2021, 4:35am UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107 "2021-05-31T04:35:52Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@brett\_knoss](https://discourse.julialang.org/u/brett_knoss)
#### Post date: [May 31, 2021, 4:35am UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/1 "2021-05-31T04:35:52Z")
I’m working on an accounting program, and I want to be able to have a function or macro that adds new account values to my dataframe, or another form that works as a CSV, XLSX or Database.
```julia
Accounts = Dict(
# A Dictionary of all my accounts
## List of Accounts and Starting Balance
# Chequing
"Chequing" => [0], #replace with balance as of Jan 1, 2019
#Assets
"Accounts Reciveable" => [0], #replace with balance as of Jan 1, 2019
#Debts
"Accounts Payable" =>[0])
spreadsheet = DataFrame(
Date=["Jan 1, 2000"], #start of the new year
chequing=[0],
accounts_reciveable=Accounts["Accounts Reciveable"],
#Debts
#Debts--Credit when owed payment, Debit when recive payment
accounts_payable= Accounts["Accounts Payable"])
macro transaction(
Journal_Entry,
newdate,
newdebit,
debit_amount,
newcredit,
credit_amount,
new_memo)
ex=quote
if $credit_amount != $debit_amount
"Error--Debit and Credit must match"
else
push!(spreadsheet,(defaultRow...,
Accounts["Chequing"]=
0,
Accounts["Wages"] = $debit_amount,
Accounts["Personal"]= $credit_amount,
Date=$newdate,
memo=$new_memo
))
#figure out how to delete this row, and get around repeat variables
# I might have to save each transaction to the spreadsheet, at this stage
end
if $credit_amount == $debit_amount
$Journal_Entry=DataFrame(
EntryDate=[$newdate,"","",""],
Credited_Accounts=["", $newcredit,"",""],
Debited_Accounts=["","",$newdebit,""],
Credit=[0,$credit_amount,0,0],
Debit=[0,0,$debit_amount,0],
Balance=[0,0,0,0])
end
end
esc(ex)
end
@transaction(JE3,"Jan 1, 2019", "Wages", 1000, "Chequing", 1000,"this is a test")
```
How do I get this to work?
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [May 31, 2021, 4:56am UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/2 "2021-05-31T04:56:58Z")
do you have a SAS background or something? wondering why u r using a macro. I didn’t read the code as it’s too long.
If you can simplify your problem and make an Minimal Working Example by following this guide
> [@Please read: make it easier to help you](https://discourse.julialang.org/t/psa-make-it-easier-to-help-you/14757):
>
> Welcome to the Julia Discourse! We are enthusiastic about helping Julia programmers, both beginner and experienced. This public service announcement (PSA) outlines best practices when asking for help. Following these points makes it easier for us to help you and more likely you’ll get a prompt, useful answer. Keywords are highlighted to make it easier to refer to specific points. Choose a descriptive title that captures the key part of your question, eg “plots with multiple axes” instead of …
An example is this really easy to follow post on how to ask question to maximize the chances of getting help:
> [@Sub-sample array until a target number of unique elements is left](https://discourse.julialang.org/t/sub-sample-array-until-a-target-number-of-unique-elements-is-left/61981):
>
> Hey! I’m in need of a fast method that uniformly subsamples the elements of a given array until only a specified number of unique elements is left. For example input array=[4,1,2,2,4] and target\_unique\_el=2, then the (random) result could be res=[4,2,2] (as it has two unique elements left). My current implementation is: using Random using BenchmarkTools function subsample\_unique!(array, target\_unique\_el) shuffle!(array) # for uniform sampling unique\_el = length(Set(array)) while …
For me, the original is too long and the code is too long. So I just switched and don’t wanna do it. I’d say, the more prep work you do the more likely you will get help in a timely manner.
---
### Author:  [@nilshg](https://discourse.julialang.org/u/nilshg)
#### Post date: [May 31, 2021, 6:53am UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/3 "2021-05-31T06:53:02Z")
I don’t think the example is necessarily too long, just a bit convoluted. My question would be the same though, why do you think you need a macro? It seems like all you need is a DataFrame that you `push!` new rows to for each transaction, and then (maybe?) calculate a new balance after the transaction in an additional column?
---
### Author:  [@brett\_knoss](https://discourse.julialang.org/u/brett_knoss)
#### Post date: [May 31, 2021, 7:10am UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/4 "2021-05-31T07:10:36Z")
The macro is because I plan to use it over and over. I could just use the dataframe, but I wanted to have the name of the account in a dataframe and I wasn’t able to do that with a dataframe.
---
### Author:  [@nilshg](https://discourse.julialang.org/u/nilshg)
#### Post date: [May 31, 2021, 8:00am UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/5 "2021-05-31T08:00:41Z")
I’m still 99.9% sure that you don’t need (and shouldn’t use) a macro here - using something “over and over” is not a reason to use a macro. In most scenarios that’s the use case a function is for.
In any case it’s quite hard to understand what you’re actually trying to do. Could you mock up an example of your desired use case and output? Doesn’t have to be working code, but at least it should help others understand the structure of your envisaged database.
---
### Author:  [@oheil](https://discourse.julialang.org/u/oheil)
#### Post date: [May 31, 2021, 8:40am UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/6 "2021-05-31T08:40:23Z")
Despite that the others are right, that this should be a function, not a macro, but looking at the code, it seems, that there is something wrong in your push! line of the macro:
```julia
push!(spreadsheet,(defaultRow...,
Accounts["Chequing"]=0,
Accounts["Wages"] = $debit_amount,
Accounts["Personal"]= $credit_amount,
Date=$newdate,
memo=$new_memo
))
```
First I can’t find why there is `defaultRow...,` in the `Tuple`. There is no hint to it in the documentation “[Functions · DataFrames.jl](https://dataframes.juliadata.org/stable/lib/functions/#Base.push!)” , so my guess is, this must be removed. Perhaps it’s from an older version of DataFrames, I am on v1.1.1.
But still, the `Tuple` doesn’t match your `spreadsheet`. It has 4 columns, and the Tuple you provide in push! has 5 columns. It seems you are mixing up your `spreadsheet` with that `Journal_Entry` you create.
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [May 31, 2021, 8:40am UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/7 "2021-05-31T08:40:44Z")
> [@brett\_knoss](#):
>
> macro is because I plan to use it over and over
Maybe in SAS u would use a macro for. I think what u want is a function. But the example is too long. Distill it to the simplest u can think of.
---
### Author:  [@brett\_knoss](https://discourse.julialang.org/u/brett_knoss)
#### Post date: [May 31, 2021, 5:23pm UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/8 "2021-05-31T17:23:48Z")
No, I think the problem is that trying to have dictionaries and dataframes
This did work
```julia
Accounts=DataFrame(
Date=["Jan 1, 2000"]
Chequing=[0],
Income=[0],
Expenses=[0])
Memo=["this is a memo"]
macro transaction(
Journal_Entry,
newdate,
newdebit,
debit_amount,
newcredit,
credit_amount,
new_memo)
ex=quote
if $credit_amount != $debit_amount
"Error--Debit and Credit must match"
else
push!(spreadsheet,(defaultRow...,
Accounts["Chequing"]=
0,
$newdebit = $debit_amount,
$newcredit=$credit_amount,
Date=$newdate,
memo=$new_memo
))
#figure out how to delete this row, and get around repeat variables
# I might have to save each transaction to the spreadsheet, at this stage
end
end
esc(ex)
end
@transaction(JE3,"Jan 1, 2019", "Wages", 1000, "Chequing", 1000,"this is a test")
```
The problem is when I try to add the Journal entry
```julia
$Journal_Entry=DataFrame(
EntryDate=[$newdate,"","",""],
Credited_Accounts=["", $newcredit,"",""],
Debited_Accounts=["","",$newdebit,""],
Credit=[0,$credit_amount,0,0],
Debit=[0,0,$debit_amount,0],
Balance=[0,0,0,0])
end
```
I can get this to work, by having a string for `newcredit` and `newdebit`, but then the first part doesn’t work.
What I want is a function or macro, that is able to create a journal entry, and add a new value to my general ledger. Is there a way to have a CSV from a dictionary, and add the last value of a dictionary to a dataframe?
---
### Author:  [@sijo](https://discourse.julialang.org/u/sijo)
#### Post date: [May 31, 2021, 6:44pm UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/9 "2021-05-31T18:44:31Z")
Let’s start with this piece of the puzzle:
> Is there a way to have a CSV from a dictionary, and add the last value of a dictionary to a dataframe?
Please give a small but complete example of the input and the result that you want. Just the data, but completely specified (this is different from your example because in your example we don’t know what is `JE3` and we don’t know what you expect as result; please show the result you want).
---
### Author:  [@brett\_knoss](https://discourse.julialang.org/u/brett_knoss)
#### Post date: [May 31, 2021, 8:18pm UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/10 "2021-05-31T20:18:24Z")
OK
I have a DataFrame with one row
Income Expenses
Chequing Wages Sales Rent Personal Inputs Ducks
100 0 0 100 0 0 0 0 0 0 0 0 0 0
Each account has a debit and a credit (1st and 2nd respectively. I want it to give me a error if the sum of the credits does not equal the sum of the debits
Else
I wan it to add a row to the DataFrame, like this
Chequing Wages Sales Rent Personal Inputs Ducks
100 0 0 100 0 0 0 0 0 0 0 0 0 0
0 100 0 0 0 0 0 0 10 0 20 0 70 0
and create a Journal Entry as a DataFrame
Date Debited Accounts Credited Accounts Debit Credit
“Jan 1, 2000” “Chequing” “” 100 0
“” “” “Personal” 10
“” “” “Inputs” 0 20
“” “” “Ducks” 0 70
“” “Total” “Balance” 100 100
And I want to have all of this in one line of code.
JE3 Stands for Journal Entry 3 , the important thing here is that it gives an error if I repeat the Journal Entry. That way, it’s harder to add a row to the first DataFrame by mistake.
---
### Author:  [@brett\_knoss](https://discourse.julialang.org/u/brett_knoss)
#### Post date: [May 31, 2021, 8:19pm UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/11 "2021-05-31T20:19:49Z")
Those are supposed to be aligned columns, maybe I’ll try to do it as a screenshot.
---
### Author:  [@xiaodai](https://discourse.julialang.org/u/xiaodai)
#### Post date: [May 31, 2021, 11:35pm UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/12 "2021-05-31T23:35:06Z")
> [@brett\_knoss](#):
>
> maybe I’ll try to do it as a screenshot.
A suggestion to make it better. Write the Julia code that would write the data into a CSV and is runnable.
E.g.
```julia
using CSV, DataFrames
df = DataFrame(col1 = [1,2,3], col2 =["def", "ghi", "abc"])
CSV.write("some_path.csv", df)
```
---
### Author:  [@brett\_knoss](https://discourse.julialang.org/u/brett_knoss)
#### Post date: [June 1, 2021, 1:54am UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/13 "2021-06-01T01:54:18Z")
```julia
General_Ledger=DataFrame(Chequing=[0],Inputs=[0])
JE3=DataFrame(
EntryDate=[$newdate,"","",""],
Credited_Accounts=["", "Chequing","",""],
Debited_Accounts=["","","Inputs",""],
Credit=[0,100,0,0],
Debit=[0,0,100,0])
```
```julia
General_Ledger=DataFrame(Chequing=[0,100],Inputs=[0,100]
```
---
### Author:  [@brett\_knoss](https://discourse.julialang.org/u/brett_knoss)
#### Post date: [June 1, 2021, 4:27am UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/14 "2021-06-01T04:27:56Z")
```julia
macro transaction(
journal_entry,
newdate,
newdebit,
debit_amount,
newcredit,
credit_amount,
new_memo)
ex=quote
if $credit_amount != $debit_amount
"Error--Debit and Credit must match"
else
push!(spreadsheet,(defaultRow...,
$newcredit=$credit_amount,
$newdebit= $debit_amount,
date=$newdate,
memo=$new_memo
))
end
$journal_entry=DataFrame(
Date=[$newdate,"","",""],
Credited_Accounts=["", String($newcredit),"",""],
Debited_Accounts=["","",String($newdebit),""],
Credit=[0,$credit_amount,0,0],
Debit=[0,0,$debit_amount,0],
Memo=["","","",$new_memo])
end
esc(ex)
end
@transaction(JE5,"Jan 1, 2019", wages, 1000, chequing, 1000, "this is a test")
```
This does everything I want, except save the name of an account as a string. I have to give each entry a new name, I need the debit and credit to balance. I just can’t have the name of the accounts.
---
### Author:  [@sijo](https://discourse.julialang.org/u/sijo)
#### Post date: [June 1, 2021, 6:49am UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/15 "2021-06-01T06:49:33Z")
Here’s a way to do it for what I understood of the task:
```julia
using DataFrames
# Initialize accounts data frame with one row
accounts = DataFrame(
Chequing_debit=[100], Chequing_credit=[0],
Wages_debit=[0], Wages_credit=[100],
Sales_debit=[0], Sales_credit=[0],
Rent_debit=[0], Rent_credit=[0],
Personal_debit=[0], Personal_credit=[0],
Inputs_debit=[0], Inputs_credit=[0],
Ducks_debit=[0], Ducks_credit=[0],
)
# Initialize journal with 4 rows
journal = DataFrame(Date=["Jan 1, 2000" for _ in 1:4],
Debited_account=["Chequing", "", "", ""],
Credited_account=["", "Personal", "Inputs", "Ducks"],
Debit=[100, 0, 0, 0],
Credit=[0, 10, 20, 70],
)
```
The initial data frames look like this:
```julia
accounts:
Row │ Chequing_debit Chequing_credit Wages_debit Wages_credit Sales_debit Sales_credit Rent_debit Rent_credit Personal_debit Personal_credit Inpu ⋯
│ Int64 Int64 Int64 Int64 Int64 Int64 Int64 Int64 Int64 Int64 Int6 ⋯
─────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
1 │ 100 0 0 100 0 0 0 0 0 0 ⋯
journal:
Row │ Date Debited_account Credited_account Debit Credit
│ String String String Int64 Int64
─────┼───────────────────────────────────────────────────────────────
1 │ Jan 1, 2000 Chequing 100 0
2 │ Jan 1, 2000 Personal 0 10
3 │ Jan 1, 2000 Inputs 0 20
4 │ Jan 1, 2000 Ducks 0 70
```
Now a function to add a transaction:
```julia
function transaction!(date, debit_account, debit_amount, credit_account, credit_amount)
if debit_amount != credit_amount
error("Debit ($debit_amount) and credit ($credit_amount) must match")
end
# Prepare row as named tuple, starting with zero values overwritten by values for the specified accounts
new_row = (; (Symbol.(names(accounts)) .=> 0)...,
Symbol(debit_account, "_debit") => debit_amount,
Symbol(credit_account, "_credit") => credit_amount)
push!(accounts, new_row)
push!(journal, (; Date=date, Debited_account=debit_account, Credited_account=credit_account,
Debit=debit_amount, Credit=credit_amount))
end
```
Test the function:
```julia
julia> transaction!("Jan 1, 2019", "Wages", 1000, "Chequing", 1000)
5×5 DataFrame
Row │ Date Debited_account Credited_account Debit Credit
│ String String String Int64 Int64
─────┼───────────────────────────────────────────────────────────────
1 │ Jan 1, 2000 Chequing 100 0
2 │ Jan 1, 2000 Personal 0 10
3 │ Jan 1, 2000 Inputs 0 20
4 │ Jan 1, 2000 Ducks 0 70
5 │ Jan 1, 2019 Wages Chequing 1000 1000
```
Note: this is all with integers… maybe you want to create the data frames with another number type.
---
### Author:  [@brett\_knoss](https://discourse.julialang.org/u/brett_knoss)
#### Post date: [June 1, 2021, 3:31pm UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/16 "2021-06-01T15:31:20Z")
OK, I you did a function, then used symbol. That should allow me to do a function
---
### Author:  [@sijo](https://discourse.julialang.org/u/sijo)
#### Post date: [June 1, 2021, 4:01pm UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/17 "2021-06-01T16:01:00Z")
Yes with a symbol and a pair `=>` you can generate a named tuple where the keys are computed. For example instead of `(; a=1, b=2)` you can write
```julia
sym = :b
(; a=1, sym => 2)
```
By the way, to define the new row for the data frame you can also use a Dict instead of a named tuple (this way you can use strings for the keys instead of symbols). Replace
```julia
new_row = (; (Symbol.(names(accounts)) .=> 0)...,
Symbol(debit_account, "_debit") => debit_amount,
Symbol(credit_account, "_credit") => credit_amount)
```
with
```julia
new_row = Dict((names(accounts) .=> 0)...,
debit_account*"_debit" => debit_amount,
credit_account*"_credit" => credit_amount)
```
---
### Author:  [@brett\_knoss](https://discourse.julialang.org/u/brett_knoss)
#### Post date: [June 1, 2021, 5:18pm UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/18 "2021-06-01T17:18:51Z")
```julia
function transaction!(date,journalentry, debit_account, debit_amount, credit_account, credit_amount)
if debit_amount != credit_amount
error("Debit ($debit_amount) and credit ($credit_amount) must match")
end
# Prepare row as named tuple, starting with zero values overwritten by values for the specified accounts
new_row = (; (Symbol.(names(accounts)) .=> 0)...,
Symbol(debit_account, "_debit") => debit_amount,
Symbol(credit_account, "_credit") => credit_amount))
push!(accounts, new_row)
Symbol(journalentry)=DataFrame(Date=[newddate],
Symbol(debited_account)=[0],
Symbol(credit_account)=[0],
balance=[0])
push!(journalentry, ( Date="",
Symbol(debited_account)=debit_amount,
Symbol(credited_accoun)=0,
Balance=0))
push!(journalentry, ( Date="",
Symbol(debited_account)=debit_amount,
Symbol(credited_accoun)=0,
Balance=0))
end
```
I tried to change it so that I could create a new journal entry for each transaction, with a unique name, so that Pluto repeat transactions, but it says I have an extra “)”.
---
### Author:  [@oheil](https://discourse.julialang.org/u/oheil)
#### Post date: [June 1, 2021, 5:23pm UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/19 "2021-06-01T17:23:11Z")
The extra `)` is in `new_row = ...`. Just remove the last one.
---
### Author:  [@sijo](https://discourse.julialang.org/u/sijo)
#### Post date: [June 1, 2021, 6:14pm UTC](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107/20 "2021-06-01T18:14:41Z")
And here’s a simpler way, in case it’s OK to have `missing` instead of `0` values for accounts that are not touched by a transaction:
```julia
using DataFrames
accounts = DataFrame()
journal = DataFrame()
function transaction!(date, debit_account, debit_amount, credit_account, credit_amount)
if debit_amount != credit_amount
error("Debit ($debit_amount) and credit ($credit_amount) must match")
end
push!(accounts, cols=:union, Dict(debit_account*"_debit" => debit_amount,
credit_account*"_credit" => credit_amount))
push!(journal, (; date, debit_account, credit_account, debit_amount, credit_amount))
end
transaction!("Jan 1, 2019", "Wages", 1000, "Chequing", 1000)
transaction!("Jan 2, 2019", "Personal", 300, "Rent", 300)
julia> accounts
2×4 DataFrame
Row │ Chequing_credit Wages_debit Rent_credit Personal_debit
│ Int64? Int64? Int64? Int64?
─────┼───────────────────────────────────────────────────────────
1 │ 1000 1000 missing missing
2 │ missing missing 300 300
julia> journal
2×5 DataFrame
Row │ date debit_account credit_account debit_amount credit_amount
│ String String String Int64 Int64
─────┼─────────────────────────────────────────────────────────────────────────
1 │ Jan 1, 2019 Wages Chequing 1000 1000
2 │ Jan 2, 2019 Personal Rent 300 300
```
For the journal, columns are created implicitly when the first row is added. For `accounts`, columns are added as required every time a row is pushed that uses new accounts. With the keyword argument `cols=:union` we don’t need to give default values for the row: unspecified values are set to `missing`.
[Next page](https://discourse.julialang.org/t/use-a-string-for-a-variable-name-and-convert-dictionaries-to-dataframes/62107.md?page=2)