python 3.x - Create Table from Year "Start" to the "End"

Question

I have a list. Mostly obituary related.

Leonard Wilson 1867 - 1936
Mark Jonson 1892 - 1961
Alex Jean Kinshaw 1951 - 1993
Elizabeth Mae Martin 1934 - 1998

The data needs to be analysed for a study and it needs to be arranged in 'csv' format, with a timeline(delimited by "," and using "-" for null values) starting from 1850 upto 2015.

Leonard Wilson,-,-,-,-,-,-,-,-,-,-,-,-,-,-,1867,1868,1869......1934,1935,1936,-,-,-,-,-,-,-,-,-,-,-,-
Mark Jonson,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,1892,1893,1894,1895,1896,1897......,1958,1959,1960,1961,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-
....


# All years in the middle needs to be populated please

In the above data you can see that the years before the person was born is marked by '-' and also the years after death(till 2015) with the same. All the years in between, needs to be populated.

The python/pandas code needs to detect the year of start and end, and

1. fill the before null values
2. in-middle years and
3. ending null values

Is there anyway this can be achieved, as I have a data of over 30k rows?

Answer 1

Yep, you can do it like this:

df = pd.read_clipboard(header=None, sep='ss+')

df_a = df[0].str.rsplit(n=3, expand=True)

df_a = df_a.set_index(0)

full_range = pd.date_range('12/31/1850', '12/31/2015', freq='AS') 
df_a['range'] = [','.join(pd.date_range(i, j, freq='AS')
                            .to_series()
                            .dt.strftime('%Y')
                            .reindex(full_range, fill_value='-')) for i, j in zip(df_a[1], df_a[3])]

df_a.to_csv('test.csv')

Output: