Python Forum
Pythonic way to standardize dates across two excel sheets?
Thread Rating:
  • 2 Vote(s) - 2 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Pythonic way to standardize dates across two excel sheets?
#1
Hello! I am using pandas and working with one financial excel sheet where I count streaks of consecutive increases or decreases. I loaded everything into a dataframe, used pct_change() then wrote a function that takes the dataframe and returns a list of paired tuples of (time, streak) for each column in the df. The time is saved as a numeric value corresponding to the row so sample data may be (5, 2), (10,3), etc. I have another sheet of weights/opinions - I want to find out what the weight was when a streak occurred. The dates in the excel sheet are broken down by ten minute intervals on the prices sheet and on the weights sheet there is just a weight for each day. The prices sheet has its Date column in format of Day/Month/Year TI:ME(military) and on the weight sheet Day/Month/Year.

This is where I don't know how to proceed. I didn't load the Date column of the price series into my dataframe so that's why the data of (time, streak) is saved by row# and not the actual time. I don't know what approach to take to cross check between the two sheets here. Right now my thought is that when a streak occurs I should take the row #, go back and check what date corresponds to it, then go to the other sheet with that date and find out what the value is. My other thought was to take the weight sheet and convert it into the same format as the price sheet. So I would need to find the start of each day's value then add that value 144 times (24*60/10) then do the same for the next day. That way I would have two dataframe with similar rows and I could just cross check between the two. Point of issue is that the dates don't start at the same time - that is a secondary issue to the fact that I don't know what to do, but if you can help addressing that as well I'd appreciate it.

Can someone help with some concept and pseudo code as for what is the best approach?
Reply
#2
Start here: http://pbpython.com/excel-file-combine.html
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to count total number of sheets in an excel workbook using polars sayyedkamran 0 630 Oct-27-2023, 09:54 PM
Last Post: sayyedkamran
  Reading Multiple Sheets using Pandas dhiliptcs 1 4,011 Sep-30-2019, 11:26 PM
Last Post: scidam
  Write data into existing Excel (xlsx) file with multiple sheets BNB 1 15,306 Jun-01-2017, 04:22 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020