Indexing Pandas Dataframe

Question

I have 2 pandas dataframes with names and scores.

The first dataframe is is in the form:

df_score_1

        A   B   C   D   
    A   0   1   2   0   
    B   1   0   0   2   
    C   2   0   0   3   
    D   0   2   3   0

where

df_score_1.index

Index(['A', 'B', 'C', 'D'],dtype='object')

The second dataframe is from a text file with three columns which does not display zeros but only positive scores (or non-zero values)

df_score_2

A B 1
A C 1
A D 2
B C 5
B D 1

The goal is to transform df_score_2 into the form df_score_1 using pandas commands. The original form is from a networkx output nx.to_pandas_dataframe(G) line.

I've tried multi-indexing and the index doesn't display the form I would like. Is there an option when reading in a text file or a function to transform the dataframe after?

I think need `pivot` like `df_score_2.pivot('col1','col2','col3')` — jezrael, Mar 28 '18 at 05:12

score 0 · Answer 1 · answered Mar 28 '18 at 04:31

0

are you trying to merge the dataframes? or you just want them to have the same index? if you need the same index then use this:

l=df1.index.tolist()
df2.set_index(l, inplace=True)

answered Mar 28 '18 at 04:31

Lior T

137
2
9

I tried that as a previous solution and get the error "KeyError: 'A' – d..b Mar 28 '18 at 04:48

d..b · Answer 2 · 2018-03-28T04:51:14.640

0

crosstab and reindex are the best solutions I've found so far:

  df = pd.crosstab(df[0], df[1], df[2], aggfunc=sum)

  idx = df.columns.union(df.index)
  df = df.reindex(index=idx, columns = idx)

The output is an adjacency matrix with NaN values instead of mirrored.

Here's a link to a similar question

edited Mar 28 '18 at 04:51

answered Mar 28 '18 at 04:46

d..b

1
1

score 0 · Answer 3 · answered Mar 28 '18 at 04:59

0

I think you need,

 df_score_2.set_index(df_score_1.index,inplace=True)

answered Mar 28 '18 at 04:59

Pyd

6,017
18
52
109

Indexing Pandas Dataframe

3 Answers3