How do you aggregate multiple columns from large DataFrame using pandas?

user_8371954 0
Viewed 144

I am importing an excel worksheet using pandas and trying to remove any instance where there is a duplicate area measurement for a given Frame. The sheet I'm playing with looks vaguely like the table below wherein there are n number of files, a measured area from each frame of an individual file, and the Frame Number that corresponds to each area measurement.

Filename.0 Area.0 Frame.0 Filename.1 Area.1 Frame.1 ... Filename.n Area.n Filename.n
Exp327_Date_File_0 600 1 Exp327_Date_File_1 830 1 ... Exp327_Date_File_n 700 1
Exp327_Date_File_0 270 2 Exp327_Date_File_1 730 1 ... Exp327_Date_File_n 600 2
Exp327_Date_File_0 230 3 Exp327_Date_File_1 630 2 ... Exp327_Date_File_n 500 3
Exp327_Date_File_0 200 4 Exp327_Date_File_1 530 3 ... Exp327_Date_File_n 400 4
NaN NaN NaN Exp327_Date_File1 430 4 ... NaN NaN NaN

If I manually go through the excel worksheet and concatenate the filenames into just 3 unique columns containing my entire dataset like so:

Filename Area Frame
Exp327_Date_File_0 600 1
Exp327_Date_File_0 270 2
etc... etc... etc...
Exp327_Date_File_n 530 4

I have been able to successfully use pandas to remove the duplicates using the following: df_1 = df.groupby(['Filename', 'Frame Number']).agg('Area': 'sum')

However, manually concatenating everything into this format isn't feasible when I have hundreds of File replicates and I will then have to separate everything back out into multiple column-sets (similar to how the data is presented in Table 1). How do I either (1) use pandas to create a new Dataframe with every 3 columns stacked on top of each other which I can then group and aggregate before breaking back up into individual sets of columns based on Filename or (2) loop through the multiple filenames and aggregate any Frames with multiple Areas? I have tried option 2:

(row, col) = df.shape #shape of the data frame the excel file was read into
for count in range(0,round(col/3)): #iterate through the data
     aggregation_functions = {'Area.'+str(count):'sum'} #add Areas together
     df_2.groupby(['Filename.'+str(count), 'Frame Number.'+str(count)]).agg(aggregation_functions)

However, this just returns the same DataFrame without any of the Areas summed together. Any help would be appreciated and please let me know if my question is unclear

pandas aggregation dataframe python
1 Answers

Here is a way to achieve option (1):

import numpy as np
import pandas as pd

# sample data
df = pd.DataFrame({'Filename.0': ['Exp327_Date_File_0', 'Exp327_Date_File_0', 
                                  'Exp327_Date_File_0', 'Exp327_Date_File_0', 
                                  np.NaN], 
                   'Area.0': [600, 270, 230, 200, np.NaN],
                   'Frame.0': [1, 2, 3, 4, np.NaN],
                   'Filename.1': ['Exp327_Date_File_1', 'Exp327_Date_File_1', 
                                  'Exp327_Date_File_1', 'Exp327_Date_File_1', 
                                  'Exp327_Date_File_1'],
                   'Area.1': [830, 730, 630, 530, 430],
                   'Frame.1': [1, 1, 2, 3, 4],
                   'Filename.2': ['Exp327_Date_File_2', 'Exp327_Date_File_2', 
                                  'Exp327_Date_File_2', 'Exp327_Date_File_2', 
                                  'Exp327_Date_File_2'],
                   'Area.2': [700, 600, 500, 400, np.NaN],
                   'Frame.2': [1, 2, 3, 4, np.NaN]})

# create list of sub-dataframes, each with 3 columns, partitioning the original dataframe
subframes = [df.iloc[:, j:(j + 3)]  for j in np.arange(len(df.columns), step=3)]

# set column names to the same values for each subframe
for subframe in subframes:
    subframe.columns = ['Filename', 'Area', 'Frame']
    
# concatenate the subframes
df_long = pd.concat(subframes)
df_long

    Filename            Area    Frame
0   Exp327_Date_File_0  600.0   1.0
1   Exp327_Date_File_0  270.0   2.0
2   Exp327_Date_File_0  230.0   3.0
3   Exp327_Date_File_0  200.0   4.0
4   NaN                 NaN     NaN
0   Exp327_Date_File_1  830.0   1.0
1   Exp327_Date_File_1  730.0   1.0
2   Exp327_Date_File_1  630.0   2.0
3   Exp327_Date_File_1  530.0   3.0
4   Exp327_Date_File_1  430.0   4.0
0   Exp327_Date_File_2  700.0   1.0
1   Exp327_Date_File_2  600.0   2.0
2   Exp327_Date_File_2  500.0   3.0
3   Exp327_Date_File_2  400.0   4.0
4   Exp327_Date_File_2  NaN     NaN
user_130141720
Related