In a dataframe in pandas, how can I push the front column value of specific row to the back and fill-CodePudding

data = {'1':['cd','aa','1a'],
        '2':['ab','1b'],
        '3':['1a']}

Wanted output:

data = {'1':['cd','aa','1a'],
        '2':['00','ab','1b'],
        '3':['00','00','1a]}

So I solved it as follows, but the problem is that it is too slow. Thank you for your reply

import pandas as pd

for i in (len(data)):
    print(i)
    count = 0
    if ((data['1'].isna())[i]==True):
      count = 3
    elif ((data['2'].isna())[i]==True):
      count = 2
    elif ((data['3'].isna())[i]==True):
      count = 1
    data.loc[i] = data.loc[i].shift(count, fill_value='00')

there are 8 columns for my case, this is an example

CodePudding user response：

Try:

data = {'1':['cd','aa','1a'],
        '2':['ab','1b'],
        '3':['1a']}

# FOR EACH ITEM, INSERT '00' AT FIRST POSITION    
# TILL LENGTH IS ADEQUATE:
for d in data:
    while len(data[d]) < 3: 
        data[d].insert(0, '00')

print(data)

Output:

{'1': ['cd', 'aa', '1a'], '2': ['00', 'ab', '1b'], '3': ['00', '00', '1a']}

CodePudding user response：

You can use numpy on your DataFrame to sort the values using the NA/not-NA property:

data = {'1':['cd','aa','1a'],
        '2':['ab','1b'],
        '3':['1a']}

df = pd.DataFrame.from_dict(data, orient='index').T
idx = np.argsort(df.notna().to_numpy(), axis=0)

out = (pd.DataFrame(df.to_numpy()[idx, np.arange(df.shape[1])],
                    columns=df.columns,
                    index=df.index)
         .fillna('00')
      )

Or with pandas' apply, which is shorter but slower for large datasets:

out = (df
   .apply(lambda r: r.sort_values(key=pd.notna, ignore_index=True))
   .fillna('00')
 )

Output:

    1   2   3
0  cd  00  00
1  aa  ab  00
2  1a  1b  1a

NB. If you want a dictionary as output:

out.to_dict('list')
{'1': ['cd', 'aa', '1a'],
 '2': ['00', 'ab', '1b'],
 '3': ['00', '00', '1a']}