Creating new column using column header and row value

Question

I have data that is indexed by date, with time as separate columns for each row. Not ideal for comparison. RecordDate is an object while the time columns are float64.

My initial idea was to convert to datetime and look to merge, however, I understand this wouldn't work due to the time being a column header.

Here is an example of my data set:

  RecordDate    T0000   T0030   T0100   T0130   T0200   T0230   T0300
    15/04/2015  0        0       0       0       0       0       0
    16/04/2015  0        0       0       0       0       0       0
    17/04/2015  0        0       0       0       0       0       0
    18/04/2015  0        0       0       0       0       0       0

Here is an example of my ideal outcome:

RecordDate           Value
15/04/2015 00:00       0
15/04/2015 00:30       0
15/04/2015 01:00       0

Any thoughts much appreciated!

Michał Stolarczyk · Accepted Answer · 2018-10-18 15:15:50Z

2

You can use pd.DataFrame.melt method. For example for following code

df = pd.DataFrame({
    "RecordDate": ["15/04/2015", "16/04/2015"],
    "T0000": [1, 2],
    "T0030": [3, 4]
})
df.melt(id_vars=["RecordDate"], var_name="RecordHour")

gives output

   RecordDate RecordHour  value
0  15/04/2015      T0000      1
1  16/04/2015      T0000      2
2  15/04/2015      T0030      3
3  16/04/2015      T0030      4

Then columns RecordDate and RecordHour can be combined to one datetime column.

edited Oct 18, 2018 at 15:15

answered Oct 18, 2018 at 15:10

Michał Stolarczyk

4001 gold badge2 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Creating new column using column header and row value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related