1

Is it possible to keep multiple values of same key while reading excel into dictionary using pandas. For ex I have below excel sheet Data.xlsx:

 Col1  Col2  Col3 Col4
  A     1     2    3
  A     4     5    6
  B     7     8    9

Now i want to put index on Col1 while reading this excel table in to dictionary using pandas but want to keep key A twice in the dictionary with multiple values. The output should look like:

 {A:{Col2:'1',Col3:'2',Col3:'3'}, A:{Col2:'4',Col3:'5',Col3:'6'},B:{Col2:'7',Col3:'8',Col3:'9'}

I used below code:

 Sheet = {}
 Data = {}
 xls = pd.ExcelFile('Data.xlsx')
 for i in xls.sheet_names:
    Sheet[i]= pd.read_excel(xls, i)
    Sheet[i].set_index('Col1',  inplace=True)
    Data[i] = Sheet[i].to_dict(orient='index')

UPDATE:

The reason I want to have multiple occurrence of same key with different values is because my further work is make a model based on values of other columns for A. I can't afford to loose the second row. If I have both rows for key A, I need to further analyze how the data is performing with respect to key Col2. Any further suggestions are more than welcome here if dictionary is not the best option.

Mohit Sharma
  • 179
  • 1
  • 2
  • 11
  • 2
    this contradicts the nature of dictionary - it can't have duplicated keys. You can have a list instead of a dict – MaxU - stand with Ukraine Jan 16 '18 at 14:12
  • @MaxU So a simple answer to my question would be its not possible. Could you suggest any other alternative to my situation? – Mohit Sharma Jan 16 '18 at 14:15
  • @MohitSharma, I have already done - use list/array of dicts instead of dict of dicts – MaxU - stand with Ukraine Jan 16 '18 at 14:20
  • It seems, you use already panda data frames. You can filter them directly and analyse the data from these filtered results, e.g. `df[df.Col1 == "A"]["Col2"].mean()`. See for instance here: https://stackoverflow.com/a/11872393/8881141 or [this pandas tutorial](https://pandas.pydata.org/pandas-docs/stable/10min.html). numpy/scipy are also great tools to analyse array data. – Mr. T Jan 16 '18 at 15:21

0 Answers0