Reputation: 13
I have 100 csv files in a folder. all of them has a column name
which is the name of the file and column z value.
import pandas as pd
df = pd.read_csv("ProfileGraph1.csv")
df.head()
Name Z
0 1 -3.687422
1 1 -3.688351
2 1 -3.684376
3 1 -3.691209
4 1 -3.693000
df = pd.read_csv("ProfileGraph2.csv")
df.head()
Name Z
0 2 -3.691955
1 2 -3.694228
2 2 -3.692559
3 2 -3.699092
4 2 -3.698381
df = pd.read_csv("ProfileGraph3.csv")
df.head()
Name Z
0 3 -3.693265
1 3 -3.694765
2 3 -3.693598
3 3 -3.697865
4 3 -3.699872
I would like to go through each of them and convert Z
column of each csv file to a row and store it in a new csv file, and append all of them to a new csv file. this is the output that I made it manually:
df = pd.read_csv("filename.csv")
df.head()
Name 1 2 3 4 5
0 1 -3.687422 -3.688351 -3.684376 -3.691209 -3.693000
1 2 -3.691955 -3.694228 -3.692559 -3.699092 -3.698381
2 3 -3.693265 -3.694765 -3.693598 -3.697865 -3.699872
Upvotes: 1
Views: 42
Reputation: 863801
First loop by list of all files and create big DataFrame by concat
, then reshape by cumcount
for counter with unstack
:
import glob
files = glob.glob('files/*.csv')
dfs = [pd.read_csv(fp) for fp in files]
df = pd.concat(dfs, ignore_index=True)
df = df.set_index(['Name',df.groupby('Name').cumcount()])['Z'].unstack().reset_index()
Upvotes: 1