How to create new row out of calculation across other rows in Pandas Data Frame?

Viewed 960

I want to create a new row via row calculations. My DataFrame looks like this:

Para   01.21    02.21   03.21   
<48h     22      25      38 
>48h     0       1        3

I want to calc the percentage across each row such that:

Goal

Para   01.21    02.21   03.21   
<48h     22      25      38 
>48h     0       1        3
Perc.    0%      4%       8%

I had some ideas with appending a new DataFrame, but nothing really succesful. Thank you for your help!

5 Answers

Assuming you are happy to modify the input DataFrame in place, this will produce the exact output you've requested:

df.loc['Perc.'] = (
    df.loc['>48h'] / df.loc['<48h']
).apply(lambda x: f"{x:.0%}")

This assumes that the first column in your printed output is the index of the DataFrame.

To unpack this a bit:

  1. The assignment df.loc['Perc.'] = will create a new row populated with the value on the right-hand side of the =.
  2. df.loc['>48h'] / df.loc['<48h'] computes the desired ratio, as a float
  3. The .apply call calls a function on each element of the new row, to convert the raw percentage number to a string formatted in the manner you requested. To unpack this a little further:
    • f"{x}" converts x to a string (it is essentially equivalent to str(x))
    • f"{x:.0%}" enhances the above by adding the .0% format specifier, which will display the value as a percentage with 0 decimal places (i.e. multiply by 100, round to 0 decimal places, and then add a % sign). There are various other format specifiers available for floating point data.

Note that if you want to do any further processing on the resulting value, I would recommend not doing step 3 - once you've converted your data into a string it obviously can't be e.g. multiplied by other data.

If you want the best of both worlds, keeping the internal value as a float ratio, but displaying them as a percentage, you can do this:

df.loc['Perc.'] = (
    df.loc['>48h'] / df.loc['<48h']
)

display_str = df.T.to_string(formatters={"Perc.": lambda x: f"{x:.0%}"})
print(display_str)

This will modify df to add the Perc. row, but its values will stay as the actual float ratio. Then display_str will be an appropriately-formatted string representation of the whole dataframe, including the percentage.

Note that formatters accepts a dict keyed by column names, not rows, so you must first transpose your data (df.T), the resulting output will be transposed:

   Para  <48h  >48h Perc.
0  1.21  22.0   0.0    0%
1  2.21  25.0   1.0    4%
2  3.21  38.0   3.0    8%

If you're working inside a Jupyter notebook, you can also use the df.style.format method to achieve something similar; calling

df.T.style.format({"Perc.": "{:.0%}"})

will return a pandas.io.formats.style.Styler object, but if you're in a Jupyter notebook, this will be rendered like a DataFrame with the relevant column formatted as above. Again though, this only works on a column-by-column basis, so you'll need to transpose.

Use DataFrame.loc for select rows:

#If first column is not index create it
#df = df.set_index('Para')


df.loc['Perc'] = df.loc['>48h'].div(df.loc['<48h']).mul(100).round()
print (df)
      01.21  02.21  03.21
Para                     
<48h   22.0   25.0   38.0
>48h    0.0    1.0    3.0
Perc    0.0    4.0    8.0

Better is transpose first, so is possible select by columns:

#If first column is not index create it
#df = df.set_index('Para')

df = df.T

df['Perc'] = df['>48h'].div(df['<48h']).mul(100).round()
print (df)
Para   <48h  >48h  Perc
01.21    22     0   0.0
02.21    25     1   4.0
03.21    38     3   8.0
para = ['01.21', '02.21', '03.21']
a = np.array(([22, 25, 38]))
b = np.array(([0, 1, 3]))
df = pd.DataFrame([para, a, b], index=['Para', '<48h', '>48h'], columns=['col0', 'col1', 'col2'])

perc = np.array(((b/a)*100))

df2 = pd.DataFrame([perc], index=['Perc.'], columns=['col0', 'col1', 'col2'])
df = df.append(df2)
print(df)

Output:

        col0   col1     col2
Para   01.21  02.21    03.21
<48h      22     25       38
>48h       0      1        3
Perc.      0      4  7.89474

It's not best practice to store data like this, read Hadley's tidy data paper.

But in a line you could do it thusly.

df.loc['perc'] = df.iloc[2]/df.iloc[1]

df.loc[key] = row

this is how we create new row in pandas data frame

Related