Subtraction between rows with pandas

user_13769249 0
Viewed 266

I'm trying to parse a csv file and print certain timeseries graphs.

About csv file: The csv file contains a lot of data from which I need to handle confirmedInfections of it based on the id inside a for loop. The csv file looks like that:

ID,name,date,confirmedInfections
DE2,BAYERN,2020-02-24,19
DE2,BAYERN,2020-02-25,19
DE2,BAYERN,2020-02-26,21
DE1,BADEN-WÃœRTTEMBERG,2020-02-24,1
DE1,BADEN-WÃœRTTEMBERG,2020-02-25,3
DE1,BADEN-WÃœRTTEMBERG,2020-02-26,7

From that file, I need to parse every row and subtract the confirmedInfections in order to find the daily infections. At this moment if I choose the city with ID DE2 and date 2020-03-01 the infections are a summation from the beggining until the day I have chosen. So, I need to edit this file in order to subtract and find daily cases to print proper time series. For that purpose of subtraction I found out pandas.shift(1) method but it seems that it doesn't work.

My code:

def main(file):
    id_array = ['DE2', 'DE1']
    df = pd.read_csv(file, header='infer', parse_dates=['date'])
    for key in id_array:
        if (df.loc[df['ID'] == key]):
            df['confirmedInfections'] = df['confirmedInfections']-df['confirmedInfections'].shift(1)
    print(df)


main('data.txt')

Desired output:

#For DE2
ID,name,date,confirmedInfections
DE2,BAYERN,2020-02-24,19
DE2,BAYERN,2020-02-25,0
DE2,BAYERN,2020-02-26,2

#For DE1
ID,name,date,confirmedInfections
DE1,BADEN-WÃœRTTEMBERG,2020-02-24,1
DE1,BADEN-WÃœRTTEMBERG,2020-02-25,2
DE1,BADEN-WÃœRTTEMBERG,2020-02-26,4

Error that I get:

ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Any thoughts?

pandas data-analysis dataframe python
2 Answers

Considering df is read as:

df = pd.read_csv(file, header='infer', parse_dates=['date'])

Update "confirmedInfections" col as required diff:

df["confirmedInfections"] = df.groupby('ID')['confirmedInfections'].diff().fillna(df.confirmedInfections).astype(int)

Explanation Grouping the df by ID and updating rows 'confirmedInfections' column with difference in values between rows then updating the NaNs(first row) of each groupedID with original value. Since the calculations on 'confirmedInfections' resulted in float type hence typecasted the column to int. Finally splitting df into multiple dfs below.

Split df in multiple dfs

for id in df.ID.unique():
    print('DataFrame', id)
    print(df.groupby('ID').get_group(id))

Output

DataFrame DE2
    ID    name        date  confirmedInfections
0  DE2  BAYERN  2020-02-24                   19
1  DE2  BAYERN  2020-02-25                    0
2  DE2  BAYERN  2020-02-26                    2
DataFrame DE1
    ID               name        date  confirmedInfections
3  DE1  BADEN-WÃRTTEMBERG  2020-02-24                    1
4  DE1  BADEN-WÃRTTEMBERG  2020-02-25                    2
5  DE1  BADEN-WÃRTTEMBERG  2020-02-26                    4
user_21069340

You can use diff() for your problem instead of shift().For the other parts, this might not be the most optimal solution, but should get the work done.Since, you have only two IDs in your question and you want separate DataFrames for both of them, I would suggest creating the two DataFrames first:

df = pd.read_csv(file_name_with_path, header='infer', parse_dates=['date'])
#since there are only two IDs you can do it this way else there might be some commplex solutions 
df_DE1 = df.loc[df.ID == 'DE1']
df_DE2 = df.loc[df.ID == 'DE2'] 

def get_diff(df):
   first_val = df['confirmedInfections'][0] #Need to store the first row value since diff() method would otherwise render it as NaN
   df['confirmedInfections'] = df['confirmedInfections'].diff(1)
   df['confirmedInfections'][0] = first_val



df_list = [df_DE1, df_DE2]

for df in df_list:
    get_diff(df)
user_156044840
Related