2

I have a dataframe including all countries and datetime ranging from "1/22/20" to "2/22/20".

Here is my dataframe Column shown below.

Country 1/22/20 1/23/20 1/24/20 1/25/20 1/26/20 1/27/20 1/28/20 1/29/20 1/30/20...

I try to melt dataframe to get values in terms of datetime and country like 

US 1/25/20 28

but all values defined as NaN

Australia   2020-01-22  NaN

How can I fix it?

Here is my code snippet

def meltDataFrame(df,id_vars,value_vars,var_name,value_name):
    return pd.melt(df,
        id_vars= id_vars,
        value_vars = value_vars,
        var_name= var_name,
        value_name= value_name)

data_df_melt = meltDataFrame(data_df.reset_index(),
        ['Country'],pd.date_range('1/22/20', '3/18/20', freq='D'),'Date','Total_Count')
S.N
  • 2,157
  • 3
  • 29
  • 78

1 Answers1

2

Problem is columns names are not datetimes.

So convert all columns names without first to datetimes:

df.columns = df.columns[:1].tolist() + pd.to_datetime(df.columns[1:]).tolist()

And then melt.

Sample:

print (df)
     Country  1/22/20  1/23/20  1/24/20
0  Australia       11       42       53

df.columns = df.columns[:1].tolist() + pd.to_datetime(df.columns[1:]).tolist()
print (df)
     Country  2020-01-22 00:00:00  2020-01-23 00:00:00  2020-01-24 00:00:00
0  Australia                   11                   42                   53
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • When I draw a plot , datetime is appeared like 2020-01-22T00:00:00.000000000. How can I get only 2020-01-22 – S.N Mar 19 '20 at 10:18
  • @TonyBrand change my solution with `df.columns = df.columns[:1].tolist() + pd.to_datetime(df.columns[1:]).date.tolist()` and then use `data_df_melt = meltDataFrame(data_df.reset_index(), ['Country'],pd.date_range('1/22/20', '3/18/20', freq='D').date ,'Date','Total_Count')` – jezrael Mar 19 '20 at 10:19
  • @TonyBrand - for convert datetimes to dates in columns and in `meltDataFrame` function – jezrael Mar 19 '20 at 10:20
  • I use this code `data_df_melt = meltDataFrame(data_df.reset_index(), ['Country'],pd.date_range('1/22/20', '3/18/20', freq='D').date ,'Date','Total_Count')` but it doesn't work. I produces `NaN` – S.N Mar 19 '20 at 10:24
  • @TonyBrand - yes, because you forget for `df.columns = df.columns[:1].tolist() + pd.to_datetime(df.columns[1:]).date.tolist()` - there is already necessary create dates – jezrael Mar 19 '20 at 10:26
  • It appeared again like `2020-01-22T00:00:00.000000000` in seaborn after I did what you said – S.N Mar 19 '20 at 10:30
  • @TonyBrand hmmm, is possible check [this](https://stackoverflow.com/questions/17452179/not-write-out-all-dates-on-an-axis-matplotlib) ? – jezrael Mar 19 '20 at 10:32
  • 1
    I forgot to remove date format code. After removing it , the issue is disappeared. – S.N Mar 19 '20 at 10:35