CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
Leonardo Amador
Leonardo Amador

Reputation: 89

How to created new rows on pandas using data from existing colum?

I want to get the data from some columns and create new rows with it. In my case, the same product was can be sold many times, and each time it was sold again, my df created a new column, I'd like to take the data from those columns and put them in rows, keeping the same "prevenda" field.

It looks like this:

ID Product Value Produc01 Value01
0 A 1,20 a 2
1 A 3,30 b 9
2 B 5,50 c 4
3 B 4,30 d 2
4 B 450 Nan Nan
5 B 6,30 Nan Nan
6 C 9,30 Nan Nan

and I'd to look like this:

ID Product Value 
 0      A    1,20   
 1      A    3,30   
 2      B    5,50   
 3      B    4,30  
 4      B    450    
 5      B    6,30   
 6      C    9,30   
 0      a    2
 1      b    9
 2      c    4
 3      d    2

Keeping the same ID. but putting the values from the columns in others rows.

Upvotes: 1

Views: 60

Answers (2)

mozway
mozway

Reputation: 262164

Here is a generic solution that will work with any shape.

You can set up identical column names (you need a MultiIndex to handle duplicated name), stack, and finally drop the useless level:

df2 = df.set_index('ID')
df2.columns = pd.MultiIndex.from_frame(df2.columns.str.extract('(\D+)(\d*)'))
(df2.stack()
    .sort_index(level=1)
    .dropna(how='all')
    .droplevel(1)
)

output:

0  Product Value
ID              
0        A  1,20
1        A  3,30
2        B  5,50
3        B  4,30
4        B   450
5        B  6,30
6        C  9,30
0        a   2.0
1        b   9.0
2        c   4.0
3        d   2.0

Upvotes: 2

U13-Forward
U13-Forward

Reputation: 71610

Try pd.concat:

pd.concat([df.iloc[:, :3], df.set_index('ID').iloc[:, 2:].reset_index().set_axis(['ID', 'Product', 'Value'], axis=1)], ignore_index=True).dropna()

    ID Product Value
0    0       A  1,20
1    1       A  3,30
2    2       B  5,50
3    3       B  4,30
4    4       B   450
5    5       B  6,30
6    6       C  9,30
7    0       a     2
8    1       b     9
9    2       c     4
10   3       d     2
>>>

Upvotes: 1

Related Questions