Space-Time-Solar-Ecology - a time map that is challanging (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 07:32
Joined
Oct 22, 2009
Messages
2,803
The question is: How to take this Cross-Tab of Sun's days (Calendar) and bring them into Columns of Earth's Year, Day calendar for the Value (X, ., *).

The following is a daily log of the Sun's Geomagnetic Field as observed from Earth. Or, as Affected by Earth. For those of you who didn't know the Sun rotates (e.g. a Day on the Sun), the observation is based on the same location of the Sun's Equator.
Please ignore the fact that the poles of the Sun rotate at a different rate than the Equator. The observations are made from a reference point of the Equator.
From the Earth's point of view (as the Earth is in orbit) the same Sun's Equator Location appears to the Earth's POV every 27 Days (and 45 minutes).

The Data format from 1926 to today is in this format: Top 7
1926 01 23 1272 XXXXXX.....XX.....XXXXXXXXX <== 27 columns for 27 Sun days
1926 02 19 1273 XXXXXX.....XX.XXX.XXXXX..X.
1926 03 18 1274 XXXX......XXX......XXXXXX..
1926 04 14 1275 X.XX.....XX.X.......XXXXXXX
1926 05 11 1276 XXXXXX...............XXX.X.
1926 06 07 1277 XXXXXXXX..........**XXXX..X
1926 07 04 1278 XXXX.XXX.............X.....

1926 is the Earth Year
01, 02 ... is the Earth Month.
the 23, 19, 18, 14, 11, 07, 04... is the Earth Year / Month Start date for the columns of data.

The Columns of data measure the direction of the Sun's Magnetic Lines of Force X, . , or * (North, South, or none)
Back to the 23, 19, 18, 14, 11, 07, 04...
From the Suns point of view, the same landmark (kind of like a Greenwich Mean Time Meridian for the Sun) comes back into alignment with the Earth.

So, the 27 data columns (, . , *) are constant. But, the Earth Start date actually is the offset of that Data Column. This means that the Yr/Mo are not always unique!

Attached is an Excel Workbook with the text data parsed into Excel with a small example. It has example data and a short desired output below.

This is one of a dozen observations for one index.
As the day ends here, this is on my plate to return to later.

Guessing it isn't just the standard pivot table. LOL

FYI: The solar wind is composed of charged particles ejected from the Sun that flow continuously through interplanetary space. The solar wind carries part of the Sun's magnetic field into space. Before completing this research, scientists knew that features of the solar wind reaching the Earth tended to repeat about every 27 days, said Neugebauer. The new information pinpoints the repetition interval at 27 days and 43 minutes and shows that the Sun has kept this steady rhythm, much like a metronome, for at least 38 years. (note: that was in 2003, it has still stayed steady)
 

Attachments

  • SolarPolarEx.xls
    27.5 KB · Views: 147

static

Registered User.
Local time
Today, 13:32
Joined
Nov 2, 2015
Messages
823
Unless there's a reason not to, :confused: just store it as is.

You can easily check that all values are present by checking the string's length and extract values from any position using mid().
 

plog

Banishment Pending
Local time
Today, 08:32
Joined
May 11, 2011
Messages
11,611
In Excel, you should write a macro that transforms your data as you need it. What I usually do is make a spreadsheet with a 'Raw' and 'Extracted' tab. I paste the data I get into the 'Raw' tab and set up the 'Extracted' tab with the column headers I want the end result to have.

Then I start writing VBA to go through line by line to extract the data as I need it from the Raw tab to the Extracted tab.

In terms of pseudo-code it will look like this:

Code:
Dim curr_ExtractedRow = 2
 ' will hold what row data is to go on in Extracted tab

Dim curr_Year, curr_Month, curr_Day
    ' will hold Year/Month/Day from row in Raw   

Dim tab_Raw, tab_Extracted
    ' will hold references to each tab of spreadsheet

Starting at Row 2 on tab_Raw For Each Row:
--store tab_Raw.ColumnA value in curr_Year
--store tab_Raw.ColumnB value in curr_Month
--store tab_Raw.ColumnC value in curr_Day
--Loop From 0-26:
----put curr_Year in tab_Extracted.ColumnA of curr_ExtractedRow
----put curr_Month in tab_Extracted.ColumnB of curr_ExtractedRow
----put (curr_Day + loop counter) in tab_Extracted.ColumnC of curr_ExtractedRow
----put value from corresponding column (E-AE) in tab_Extracted.ColumnD of curr_ExtractedRow
----increment curr_ExtractedRow
--End Loop

Run that on your data and then import the Extracted tab's data into Access.
 

Users who are viewing this thread

Top Bottom