Writing data to csv

Specific Domains Data
1

Hi Guys, I am working with data set and writing my results back to the csv file. my code is

Result = [permutedims(Names); K2Score; ShortestPath; score; cum_weight]
save_file = save_dialog("Save Hierarchy as...")
CSV.write(save_file, Tables.table(Result::AbstractMatrix),  writeheader = false)

But in my csv, second row is not printed completely. Please see the attachment. I am am not able to figure out why this problem is occurring. Please suggest
Hierarchy_2409_20_2.csv

FC6319|FC343|FC347|FC342|FC345|FC6412|FC624|FC341|FC346|FC623|FC521|FC511|FC6327|FC121|FC647|FC641|FC229|FC223|FC212|FC6318|FC6320|FC643|FC221|FC612|FC6326|FC6413|FC324|FC228|FC6415|FC6321|FC621|FC634|FC214|FC646|FC6411|FC331|FC323|FC311|FC122|FC6317|FC6417|FC622|FC6322|FC6416|FC112|FC6414|FC142|FC141|FC213|FC411|FC6324|FC635|FC222|FC348|FC6329|FC6418|FC344|FC6410|FC531|FC611|FC131|FC6313|FC6328|FC644|FC642|FC6325|FC6323|FC211|FC111|FC633|FC6315|FC431|FC6314|FC649|FC645|FC648
-0.1|-6.946975992|-9.704060528|-10.13459927|-11.76523939|-5.318119994|-16.48511095|-17.4180475|-8.014335737|-19.12279559|-21.06519192|-22.29539557|-22.42892697|-22.85249704||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
0|6.946975992|9.704060528|10.13459927|11.76523939|12.26509599|16.48511095|17.4180475|18.14893501|19.12279559|21.06519192|22.29539557|22.42892697|22.85249704|22.99208407|24.88875998|26.61810894|27.22223017|27.27018184|27.82719685|28.71388505|28.71816977|28.87928912|29.30310337|30.80718076|31.14169739|31.27883441|31.90198388|32.05983655|32.19512514|33.55005958|34.29199693|34.38689053|35.11978237|36.17917395|36.29903915|36.36459274|36.41463284|36.52898174|36.72125212|36.9248774|37.22907688|37.75155597|38.68059033|39.81934404|40.49205992|40.56211362|40.95509488|41.00858356|41.70400089|42.27212758|42.66619665|43.33802818|43.73873046|44.82420051|45.4864064|46.37319664|46.82936445|48.68645221|48.98534841|52.84866113|54.12504517|54.61579103|54.98143365|56.63497877|57.93950342|58.42245352|60.13932878|60.33615502|62.41888179|66.76674417|69.31378239|70.26831084|74.42812819|79.69290729|100.1011926
10|9.375404205|9.12751744|9.088808124|8.942198871|8.897257255|8.517839851|8.433960441|8.368247062|8.280688213|8.106049265|7.995442862|7.983437185|7.945354415|7.932804283|7.762276013|7.606791945|7.552475998|7.54816471|7.498084038|7.418362772|7.417977536|7.403491454|7.365386731|7.230156608|7.200080546|7.187750692|7.131723934|7.117531555|7.105367891|6.983547065|6.916840206|6.908308416|6.842414829|6.747165972|6.736389009|6.73049515|6.725996094|6.715715096|6.698428256|6.680120506|6.65277023|6.605794647|6.52226608|6.419881851|6.359398627|6.353100167|6.317767608|6.312958492|6.250434203|6.19935449|6.163924127|6.103520413|6.067493664|5.969900117|5.910361835|5.830631395|5.789617795|5.622648857|5.595775393|5.248428737|5.1336703|5.089547821|5.056673253|4.908004633|4.790716102|4.747294532|4.592931963|4.575235508|4.387979589|3.997067548|3.768065841|3.682245125|3.308240029|2.834888906|1
0.020064421|0.018811206|0.018313835|0.018236167|0.017942004|0.017851832|0.017090553|0.016922253|0.016790403|0.016614722|0.016264319|0.016042393|0.016018305|0.015941894|0.015916713|0.015574557|0.015262588|0.015153606|0.015144956|0.015044472|0.014884515|0.014883743|0.014854677|0.014778222|0.014506891|0.014446545|0.014421806|0.014309391|0.014280915|0.014256509|0.014012083|0.013878239|0.013861121|0.013728909|0.013537798|0.013516175|0.013504349|0.013495322|0.013474694|0.013440009|0.013403275|0.013348398|0.013254145|0.013086549|0.012881121|0.012759765|0.012747128|0.012676235|0.012666586|0.012541134|0.012438646|0.012367557|0.01224636|0.012174075|0.011978259|0.011858799|0.011698824|0.011616533|0.011281519|0.011227599|0.010530668|0.010300412|0.010211883|0.010145922|0.009847627|0.009612295|0.009525172|0.009215452|0.009179945|0.008804227|0.008019885|0.007560406|0.007388212|0.006637792|0.00568804|0.002006442
2

