0

I have two excel files:

excel file 1 which is shown on image 1, contains standard time series data.

excel file 2 which is shown on image 2, contains time series recording of different geographical location and the wind speed values of corresponding geographical location.

The aim of project is merged wind speed values of excel file 2 with standard time series of excel file 1, if the time series recording rows at excel file 2 match with the standard time series of excel file 1. The expected result is shown as image 3.

[Here is the link for the excel files] (https://drive.google.com/drive/folders/1xI6ccJ9QYDi7Np4Sbj8wpkafXllmXpp-?usp=drive_link)

Could our experts help me with that?

enter image description here

image 1

enter image description here

image 2

enter image description here

image 3

Improve this question
0

1 Answer 1

Reset to default
0

Well you will have to use a lot more dataframes than just two. From my perspective you should first revamp your csv/excel files headers as they are repetitive in the second file. The columns of second file should be:

aboyne-no-2-time, dyce-time, portglenone-time, aldergrove-time, ballypatrick-forest-time, aboyne-no-2, dyce, portglenone, aldergrove, ballypatrick-forest

This way u will have separate columns names for time and their count. Secondly, you should create basic dataframes first:

timestamp_df = pd.read_excel(r'F:\test\Time_series.xlsx')
time_speed_df = pd.read_excel(r'F:\test\test.xlsx')

Now you have five columns with timestamps on them but differently. So you have to join them based on the timestamp u want to follow, e.g.

aboyne-no-2
    aboyne_df = pd.merge(timestamp_df, time_speed_df[['aboyne-no-2-time','aboyne-no-2']], left_on='time_standard', right_on='aboyne-no-2-time', how='left')

Do the same things with other 5 as well. This will allow u to merge ur datasets based on timestamps of each name and only fetch their responsive count against it.

Helpful Material:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.