1

If I have a abnormal column 2019/2/1 in the following dataframe:

date,type,ratio,2019/2/1
2019/1/1,food,0.4,0.3
2019/1/1,vegetables,0.2,0.6
2019/1/1,toy,0.1,0.5

How could I vertically append 2019/2/1 to ratio?

The expected result will like this:

       date        type  ratio
0  2019/1/1        food    0.4
1  2019/1/1  vegetables    0.2
2  2019/1/1         toy    0.1
3  2019/2/1        food    0.3
4  2019/2/1  vegetables    0.6
5  2019/2/1         toy    0.5
ah bon
  • 9,293
  • 12
  • 65
  • 148
  • 1
    use melt? `df.melt(['date','type'],value_name='ratio').drop('variable',1)` – anky Mar 23 '20 at 04:11
  • 1
    @ahbon - do you need `(df.rename(columns={'ratio':'2019/1/1'}).drop('date', 1).melt(['type'],value_name='ratio', var_name='date'))` ? – jezrael Mar 23 '20 at 04:29
  • 1
    @ahbon - Or somethig like `df['date'] = pd.to_datetime(df['date']) df = df.melt(['date','type'],value_name='ratio') df['date'] = pd.to_datetime(df.pop('variable'), errors='coerce').fillna(df['date'])` – jezrael Mar 23 '20 at 04:30
  • @anky_91, thanks but your solution give all date is `2019/1/1`, in fact, the appended rows should be `2019/2/1`. – ah bon Mar 23 '20 at 05:12
  • @jezrael, both of your solution give the expected result, thanks. – ah bon Mar 23 '20 at 05:15

1 Answers1

2

First idea is rename column ratio before melt:

df1 = (df.rename(columns={'ratio':'2019/1/1'})
         .drop('date', 1)
         .melt('type',value_name='ratio', var_name='date'))

print (df1)
         type      date  ratio
0        food  2019/1/1    0.4
1  vegetables  2019/1/1    0.2
2         toy  2019/1/1    0.1
3        food  2019/2/1    0.3
4  vegetables  2019/2/1    0.6
5         toy  2019/2/1    0.5

Another is replace datetimes from columns to date column after melt:

df['date'] = pd.to_datetime(df['date']) 
df2 = df.melt(['date','type'],value_name='ratio') 
df2['date'] = pd.to_datetime(df2.pop('variable'), errors='coerce').fillna(df2['date'])
print (df2)
        date        type  ratio
0 2019-01-01        food    0.4
1 2019-01-01  vegetables    0.2
2 2019-01-01         toy    0.1
3 2019-02-01        food    0.3
4 2019-02-01  vegetables    0.6
5 2019-02-01         toy    0.5
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252