Creating a dictionary from Excel in every sheet-CodePudding

I want to create a dictionary from the values I get from Excel cells.

This my Excel spreadsheet:

My expectation about the dictionary is like this:

{'Ancolmekar': array([ 3. , 20. ,  6. , ...,  0.5,  0.5,  0.5]),
 'Cidurian': array([0.5, 0.5, 0.5, ..., 0.5, 0.5, 6. ]),
 'Dayeuhkolot': array([0.5, 0.5, 0.5, ..., 5.5, 1.5, 0.5]),
 'Hantap': array([ 1.5, 17.5,  3. , ...,  0.5,  5. ,  1.5]),
 'Kertasari': array([ 1.5,  2.5,  0.5, ...,  1.5, 10. ,  1.5]),
 'Meteolembang': array([ 0.5,  0.5,  0.5, ...,  0.5,  0.5, 10.5]),
 'Sapan': array([ 0.5,  0.5,  6. , ...,  0.5, 13.5, 24. ])}

Any idea of the code?

My code is like this:

# Read Excel data
xl = pd.read_excel('export_3.xlsx')
contoh = pd.read_excel('export_3.xlsx', header=None, sheet_name='EPA BINER')
contoh1 = pd.read_excel('export_3.xlsx', header=None, sheet_name='EPA')
contoh2 = pd.read_excel('export_3.xlsx', header=None, sheet_name='SHORT BINER')
contoh3 = pd.read_excel('export_3.xlsx', header=None, sheet_name='SHORT EPA')
contoh4 = pd.read_excel('export_3.xlsx', header=None, sheet_name='LONG BINER')
contoh5 = pd.read_excel('export_3.xlsx', header=None, sheet_name='LONG EPA')

The total of rows is 43824.

CodePudding user response：

I guess you could go with :

path = 'export_3.xlsx'
file = pd.ExcelFile(path)
sheets = file.sheet_names

for sheet in sheets:
    sheet_dict = {}
    contoh = pd.read_excel(path,  sheet_name=sheet)
    for col in contoh.columns:
        sheet_dict[col] = list(contoh[col])
    print(sheet_dict, "\n\n\n")

This way you have one dictionary per sheet in your excel, that you can store if needed in a list declared before the for loop.

Instead of using list(contoh[col]) I guess you could also use np.array(list(contoh[col]))

CodePudding user response：

Transpose the df, combine all column values in each row to list and print/save as json with column orient Use the similar approach according to your requirement.

contoh = pd.read_excel('export_3.xlsx',header=None,sheet_name='EPA BINER')
df = contoh.transpose()
df['new'] = df.values.tolist()
df = df['new']
df.to_json(orient = 'columns')