CODEWITHSUNDEEP
pythonpandas
mikazz
mikazz

Reputation: 179

Pandas get cell index by header and row names

I would like to acces cells by header and row names (size can vary)

df = pandas.read_excel('file.xlsx')
print(df)

  id  A  B  C
0  D  1  2  3
1  E  4  5  6
2  F  7  8  1

# into this...

{
("A", "D") : "1",
("B", "D") : "2",
    ...
}

Upvotes: 1

Views: 250

Answers (1)

jezrael
jezrael

Reputation: 863166

If order is not important create MultiIndex Series by DataFrame.set_index with DataFrame.unstack and then use Series.to_dict:

d = df.set_index('id').unstack().to_dict()
print (d)
{('A', 'D'): 1, ('A', 'E'): 4, ('A', 'F'): 7, ('B', 'D'): 2, 
 ('B', 'E'): 5, ('B', 'F'): 8, ('C', 'D'): 3, ('C', 'E'): 6, ('C', 'F'): 1}

If order is important (python 3):

d = df.set_index('id').unstack().sort_index(level=1).to_dict()
print (d)
{('A', 'D'): 1, ('B', 'D'): 2, ('C', 'D'): 3, ('A', 'E'): 4, ('B', 'E'): 5, 
 ('C', 'E'): 6, ('A', 'F'): 7, ('B', 'F'): 8, ('C', 'F'): 1}

df1 = df.set_index('id')
c = np.tile(df1.columns, len(df1))
i = np.repeat(df1.index, len(df1.columns))
v = np.ravel(df1)

d = {(a,b):c for a,b,c in zip(c,i, v)}
print (d)
{('A', 'D'): 1, ('B', 'D'): 2, ('C', 'D'): 3, ('A', 'E'): 4, ('B', 'E'): 5, 
 ('C', 'E'): 6, ('A', 'F'): 7, ('B', 'F'): 8, ('C', 'F'): 1}

Upvotes: 4

Related Questions