concatenate dataframes vertically

Question

I want to concatenate dataframes vertically. Each dataframe I have is created from a file in a directory and I want to concatenate all of them. I can do this for each individual file:

df1 = pd.read_csv('C:/Users/Desktop/folder/file1.csv', usecols = 'name')
df2 = pd.read_csv('C:/Users/Desktop/folder/file1.csv', usecols = 'reads')

result = pd.concat([df1, df2], axis=1)

But, I'd have to do this for each individual file at a time. I tried saving the values in an empty array like this:

for file in glob.glob('C:/Users/Desktop/folder/file*.csv'):

    df1 = pd.read_csv(file, usecols='name')
    df2 = pd.read_csv(file, usecols='reads')

    collected_columns.append(df1['name'])
    collected_columns.append(df2['reads'])
    
final_df = pd.concat(df1, df2, join='outer', axis=1, sort=True)

# dataframe to csv
final_df.to_csv('C:/Users/Desktop/folder/TEST.csv')

but this keeps resulting in a dataframe with each column from each file side by side. I hope this makes sense, if anyone can help I'd greatly appreciate it!

Answer 1

Let's assume that result of first concatenation is as follows:

first_concat = pd.concat([df1, df2], axis=1)
    name    reads
0   Joe     1
1   Jack    2
2   John    3

And you have another file based on which you have another concatenation (the same code as the first file):

second_concat = pd.concat([df3, df4], axis=1)

    name    reads
0   Ava     11
1   Adam    22

In order to concat these two vertically, you should do:

all_df = [first_concat, second_concat]
final_df = pd.concat(all_df, ignore_index=True)

    name    reads
0   Joe     1
1   Jack    2
2   John    3
3   Ava     11
4   Adam    22

Then you can use it in your for loop easily:

all_df = []
for file in glob.glob('C:/Users/Desktop/folder/file*.csv'):
    df1 = pd.read_csv(file, usecols='name')
    df2 = pd.read_csv(file, usecols='reads')
    df_nr_concat = pd.concat([df1, df2], axis=1)
    all_df.append(df_nr_concat)
final_df = pd.concat(all_df, ignore_index=True)

Answer 2

Here I'm providing code with output:

Code:

import pandas as pd

example_data_1 = {
    'Name': ['Alice', 'Bob', 'Charlie'],
    'Age': [25, 30, 35],
    'Salary': [50000, 60000, 70000]
}
df1 = pd.DataFrame(example_data_1)

example_data_2 = {
    'Name': ['David', 'Emily', 'Frank'],
    'Age': [28, 22, 33],
    'Salary': [72000, 48000, 53000]
}
df2 = pd.DataFrame(example_data_2)

Concatenated_df = pd.concat([df1, df2], ignore_index=True)
print("Concatenated DataFrame:")
print(Concatenated_df)

Output:

Concatenated DataFrame:
Name  Age  Salary
0    Alice   25   50000
1      Bob   30   60000
2  Charlie   35   70000
3    David   28   72000
4    Emily   22   48000
5    Frank   33   53000