CODEWITHSUNDEEP
pandascsvexport-to-csv
Anahita Kp
Anahita Kp

Reputation: 13

How to convert columns values of a csv file to different format structure in pandas?

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

Answers (1)

jezrael
jezrael

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

Related Questions