How can I turn rows into columns?

Question

I have a pandas Data Frame that looks like this:

+------+------------+-----------+
| FIPS |    Date    | Confirmed |
+------+------------+-----------+
|   66 | 04/02/2020 |        82 |
|   66 | 04/03/2020 |        84 |
|   66 | 04/04/2020 |        93 |
+------+------------+-----------+

I want to turn this into one row with the date appended to the confirmed column:

+------+--------------------+--------------------+--------------------+
| FIPS | Confirmed_20200402 | Confirmed_20200402 | Confirmed_20200402 |
+------+--------------------+--------------------+--------------------+
| 66   | 82                 | 84                 | 93                 |
+------+--------------------+--------------------+--------------------+

I tried to use pivot_table, however, that giving me a multi-index table which isn't what I want.

How can I go about getting my desired output?

use the same and merge the multiindex into 1: https://stackoverflow.com/questions/14507794/pandas-how-to-flatten-a-hierarchical-index-in-columns — anky, Aug 18 '20 at 18:42

YOLO · Answer 1 · 2020-08-18T18:53:24.140

2

You can do:

df['Date']= pd.to_datetime(df['Date'], dayfirst=True).dt.date

dd = pd.pivot_table(df, index='FIPS', columns='Date', values='Confirmed')

# fix column names
dd.columns.name = None
dd.columns = dd.add_prefix('Confirmed_').columns.str.replace('\-','')

dd = dd.reset_index()
print(dd)

   FIPS  Confirmed_20200204  Confirmed_20200304  Confirmed_20200404
0    66                  82                  84                  93

edited Aug 18 '20 at 18:53

answered Aug 18 '20 at 18:47

YOLO

20,181
5
20
40

I'm getting an Attribute Error when I try this. The error says "Can only use .str accessor with Index, not MultiIndex" – ms25297 Aug 18 '20 at 18:52
@ms25297 how does your `dd.columns` look like? I dont get such error on the given data – YOLO Aug 18 '20 at 18:55

score 2 · Answer 2 · answered Aug 18 '20 at 18:50

2

Use unstack:

df['Date'] = pd.to_datetime(df['Date']).dt.strftime('%Y%m%d')
df = df.set_index(['FIPS', 'Date']).unstack()
df.columns = df.columns.get_level_values(0) + '_' + df.columns.get_level_values(1)

answered Aug 18 '20 at 18:50

Code Different

90,614
16
144
163

score 0 · Accepted Answer · answered Aug 18 '20 at 18:57

We can achieve your output with pivot and flattening the multiIndex columns.

df = pd.DataFrame({'FIPS':[66]*3
                  ,'Date':pd.date_range('04/02/2020', periods=3, freq='D')
                  ,'Confirmed':[82,84,93]})

df_p = df.pivot('FIPS','Date')
df_p.columns = [f'{i}_{j.strftime("%Y%m%d")}' for i, j in df_p.columns]
df_p

Output:

      Confirmed_20200402  Confirmed_20200403  Confirmed_20200404
FIPS                                                            
66                    82                  84                  93

Another way using pivot and defining values parameter:

df_p = df.assign(Date=df['Date'].dt.strftime('%Y%m%d')).pivot('FIPS', 'Date', 'Confirmed').add_prefix('Confirmed_')
df_p

Output:

Date  Confirmed_20200402  Confirmed_20200403  Confirmed_20200404
FIPS                                                            
66                    82                  84                  93

How can I turn rows into columns?

3 Answers3