pd.melt() a dictionary/series of dataframes

user_14783839 0
Viewed 103

For example I have the following map:

  {'df1': Jan    Feb    Mar
           1      3      5
           2      4      6
   'df2': Jan    Feb    Mar
           7      9      11
           8      10     12
    ......}

And I want the following output:

Jan  1
Jan  2
Feb  3
Feb  4
Mar  5
Mar  6
Jan  7
Jan  8
Feb  9
Feb  10
Mar  11
Mar  12

Does anyone knows if its possible to do it this way? What I have tried is to iterate through DataFrames to try getting

  {'df1': Jan  1
          Jan  2
          Feb  3
          Feb  4
          Mar  5
          Mar  6

   'df2': Jan  7
          Jan  8
          Feb  9
          Feb  10
          Mar  11
          Mar  12

by using

for x in dfMap:
    df = pd.melt(list(x.values()))

Then try to concat it with df1m =

pd.concat(df.values(), ignore_index=True)

Which gave me error

AttributeError: 'list' object has no attribute 'columns'

I am fairly new to programming and really wanted to learn, will be nice if anyone can explain how this works, and why list or dict_values object has no attribute 'columns'.

Thanks in advance!

pandas dataframe excel dictionary python
1 Answers

You can concat and stack:

out = pd.concat(d.values()).stack().droplevel(0)

Or:

out = pd.concat(d.values()).melt()

Example:

df = pd.DataFrame(np.arange(1,10).reshape(-1,3),columns=['Jan','Feb','Mar'])
d = {}
for e,i in df.iterrows():
    d[f"df{e+1}"] = i.to_frame().T
print(d,'\n')

out = pd.concat(d.values()).stack().droplevel(0)
print(out)

    {'df1':    Jan  Feb  Mar
0    1    2    3, 'df2':    Jan  Feb  Mar
1    4    5    6, 'df3':    Jan  Feb  Mar
2    7    8    9} 

Jan    1
Feb    2
Mar    3
Jan    4
Feb    5
Mar    6
Jan    7
Feb    8
Mar    9
dtype: int32

With melt:

out = pd.concat(d.values()).melt()
print(out)

  variable  value
0      Jan      1
1      Jan      4
2      Jan      7
3      Feb      2
4      Feb      5
5      Feb      8
6      Mar      3
7      Mar      6
8      Mar      9

EDIT, for edited question , try:

out = pd.concat(d).stack().sort_index(level=[0,-1]).droplevel([0,1])

Example below:

df = pd.DataFrame(np.arange(1,13).reshape(3,-1).T,columns=['Jan','Feb','Mar'])
d = {}
for e,i in df.groupby(df.index//2):
    d[f"df{e+1}"] = i
print(d,'\n')

out = pd.concat(d).stack().sort_index(level=[0,-1]).droplevel([0,1])
print(out)

{'df1':    Jan  Feb  Mar
0    1    5    9
1    2    6   10, 'df2':    Jan  Feb  Mar
2    3    7   11
3    4    8   12} 

Jan     1
Jan     2
Feb     5
Feb     6
Mar     9
Mar    10
Jan     3
Jan     4
Feb     7
Feb     8
Mar    11
Mar    12
dtype: int32

Or you can also convert the dataframe names as int and then sort:

out = (pd.concat(d.values(),keys=[int(key[2:]) for key in d.keys()])
   .stack().sort_index(level=[0,-1]).droplevel([0,1]))
user_98406370
Related