Python Panda Convert Tabular Data To Column Format Data

Question

I have below tabular data and I want to convert to format at bottom so that i can load it into my database.

How can i achieve it by using Panda?

I am using Python but excel method also welcome

Answer 1

You need:

data.set_index(['date']).stack(dropna=False).reset_index(name='Number').rename(columns={'level_1':'Item'})

Output:

   date Item    Number
0   1   A   NaN
1   1   B   12.0
2   1   C   10.0
3   1   D   NaN
4   2   A   10.0
5   2   B   30.0
6   2   C   NaN
7   2   D   NaN
8   3   A   NaN
9   3   B   NaN
10  3   C   NaN
11  3   D   90.0

Answer 2

Use df.fillna() to fill the blank with NULL value, followed by df.melt() and then followed with df.set_index(). At the end, rename the column to columns name you desired with df.rename() or more details in this topic

In []: df
Out[]: 
   Date     A     B    C     D
0     1   NaN  54.0  435   NaN
1     2  23.0   3.0  345   NaN
2     3  43.0   4.0   78   NaN
3     4   4.0   NaN  423   NaN
4     5  34.0  54.0    4  45.0
5     6   NaN  54.0    3  87.0

In []: (
  ...: df.fillna('NULL')
  ...:     .melt(id_vars='Date', value_vars=['A','B','C','D'])
  ...:     .set_index('Date')
  ...:     .rename(columns={'variable':'Item', 'value':'Number'}))
  ...: )

Out[]: 
     Item Number
Date            
1       A   NULL
2       A     23
3       A     43
4       A      4
5       A     34
6       A   NULL
1       B     54
2       B      3
3       B      4
4       B   NULL
5       B     54
6       B     54
1       C    435
2       C    345
3       C     78
4       C    423
5       C      4
6       C      3
1       D   NULL
2       D   NULL
3       D   NULL
4       D   NULL
5       D     45
6       D     87