We will be migrating from Ask to Discourse on the first week of August, read the details here

# Automatic Series Numbering in Calc [closed]

Hi all,

I have a large data set representing the positions of particles through time. Each row is a single "snapshot" of a particle, and there are anywhere from 10 to 40 rows corresponding to each particle.

The beginning cell of each row has the particle number. So, for example, if I have 10 snapshots of particle 1 through time, I would have 10 rows, each beginning with "1", with a different position in each. Each row also has the particle velocity.

I would like to graph the velocity versus the position of each particle, with each particle in its own series (so each has its own line on the plot). How can I make Calc number the series based on the particle numbers?

I don't have enough Karma to upload a file. I will include a CSV snippet here:

Particle Number,X Centroid (µm),Y Centroid (µm),Velocity (µm/s),Deformation Parameter (W-H)/(W+H)
1,222.015991795,3758.0415342354,,-0.0833333333
1,222.984248255,3820.6932611265,38496.7336008294,-0.0847457627
1,223.7581421031,3892.8035540998,44305.8677241967,-0.0598290598
1,223.1496876992,3969.7013478402,47246.1529830517,-0.0598290598
1,225.4753719419,4051.9608901154,50559.034301425,-0.0588235294
1,222.5691311986,4137.2260915408,52415.8856346383,-0.0806451613
1,224.9039207106,4235.3305501849,60290.7489673911,-0.0743801653
1,224.2745440024,4335.1406446645,61322.8143663743,-0.0967741935
1,225.4821970478,4448.1026409769,69405.8621392055,-0.0819672131
1,225.4323949873,4572.2084882805,76248.4915651584,-0.0983606557
1,225.8785523883,4708.06991619,83471.360844772,-0.1056910569
1,226.197124848,4853.781466154,89522.8692990339,-0.1129032258
2,225.2324523214,2517.2939593425,,0.2440944882
2,224.0740738475,2618.6003442887,62244.958938006,0.2195121951
2,225.4794566456,2693.0737715051,45763.3314753951,0.1451612903
2,227.3462673167,2755.3171520603,38258.451794469,0.1016949153
2,223.1954193451,2814.6344437663,36532.6368423524,0.05
2,223.733288853,2861.6242126721,28871.5922790748,0.0256410256
2,221.2763285692,2907.7682285013,28390.2439397684,0.0344827586
2,220.9793206578,2952.1407133947,27262.2977272559,0.0178571429
2,222.9025981039,2992.5451624324,24851.9016692665,0
2,221.2061513443,3035.506995702,26415.5772270404,0.0178571429
2,219.9296240747,3077.8871452447,26049.4395260045,-0.0088495575
2,219.8068604319,3116.7611348532,23883.6257495288,0.009009009
2,218.1871438763,3154.134646344,22983.1924362137,-0.0088495575
2,221.0256653761,3197.5513884105,26731.4424258239,0
2,220.1840008748,3237.4367060177,24510.3044688437,0
2,220.3769149543,3273.729051241,22297.7040166857 ...
edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-11-05 05:52:19.573572

Sort by » oldest newest most voted

Hi ottobonn, please take a look to see if the attached file is in direction what you are looking for.

After import data from CSV or pasted as unformatted text in the same way as the CSV.

Create a Data Pilot with "Y Centroid" as row field, "particle number" as column field and "Velocity" (average) as Data field. I think in this way the data are how you need to do the chart.

AutomaticSeriesNumbering.jpg
Please change file extension to .ods, because maybe there is an error an Ask and do not allow upload ods files.

more

That is what I am looking for! Can you explain a bit further how to create the pivot table? Thanks.

( 2013-07-31 23:14:25 +0200 )edit

Ah, I see how you did it. A Pivot Table sorts the data by Y position, which putting them in columns by their particle number. Thanks!

( 2013-07-31 23:21:16 +0200 )edit

How large is large? If this is but a subset of your data, then maybe gnuplot or R (specifically RSQLite) might better serve your needs. Steep learning curve is a downside, though.

more