Mismatched columns: Add new row and replace with N/A if the date doesn't exist for that data (python)

user_17081051 0
Viewed 79

I have a very large dataset with information in different columns in the format below.

    DATE        Data    DATE.2      Data2   DATE.3      Data3   DATE.4      Data4 Data5
0   2018-01-01  2.4054  2018-01-02  9.77    2018-01-02  2695.81 2018-01-01  98  358
1   2018-01-02  2.4633  2018-01-03  9.15    2018-01-03  2713.06 2018-01-02  98  355
2   2018-01-03  2.4471  2018-01-04  9.22    2018-01-04  2723.99 2018-01-03  99  348
3   2018-01-04  2.4525  2018-01-05  9.22    2018-01-05  2743.15 2018-01-04  98  340
4   2018-01-05  2.4763  2018-01-08  9.52    2018-01-08  2747.71 2018-01-05  98  336
5   2018-01-08  2.4800  2018-01-09  10.08   2018-01-09  2751.29 2018-01-08  97  335
6   2018-01-09  2.5530  2018-01-10  9.82    2018-01-10  2748.23 2018-01-09  96  333

I'm going through a cleaning process, and I need there to be only one date column instead of 4. As you can see from the data, the dates do not match up on each row, therefore I need to work out a way of getting the code to create a new row with N/A in it if there is no data in the relevant date column for that day.

For example I need the code to write:

    DATE        Data     Data2    Data3  Data4 Data5
0   2018-01-01  2.4054   N/A      N/A     98   358
1   2018-01-02  2.4633   9.77     2695.81 98   355

Does anyone know how to achieve this? Thanks in advance for any advice/pointers.

jupyter dataframe python
2 Answers

There are many ways to achieve this.

You can try creating a new dataframe using your dataset.

  1. Create a new dataframe with column date and insert all dates (date.2, date.3 etc.) in your column from old df (dataframe).
  2. Remove duplicates in this column (if exists)
  3. Next create the Data, Data2, Data3, Data4 columns with default value N/A.
  4. Pick Data, Data2, Data3, Data4 values where (data == new_df.date OR data.2 == new_df.date .....)

The functions for these steps are available in pandas.

user_116723520

Managed to get it sorted in the end:

df_1 = af[['DATE', 'Data']]
df_2 = af[['DATE.2', 'Data2']].rename(columns = {'DATE.2': 'DATE'})
df_3 = af[['DATE.3', 'Data3']].rename(columns = {'DATE.3': 'DATE'})
df_4 = af[['DATE.4', 'Data4', 'Data5']].rename(columns = {'DATE.4': 'DATE'})

new = df_1.merge(df_2, on = 'DATE', how = 'outer').merge(df_3, on = 'DATE', how = 'outer').merge(df_4, on = 'DATE', how = 'outer')
new['Data'].fillna("N/A", inplace = True)
new['Data2'].fillna("N/A", inplace = True)
new['Data3'].fillna("N/A", inplace = True)
new['Data4'].fillna("N/A", inplace = True)
new['Data5'].fillna("N/A", inplace = True)
new
user_170810510
Related