how do i take a python pandas dataframe and create a new table using the column and row names as the new column

Question

I was hoping someone could point me in the right direction. I have a dataframe that I would like to take the first column, join it with the name of the rest of the columns and assign the value to this new column.

2020-03-20DF.csv

Store,Total Started,2 Week,4 Week,5 Week,6 Week
Boston,9,0,5,1,3
New York,3,0,0,0,3
San Diego,6,0,6,0,0
Tampa Bay,1,0,1,0,0
Houston,14,0,7,0,7
Chicago,2,0,0,0,2

what i have so far

import pandas as pd
df1 = pd.read_csv('2020-03-20DF.csv')
df1.set_index('Store', inplace=True)
print(df1)

           Total Started  2 Week  4 Week  5 Week  6 Week
Store                                                   
Boston                 9       0       5       1       3
New York               3       0       0       0       3
San Diego              6       0       6       0       0
Tampa Bay              1       0       1       0       0
Houston               14       0       7       0       7
Chicago                2       0       0       0       2

What I would like to see is

Boston-2 Week  Boston-4 Week Boston-5 Week Boston-6 Week
   0                5             1            3

etc.

found an answer right here on stack stackoverflow.com/questions/53185860/… — drmcchamburgers
– drmcchamburgers, Commented Mar 22, 2020 at 18:20
Does this answer your question? DataFrame Pandas - Flatten dataframe using index and column name as the new column name — AMC
– AMC, Commented Mar 22, 2020 at 22:15
i did find that, but Sayandip's answer is simpler for what i was doing. — drmcchamburgers
– drmcchamburgers, Commented Mar 26, 2020 at 23:41

Sayandip Dutta · Accepted Answer · 2020-03-22 18:34:07Z

For the particular case:

>>> df[df['Store'] == 'Boston'].filter(like='Week').add_prefix('Boston-')
   Boston-2 Week  Boston-4 Week  Boston-5 Week  Boston-6 Week
0              0              5              1              3

# generally:
>>> for store in df['Store']:
...     print(df[df['Store'] == store].filter(like='Week').add_prefix(f'{store}-'))

   Boston-2 Week  Boston-4 Week  Boston-5 Week  Boston-6 Week
0              0              5              1              3
   New York-2 Week  New York-4 Week  New York-5 Week  New York-6 Week
1                0                0                0                3
   San Diego-2 Week  San Diego-4 Week  San Diego-5 Week  San Diego-6 Week
2                 0                 6                 0                 0
   Tampa Bay-2 Week  Tampa Bay-4 Week  Tampa Bay-5 Week  Tampa Bay-6 Week
3                 0                 1                 0                 0
   Houston-2 Week  Houston-4 Week  Houston-5 Week  Houston-6 Week
4               0               7               0               7
   Chicago-2 Week  Chicago-4 Week  Chicago-5 Week  Chicago-6 Week
5               0               0               0               2

drmcchamburgers · Accepted Answer · 2020-03-22 18:23:25Z

0

as mentioned, used the code example from another post

import pandas as pd
df1 = pd.read_csv('2020-03-20DF.csv')
df1.set_index('Store', inplace=True)
s = df1.stack()
df2 = pd.DataFrame([s.values], columns=[f'{i}-{j}' for i, j in s.index])
with pd.option_context('display.max_rows', None, 'display.max_columns', None):
    print(df2)

DataFrame.stack

answered Mar 22, 2020 at 18:23

drmcchamburgers

786 bronze badges

Comments

Bill · Accepted Answer · 2020-03-22 19:14:19Z

0

Would this be a suitable alternative?

df2 = df1.drop('Total Started', axis=1).stack()
print(df2.head())

Store           
Boston    2 Week    0
          4 Week    5
          5 Week    1
          6 Week    3
New York  2 Week    0
dtype: int64

It uses a multi-index.

Then, use tuples to index the values you want.

E.g.

df2[('Boston', '4 Week')]

5

To get to what you actually asked for (a single-level index with joined strings) you could do:

df2.index = pd.Series(df2.index.values).apply('-'.join)
print(df2.head())

Boston-2 Week      0
Boston-4 Week      5
Boston-5 Week      1
Boston-6 Week      3
New York-2 Week    0
dtype: int64

edited Mar 22, 2020 at 19:14

answered Mar 22, 2020 at 18:46

Bill

11.8k13 gold badges68 silver badges100 bronze badges

3 Comments

drmcchamburgers Over a year ago

looks like it works to drive down to single values. the reason i wanted the data in single rows is for further processing into a database.

Bill Over a year ago

Fair enough. Then you would have to reindex with pd.Series(df2.index.values).apply('-'.join) which is getting a bit messy...

Bill Over a year ago

I added this second step to the answer.

Collectives™ on Stack Overflow

how do i take a python pandas dataframe and create a new table using the column and row names as the new column

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related