Convert a pandas DataFrame to a dict with row and column name for each value-CodePudding

I want to convert a dataframe (pandas) to a dictionary with the labels in column 0 and the column names for each value. Additionally, I need to change the column names to 1-100 (there are 100 columns).

The dataframe looks like this:

I would like to get an ouput like this:

{(1,'DC-Vienna'):8831, (2, 'DC-Vienna'):10174, ...
 (1, 'DC-Valencia'):3012, (2, 'DC-Valencia'):2276, ...
 ...}

I was able to convert it to a dictionary of course, but I need it in a specific format without the row indices and with changed column names. I'm quite new to Python so all these things are unfamiliar to me. There might be a few basic steps involved here that I just completely missed as well. Such as renaming the columns of the dataframe.

Hope someone can help! Thanks in advance!

CodePudding user response：

I create a minified dataset that represents your problem. And here is a code that does what you need.

The likely was that you needed to move Distribution column to index and only then apply .to_dict method. Then you get index transformed into two-item tuples, as you wanted.

import pandas as pd
df = pd.DataFrame({
    "Distribution": ["Viena", "Kaunas"],
    "1": [2, 3],
    "2": [4, 5],
})
rdf = df.set_index("Distribution").stack().swaplevel().to_dict()
rdf

Results in :

{
  ('1', 'Viena'): 2,
  ('2', 'Viena'): 4,
  ('1', 'Kaunas'): 3,
  ('2', 'Kaunas'): 5}