How to generate a custom master dataframe from a folder of CSVs with Pandas?

user_11365436 0
Viewed 48

I have a folder with multiple CSV files. Each CSV file has the same dimensions. They all have 2 columns, and the first column of each is the same. Is there a way to import all the CSVs and concatenate into one Dataframe in which the first file provides the first column along with its second column, and the subsequent files just have their second column of values added next to that? The header of the second column for each file is unique, but they have the same header of the first file.

pandas numpy python
2 Answers

This would give you a combination of all your file in path folder you can find all material related to merge or combine df in here

Check out for all sort of combination for df(CSV that you read as df)

import pandas as pd
import os
path='path to folder'
all_files=os.listdir(path)
li = []
for filename in all_files:
    df = pd.read_csv(path+filename, index_col='H1')
    print(df)
    li.append(df)
frame = pd.concat(li, axis=1, ignore_index=False)

frame.to_csv(path+'out.csv')
print(frame)

input files are like:

File1
+----+----+
| H1 | H2 |
+----+----+
|  1 | A  |
|  2 | B  |
|  3 | C  |
+----+----+
File2:
+----+----+
| H1 | H2 |
+----+----+
|  1 | D  |
|  2 | E  |
|  3 | F  |
+----+----+
File13:
+----+----+
| H1 | H2 |
+----+----+
|  1 | G  |
|  2 | H  |
|  3 | I  |
+----+----+

Output is:(saved in out.csv file in same directory)

+----+----+----+----+
| H1 | H2 | H2 | H2 |
+----+----+----+----+
|  1 | A  | D  | G  |
|  2 | B  | E  | H  |
|  3 | C  | F  | I  |
+----+----+----+----+
user_31039130

Here is how I will proceed. I am assuming that only csv files are present in the folder.

import os
import pandas as pd

files = os.listdir("path_of_the_folder")
dfs = [pd.read_csv(file).set_index('col1') for file in files]
df_final = dfs[0].join(dfs[1:])
user_45519840
Related