I have data (example A, B, C,D) imported from different excel files and I want to form a Data Frame by joining similar column from each excel file sheet. Each file sheets have more than 100 columns. I used pd.concat in for loops to easier data joining as follows.
import pandas as pd
import numpy as np
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"
A = pd.DataFrame({'q1': ['16', '5','16', '5','13', '3'],
'q2': ['16', '3','16', '10','6', '1'],'q3': ['1', '2','1', '5','12', '13'],'q4': ['30', '25','50', '9','7', '6'],
'q5': ['15', '4','18', '8','3', '1'],'q6': ['11', '9','16', '5','13', '3'],'q7': ['20', '5','12', '5','3', '2']})
B = pd.DataFrame({'q1': ['1', '3','12', '5','1', '3'],
'q2': ['6', '13','6', '1','7', '3'],'q3': ['3', '4','1', '2','3', '1'],'q4': ['27', '22','5', '19','9', '1'],
'q5': ['15', '4','18', '8','3', '1'],'q6': ['11', '9','16', '5','13', '3'],'q7': ['20', '5','12', '5','3', '2']})
C = pd.DataFrame({'q1': ['16', '5','16', '5','13', '3'],
'q2': ['16', '3','16', '10','6', '1'],'q3': ['1', '2','1', '5','12', '13'],'q4': ['30', '25','50', '9','7', '6'],
'q5': ['15', '4','18', '8','3', '1'],'q6': ['11', '9','16', '5','13', '3'],'q7': ['20', '5','12', '5','3', '2']})
D = pd.DataFrame({'q1': ['16', '5','16', '5','13', '3'],
'q2': ['16', '3','16', '10','6', '1'],'q3': ['1', '2','1', '5','12', '13'],'q4': ['30', '25','50', '9','7', '6'],
'q5': ['15', '4','18', '8','3', '1'],'q6': ['11', '9','16', '5','13', '3'],'q7': ['20', '5','12', '5','3', '2']})
#Iterating selection by for loop
i=0
df_data = []
Dt= {}
for column in A:
# Select column contents by column
Aa =A[column]
Ba=B[column]
Ca=C[column]
Da=D[column]
Dk=pd.concat([Aa, Ba,Ca,Da],axis=1, keys=['T1', 'T2','T3', 'T4'])
Dt[i]=pd.concat([Aa, Ba,Ca,Da],axis=1, keys=['T1', 'T2','T3', 'T4'])
df_data.append(Dk)
df_data
For analysis purposes i want to extract each for loop iteration data as
qu1=df_data[0:1]
qu1
How, can i convent the data(qu1) into data-frame.I tried indexing qu1 in diferent ways and i get error 'list' object has no attribute 'columns'
#qu1.to_excel(r'D:desktopPython_dataemp_dfs.xlsx', index = False)
#qu1.values
#qu1['col1'] = qu1['col1'].apply(np.array)
qu1.columns
I want to get individual data frame like these from df_data (generated by for loop above)
#Manual column selection into data frame with pd.contact
data1=pd.concat([A['q1'], B['q1'],C['q1'],D['q1']],axis=1,keys=['T1','T2','T3', 'T4'])
data2=pd.concat([A['q2'],B['q2'],C['q2'],D['q2']],axis=1, keys=['T1', 'T2','T3', 'T4'])
data3=pd.concat([A['q3'], B['q3'],C['q3'],D['q3']],axis=1, keys=['T1','T2','T3', 'T4'])
data4=pd.concat([A['q4'],B['q4'],C['q4'],D['q4']],axis=1, keys=['T1','T2','T3', 'T4'])
data5=pd.concat([A['q5'], B['q5'],C['q5'],D['q5']],axis=1, keys=['T1','T2','T3', 'T4'])
data6=pd.concat([A['q6'], B['q6'],C['q6'],D['q6']],axis=1, keys=['T1', 'T2','T3', 'T4'])
data1
data2
data3
data4
data5
data6
Kindly, I am requesting help