Apr-19-2020, 03:38 PM
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
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