split dataframe by empty row and reshape by headers

Question

I have a csv sheet with multiple tables per sheet like this:

Name     Header-1     Header-8     Header 3
Random Note
Jack     X                         X
Jane                    X
NAN      NAN          NAN          NAN
Name     Header 3     Header 2     Header 7
Random note
Jeremy   X            X
Joey                               X

Could I split the tables by blank rows then reshape them into one dataframe to result like this:

Name     Header-1     Header-2     Header-3     .....
Jack     X
Jane                    X
Jeremy                              X
Joey         X          X            X

I would like to use the blank rows as a new index and read each table as a new df. The headers of each table are the same, they're just not all in the right order. Eventually - I'd like to stitch them back together into one clean DF.

Answer 1

Lets suppose you have the csv set as follows:

Name,Header-1,Header-2,Header-3
Random,Note, , 
Jack,X,X,   
Jane,X, , 
,,,
Name,Header-3,Header-2,Header-1
Random,note, , 
Jeremy,X,X, 
Joey, , ,X

You can work on this file with following code which is self explanotary:

import pandas as pd
# Read csv file
df = pd.read_csv("D:/tmp/data.csv", sep=',')

#Find columns which are null, create partitions and group by them
isnull = (df["Name"].isnull())
partitions = (isnull != isnull.shift()).cumsum()
gb = df[~isnull].groupby(partitions)
keys = gb.groups.keys()

# Extract all the dataframes
dfs = [gb.get_group(g) for g in keys]

datas = []
# Set the header as first row for all dataframes that are not the first one
for i,data in enumerate(dfs):
    if i!=0:    # First dataframe has already set the correct header
        data.columns = data.ix[data.index[0]]
        data = data.drop(data.index[0])
    datas.append(data)

# Concatenate the dataframes and reset the index
df_concat = pd.concat(datas)
df_out = df_concat.reset_index(drop=True)

# Change the order of the columns to get "Name" as first column
cols = df_out.columns.tolist()
cols = cols[-1:] + cols[:-1]
df_out = df_out[cols]

So your input is:

>>> df
     Name  Header-1  Header-2  Header-3
0  Random      Note                    
1    Jack         X         X           
2    Jane         X                    
3     NaN       NaN       NaN       NaN
4    Name  Header-3  Header-2  Header-1
5  Random      note                    
6  Jeremy         X         X          
7    Joey                             X

Note that in this example header is in different order in second dataframe to extract.

And your output would be:

>>> df_out
     Name Header-1 Header-2 Header-3
0  Random     Note                  
1    Jack        X        X         
2    Jane        X                  
3  Random                       note
4  Jeremy                 X        X
5    Joey        X