Instead of this line, create a DataFrame

DataFrame([K2Score; ShortestPath; score; cum_weight], Names)

then try saving the CSV.

1 Like
3

Hi @pdeffebach Thanks for your response. I tried to implement the change you have suggested. I got following error.

MethodError: no method matching sortslices(::DataFrame; dims=2, by=var"#5#6"())
Closest candidates are:
  sortslices(::AbstractArray; dims, kws...) at multidimensional.jl:1751

Stacktrace:
 [1] top-level scope at In[7]:16
 [2] include_string(::Function, ::Module, ::String, ::String) at .\loading.jl:1091
4

@ashwanimalviya, please read the guidelines here and post again your MWE in proper format. Thank you.

5

Hi, @rafael.guerra Thanks for the suggetion. I have edited it. I hope it is perfect formate what I understand by the above link.

6

Sorry but this still isn’t an MWE - save_dialog is not defined. Also, none of the variables in Result are defined.

Can you copy the output you get when you run Peter’s suggestion (without any writing to csv, just what’s printed in the REPL):

julia> DataFrame([K2Score; ShortestPath; score; cum_weight], Names)
7

Hi @nilshg
Thanks for your reply. I try my best to let you understand the problem.

I am using the following packages in Julia version 1.5.3

import Pkg

Pkg.add("BayesNets")
Pkg.add("DataFrames")
Pkg.add("Discretizers")
Pkg.add("LightGraphs")
Pkg.add("Printf")
Pkg.add("CSV")
Pkg.add("Random")
Pkg.add("SpecialFunctions")
Pkg.build("SpecialFunctions")
Pkg.add("TikzGraphs")
Pkg.add("TikzPictures")
Pkg.add("PGFPlots")
Pkg.add("Plots")
Pkg.add("LaTeXStrings")
Pkg.add("GraphPlot")
Pkg.add("StatsPlots")
Pkg.add("MetaGraphs")
Pkg.add("Colors")
Pkg.add("GR")
Pkg.add("Gtk")

I have created some of my own function to make all the calculations as follow.

include("K2Algorithm_Updated.jl")
include("RepalceZerosInK2Score.jl")
include("RepalceK2ScoreInDAG.jl")
include("FindRoot.jl")
include("ShortestPath.jl")
include("WeightCalculation.jl")

# Application of functions
DAG, Graph, Names, BS, K2Score  = main(20,true);
PlotNames = convertNames2string(Names);
GRAPH = TikzGraphs.plot(Graph, PlotNames, node_style="draw, rounded corners, fill=blue!10")


K2Score = replaceZeros(K2Score)
distmx = replace_K2S_in_DAG(DAG, K2Score)
root = Find_root(DAG)
ShortestPath = Shortest_Path(Graph, root, distmx)
# Calculation of final result
score = Weight(ShortestPath)
total_weight = sum(score)
cum_weight = score./total_weight
Result = DataFrame([K2Score; ShortestPath; score; cum_weight], permutedims(Names))
# Making heirarchy by arranging the result in ascending order.
xs = Result
Result = sortslices(xs; dims=2, by=x->x[3])
#Exporting the result into .csv file
save_file = save_dialog("Save Hierarchy as...")
CSV.write(save_file, Tables.table(Result::AbstractMatrix),  writeheader = false)

I have used permutedims() to transpose Names .

save_dialog is from Gtk package and has been used to save the final result at a specific place.

Following is the error msg from REPL and it is same for with and without csv.

MethodError: no method matching DataFrame(::Array{Float64,2}, ::Array{String,2})

