0

edit: in response to Jezrael misunderstanding what I am looking to do.

Using his example: What I am hoping to do is keep rb85 and rb87 values separate. I am trying to take my columns of values and create a new df which is half the rows# and has columns composed of either exclusively the odd or even index of the original. So rather than his example, I want to go from:

    rb85  rb87  rf85  rf87
0   7.0   NaN   3.0   NaN
1   NaN   5.0   NaN   4.0
2   9.0   NaN   9.0   NaN
3   NaN   5.0   NaN   5.0
4   2.0   NaN   2.0   NaN
5   NaN   4.0   NaN   4.0

to this: 

   rb85  rb87  rf85  rf87
0   7.0   5.0   3.0   4.0 
1   9.0   5.0   9.0   5.0
2   2.0   4.0   2.0   4.0

How can I add every other value in a series and avoid nans? In my original data set I had two samples (rb85 and rb87) of data (shunt resistor or sr for short) taken sequentially ex: [sr85(1), sr87(1), sr85(2), sr87(2),...]

I am looking at how to take all rb85 (every even entry) and place it within its own series, and do the same for rb87 and both sample's output (rf85 and rf87).

Original df

screen cap of code

Max D
  • 47
  • 5

2 Answers2

1

EDIT: If possible, seelct each second column by indexing by DataFrame.iloc and then use DataFrame.shift, last remove only missing rows by DataFrame.dropna and how='all' parameter:

df.iloc[:, 1::2] = df.iloc[:, 1::2].shift(-1)
df = df.dropna(how='all')
print (df)
   rb85  rb87  rf85  rf87
0   7.0   5.0   3.0   4.0
2   9.0   5.0   9.0   5.0
4   2.0   4.0   2.0   4.0

Another solution with justify function:

df = pd.DataFrame(justify(df.values, invalid_val=np.nan, side='up', axis=0), 
                  columns=df.columns).dropna(how='all')
print (df)
   rb85  rb87  rf85  rf87
0   7.0   5.0   3.0   4.0
1   9.0   5.0   9.0   5.0
2   2.0   4.0   2.0   4.0
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Thank you for your reply! I'm sorry that I wasn't clear. What I am hoping to do is keep rb85 and rb87 values separate. I am trying to take my columns of values and create a new df which is half the rows# and has columns composed of either exclusively the odd or even index of the original. I've updated my original question to reflect this better. – Max D Apr 18 '20 at 01:34
0

I have a few ideas on how this can be achieved depending on size of your data + how dynamic it needs to be. But just to answer your question you can shift back every second column by 1 row and then drop rows that are nan:

for col in df.columns[1::2]:
    df[col] = df[col].shift(-1)
df.dropna(inplace=True)

output

    rb85    rb87    rf85    rf87
0   7.0     5.0     3.0     4.0
2   9.0     5.0     9.0     5.0
4   2.0     4.0     2.0     4.0

you can then reset index if needed

df.reset_index(drop=True, inplace=True)

    rb85    rb87    rf85    rf87
0   7.0     5.0     3.0     4.0
1   9.0     5.0     9.0     5.0
2   2.0     4.0     2.0     4.0
Biarys
  • 1,065
  • 1
  • 10
  • 22