How to convert transform a dataframe in python

Question

I have a dataframe with the following values

Date,     value
2017/1/1, 5
2017/4/1, 6
2017/4/5, 12
2017/7/2, 15
2018/4/1, 50
2018/7/7, 11
2017/1/1, 5

I would like to convert this dataframe to the following format where the columns are the year/month ad the values are in the rows

2017/1   2017/4  2017/7  2018/1  2018/4  2018/7
 5         6     15      5       50      11
           12

How can I do that?

Could you please cross check the output you've provided with respect to your input? — cs95
– cs95, Commented Aug 26, 2018 at 21:46
I'm assuming a typo on the last date... 2018/1/1 given the output. — AChampion
– AChampion, Commented Aug 26, 2018 at 22:06

cs95 · Accepted Answer · 2018-08-26 21:45:49Z

1

You can groupby and reshape to get your result:

v = (df.set_index('Date')['value']
       .groupby(by=lambda x: x.rsplit('/', 1)[0], axis=0)
       .apply(list))
pd.DataFrame(v.values.tolist(), index=v.index).T

   2017/1  2017/4  2017/7  2018/4  2018/7
0     5.0     6.0    15.0    50.0    11.0
1     5.0    12.0     NaN     NaN     NaN

answered Aug 26, 2018 at 21:45

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kevh Over a year ago

maybe since we know these are dates, we can use something like this to handle the month: df['month'] = pd.to_datetime(df['dates']).map(lambda x: x.strftime('%Y-%m'))

Alexander · Accepted Answer · 2018-08-26 22:21:29Z

Sample DataFrame

df = pd.DataFrame({
    'Date': pd.DatetimeIndex(
        ['2017-01-01', '2017-04-01', '2017-04-05',
         '2017-07-02', '2018-04-01', '2018-07-07',
         '2017-01-01'], freq=None),
     'value': [5, 6, 12, 15, 50, 11, 5]})

Solution

Create a dictionary holding the year-month as keys (string format) and values as lists of relevant values.

d = (df
 .assign(year_month=df['Date'].dt.strftime('%Y/%m'))
 .groupby('year_month')['value']
 .apply(list)
 .to_dict()
)

df = pd.concat([pd.Series(v, name=k) 
                for k, v in d.iteritems()], axis=1)[sorted(d)]

>>> df
   2017/01  2017/04  2017/07  2018/04  2018/07
0        5        6       15       50       11
1        5       12      NaN      NaN      NaN

Collectives™ on Stack Overflow

How to convert transform a dataframe in python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related