Timechart count by a changing field name

[richnsanders_70]

I receive a new csv file every day in the following format:

color  1/22/20 1/23/20 1/24/20 1/25/20

yellow     1                2                0                 5

green        0              2                4                  3

purple     7             200             5                   3

Column 1 is the color, the column to the right starts a date column.  Every day there will be a new column that is dated from the previous day.  Today is Jun 29, so the csv I received this morning has columns from 1/1/2020 through 6/28/2020, all with a number indicating how many code yellows, greens, purples (and several other colors) occurred on that day.  

My search has started as:

index="coded-colors" sourcetype="csv" | stats sum(*) by color

Unfortunately, this chart isn't very good.  I get all my colors on the (line) chart, but instead of dates moving from left to right, there are on top of each other.  All the dates for yellow are stacked on top of each other, all the dates for red are stacked on top of each other, and the colors move from left to right.

I'd like to have a nice line chart that has 1/1/2020 on the far left and the current date on the far right. I would like the "elevation" of the chart to be determined by the number of events for that day/date and colors.  What has me stumped is how to use the column names as they continually change and are the actual date.

I have no control over the csv file I receive.  Arg.

Thank you in advance.

R

[to4kawa]

index="coded-colors" sourcetype="csv" 

| untable color _time count

| xyseries _time color count

try untable and xyseries

[richnsanders_70]

I need to research your solution a bit, but just with a quick attempt, I wasn't able to get the results I needed, but thank you very much for your reply.

[richgalloway · Accepted solution]

I was able to make this work by transposing the data so the date columns become rows (events).  Then they can be converted into timestamps and plotted with timechart.  See the example query.

| makeresults 
| eval _raw="color 1/22/20 1/23/20 1/24/20 1/25/20
yellow 1       2       0      5
green  0       2       4      3
purple 7       200     5      3" 
| multikv forceheader=1 
`comment("Above just defines test data")`
`comment("Get rid of unwanted fields. Should be left with color and dates")`
| fields - _* linecount 
| table color * 
`comment("Rotate the table")`
| transpose 0 header_field=color column_name=date 
`comment("Convert date field into a timestamp")`
| eval _time=strptime(date, "%m_%d_%y") 
`comment("Don't need the date field now")`
| fields - date 
| timechart span=1d sum(*) as *

[richnsanders_70]

First, thank you for the inline comments, they were very helpful and educational.  Your solution worked.  Thanks for your time and help!