How to add a footer(row) to the dataframe with different column length in python?-CodePudding

The cursor object executes query at Oracle and gets the result using fetchAll method, and am able to convert them into csv file.

But I want to add footer(<some static value>,<total number of records present in the dataframe>,<current date in DDMMYY format>) at end of the file like below:

code:

statement= 'select firstname,lastname,age,gender,college,university from students;'
cursor.execute(statement)
result_list = cursor.fetchall();
df = pd.DataFrame(result_list)
print(df)

output:

   0       1     2   3    4            5
0  arun    sai   25  M  testcollege  testuniversity
1  varun   tej   28  F  testcollege  testuniversity
2  rachel  green 27  M  testcollege  testuniversity
3  le      blanc 25  M  testcollege  testuniversity

Expected Output:

   0       1     2   3    4            5
0  arun    sai   25  M  testcollege  testuniversity
1  varun   tej   28  F  testcollege  testuniversity
2  rachel  green 27  M  testcollege  testuniversity
3  le      blanc 25  M  testcollege  testuniversity
4  ABC     4     011221

How to achieve it with pandas dataframe?

I have tried many ways using dataframe append and Dataframe.loc operation but not able to achieve.

Current csv file:

arun,sai,25,M,testcollege,testuniversity
varun,tej,28,F,testcollege,testuniversity
rachel,green,27,M,testcollege,testuniversity
le,blanc,25,M,testcollege,testuniversity

Expected csv file:

arun,sai,25,M,testcollege,testuniversity
varun,tej,28,F,testcollege,testuniversity
rachel,green,27,M,testcollege,testuniversity
le,blanc,25,M,testcollege,testuniversity
ABC,4,071221

Using:

Python3.5

Pandas1.3.4

CodePudding user response：

It depends on whether or not you are directly using DataFrame to present the data to the end-users--while it is easy to used, I would argue that DataFrame is more about data manipulation and less about data presentation.

The current way we achieve this is to not presenting the table with DataFrame and instead we use another HTML layer to present the final table to users.

The framework we are using is called DataTables. However, if currently you are only in the Jupyter Notebook's world, then this approach would not work...

CodePudding user response：

After all analysis, I believe doing manipulation at Dataframe is not correct for this scenarion even could not achieve it too.

since my result records from the database will be large in number, going ahead with pandas only to write the result into a csv file, but for the footer handling at bottom using csv writer and appending the footer at last row. As of now I can see this is the only work around and expecting for a better answer from community

# Pre-requisite - Import the writer class from the csv module
from csv import writer
  
# The data assigned to the list 
list_data=['03','Smith','Science']
  
# Pre-requisite - The CSV file should be manually closed before running this code.

# First, open the old CSV file in append mode, hence mentioned as 'a'
# Then, for the CSV file, create a file object
with open('CSVFILE.csv', 'a', newline='') as f_object:  
    # Pass the CSV  file object to the writer() function
    writer_object = writer(f_object)
    # Result - a writer object
    # Pass the data in the list as an argument into the writerow() function
    writer_object.writerow(list_data)  
    # Close the file object
    f_object.close()