0

I have Excel with 3 sheets: Gross, Margin, Revenue. Each of them has a table with same columns and row headers.

I need to :

1) iterate through each sheet and save into a dataframe

2) unpivot each dataframe

3) append value columns from each daraframe into one.

Gross:

enter image description here

Margin:

enter image description here

Revenue:

enter image description here

Outcome should be like this:

enter image description here

If I use sheet_name=None then I get an error:

import pandas as pd
df = pd.read_excel('BudgetData.xlsx', sheet_name=None,index=False)
df_unpv = df.melt(id_vars=['Company'], var_name ='Month', value_name = 'Gross Revenue')
print(df_unpv)

Error I get:

AttributeError                            Traceback (most recent call last)
<ipython-input-60-ee1791c449b1> in <module>
      1 import pandas as pd
      2 df = pd.read_excel('BudgetData.xlsx', sheet_name=None,index=False)
----> 3 df_unpv = df.melt(id_vars=['Company'], var_name ='Month', value_name = 'Gross Revenue')
      4 df_unpv
      5 

AttributeError: 'collections.OrderedDict' object has no attribute 'melt'

Excel file with sample data can be found here: https://www.dropbox.com/s/9dsnylng70t5a8i/Count%20Open%20and%20Closed%20at%20Point%20of%20time.pbix?dl=0

Serdia
  • 4,242
  • 22
  • 86
  • 159
  • First try concating the values of the ordered dict: `df = pd.concat(df.values(),sort=False)` – anky Jan 28 '20 at 16:40

1 Answers1

1

You are trying to call the .melt() method from your df object, instead of calling it from pd as the docs say:

pandas.melt(frame, id_vars=None, value_vars=None, var_name=None, value_name='value', col_level=None)

Parameters:
frame : DataFrame id_vars : tuple, list, or ndarray, optional Column(s) to use as identifier variables.

value_vars : tuple, list, or ndarray, optional Column(s) to unpivot. If not specified, uses all columns that are not set as id_vars.

var_name : scalar Name to use for the ‘variable’ column. If None it uses frame.columns.name or ‘variable’.

value_name : scalar, default ‘value’ Name to use for the ‘value’ column.

col_level : int or string, optional If columns are a MultiIndex then use this level to melt.

alberto vielma
  • 2,302
  • 2
  • 8
  • 15
  • Thanks, But it gives me an error. `'collections.OrderedDict' object has no attribute 'columns'`. @anky_91 solution works. I just need to understand how to append data – Serdia Jan 28 '20 at 17:07