按空行拆分数据帧，按表头整形

Question

我有一个csv表，每个表有多个表，如下所示：

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

我是否可以将表按空行拆分，然后将它们重塑为一个数据帧，结果如下：

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

我想使用空行作为新的索引，并将每个表作为新的df读取。每个表的标题都是相同的，只是它们的顺序不正确。最终-我想把它们缝回一个干净的DF。

Answer 1

假设您的csv设置如下：

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

您可以使用以下不言自明的代码来处理此文件：

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]

所以你的输入是：

>>> 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

请注意，在本例中，报头在要提取的第二个数据帧中的顺序不同。

您的输出将是：

>>> 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