adding new rows based on column header

Question

columns= ['A','B','C']
df= pd.DataFrame(columns=columns)

I have a empty dataframe like above with named column header and I have files where each file has data like A 32, B 43 , C 21 and so on. I want to fill the dataframe in such a way that each file takes up one row and adds the data in each file in the respective column headers.

example-

let there be two files like-

file1    file2

A 32     A 56
B 31     B 34
C 45     C 12

then dataframe will be

  'A'    'B'  'C'
  32     31    45
  56     34    12

edit-

for root, dirs, files in os.walk(dir_M):
    for name in files:

        L = [pd.read_csv(f, index_col=[0], header=None, sep='\s+')[1] for f in files]
        df = pd.concat(L, axis=1).T

jezrael · Accepted Answer · 2018-05-29 06:38:02Z

2

Use list comprehension for list of DataFrames with concat and transpose:

#add /*.* for read all files
currentdir = 'path/*.*'

L = []
#loop by files
for file in glob.glob(currentdir):
    print (file)

    s = pd.read_csv(file, sep='|', header=None)[0]
    if (s.astype(str).str.strip().str.contains('\s+').any()):
        #print (s)
        df = s.str.rsplit(n=1, expand=True)
        df.columns = ['a','b']
        L.append(df.set_index('a')['b'])


df = pd.concat(L, axis=1, keys=range(len(L))).T

edited May 29, 2018 at 6:38

answered May 28, 2018 at 13:23

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

ubuntu_noob Over a year ago

each line in the file is a space separated file....like word=line.split(" ") and then the word[0] is the column header and word[1] is the column value

ubuntu_noob Over a year ago

I am fetching the file from a directory so I am using os.walk with the solution but the error is coming file not found

jezrael Over a year ago

@ubuntu_noob - solution was changed.

jezrael Over a year ago

@ubuntu_noob - is possible use glob by directory, which return list of all files?

ubuntu_noob Over a year ago

the index column is all 1...how to make it as numbered again and fillna as 0?

|

Collectives™ on Stack Overflow

adding new rows based on column header

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related