You might have used a 2d row vector where a 1d column vector was required.
Note the difference between 1d column vector [1,2,3] and 2d row vector [1 2 3].
You can convert to a column vector with the vec() function.
Closest candidates are:
  DataFrame(::AbstractArray{T,2} where T) at deprecated.jl:70
  DataFrame(::AbstractArray{T,2} where T, ::AbstractArray{Symbol,1}; makeunique) at C:\Users\tecnico2\.julia\packages\DataFrames\3mEXm\src\dataframe\dataframe.jl:322
  DataFrame(::AbstractArray{T,2} where T, ::AbstractArray{var"#s45",1} where var"#s45"<:AbstractString; makeunique) at C:\Users\tecnico2\.julia\packages\DataFrames\3mEXm\src\dataframe\dataframe.jl:327
  ...

Stacktrace:
 [1] top-level scope at In[16]:9
 [2] include_string(::Function, ::Module, ::String, ::String) at .\loading.jl:1091

Thanks again for taking your time. I hope this will be more clear. Please write if you need anymore information.

Ashwani

8

Names is not defined in your code afaict, but that’s where your problem lies. Peter’s suggestion is asking you to do this:

julia> using DataFrames

julia> x = rand(3, 3)
3Ă—3 Matrix{Float64}:
 0.568797  0.879866  0.178767
 0.699596  0.925057  0.318236
 0.673037  0.847415  0.22209

julia> names = ["a", "b", "c"]
3-element Vector{String}:
 "a"
 "b"
 "c"

julia> DataFrame(x, names)
3Ă—3 DataFrame
 Row │ a         b         c        
     │ Float64   Float64   Float64  
─────┼──────────────────────────────
   1 │ 0.568797  0.879866  0.178767 
   2 │ 0.699596  0.925057  0.318236 
   3 │ 0.673037  0.847415  0.22209

As you can see from your error, your Names seems to be a matrix rather than a vector, maybe because you are calling permutedims. Just make sure that Names is a vector and you should be good to go.

1 Like
9

I just mean make it a dataframe for printing to CSV, not for your whole analysis.

I mean, Tables.table should work, and its not great that it doesn’t. But its easier to diagnose what’s going on when you call DataFrame. And it’s more likely to solve your immediate problem and have it “just work”.

10

Dear @rafael.guerra and @pdeffebach Thank you very much for your time take for this issue.

@rafael.guerra suggestion worked for until line 9, by using vec() for Names.

K2Score = replaceZeros(K2Score)
distmx = replace_K2S_in_DAG(DAG, K2Score)
root = Find_root(DAG)
ShortestPath = Shortest_Path(Graph, root, distmx)
# Calculation of final result
score = Weight(ShortestPath)
total_weight = sum(score)
cum_weight = score./total_weight
Result = DataFrame([K2Score; ShortestPath; score; cum_weight], vec(Names))

REPL print is 





4 rows Ă— 82 columns (omitted printing of 75 columns)

FC111	FC1141	FC1142	FC113	FC1321	FC214	FC2161
Float64	Float64	Float64	Float64	Float64	Float64	Float64
1	-280.721	-314.661	-278.807	-290.976	-295.954	-262.717	-198.453
2	389.652	1362.99	1048.33	680.628	769.524	1032.24	473.57
3	7.45589	1.10078	3.15526	5.55605	4.97564	3.26031	6.90797
4	0.0128894	0.00190298	0.00545468	0.00960508	0.00860168	0.00563629	0.0119422

I have to sort this output by row 2 in ascending order. So when I am using

# Making heirarchy by arranging the result in ascending order.
xs = Result
Result = sortslices(xs; dims=2, by=x->x[2])

I am getting the following error.

MethodError: no method matching sortslices(::DataFrame; dims=2, by=var"#17#18"())
Closest candidates are:
  sortslices(::AbstractArray; dims, kws...) at multidimensional.jl:1751

Stacktrace:
 [1] top-level scope at In[30]:12
 [2] include_string(::Function, ::Module, ::String, ::String) at .\loading.jl:1091

Is sortslices() defined in DataFrames? Can you please suggest what best methods can be for sorting by row in DataFrames?

11

This is a different problem though, it looks like now you want to sort a DataFrame which you can do by just

sort!(Result, :FC1141)

(assuming that that’s the column you want to sort by).

12

Hi @nilshg thanks but I want to sort rows by values obtained in row 2. sort!() method works only for sorting by columns I guess.

13

Ah right, that is not a good usecase for DataFrames, in that case I would sort the matrix before turning it into a DataFrame, but again all that is likely unrelated to your issue with writing a csv.

14

