Python Forum
How can I convert specific rows from excel to pandas dataframe?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How can I convert specific rows from excel to pandas dataframe?
#1
Hello, I have some excel work sheets files that have this type of format (7400 groups of rows separated by two rows: "TEMP 25" and "1 Jan 1, 1989 6 -0.0398 1")

TEMP 25
10 20 30 12 2
50 66 55 3
32 2 3
1 Jan 1, 1989 6 -0.0398 1
TEMP 25
22 3 30
50 2 55 3
2 Jan 2, 1989 6 -0.0399 2
TEMP 25
23 33 43 5 72
1 100 3 23
2 44
23 34 100
3 Jan 3, 1989 6 -0.0407 3
... (more 7400 groups like the previous)

I need to convert each worksheet to a pandas df and then get the data into the following format:

OUTPUT: Pandas data frame
10 20 30 12 2 50 66 55 3 32 2 3
22 3 30 50 2 55 3
23 33 43 5 72 1 100 3 23 2 44 23 34 100

I need to union the rows inside each group of values. The only thing that is constant is the two rows that appear bettwen each group of data.
I can convert the excel file to a pandas df, but I donĀ“t know how to format the data. Can this be be easily done with pandas?

Thank you
Reply
#2
Please use proper code tags while writing your code
pyzyx3qwerty
"The greatest glory in living lies not in never falling, but in rising every time we fall." - Nelson Mandela
Need help on the forum? Visit help @ python forum
For learning more and more about python, visit Python docs
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to convert .trc/.txt file into excel using python ebola 3 1,991 Jan-15-2023, 10:37 PM
Last Post: Yoriz
  Pandas dataframe indexing pythonNovice 1 1,000 Jun-16-2022, 04:43 PM
Last Post: jefsummers
  Histogram using pandas dataframe not showing proper output ift38375 1 2,188 Jul-04-2019, 10:43 PM
Last Post: scidam
  Convert string to a specific number of bytes artblinked 1 2,424 Mar-28-2019, 08:43 PM
Last Post: Larz60+
  pandas dataframe next rows value metalray 2 10,144 Mar-06-2017, 11:31 AM
Last Post: metalray
  pandas dataframe group by count index metalray 5 10,280 Mar-01-2017, 09:14 AM
Last Post: metalray
  pandas convert to tuple & float to float64 metalray 6 17,723 Feb-27-2017, 12:50 PM
Last Post: metalray
  pandas dataframe.replace regex metalray 3 24,646 Feb-24-2017, 12:58 PM
Last Post: zivoni
  pandas dataframe substracting columns: key error metalray 2 7,037 Feb-24-2017, 07:59 AM
Last Post: metalray

Forum Jump:

User Panel Messages

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