1

I am filtering an external data source that is formatted as an excel file. There is nothing I can do to change how the file is generated. I need to filter out rows that are useless and combine pairs of rows into one. The process I have so far works on the filtering, but not on the joining of related data in two successive rows into one row.

The dataframes are not converted nicely for stackoverflow, but I have hand-tweaked them below.

Data Transformations

Convert downloads into useful formats

import pandas as pd
from pandas          import DataFrame
from pandas.io.excel import read_excel
cpath = os.path.join (download_path, classes_report)
print (pd.__version__)

df = pd.read_excel (cpath, sheetname=0, header=None)
df.to_string()

0.14.1

0 1 2 3 4 5 0 Session: 2014-2015 NaN NaN NaN NaN NaN 1 Class Information Age Enrolled Key Room NaN 2 Math 10 12 / 18 03396 110 09:00:00 3 Teacher: Joe M Teacher NaN NaN NaN NaN 4 NaN NaN NaN NaN NaN NaN 5 NaN NaN NaN NaN 6 NaN NaN NaN NaN 7 NaN NaN NaN NaN NaN NaN 8 NaN NaN NaN NaN NaN NaN 9 Number of Classes: 1 Number of Students: 12 / 18 NaN NaN NaN NaN 10 Class Information Ages Enrolled Key Room NaN 11 Art 18 - 80 3 / 24 03330 110 10:00:00 12 Teacher: John A Instructor NaN NaN NaN NaN 13 NaN NaN NaN NaN NaN NaN 14 NaN NaN NaN NaN 15 NaN NaN NaN NaN 

# Eliminate any rows where first column is NaN, contains 'Number of Classes', 'Class Information'
# or is blank
# The 5th column is tuition.

cf = df[df[0].notnull ()][1:]
cf = cf [~cf[0].str.contains ('Number of Classes')]
bf = cf[~cf[0].isin ([' ', 'Class Information'])]
bf.to_string()

0 1 2 3 4 5 2 Math 10 12 / 18 03396 110 09:00:00 3 Teacher: Joe M Teacher NaN NaN NaN NaN 11 Art 18 - 80 3 / 24 03330 110 10:00:00 12 Teacher: John A Instructor NaN NaN NaN NaN 

left  = DataFrame(bf.values [::2], index=bf.index[::2])
right = DataFrame(bf.values [1::2], index=bf.index[1::2])
pd.concat([left, right], axis=1).to_string ()

0 1 2 3 4 5 0 1 2 3 4 5 2 Math 10 12 / 18 03396 110 09:00:00 NaN NaN NaN NaN NaN NaN 3 NaN NaN NaN NaN NaN NaN Teacher: Joe M Teacher NaN NaN NaN NaN 11 Art 18 - 80 3 / 24 03330 110 10:00:00 NaN NaN NaN NaN NaN NaN 12 NaN NaN NaN NaN NaN NaN Teacher: John A Instructor NaN NaN NaN NaN

The goal here was to have the last five columns of the "Math" row to contain the columns starting with "Teacher:", and similarly for the "Art" row, leaving a dataframe with two rows instead of four.

Fred Mitchell
  • 2,145
  • 2
  • 21
  • 29
  • Try `pd.concat([left, right], axis=1, ignore_index=True)` – EdChum Apr 12 '15 at 19:55
  • 1
    You explicitly set the index for your right df, if it's the same number of rows and you want them to align why not just use the same indices as left: `right = DataFrame(bf.values [1::2], index=left.index)`? then the concat would produce what you want no? – EdChum Apr 12 '15 at 20:39
  • @EdChum - That works. If you write it as an answer, I can mark it as accepted. – Fred Mitchell Apr 12 '15 at 20:59

1 Answers1

1

Your attempt to concat aligned the 2 df's by index thereby producing a disjointed df with 4 rows rather than 2 rows:

right = DataFrame(bf.values [1::2], index=bf.index[1::2])

The above creates a new df using the values from your df but you also took the index values also, seeing as the left and right df's have the same number of rows and you're want to concatenate them column-wise so that the indices align then you can just use the same index from the left df:

right = DataFrame(bf.values [1::2], index=left.index)

This will produce the desired concatenated df.

EdChum
  • 376,765
  • 198
  • 813
  • 562