Pandas Multiple dataframes not Stacking Vertically

Question

I have this python program which used to get sql query results on many servers and what i want to do is stack the each of results on to one excel but when i use pandas.concat([],axis=0) the next dataframe is jumping to next index column rather than stacking.i used ignore_index=True but the dataframes don't ignore the indexing

from ast import Not
from cmath import inf
from multiprocessing import connection
from operator import index
from sqlite3 import Cursor
from unittest.result import failfast
import mysql.connector
from mysql.connector import errorcode
from numpy import save

import pandas as pd
from requests import NullHandler
from sqlalchemy import false
#importing Queries
from Queries import *

#Location Ip's
from locations import locs

from env import *

startDate='2022-05-01'
endDate='2022-05-15'

from lib import fileChecker,logwriter

    

from cmath import inf
from multiprocessing import connection
from operator import index
from sqlite3 import Cursor
from unittest.result import failfast
import mysql.connector
from mysql.connector import errorcode

import pandas as pd
from sqlalchemy import false
#importing Queries
from Queries import *

#Location Ip's
from locations import locs

from env import *

startDate='2022-05-01'
endDate='2022-05-15'

from lib import fileChecker,logwriter

    

def executor(QUERY):
    alldf = None
    
    for type,info in locs.items():
        fileChecker(type)

        for ip,locName in info.items():

            try:
                cnx = mysql.connector.connect(user=usr, password=passwd,host=ip, database=db)

                if cnx.is_connected():
                    print("Connection Succesfull to {}".format(locName))
                    logwriter(type,ip,locName,True)


                    location = cnx.cursor(buffered=True)
                    location.execute("SELECT loccod FROM docparameters d limit 1")

        
                    loc = location.fetchone()[0]
        
                    
                    cursor = cnx.cursor()
                    cursor.execute(QUERY)
                    df = pd.DataFrame(cursor.fetchall())
                    

                    if alldf is not None:
                       if not df.empty:
                           alldf = pd.concat([alldf,df],ignore_index=True,axis=0)
                    else:
                        alldf = df
                 
                
                    print(df)
                    field_names = [ i[0] for i in  cursor.description]
                    print(field_names)
                        
                    xlswriter = pd.ExcelWriter('{}/{}.xls'.format(type,loc),engine='openpyxl')

                    if not df.empty:
                        df.columns = field_names  
                      
                        df.to_excel(xlswriter,index=false)

                        xlswriter.save()
                    else:
                        cnx.close()
   
            except mysql.connector.Error as err:

                if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
                    print("Something wrong with your username or password")
                elif err.errno == errorcode.ER_BAD_DB_ERROR:
                    print("DATABASE does not exist")
                else:
                    print(err)
                    print("Connectin Failed to %s"%(loc))
                    logwriter(type,ip,locName,False)

                   
            else:
                cnx.close()

    return alldf
   









def saveToExcel(query,filename):

    xlswriter = pd.ExcelWriter("%s.xls"%(filename),engine='openpyxl')
    queryDatas = executor(query)
    print(queryDatas)
    export = queryDatas
    export.to_excel(xlswriter)
    xlswriter.save()


    print("succes savetoExcel")


saveToExcel('desc rms_itmmaster;','peoples')

Reduce your code to the bare minimum to show the problem. mysql has no use for this problem, nor writing to xls, nor all the imports. Reduce it to just a Pandas problem; don't let us wade through your code. — 9769953, Jun 07 '22 at 02:43

score 0 · Answer 1 · answered Jun 07 '22 at 05:23

0

Are you sure that the dataframes are the same? If the columns are different, your code will also create extra columns.

Check this discussion: Pandas merge two dataframes with different columns

Would be good if you showed us part of the result and also I don't think this is a data-science tag.

answered Jun 07 '22 at 05:23

grietbroek

73
7

yes all the columns are same – devMe Jun 11 '22 at 05:14
i found a way to fix the the data not stacking rather than concatenating the dataframes to the df i appended all the data to list and used for loop to concat the data it worked well `if not df.empty: dataFrameStack.append(df)` and `def dfConcat(dflist): concatedDf = pd.DataFrame() for i in dflist: concatedDf = pd.concat([concatedDf,i],axis=0,ignore_index=True) return concatedDf` this function used to concat all the dataframes stacked – devMe Jun 19 '22 at 10:06

Pandas Multiple dataframes not Stacking Vertically

1 Answers1