Pandas dataframe copy or append column views into new rows

Question

I am struggling to append a copy of a DataFrame view into new rows of another dataframe.

On the following table:

Description, Item Number, Company A, Company B, Company C
Assets, 1, 100000, 50000, 20000
Liabilities, 2, 80000, 10000, 15000

I would like to recreate as

Description, Item Number, Company, Value
Assets, 1, Company A, 100000
Liabilities, 2, Company A, 80000
Assets, 1, Company B, 50000
Liabilities, 2, Company B, 10000
Assets, 1, Company C, 20000
Liabilities, 2, Company C, 15000

Code used

ll=list(range(2,df.shape[1]))
retobj = pd.DataFrame(columns=[df.columns[0:2]]) #create dataframe to return
for item in ll:
    nl=[0,1]
    nl.append(item)
    tdf=df.iloc[:,nl].copy() #tdf-temporary data frame
    prevheading=tdf.columns[-1]
    tdf.rename(columns = {prevheading:'Value'}, inplace=True)
    tdf['Company'] = prevheading
    retobj.append(tdf)

It copies the first company correctly but then fails with the following error: AttributeError: 'NoneType' object has no attribute 'is_extension'.

The reason for this approach is that the data received is variable in terms of columns. i.e. multiple companies can be received.

Does this answer your question? [How to pivot a dataframe](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) — Ric S, Jul 18 '20 at 13:09
`df.melt(['Description', ' Item Number'], var_name='Company')`? — Ch3steR, Jul 18 '20 at 13:12

baxx · Answer 1 · 2020-07-18T13:47:08.750

df.melt using id_vars is useful for this:

from io import StringIO
import pandas as pd

csv = '''\
Description, Item Number, Company A, Company B, Company C
Assets, 1, 100000, 50000, 20000
Liabilities, 2, 80000, 10000, 15000
'''.replace(' ', '')

df = pd.read_csv(StringIO(csv))

df_new = df.melt(id_vars=['Description', 'ItemNumber'],
            var_name='Company, 
            value_name='Value')

Which gives:

   Description  ItemNumber   Company   Value
0       Assets           1  CompanyA  100000
1  Liabilities           2  CompanyA   80000
2       Assets           1  CompanyB   50000
3  Liabilities           2  CompanyB   10000
4       Assets           1  CompanyC   20000
5  Liabilities           2  CompanyC   15000

You can replace `df.melt(id_vars=['Description', 'ItemNumber'], var_name='Company').rename(columns = {'value' : 'Value'})` with `df.melt(id_vars=['Description', ' Item Number'], var_name='Company',value_name='Value')` — Ch3steR, Jul 18 '20 at 13:44

Pandas dataframe copy or append column views into new rows

1 Answers1