Python Forum
Help with poorly formatted excel data
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Help with poorly formatted excel data
#1
Hello - I have an excel file that is used to visually track and modify a schedule for multiple manufacturing lines. My goal is to write a python script that will reformat the data in the excel file into a format that can be used in more sophisticated visualizations (Tableu / Spotfire ect); however I am having a hard transposing the data.

The excel file is in a wide format, as below where each new column represents a new calendar week and the rows correspond to project information that is planned to be running during that week. The rows would be repeated for different manufacturing lines.

WEEK OF:, 6-Jan, 13-Jan, 20-Jan, 27-Jan, 3-Feb, .....
Project
Activity #1
Activity #2
Comment
Plant Contact

I`d like to reformat the data into a tall format, like below

Week OF:, Project, Activity #1, Activity #2, ect
13-Jan
20-Jan
27-Jan
3-Feb

I can bring the excel into python as a dataframe and slice the dataframe but am stuck on how to transpose it. Any advice?
Reply
#2
(Jan-13-2022, 12:00 AM)armitron121 Wrote: .... t am stuck on how to transpose it. Any advice?

With Pandas transpose ?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Data Sorting and filtering(From an Excel File) PY_ALM 0 1,012 Jan-09-2023, 08:14 PM
Last Post: PY_ALM
  Exporting data frame to excel dyerlee91 0 1,606 Oct-05-2021, 11:34 AM
Last Post: dyerlee91
  [Pandas] Write data to Excel with dot decimals manonB 1 5,775 May-05-2021, 05:28 PM
Last Post: ibreeden
  excel data erase d8a988 1 2,104 Apr-27-2020, 05:43 PM
Last Post: snippsat
  add formatted column to pandas data frame alkaline3 0 1,643 Mar-22-2020, 06:44 PM
Last Post: alkaline3
  Python read Excel cell data validation anantpatil 0 4,107 Jan-31-2020, 04:57 PM
Last Post: anantpatil
  Need Help With Filtering Data For Excel Files Using Pandas eddywinch82 9 5,990 Aug-06-2019, 03:44 PM
Last Post: eddywinch82
  Aligning excel data gat 1 2,185 Jun-17-2019, 07:05 PM
Last Post: michalmonday
  Copy raw data in excel to another new excel file keerthiprashanth 5 3,830 Oct-20-2018, 10:13 AM
Last Post: volcano63
  I'm working onn below code to extract data from excel using python kiran 1 3,238 Oct-24-2017, 01:42 PM
Last Post: kiran

Forum Jump:

User Panel Messages

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