How to store values from loop to a dataframe?

Question

I have created a loop that generates some values. I want to store those values in a data frame. For example, completed one loop, append to the first row.

def calculate (allFiles):

    result = pd.DataFrame(columns = ['Date','Mid Ebb Total','Mid Flood Total','Mid Ebb Control','Mid Flood Control'])

    total_Mid_Ebb = 0
    total_Mid_Flood = 0
    total_Mid_EbbControl = 0
    total_Mid_FloodControl = 0

    for file_ in allFiles:
        xls = pd.ExcelFile(file_)
        df = xls.parse('General Impact')
        Mid_Ebb = df[df['Tidal Mode'] == "Mid-Ebb"] #filter 
        Mid_Ebb_control = df[df['Station'].isin(['C1','C2','C3'])] #filter control
        Mid_Flood = df[df['Tidal Mode'] == "Mid-Flood"] #filter
        Mid_Flood_control = df[df['Station'].isin(['C1','C2','C3', 'SR2'])] #filter control
        total_Mid_Ebb += Mid_Ebb.Station.nunique() #count unique stations = sample number
        total_Mid_Flood += Mid_Flood.Station.nunique()
        total_Mid_EbbControl += Mid_Ebb_control.Station.nunique()
        total_Mid_FloodControl += Mid_Flood_control.Station.nunique()

    Mid_Ebb_withoutControl = total_Mid_Ebb - total_Mid_EbbControl
    Mid_Flood_withoutControl = total_Mid_Flood - total_Mid_FloodControl

    print('Ebb Tide: The total number of sample is {}. Number of sample without control station is {}. Number of sample in control station is {}'.format(total_Mid_Ebb, Mid_Ebb_withoutControl, total_Mid_EbbControl))
    print('Flood Tide: The total number of sample is {}. Number of sample without control station is {}. Number of sample in control station is {}'.format(total_Mid_Flood, Mid_Flood_withoutControl, total_Mid_FloodControl))

The dataframe result contains 4 columns. The date is fixed. I would like to put total_Mid_Ebb, Mid_Ebb_withoutControl, total_Mid_EbbControl to the dataframe.

Answer 1

Note this does not produce a row per file as requested, but it more of a comment about general use of Pandas for problems like this - it is often easier to read all the data then process using the pandas files than to write your own loops over different cases.

I think you are not using pandas in the idiomatic way here. I think you will save a lot of code and get a more understandable result if you do it this way:

controlstations = ['C1', 'C2', 'C3', 'SR2']
df = pd.concat(pd.read_excel(file_, sheetname='General Impact') for file_ in files)
df['Control'] = df.Station.isin(controlstations)
counts = df.groupby(['Control', 'Tidal Mode']).Station.agg('nunique')

So here you are reading all the excel files into a single dataframe first, then adding a column to indicate if that is a control station or not, then using groupby to count the different combinations.

counts is a series with a two-dimensional index (for some made up data):

Control  Tidal Mode
False    Mid-Ebb       2
         Mid-Flood     2
True     Mid-Ebb       2
         Mid-Flood     2

You can access the values you have in your function like this:

total_Mid_Ebb = counts['Mid-Ebb'].sum()
total_Mid_Ebb_Control = counts['Mid-Ebb', True]
total_Mid_Flood = counts['Mid-Flood'].sum()
total_Mid_Flood_Control = counts['Mid-Flood', True]

After which you can easily add them to a DataFrame:

import datetime
today = datetime.datetime.today()
totals = [total_Mid_Ebb, total_Mid_Flood, total_Mid_Ebb_Control, total_Mid_Flood_Control]
result = pd.DataFrame(data=[totals], columns=['Mid Ebb Total', 'Mid Flood Total', 'Mid Ebb Control', 'Mid Flood Control'],
                       index=[today])

Answer 2

I believe you need append scalars in loop to list of tuples and then use DataFrame constructor. Last count differences in result DataFrame:

def calculate (allFiles):

    data = []
    for file_ in allFiles:
        xls = pd.ExcelFile(file_)
        df = xls.parse('General Impact')
        Mid_Ebb = df[df['Tidal Mode'] == "Mid-Ebb"] #filter 
        Mid_Ebb_control = df[df['Station'].isin(['C1','C2','C3'])] #filter control
        Mid_Flood = df[df['Tidal Mode'] == "Mid-Flood"] #filter
        Mid_Flood_control = df[df['Station'].isin(['C1','C2','C3', 'SR2'])] #filter control
        total_Mid_Ebb = Mid_Ebb.Station.nunique() #count unique stations = sample number
        total_Mid_Flood = Mid_Flood.Station.nunique()
        total_Mid_EbbControl = Mid_Ebb_control.Station.nunique()
        total_Mid_FloodControl = Mid_Flood_control.Station.nunique()
        data.append((total_Mid_Ebb, 
                     total_Mid_Flood, 
                     total_Mid_EbbControl, 
                     total_Mid_FloodControl))

    cols=['total_Mid_Ebb','total_Mid_Flood','total_Mid_EbbControl','total_Mid_FloodControl']

    result = pd.DataFrame(data, columns=cols)
    result['Mid_Ebb_withoutControl'] = result.total_Mid_Ebb - result.total_Mid_EbbControl
    result['Mid_Flood_withoutControl']=result.total_Mid_Flood-result.total_Mid_FloodControl

    #if want check all totals
    total = result.sum()
    print (total)


    return result

Answer 3

Here is an example of loading data per column in a dataframe after each iteration of a loop. While this is not THE only method, it's one that helps understand concept better.

Necessary imports

import pandas as pd
from random import randint

First define an empty data-frame of 5 columns to match your problem

df = pd.DataFrame(columns=['A','B','C','D','E'])

Next we iterate through for loop and generate value using randint() and add one value at a time to each column Staring with 'A' all the way to 'E',

for i in range(5): #add 5 rows of data
    df.loc[i, ['A']] = randint(0,99)
    df.loc[i, ['B']] = randint(0,99)
    df.loc[i, ['C']] = randint(0,99)
    df.loc[i, ['D']] = randint(0,99)
    df.loc[i, ['E']] = randint(0,99)

We get a DF whose 5 rows are populated.

>>> df
    A   B   C   D   E
0   4  74  71  37  90
1  41  80  77  81   8
2  14  16  82  98  89
3   1  77   3  56  91
4  34   9  85  44  19

Hope above helps and you are able to tailor to your needs.