OP, please read my post earlier. I am suggesting only making a DataFrame for the purposes of writing to CSV, not for your whole analysis. I’m just trying to help you debug your problem with writing to CSV.

1 Like
15

Hi @pdeffebach I applied your idea of turning it in DataFrames just before writing in csv like below

Result = [permutedims(Names); K2Score; ShortestPath; score; cum_weight]
xs = Result
Result = sortslices(xs; dims=2, by=x->x[3])
Result = DataFrame(Result)
save_file = save_dialog("Save Hierarchy as...")
CSV.write(save_file, Result,  writeheader = false)

Although I am not getting any error but old problem persists.
Any other suggestion?

16

Hi @nilshg and @pdeffebach I found that there is something wrong with the variable K2Score . I tried to export it separately into csv but all columns are not being printed. I tried to convert it into DataFrame and print but the problem persists. The REPL is printing all columns but while printing in csv is the problem. I can not understand what is going wrong with me. typeof(K2Score) is Array{Float64,2}

17

How are you writing it to csv? If it’s an Array presumably you are doing something else to it beforehand because

julia> using CSV

julia> CSV.write("test.csv", rand(10, 10))
ERROR: ArgumentError: a 'Matrix{Float64}' is not a table; see `?Tables.table` for ways to treat an AbstractMatrix as a table
18

Hi @nilshg I figured out the problem. K2Score contains all negative values of Float64. When I changed these values to positive and my problem solved.
I I have got a serious doubt that If there is negative values in our calculation then the problem occur every time?
Is there anyone who has faced a similar problem while writing CSV which contains negative values and all the columns is not printed correctly?

19

That is a very odd error, and likely what’s going on is slightly different.

If you can show us a little bit of K2Score we will be able to help you better understand the problem.

```
use three backticks like this 
```
1 Like
20

Hi, @pdeffebach I guess you would like to see the code of K2Score function.

function k2(DataObj,Order,u)

    # implementation of the K2 algoithm.
    # input is the DataObj which is the julia version of a Object. Contains all the information of the dataset
    # Order is the given topological sort for network
    # u is the max number of parents for a given node


    # copying the object to a new variable

    LG = DataObj;

    #number of variables in the dataset
    Dim = LG.VarNumber;

    # initial graph structure. We assume fully unconnected graph
    DAG = zeros(Dim,Dim);

    # initialize the K2Score this is dependant on the G-function and provides the search strategy for
    # finding the network structure
    K2Score = zeros(1,Dim);

    for p = 2:Dim

        # initialize a parent vector for a given node. The for loop means we are going to try a
        # a different number of parents for the node

        parent = zeros(Dim,1);

        # Ok is a helper function so that when we do not find a better score we break out of the loop and move on

        Ok = 1;
        P_old = -Inf; #the initiate state

        # sum of parents must be less than or equal to u since u is our max number of parents for a given node

        while Ok == 1 && sum(parent) <= u

            #initial local max
            LocalMax = -Inf;

            # our initial node
            LocalNode = 0;

            # q is going to be a node from the topological sort to adjust
            for q = (p-1):(-1):1

                if parent[Order[q]] == 0

                    # Don't forget that Order is our topological sort so when we index order we are
                    # selecting a node. Now by indexing parent, we are seeing if there is a parent for this node.

                    # greedily add a parent to this node
                    parent[Order[q]] = 1;

                    # finder finds (shocker haha) the indicies of parent which correspond to the children

                    temp_parent = reshape(parent,length(parent),)



                    finder = reshape(findall(x -> x==1,temp_parent),1,length(findall(x -> x==1,temp_parent)));

                    # calculate the G-function on this neighborhood operation to see if it improves our score
                    LocalScore = GFunction(DataObj, Order[p],finder);

                    # conditional statement to say which neighborhood option to choose
                    if LocalScore > LocalMax
                        LocalMax = LocalScore;
                        LocalNode = Order[q]
                    end
                    parent[Order[q]] = 0;
                end
            end

            # update our state to the new value
            P_new = LocalMax;
            if P_new > P_old
                P_old = P_new;

                # if the score is better then update our parent to now have the child
                parent[LocalNode] = 1;
            else

                # if it is not then break out of the loop and move on
                Ok = 0;
            end
        end
        K2Score[Order[p]] = P_old;
        DAG[:,Order[p]] = parent;
    end

    # how you return values in julia
    DAG ,K2Score
end

Please let me know if you need anything more.