Create one row with values from table with data in diagonal only

user_12285101 0
Viewed 151

I have the following table in pandas-

>>>index1   index2   index3   index4   index5   index6   index7
0   sig      null    null     null     null     null     null
1   null      sig     null     null     null     null     null
2   null      null    sig      null     null     null     null
3   null      null    null     sig      null     null     null
4   null      null    null     null     no sig   null     null
5   null      null    null     null     null     no sig   null
6   null      null    null     null     null     null     sig

I would like to get rid of the reduce the null values and to have the data in one row like this:

>>>index1   index2   index3   index4   index5   index6   index7
0   sig      sig     sig      sig      no sig   no sig   sig

It's important to say that I get the first table with the null data by if statemnt; I have calculated statistic test for each index and based on the p value, I give each index value of sig or no sig, using append:

for i in indices:
    stat, p = friedmanchisquare(df[1][i],df[2][i],df[3][i],df[4][i],df[5][i])
    #print(i,p)
    if p<0.05:
        friedman=friedman.append({i:'sig'},ignore_index=True)
    else:
        friedman=friedman.append({i:'no sig'},ignore_index=True)

So I believe that the append creates the big table with the many null values.

My end goal: to get the one row table, either in the of loop stage (e.g to use something else than append?) or to "fix" the big table after to get the one row

pandas if-statement append null python
4 Answers

You can try np.diagonal which return diagonal elemnts:

d = pd.DataFrame([np.diagonal(df)], columns=df.columns)

  index1 index2 index3 index4  index5  index6 index7
0    sig    sig    sig    sig  no sig  no sig    sig
user_128331660

IIUC, use pandas.DataFrame.bfill:

new_df = df.bfill().head(1)
print(new_df)

Output:

  index1 index2 index3 index4  index5  index6 index7
0    sig    sig    sig    sig  no sig  no sig    sig
user_70937410

Another alternative with stack , and droplevel;

df.stack().droplevel(0).to_frame().T

  index1 index2 index3 index4  index5  index6 index7
0    sig    sig    sig    sig  no sig  no sig    sig
user_98406370

A pandas solution of np.diagonal is df.lookup

df_diag = pd.DataFrame([df.lookup(df.index, df.columns)], columns=df.columns)

Out[47]:
  index1 index2 index3 index4  index5  index6 index7
0    sig    sig    sig    sig  no-sig  no-sig    sig
user_101892140
Related