Hello!
I'm working with data from a bank exported in Excel. I have converted the file to CSV. The columns are A1 = 'state' flowed by A2.... a series of dates "yyyy/mm'. Beyond each label, the columns contain $$$ amounts in each cell. Rows 2, 3, 4, 5.... begin with "state" followed by amounts.
I am trying to build a network with nodes = states edges as dynamic outcome of $$$ flows but cannot seem to get the data structure correct. A copy of the file is attached.
Please, does anyone have advice on how to proceed? This is my first time working with this type of software, but I understand the idea and just need help getting started. A step-by-step guide or any assistance will be greatly appreciated.
Belinda
[SOLVED] Import from Excel-generated CSV
-
belindar
- Posts:1
- Joined:02 Jun 2011 21:04 [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1275: count(): Parameter must be an array or an object that implements Countable
- Attachments
-
- Pivot.csv
- (6.48KiB)Downloaded 329 times
Re: Import from Excel-generated CSV
Hey,
Financial data, cool!
An issue I see with your data is that to in order to create a network, you need a relation between your states:
$$$
State A <===> State B
From the file you sent, no such relation can be inferred, Since the financial figures are from one state, but there is no indication of which other state is involved in the transaction. In other words, your data say:
$$$
State A <===> ???
To create a network, you would need this type of data:
State A State B State C
State A x $245 $523
State B $245 x $963
State C $523 $963 x
Which would allow you to transform it into a list of edges
$245
State A <==> State B
etc...
Another format of data could be:
StateA, State B, $245, 06 June 2008
State A, State B, $450, 08 June 2009
Sate A, State C, $63, 8 March 2010
State C, State B, $45, 19 April 2010
Etc...
=> which would allow you to infer a list of nodes (State A, State B, State C) and a list of timestamped edges (well, almost identical to the one above).
In clear, the file you posted is not an adequate form of data for a network representation.
A last remark: are these data public? Just asking, because if not, this is a public forum here so it is important to anonymize any data that you post.
Best,
Clement
Financial data, cool!
An issue I see with your data is that to in order to create a network, you need a relation between your states:
$$$
State A <===> State B
From the file you sent, no such relation can be inferred, Since the financial figures are from one state, but there is no indication of which other state is involved in the transaction. In other words, your data say:
$$$
State A <===> ???
To create a network, you would need this type of data:
State A State B State C
State A x $245 $523
State B $245 x $963
State C $523 $963 x
Which would allow you to transform it into a list of edges
$245
State A <==> State B
etc...
Another format of data could be:
StateA, State B, $245, 06 June 2008
State A, State B, $450, 08 June 2009
Sate A, State C, $63, 8 March 2010
State C, State B, $45, 19 April 2010
Etc...
=> which would allow you to infer a list of nodes (State A, State B, State C) and a list of timestamped edges (well, almost identical to the one above).
In clear, the file you posted is not an adequate form of data for a network representation.
A last remark: are these data public? Just asking, because if not, this is a public forum here so it is important to anonymize any data that you post.
Best,
Clement
Re: Import from Excel-generated CSV
Note on the previous post: the presentation of the examples is not clear because the tab spaces I used disappeared when I submitted the post. In particular:
State A State B State C
State A x $245 $523
State B $245 x $963
State C $523 $963 x
Is meant to be a matrix:
-----------------State A ------- State B---------- State C
State A ----------x------------ $245---------------$523
State B---------$245------------- x---------------- $963
State C----------$523---------- $963---------------- x
Where the "x" represent an absence of data of course.
Also, the lines
$$$
State A <===> State B
and
$$$
State A <===> ???
Where meant to look like:
-----------$$$
State A <===> ???
-----------$$$
State A <===> State B
meaning: State A is connected to (another state), by a financial flow of value $$$
State A State B State C
State A x $245 $523
State B $245 x $963
State C $523 $963 x
Is meant to be a matrix:
-----------------State A ------- State B---------- State C
State A ----------x------------ $245---------------$523
State B---------$245------------- x---------------- $963
State C----------$523---------- $963---------------- x
Where the "x" represent an absence of data of course.
Also, the lines
$$$
State A <===> State B
and
$$$
State A <===> ???
Where meant to look like:
-----------$$$
State A <===> ???
-----------$$$
State A <===> State B
meaning: State A is connected to (another state), by a financial flow of value $$$