How can I add columns at specific positions in pandas?

user_14154062 0
Viewed 278

I have this dataframe:

df = pd.DataFrame({'FS':[1,1,2],'FL':[2,3,4],'GS':[5,8,9],'GL':[5,2,4],'JJ':['no','more','math']})
print(df)

    FS  FL  GS  GL   JJ
0   1   2   5   5    no
1   1   3   8   2  more
2   2   4   9   4  math

I want to add columns between the 'L'(long) and 'S' (short) in a loop b/c this dataframe is 64 columns long, but stop before the column JJ, which is filled with strings. The operation that I want to perform for these columns is: Ratio = S/(S+L) so my dataframe would look like this:

df = pd.DataFrame({'FS':[1,1,2],'FL':[2,3,4],'FRatio':[0.333,0.25,0.333],\
                   'GS':[5,8,9],'GL':[5,2,4],'GRatio':[0.5,0.8,0.692],\
                   'JJ':['no','more','math']})
print(df)

   FS  FL  FRatio  GS  GL  GRatio    JJ
0   1   2   0.333   5   5   0.500    no
1   1   3   0.250   8   2   0.800  more
2   2   4   0.333   9   4   0.692  math

I thought about melting the dataframe to a long format but each S/L pair is unique and I couldn't think of a way to keep them separated.

pandas dataframe numpy python
1 Answers

Use insert to place the column where you want. Find the location of the 'L' column for a given prefix and then place it after that. This assumes your DataFrame is organized in the beginning so that the 'XS' and 'XL' columns occur in pairs in that order.

You can either manually specify the prefixes or use all label characters except the last (i.e. drop the 'S' or 'L') for all columns before the 'JJ' column to determine what they are programmatically.

prefixes = df.columns[:df.columns.get_loc('JJ')].str[:-1].unique()
#prefixes = ['F', 'G']

for prefix in prefixes:
    s = df[f'{prefix}S']/(df[f'{prefix}S'] + df[f'{prefix}L'])
    # Location after the 'XL'
    loc = df.columns.get_loc(f'{prefix}L')+1
    df.insert(loc, f'{prefix}Ratio', s)

   FS  FL    FRatio  GS  GL    GRatio    JJ
0   1   2  0.333333   5   5  0.500000    no
1   1   3  0.250000   8   2  0.800000  more
2   2   4  0.333333   9   4  0.692308  math
user_43333590
Related