How to convert columns (pandas dataframe) into row for SQL insertion?

Question

I have a table QUESTIONNAIRE as below:

The coloumns (EXT1, EXT2, ... are the question's code; the value is the answer for the question)

I have 10000 QuestionnaireID and 50 Question's codes.

I would like to add this into a new table called ANSWER:

Hence, I'll have 50000 row of data. I can also download the data into pandas dataframe. How can I do transform it with sql-query or pandas?

Thanks before. Cheers.

Answer 1

I created a simplified version of your dataset, but the solution fits also the complete version:

tbl1 = pd.DataFrame({
    "QID": [1,2,3,4],
    "Ext1": [5,6,7,8],
    "Ext2": [9,10,11,12],
    "Ext3": [13,14,15,16]
})

Then, I declared a DataFrame with the destination format you want:

#Declare table 2 with desired shape 
tbl2 = pd.DataFrame({  
    "QID":[],  
    "QCode":[],  
    "Answer":[]  
})  

Finally, simply looped through the data and reshaped it 'manually':

for idxRow in np.arange((tbl1.shape)[0]):
    for idxCol in np.arange((tbl1.shape)[1]):
        if(idxCol > 0):
            #Get data from tbl1 and prepare in columns
            col1 = tbl1.iloc[idxRow,0] #QID (1st column)
            col2 = (tbl1.columns)[idxCol] # Column name (2nd column)
            col3 = tbl1.iloc[idxRow,idxCol] # Data val (3rd column)

            # Add data to tbl2
            tbl2.loc[len(tbl2.index)] = [col1,col2,col3] 

Final results as below: