How do I go from long to wide in this dataframe in pandas?

Question

I have a dataframe, that looks like below.

table                            some_date     count      
A_table                          1/1/2020   451663386
                                 1/10/2020  731919336
                                 1/11/2020   95637849
                                 1/12/2020  889510610
                                 1/13/2020  834557529
                                 1/14/2020  881597773
                                 1/15/2020  838596734
                                 1/16/2020  920376244
                                 1/17/2020  832792096
                                 1/18/2020  779652777
                                 1/19/2020  834532103
                                 1/2/2020   521978525
                                 1/20/2020  777782936
                                              ...
B_table                          2/24/2020    5492546
                                 2/25/2020    5594754
                                 2/26/2020    5201614
                                 2/27/2020    5961111
                                 2/28/2020    6811793
                                 2/29/2020    6275315
                                 2/3/2020    13307059
                                 2/4/2020    11695493
                                 2/5/2020     9034222

I want to go from above to this.

some_date   A_table B_table
1/10/2020   731919336   NA
1/11/2020   95637849    NA
1/12/2020   889510610   NA
1/13/2020   834557529   NA
1/14/2020   881597773   5594754
1/15/2020   838596734   5201614
1/16/2020   920376244   5961111
1/17/2020   832792096   6811793
1/18/2020   779652777   6275315
1/19/2020   834532103   13307059
1/2/2020    521978525   11695493
1/20/2020   777782936   9034222

I tried to do a pivot

df.set_index('some_date').unstack('table')

It doesn't seem to be the right approach. Any help is appreciated.

Thanks

does this help : https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe — sammywemmy, Mar 26 '20 at 05:14

jezrael · Accepted Answer · 2020-03-26T05:25:33.487

1

It depends what is df.index.

If table is level of index use:

df1 = df.set_index('some_date', append=True)['count'].unstack(0)

If table and some_date are levels of MultiIndex use:

df1 = df['count'].unstack(0)

If table and some_date are columns then use:

df['some_date'] = df['some_date'].mask(df['some_date'] == '', np.nan).ffill()
df1 = df.pivot('some_date','table','count')

edited Mar 26 '20 at 05:25

answered Mar 26 '20 at 05:19

jezrael

822,522
95
1,334
1,252

I get a 'Key Error' for the first option – user3437212 Mar 26 '20 at 05:25
@user3437212 - what is `print(df.index)` ? – jezrael Mar 26 '20 at 05:25
It worked! df1=df['count'].unstack(0), it is a multiindex situation. Thank you! I will read up more on the unstack() function. – user3437212 Mar 26 '20 at 05:29

score 0 · Answer 2 · answered Mar 26 '20 at 05:15

You can use pandas dataframes to merge the 2 tables. Check out the documentation here. You would want to use an 'outer' join if you want to keep all the entries in both tables.

pd.merge(A_table, B_table, how='outer', on=some_date)

More detailed syntax below (copied from the link above)

pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,
     left_index=False, right_index=False, sort=True,
     suffixes=('_x', '_y'), copy=True, indicator=False,
     validate=None)

How do I go from long to wide in this dataframe in pandas?

2 Answers2