Adding up of same rows from multiple csv file in pandas

Question

I have 21 CSV files each having 48 columns and 365rows(+-5) I need to combine these 21 files into one and then perform the addition of the first rows of the first file with the first row of the second file then the first row of the third file and so on till 365th row. I have little idea that I need to use a 3D model in pandas to get this done, But I need help to achieve the below results.

file1:

  0:00 0:30 1:00 1:30 ... 23:30

1  A1   A2   A3   A4       A48
2
3
.
.
365

file2:

0:00 0:30 1:00 1:30 ... 23:30

1  *A1   *A2   *A3   *A4       *A48
2
3
.
.
365

I want to add 1st row of each of the 21 files, then the 2nd row, likewise till the 365th row. eg (A1+*A1 + A2+*A2+..+A48+*A48)

How do I read those 21 files and sum it I have tried the below code.

For combining multiple CSV I have used the below code, Here the code is able to read the files and concatenate but the concatenation leaves many nan values too. It's just getting me confused since I have been trying to get this done for long hours.

path ='C:/Users/niraj/Documents/Energy_upgraded/demandfiles'  
all_files = glob.glob(os.path.join(path, "*.csv"))  

li = []

for filename in all_files:     
    print(filename)
    df = pd.read_csv(filename, index_col=None, header=0)
    li.append(df)

frame = pd.concat(li, axis=0, ignore_index=True)

Answer 1

If I understand you correctly, I think this will get you what you want.

What you have is pretty good. If you transpose the dataframes before concatenating them you should be able to get the sum easily.

For example assume your file contains the following

1,1,1,1
2,2,2,2
3,3,3,3
4,4,4,4

I will use the same file instead of multiple files in my loop. For you the loop will be as you have it.

import pandas as pd

df_list = []

for i in range(3):
    df = pd.read_csv('file.csv', index_col=None, header=None)
    df_list.append(df.T) # here .T means you are transposing the dataframe

df_total = pd.concat(df_list)

print(df_total)

output:

   0  1  2  3
0  1  2  3  4
1  1  2  3  4
2  1  2  3  4
3  1  2  3  4
0  1  2  3  4
1  1  2  3  4
2  1  2  3  4
3  1  2  3  4
0  1  2  3  4
1  1  2  3  4
2  1  2  3  4
3  1  2  3  4

Then to sum you can do:

print(df_total.sum())

output:

0    12
1    24
2    36
3    48
dtype: int64