Count and Find Min, Max of value occurs in a dataframe column-CodePudding

I have a dataframe like that

Date       | DayName | A | B | C 
2022-03-01   Tuesday   50  20  40
2022-03-02   Wednesday 10  10  20
2022-03-03   Thurday   64  1   9
2022-03-04   Friday     9  7  12

I'd like to be add rows like :

Date       | DayName | A | B | C 
2022-03-01   Tuesday   50  20  40
2022-03-02   Wednesday 10  10  20
2022-03-03   Thurday   64  1   9
2022-03-04   Friday     9   7  12
Count                  4    4   4
Min                    9    1   9
Max                    64   20  40

I tried add a row by

new_row = {'Date':'','DayName': '','A':'','B':'','C':''}
frame = frame.append(new_row,ignore_index = True)```

But i don't know how to count and find min, max of value. Somebody help me please

CodePudding user response：

Try this

It's not the best way, but I think it suits your needs.

import pandas as pd
import numpy as np
df1 = {
    'Date':['2022-03-01', '2022-03-02', '2022-03-03', '2022-03-04'],
    'DayName':['Tuesday', 'Wednesday', 'Thurday', 'Friday'],
    'A':[50, 10, 64, 9],
    'B' :[20, 10, 1, 7],
    'C':[40, 20, 9, 12]
              }
df1 = pd.DataFrame(df1)
print(df1)
countdf = df1.count(axis=0).values.tolist()[2:]
mindf = df1.min(axis=0).values.tolist()[2:]
maxdf = df1.max(axis=0).values.tolist()[2:]
df2 = {
    'Date':['', '', ''],
    'DayName':['', '', ''],
    'A': [countdf[0],mindf[0],maxdf[0]],
    'B' :[countdf[1],mindf[1],maxdf[1]],
    'C': [countdf[2],mindf[2],maxdf[2]]
              }
df2 = pd.DataFrame(df2, index = ['count','min','max'])
print(f'\n\n-------------BREAK-----------\n\n')   

f = [df1,df2]

df1 = pd.concat(f)
print(df1)

CodePudding user response：

You can try aggregate multiple functions over the rows then concat dataframes

cols = ['A', 'B', 'C']
agg = (df[cols].agg(['count', min, max])
       .rename_axis('Date')
       .reset_index())

out = pd.concat([df, agg])

print(out)

         Date    DayName   A   B   C
0  2022-03-01    Tuesday  50  20  40
1  2022-03-02  Wednesday  10  10  20
2  2022-03-03    Thurday  64   1   9
3  2022-03-04     Friday   9   7  12
0       count        NaN   4   4   4
1         min        NaN   9   1   9
2         max        NaN  64  20  40