move column above and delete rows in pandas python dataframe

user_15452168 0
Viewed 220

I have a data frame df like this

A        B        C        D        E        F        G        H
a.1      b.1     
                  
                  c.1      d.1 
                  c.2      d.2           e.1      f.1 
                                                      

                                                     g.1       h.1

Create the sample DataFrame

from io import StringIO

s = """A,B,C,D,E,F,G,H
a.1,b.1,,,,,,
,,c.1,d.1,,,,
,,c.2,d.2,e.1,f.1,,
,,,,,,g.1,h.1"""

df = pd.read_csv(StringIO(s))

I want to remove these extra spaces and I want dataframe to start from the top row. Can anyone help.

my desired results would be

A        B        C        D        E        F        G        H
a.1      b.1      c.1      d.1      e.1      f.1      g.1       h.1
                  c.2      d.2
pandas python-2.7 dataframe python-3.x python
1 Answers

You can shift back each column by the number of preceding missing values which is found with first_valid_index:

df.apply(lambda s: s.shift(-s.first_valid_index()))

to get

     A    B    C    D    E    F    G    H
0  a.1  b.1  c.1  d.1  e.1  f.1  g.1  h.1
1  NaN  NaN  c.2  d.2  NaN  NaN  NaN  NaN
2  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN
3  NaN  NaN  NaN  NaN  NaN  NaN  NaN  NaN

To drop the rows full of NaNs and fill the rest with empty string:

out = (df.apply(lambda s: s.shift(-s.first_valid_index()))
         .dropna(how="all")
         .fillna(""))

to get

>>> out

     A    B    C    D    E    F    G    H
0  a.1  b.1  c.1  d.1  e.1  f.1  g.1  h.1
1            c.2  d.2

note: this assumes your index is 0..N-1; so if it's not, you can store it beforehand and then restore back:

index = df.index
df = df.reset_index(drop=True)
df = (df.apply(lambda s: s.shift(-s.first_valid_index()))
        .dropna(how="all")
        .fillna(""))
df.index = index[:len(df)]

To make the pulling up specific to some columns:

def pull_up(s):
    # this will be a column number; `s.name` is the column name
    col_index = df.columns.get_indexer([s.name])

   # for example: if `col_index` is either 7 or 8, pull by 4
   if col_index in (7, 8):
       return s.shift(-4)
   else:
       # otherwise, pull as much
       return s.shift(-s.first_valid_index())

# applying
df.apply(pull_up)
user_93321870
Related