Squeezing pandas DataFrame to have non-null values and modify column names

user_4932316 0
Viewed 215

I have the following sample DataFrame 

import numpy as np
import pandas as pd

df = pd.DataFrame({'Tom': [2, np.nan, np.nan], 
                   'Ron': [np.nan, 5, np.nan],
                   'Jim': [np.nan, np.nan, 6],
                   'Mat': [7, np.nan, np.nan],}, 
                   index=['Min', 'Max', 'Avg'])

that looks like this where each row have only one non-null value

    Tom Ron Jim Mat
Min 2.0 NaN NaN 7.0
Max NaN 5.0 NaN NaN
Avg NaN NaN 6.0 NaN

Desired Outcome

For each column, I want to have the non-null value and then append the index of the corresponding non-null value to the name of the column. So the final result should look like this 

    Tom_Min Ron_Max Jim_Avg Mat_Min
0     2.0    5.0      6.0    7.0

My attempt

Using list comprehensions: Find the non-null value, and append the corresponding index to the column name and then create a new DataFrame

values = [df[col][~pd.isna(df[col])].values[0] for col in df.columns]

# [2.0, 5.0, 6.0, 7.0]

new_cols = [col + '_{}'.format(df[col][~pd.isna(df[col])].index[0]) for col in df.columns]

# ['Tom_Min', 'Ron_Max', 'Jim_Avg', 'Mat_Min']

df_new = pd.DataFrame([values], columns=new_cols)

My question

Is there some in-built functionality in pandas which can do this without using for loops and list comprehensions?

pandas dataframe list-comprehension python
2 Answers

If there is only one non missing value is possible use DataFrame.stack with convert Series to DataFrame and then flatten MultiIndex, for correct order is used DataFrame.swaplevel with DataFrame.reindex:

df = df.stack().to_frame().T.swaplevel(1,0, axis=1).reindex(df.columns, level=0, axis=1)
df.columns = df.columns.map('_'.join)
print (df)
   Tom_Min  Ron_Max  Jim_Avg  Mat_Min
0      2.0      5.0      6.0      7.0
user_29010020

Use:

s = df.T.stack()
s.index = s.index.map('_'.join)
df = s.to_frame().T

Result:

# print(df)

   Tom_Min  Ron_Max  Jim_Avg  Mat_Min
0      2.0      5.0      6.0      7.0
user_128331660
Related