How to add conditional row to pandas dataframe

user_11402260 0
Viewed 83

I tried looking for a succinct answer and nothing helped. I am trying to add a row to a dataframe that takes a string for the first column and then for each column grabbing the sum. I ran into a scalar issue, so I tried to make the desired row into a series then convert to a dataframe, but apparently I was adding four rows with one column value instead of one row with the four column values.

My code:

def country_csv():
    # loop through absolute paths of each file in source
    for filename in os.listdir(source):
        filepath = os.path.join(source, filename)
        if not os.path.isfile(filepath):
            continue
        df = pd.read_csv(filepath)
        df = df.groupby(['Country']).sum()
        df.reset_index()
        print(df)
        # df.to_csv(os.path.join(path1, filename))

Sample dataframe:

                    Confirmed  Deaths  Recovered
Country                                         
Afghanistan               299       7         10
Albania                   333      20         99

Would like to see this as the first row

World 632 27 109
pandas python
2 Answers

IIUC, you can create a dict then repass it into a dataframe to concat.

data = df.sum(axis=0).to_dict()
data.update({'Country' : 'World'})
df2 = pd.concat([pd.DataFrame(data,index=[0]).set_index('Country'),df],axis=0)

print(df2)

             Confirmed  Deaths  Recovered
Country                                  
World              632      27        109
Afghanistan        299       7         10
Albania            333      20         99

or a oner liner using assign and Transpose

df2 = pd.concat(
    [df.sum(axis=0).to_frame().T.assign(Country="World").set_index("Country"), df],
    axis=0,
)



print(df2)

             Confirmed  Deaths  Recovered
Country                                  
World              632      27        109
Afghanistan        299       7         10
Albania            333      20         99
user_93751020 
import pandas as pd
import datetime as dt

df
            Confirmed   Deaths  Recovered
Country         
Afghanistan 299         7       10
Albania     333         20      99

df.loc['World']  = [df['Confirmed'].sum(),df['Deaths'].sum(),df['Recovered'].sum()]
df.sort_values(by=['Confirmed'], ascending=False)


             Confirmed  Deaths  Recovered
Country         
World        632        27      109
Albania      333        20      99
Afghanistan  299        7       10
user_80948850
Related