Parsing Pandas dataframe

Question

I have the following data in a single data frame which I parsed from XML

index                               xml_data    
0                                   \n      
1                               sessionKey  
2            JKX6G3_07092016_1476953673631  
3                                   \n      
4                                   Number  
5                                   JKX6G3  
6                                   \n      
7                            CreateDate 
8                            1468040400000  
9                                   \n      
10                              Id  
11                                83737626  
12                                       1  
13                                  \n      
14                             customerAge  
15                                      64  
16                                       1  

I like to make every row after "\n" a column and value associated with the column is the next row for example:

sessionKey  Number  CreateDate  Id  Age

JKX6G3_07092016_1476953673631   JKX6G3  1.46804E+12 83737626    64

Is there a more elegant way of doing this than: for row in doc_df.itertuples(): and going through each row and parse?

Answer 1

I'd look for the positions of the \n and add one to locate keys, and add 2 for values. Then build an array and a subsequent dataframe

v = df.xml_data.values
a, b = np.where(v == '\\n')[0][None, :] + [[1], [2]]
pd.DataFrame([v[b]], columns=v[a])

                      sessionKey  Number     CreateDate        Id customerAge
0  JKX6G3_07092016_1476953673631  JKX6G3  1468040400000  83737626          64

Answer 2

import pandas as pd
import numpy as np

# set dataframe
...

# get columns name
columns = []
count_n = 0
for i in range(0, len(df)-1):
    if (df.iloc[i]['xml_data'] == '\\n'):
        columns.append(df.iloc[i+1]['xml_data'])
        count_n += 1

# generate new df    
new_df = pd.DataFrame(columns = columns, index = np.arange(count_n))
j = 0
count = 0
# set values
for i in range(0, len(df)-2):
    if (df.iloc[i]['xml_data'] == '\\n'):
        new_df.iloc[j][df.iloc[i+1]['xml_data']] = df.iloc[i+2]['xml_data'] 
        count += 1
        if count == len(new_df):
            count = 0
            j += 1

new_df.dropna(inplace=True)

print(new_df)

Result:

                      sessionKey  Number     CreateDate        Id customerAge
0  JKX6G3_07092016_1476953673631  JKX6G3  1468040400000  83737626          64