CODEWITHSUNDEEP
python-3.xpandas
Callie Jester
Callie Jester

Reputation: 33

Pandas: Merging multiple (~30) tables together?

So I currently have 30+ individual tables in a folder that are in this format:

Name1 = pd.read_csv('Name1.txt')

Name1: 

         A    B    C    D    
type1    1    1    NaN  4
type2    2    0    NaN  2
type3    3    2.1  NaN  .2
type4    1    2    NaN  1

So I have like 30 such tables and would like to get a complete table that looks something like this:

       Name1/A  Name1/B Name1/D  Name2/A Name2/B Name2/C ...... Name30/C 
type1    1        1       4       ...     ...     ...             ...         
type2    2        0       2       ...     ...     ...             ...
type3    3        2.1     .2      ...     ...     ...             ...
type4    1        2       1       ...     ...     ...             ...

I have been attempting to use a for loop to read in the data and use pd.concat(...) but it doesn't seem to be doing anything

I suppose one of my main issues is figuring out how I am suppose to create a dataframe that combines all these different charts without having to do pd.read_csv('NameX.txt') for ALL 30 tables and then merging them all together. I assume for loops would be the main savior for this, but everytime I try to read them in a for-loop, it tells me the file can't be found.

Also if it helps, my files are named in such "Name1.txt", "Name2.txt", "Name3.txt"...and so on, also the type1 to type4 column doesn't change throughout each chart and the columns with values NaN are meant to be removed in the final, larger table.

Any suggestions would be amazing! Thanks you!

Upvotes: 0

Views: 74

Answers (2)

piRSquared
piRSquared

Reputation: 294318

Try:

names = ['Name{}'.format(i) for i in xrange(1, 31)]
files = ['{}.txt'.format(n) for n in names]

df = pd.concat([pd.read_csv(f, index_col=0) for f in files],
               axis=1, keys=names)

cols = df.columns.to_series()
df.columns = cols.str.get(0).astype(str) + '/' + cols.str.get(1)

df.iloc[:, :10]

enter image description here

Upvotes: 1

gabra
gabra

Reputation: 10564

If you do not whish everything in just one DataFrame you can try Pandas Panel

dfs = {n:pd.read_csv('Name{}.txt'.format(n) for n in range(1,30)}
panel = pd.Panel(dfs)

For more information, you can check this.

Upvotes: 1

Related Questions