Python Forum
HELP: Combine records in a table with missing start or end date
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
HELP: Combine records in a table with missing start or end date
#1
Hi guys, I'm a Clinical/Business Data Analyst for a large healthcare organization. I have recently started messing with Python to help me clean up some data, but I'm stuck. I have a report that has some start and end dates. Due to the nature of the end users, I have duplicate records because they entered some data on fields as start and ended the record on different fields with end date. I need to merge the records that have a start date but are missing and end date with the corresponding record missing a start date but have an end date. I migrated the data from Excel to SQLite using python so I can manipulate the records in either SQL or in Excel before exporting to SQLite. Here's a visual:
--------------------------------------------
ID |START |END |
1 |2019-11-6 17:30:00 |(NULL) |
2 |2019-10-5 19:31:06 |2019-10-5 19:47:33 |
3 |2019-09-6 11:21:06 |2019-09-6 12:47:39 |
1 |2019-10-6 01:21:06 |2019-10-6 02:39:32 |
1 |(NULL) |2019-11-6 17:39:00 |
--------------------------------------------

--------------------------------------------
ID |START |END |
1 |2019-11-6 17:30:00 |2019-11-6 17:39:00 |
2 |2019-10-5 19:31:06 |2019-10-5 19:47:33 |
3 |2019-09-6 11:21:06 |2019-09-6 12:47:39 |
1 |2019-10-6 01:21:06 |2019-10-6 02:39:32 |
--------------------------------------------
Reply
#2
Create a dictionary of dictionary using the IDs as the outer key and start and end as inner keys.
I will call the dictionary records
read each line in the table
if the id doesn't exist in records, set records @ id equal to a blank dictionary
if start is not NULL set the dictionary @ id @ start equal to that
if end is not NULL set the dictionary @ id @ end equal to that

Should only be 8 lines of code
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Compare current date on calendar with date format file name Fioravanti 1 123 Mar-26-2024, 08:23 AM
Last Post: Pedroski55
  Python date format changes to date & time 1418 4 518 Jan-20-2024, 04:45 AM
Last Post: 1418
  Date format and past date check function Turtle 5 4,069 Oct-22-2021, 09:45 PM
Last Post: deanhystad
  Pandas DataFrame combine rows by column value, where Date Rows are NULL rhat398 0 2,083 May-04-2021, 10:51 PM
Last Post: rhat398
  How to add previous date infront of every unique customer id's invoice date ur_enegmatic 1 2,191 Feb-06-2021, 10:48 PM
Last Post: eddywinch82
  Newbie Start with Text or Table? abrogard 5 2,469 Oct-18-2020, 12:47 PM
Last Post: jefsummers
  How to add date and years(integer) to get a date NG0824 4 2,804 Sep-03-2020, 02:25 PM
Last Post: NG0824
  Substracting today's date from a date in column of dates to get an integer value firebird 1 2,101 Jul-04-2019, 06:54 PM
Last Post: Axel_Erfurt
  How to change existing date to current date in a filename? shankar455 1 2,273 Apr-17-2019, 01:53 PM
Last Post: snippsat
  Updating records 1 to n on an SQLite table KevinBrown 2 2,609 Mar-30-2019, 05:02 PM
Last Post: KevinBrown

Forum Jump:

User Panel Messages

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