How To Stack Data Frames On Top Of Each Other In Pandas

I have a dataframe with 96 columns: df.to_csv('result.csv') out (excel): Run 1 Run 2 Run 3 Run 4 Run 5 Run 6 Run 7 Run 8 Run 9 Run 10 Run 11 Run 12 Run 1

Solution 1:

You're very nearly there.

The problem is that the column names are all different within each sub dataframe. Thus, when pandas does the concat, it doesn't just append the dataframes to the bottom, it expands the dataframe to have new colums with the right names and then appends the rows.

You can solve this by renaming the columns in the sub dataframes e.g.

for sub_df in pieces:
    sub_df.columns=range(12)

N.B.df2 to df8 contain what you want, I think. For some reason, you've made df1 contain only the first 7 columns, rather than 12. I'll assume that's a typo for now.

Resulting in full working code (I copied your input data into a file named 'data1.csv')

import pandas as pd
import numpy as np    

df = pd.read_csv('data1.csv')

df1 = df.ix[:,0:12]
df2 = df.ix[:,12:24]
df3 = df.ix[:,24:36]
df4 = df.ix[:,36:48]
df5 = df.ix[:,48:60]
df6 = df.ix[:,60:72]
df7 = df.ix[:,72:84]
df8 = df.ix[:,84:96]

pieces = (df1,df2,df3,df4,df5,df6,df7,df8)

# Give the columns the same labels in each sub dataframe# I've used numbers for convenience - you can give more descriptive names if you want
for sub_df in pieces:
    sub_df.columns=range(12)

df_final = pd.concat(pieces, ignore_index = True)

print df_final

Final note on ordering

You note the unexpected ordering of your columns in your example. This won't affect my solution, but I will explain it for completeness.

The columns in your output are in what is called 'Lexicographic ordering'. This is a common problem when sorting strings containing numbers in Python (and other languages). They are sorted in an order that looks almost right, but somehow runs 1, 10, 11 ... 19, 2, 20 and so on. That is because the ordering sorts letter by letter like a dictionary, but with 0 to 9 coming before a