How do you create a new two-column data table with averaged individual values ​​in column B when the corresponding numerical values ​​in the original table are repeated in cell A? Of course, cell A in the new table should no longer contain duplicate value

English
#1

I have experimental data, A column is for x coordinate, B is for y coordinate. I need to average in certain way the data to let Mathematica Interpolate the data. “7.29845127380816” number is a duplicate. I need to recreate data file with no duplicates in A column by averaging the numbers in B or deleting the cells with duplicates in A and corresponding B cells to them.

A B
7.27748351129652 79.5776584142218
7.28796739255234 79.6054004435235
7.29845127380816 79.6331424728251
7.29845127380816 79.6054004435235
7.30893515506398 79.6608845021267
7.3194190363198 79.6886265314284
7.32990291757562 79.6886265314284
7.34038679883144 79.7441105900316
#2

If you would use a database: SELECT "A", AVG("B") FROM "Table" GROUP BY "A"
However, spreadsheets are no databases. The single spreadsheet feature, which comes closest to the above aggregation query is the pivot table.
https://help.libreoffice.org/25.2/en-US/text/scalc/guide/datapilot_createtable.html?DbPAR=CALC#bm_id3148491

1 Like
#3

How would be in a database to delete duplicates?

#4

That depends on which duplicates you want to delete and which one you want to keep.
Dupes.odb (24.4 KB)

Open the form.
Dupes with count on the left, related table records on the right side.

1 Like
#5

I meant the cells in A column with duplicates and B column cells with numbers corresponding to duplicates in A cells.

#6

So you want to discard all rows being duplicates in A without keeping a unique row?
Spreadsheet with data in A1:B:999:
C1: =COUNTIF($A1:$A999;$A1)>1
Copy down.
Filter by TRUE.
Select filtered data.
Delete rows.
Release filter.

1 Like
#7

I inserted a pivot table with A and B columns. Then, input the line:"SELECT “A”, AVG(“B”) FROM “Table” GROUP BY “A”. What am I doing wrong?

#8

A spreadsheet is not a database.
Add one row of column labels on top of your 2 columns.
Start the pivot table wizard.
Drag the first column label to “Row Fields”.
Drag the second column label to “Data Fields”.
Double–click the data field and change “Sum” to “Average”
Confirm the dialog.
ask118221.ods (82.5 KB)

2 Likes

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.