3

I need to rearrange the data within the same row of the dataframe, where some columns may have no data. The original dataframe:

  hash   a1   a2   a3    a4    a5    b1    b2    b3    b4    b5
  0      1    2    nan   nan   nan   1     2     3     4     nan
  1      1    nan  nan   nan   nan   1     2     3     nan   nan

The dataframe that I expected to have:

  hash   a1    a2     a3     a4     a5    b1    b2     b3    b4    b5
  0      nan   nan    nan    1      2     nan   1      2     3     4     
  1      nan   nan    nan    nan    1     nan   nan    1     2     3
gyoza
  • 2,112
  • 2
  • 12
  • 18
Tony
  • 51
  • 5

2 Answers2

1

Use justify function apply per groups wit lambda function by x[0] for select first letter of column name and axis=1 for grouping by columns:

df = df.set_index('hash')
f = lambda x: pd.DataFrame(justify(x.values, invalid_val=np.nan, side='right'), 
                           columns=[f'{x.name}{y}' for y in range(1, len(x.columns) + 1)])
df = df.groupby(lambda x: x[0], axis=1).apply(f)
print (df)
   a1  a2  a3   a4   a5  b1   b2   b3   b4   b5
0 NaN NaN NaN  1.0  2.0 NaN  1.0  2.0  3.0  4.0
1 NaN NaN NaN  NaN  1.0 NaN  NaN  1.0  2.0  3.0
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
0

What about selecting a subset in a loop (e.g. [a1, a2, a3]), then transposing the subset and sorting it row-wise, glueing it back together while transposing again. 

import numpy as np
import pandas as pd

# dummy data
df = pd.DataFrame(np.random.randint(1, 10, (5, 6)),
                  columns=['a1', 'a2', 'a3', 'b1', 'b2', 'b3'])
# add some nan
df = df.mask(np.random.random(df.shape) < .3)

def rearrange_data_column_wise(df):
    col_ = set([col[0] for col in df.columns])
    df_ = pd.DataFrame()
    for col in col_:
        filter_col = [c for c in df if c.startswith(col)]
        df_sub = df[filter_col].T
        df_sub = pd.DataFrame(np.sort(df_sub.values,  axis=0),
                              index=df_sub.index,
                              columns=df_sub.columns)

        df_ = pd.concat([df_, df_sub.T], axis=1)

    return df_

df = rearrange_data_column_wise(df)
print(df.head())

Which would give you a sorted dataframe with NaN on the right side of each subset.

    a1   a2  a3   b1   b2   b3
0  4.0  NaN NaN  3.0  4.0  7.0
1  9.0  NaN NaN  4.0  5.0  9.0
2  6.0  9.0 NaN  2.0  4.0  9.0
3  3.0  7.0 NaN  7.0  9.0  NaN
4  2.0  2.0 NaN  2.0  6.0  NaN

FYI, set will change the order of the columns, but you could prevent that as shown here.

Chris
  • 1,287
  • 12
  • 31